何时在PostgreSQL中使用继承表？

Question

在什么情况下应该使用继承表？我尝试简要使用它们，但继承似乎不像OOP世界那样。我认为它是这样工作的：表“用户”具有所有用户级别所需的所有字段。表如“moderators”，“admins”，“bloggers”等，但字段不从父级检查。例如，用户具有电子邮件字段，并且继承的博客作者现在也有它，但它不会同时对用户和博客作者进行唯一验证，就像我将电子邮件字段添加到两个表中一样。我唯一能想到的用法是通常使用的字段，例如 row_is_deleted ， created_at ， modified_at 。这是继承表的唯一用途吗？

- raspi

Answer 1

在PostgreSQL中使用表继承有一些重要的原因。

假设我们需要用于统计的一些表，每个月都会创建并填充数据：

statistics
    - statistics_2010_04 (inherits statistics)
    - statistics_2010_05 (inherits statistics)

在这个示例中，每个表格都有 200 万行数据。每个表格都有 CHECK 约束条件，以确保只存储匹配月份的数据。

那么什么使得继承成为一个很酷的特性——为什么将数据分割开很酷？

• 性能：选择数据时，我们使用 SELECT * FROM statistics WHERE date BETWEEN x and Y，Postgres 只使用有意义的表格。例如，SELECT * FROM statistics WHERE date BETWEEN '2010-04-01' AND '2010-04-15' 只会扫描表格 statistics_2010_04，其他表格不会被触及——速度快！
• 索引大小：我们没有一个大而臃肿的表格，带有基于列 date 的大型索引。我们有每月小表格，带有小索引——读取更快。
• 维护：我们可以在每个月的表格上运行 vacuum full、reindex 和 cluster，而无需锁定所有其他数据。

要正确使用表格继承作为性能提升器，请查看 PostgreSQL 手册。您需要在每个表格上设置 CHECK 约束条件来告诉数据库，在哪个键上拆分（分区）数据。

我经常使用表格继承，尤其是当需要按月存储日志数据时。提示：如果存储永远不会更改的数据（日志数据），请使用 CREATE INDEX ON () WITH(fillfactor=100) 创建或索引。这意味着索引中不会为更新保留空间，因此索引在磁盘上更小。

更新： fillfactor 的默认值是 100，来源于 http://www.postgresql.org/docs/9.1/static/sql-createtable.html：

fillfactor 是一个在10到100之间的百分比，用于表格。100（完全填充）是默认值。

- S38

---

另一个分区的例子 - Frank Heikens

在第一条中，Postgres如何知道需要在哪个表中搜索？您从父表中进行选择，日期范围仅是拆分的一个方便示例。父表无法了解此逻辑。或者我错了吗？ 在第一步中，Postgres通过查询语句中指定的表名来确定需要搜索哪个表。您从父表中进行选择只是表示您希望从该表中检索数据，并不会影响Postgres确定查询要搜索的特定表。日期范围仅是一个示例，用于说明如何从父表中获取子表数据。父表本身并不了解这种逻辑。 - Alexander Palamarchuk

在父表上执行查询与在所有子表之间进行UNION ALL并在共同行上执行查询的效果基本相同。查询规划器知道定义每个分区的检查约束条件，并且只要它们不重叠分区，就使用它们来确定可以跳过检查表，CHECK指示不会返回任何行的表。 关于此的Postgres文档 - zxq9

@avesus 呵呵... 单独看上面的代码确实值得这样的讽刺。通常将这种东西封装成某种维护例程是很典型的做法。这可以简单地作为一个存储过程，在某些条件下处理它，或者是一个 cron 作业等等。按日期进行分区很常见，但我有时也会按表空间分配进行分区，这需要一些外部信息 -- 花费 30 分钟编写分区 babysitter 是非常值得的，因为它给你带来了控制。 - zxq9

嗯，你确定它不会阻塞吗？我有一个类似的设置，但是当我在单个分区上运行CLUSTER命令时，对另一个分区持有的数据运行SELECT语句会被阻塞！ - E. van Putten

Answer 2

"表继承"与"类继承"的含义不同，它们有不同的用途。

Postgres非常关注数据定义。有时是非常复杂的数据定义。OOP（以普及的Java背景为例）是关于将行为从单个原子结构中屈服于数据定义。这里“继承”的目的和意义显著不同。

在OOP领域，我可能会这样定义（在此处语法和语义上非常宽泛）：

import life

class Animal(life.Autonomous):
  metabolism = biofunc(alive=True)

  def die(self):
    self.metabolism = False

class Mammal(Animal):
  hair_color = color(foo=bar)

  def gray(self, mate):
    self.hair_color = age_effect('hair', self.age)

class Human(Mammal):
  alcoholic = vice_boolean(baz=balls)

这可能看起来像是表格：

CREATE TABLE animal
  (name       varchar(20) PRIMARY KEY,
   metabolism boolean NOT NULL);

CREATE TABLE mammal
  (hair_color  varchar(20) REFERENCES hair_color(code) NOT NULL,
   PRIMARY KEY (name))
  INHERITS (animal);

CREATE TABLE human
  (alcoholic  boolean NOT NULL,
   FOREIGN KEY (hair_color) REFERENCES hair_color(code),
   PRIMARY KEY (name))
  INHERITS (mammal);

但是行为呢？它们没有合适的位置。这不是数据库世界中所讨论的“对象”的目的，因为数据库关注的是数据，而不是过程性代码。您可以在数据库中编写函数来执行计算（通常是一个非常好的主意，但不是适合这种情况的东西），但函数并不等同于方法——在你谈论的面向对象编程中，方法是故意设计得不太灵活。

还有一件事需要指出，作为一个示意工具，继承有一个特点：截至Postgres 9.2，没有办法跨所有分区/表族成员引用外键约束。您可以编写检查来解决此问题或另寻他路，但这不是一个内置功能（实际上涉及到复杂索引问题，没有人编写必要的部分以使其自动化）。在数据库中，与表的对象继承匹配度更高的是将原理扩展到表中。就像这样：

CREATE TABLE animal
  (name       varchar(20) PRIMARY KEY,
   ilk        varchar(20) REFERENCES animal_ilk NOT NULL,
   metabolism boolean NOT NULL);

CREATE TABLE mammal
  (animal      varchar(20) REFERENCES animal PRIMARY KEY,
   ilk         varchar(20) REFERENCES mammal_ilk NOT NULL,
   hair_color  varchar(20) REFERENCES hair_color(code) NOT NULL);


CREATE TABLE human
  (mammal     varchar(20) REFERENCES mammal PRIMARY KEY,
   alcoholic  boolean NOT NULL);

现在我们有了一个规范的动物实例引用，可以可靠地用作外键引用，我们还有一个“ilk”列，引用了一个 xxx_ilk 定义表，该表指向扩展数据的“下一个”表（如果 ilk 是通用类型本身，则指示没有）。针对这种模式编写表函数、视图等非常容易，大多数 ORM 框架在您采用面向对象编程(OOP)类继承来创建对象类型族时，都会在后台执行这种操作。

- zxq9

---

如果你要添加所有已知的哺乳动物，你会继承自哺乳动物还是像这里一样有一个外键？我对外键的问题在于最终你不得不做很多连接。 - puk

@puk 首先，您需要决定为什么要添加每种已知的哺乳动物。数据的形状将由数据的使用方式确定（在这种情况下，可能没有必要为每个动物创建一个表 - 考虑用于游戏图鉴的数据库，其中确实有每种类型的怪物）。在上面的情况下，我通常会添加一个视图，即“哺乳动物JOIN人类”的最常见情况，因为每次编写连接都很麻烦。但是不要避免连接。连接是将R放入RDBMS的原因。如果您不喜欢连接，则应使用其他类型的数据库。 - zxq9

@zxq9：我猜测，由于大表导致的大量低效连接是物化视图发挥作用的地方？（我使用Postgres还不久） - Mark K Cowan

加入操作本身并不低效。低效的是在非索引、非唯一字段上尝试加入操作（因为模式远未归一化）由于设计不严谨。在这种情况下，一个物化视图可以提供帮助。物化视图也有助于在需要将规范数据作为模式基础（通常为真实情况）时，但同时需要多个工作的去规范化表示以便于处理效率（前置计算），或认知效率。如果您写入的次数多于读取的次数，则会导致性能降低。 - zxq9

@zxq9：抱歉，我应该在那里使用“慢”这个词，而不是“低效”。 - Mark K Cowan

@MarkKCowan “慢”是一个相对的术语。在大型企业系统和游戏服务器中，我们可以接受约50毫秒来返回查询结果，在我的经验中，20个表连接从未成为问题（至少在Postgres 8+中）。但是，在管理层希望在未索引数据（或派生值！）跨越5个以上的表格进行>10b行连接时要求<1ms响应的情况下……除了在上个月完成此连接并将其存储在快速K/V存储中（这本质上就是材料化视图在特殊情况下的作用），世界上没有任何系统会感觉“快”。无论是写入时间还是读取时间都无法避免权衡。 - zxq9

Answer 3

继承可以在面向对象编程中使用，只要您不需要在父表上创建外键。例如，如果您有一个存储在车辆表中的抽象类车辆和一个从它继承的车辆表，所有汽车都将在车辆表中可见，但来自驱动程序表的车辆表上的外键将无法匹配这些记录。

继承还可以用作分区工具。当您有意让表不断增长（如日志表等）时，这尤其有用。

- grégoire hubert

---

表约束不会被继承，因此它不仅仅是外键。您可以在创建子表时将表约束应用于子表，或编写触发器以实现相同的约束。 - Wexxor

Answer 4

继承的主要用途是进行分区，但有时在其他情况下也很有用。在我的数据库中，有许多仅在外键上有所不同的表。我的“抽象类”表“image”包含一个“ID”（必须在每个表中作为主键），以及PostGIS 2.0光栅图像。继承的表，例如“site_map”或“artifact_drawing”，具有外键列（“site_map”的文本列“site_name”，“artifact_drawing”表的整数列“artifact_id”等）和主键和外键约束；其余内容从“image”表继承。我怀疑未来可能需要向所有图像表添加“description”列，因此这可能可以节省大量工作而不会产生实际问题（好吧，数据库可能会运行得稍微慢一些）。

编辑：另一个很好的用途：使用未注册用户的两个表处理，其他RDBMS无法处理这两个表，但在PostgreSQL中很容易 - 只需在不关心继承的“未注册用户”表中的数据时添加ONLY即可。

- Pavel V.

Answer 5

我对继承表的唯一经验是在分区方面。它可以正常工作，但它不是PostgreSQL中最复杂和易于使用的部分。

上周我们遇到了同样的OOP问题，但我们在Hibernate方面遇到了太多问题 - 我们不喜欢我们的设置，所以我们没有在PostgreSQL中使用继承。

- Frank Heikens

Answer 6

当我需要在表之间有多对一的关系时，我使用继承。

例如：假设您想要存储带有属性x、y、旋转和比例的对象地图位置。

现在假设您有几种不同类型的对象要显示在地图上，并且每个对象都有自己的地图位置参数，并且地图参数永远不会被重用。

在这些情况下，表继承将非常有用，以避免维护不规范的表或创建位置id并将其交叉引用到其他表中。

- Maarten

Answer 7

我尝试了一些操作，不会指出数据库继承是否有任何实际用例，但会为您提供一些详细信息以做出决策。以下是PostgresQL的示例：https://www.postgresql.org/docs/15/tutorial-inheritance.html 您可以尝试下面的SQL脚本。

CREATE TABLE IF NOT EXISTS cities (
  name       text,
  population real,
  elevation  int     -- (in ft)
);

CREATE TABLE IF NOT EXISTS capitals (
  state      char(2) UNIQUE NOT NULL
) INHERITS (cities);

ALTER TABLE cities
ADD test_id varchar(255); -- Both table would contains test col
DROP TABLE cities; -- Cannot drop because capitals depends on it

ALTER TABLE cities
ADD CONSTRAINT fk_test FOREIGN KEY (test_id) REFERENCES sometable (id);

根据我的评论，让我总结一下：

• 当你 添加/删除/更新 字段时，继承表也会受到影响。
• 不能删除父表。
• 外键不会被继承。

从我的角度来看，在不断发展的应用程序中，我们很难预测未来的变化，对于我来说，我会避免将其应用于早期数据库开发。

当功能稳定并且我们想要创建与现有模型非常相似的数据库模型时，我们可以考虑使用该用例。

- Justin Dang

Answer 8

尽可能少地使用它。通常意义上，这意味着永远不要使用，因为它会导致创建违反关系模型的结构方式，例如通过打破信息原则并创建包而不是关系。

相反，使用表划分与适当的关系建模，包括进一步的规范化形式。

- Leandro

---

PostgreSQL的继承特性不违反关系模型，也不违反信息原则，即所有关系数据库中的数据都由关系中的数据值表示，所有查询结果也再次表示为关系。(https://en.wikipedia.org/wiki/Relational_model) 这一点始终如一，因为继承另一个表的所有表都是简单表。因此，也不存在所谓的“包（bag）”。 - Roland

嗯，维基百科在关系模型方面几乎不是一个可靠的参考；它拒绝承认SQL违反了关系模型。一个袋子就是一张没有键的表，因为它可能有重复项，所以不是一个关系；一个关系必须是一个集合。 - Leandro

这不是特性本身的问题，而是它的使用方式。如果您使用uuid作为标识符，您将在所有子表上拥有唯一的键。 - Roland

你说得有道理，但这里的问题是继承会导致建模者忽略关系模型。UUID不是真正的键，而是代理键。仍然需要声明自然键。 - Leandro

实际上，继承目前过于受限，无法以任何有用的方式使用。无法跨越整个层次结构进行索引是不可接受的。 - alecov