何时在 PostgreSQL 中使用继承表？

When to use inherited tables in PostgreSQL?

提问by raspi

In which situations you should use inherited tables? I tried to use them very briefly and inheritance didn't seem like in OOP world.

在什么情况下应该使用继承表？我尝试非常简短地使用它们，并且在 OOP 世界中似乎没有继承。

I thought it worked like this:

我认为它是这样工作的：

Table userswhich has all fields required for all user levels. Tables like moderators, admins, bloggers, etc but fields are notchecked from parent. For example usershas email field and inherited bloggershas it now too but it's not unique for both usersand bloggersat same time. ie. same as I add email field to both tables.

users包含所有用户级别所需的所有字段的表。、、 等表moderators，但不从父级检查字段。例如具有电子邮件字段和继承现在已经太，但它不是唯一的两个，并在同一时间。IE。与我向两个表添加电子邮件字段相同。adminsbloggersusersbloggersusersbloggers

Only usage I could think of is fields that are usually used, like row_is_deleted, created_at, modified_at. Is this the only usage for inherited tables?

我能想到的唯一用法是通常使用的字段，例如row_is_deleted、created_at、modified_at。这是继承表的唯一用法吗？

回答by S38

There are some major reasons for using table inheritance in postgres.

在 postgres 中使用表继承有一些主要原因。

Lets say, we have some tables needed for statistics, which are created and filled each month:

假设我们有一些统计所需的表，每个月都会创建和填充这些表：

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

In this sample, we have 2.000.000 rows in each table. Each table has a CHECK constraint to make sure only data for the matching month gets stored in it.

在此示例中，每个表中有 2.000.000 行。每个表都有一个 CHECK 约束，以确保只有匹配月份的数据存储在其中。

So what makes the inheritance a cool feature - why is it cool to split the data?

那么是什么让继承成为一个很酷的特性——为什么拆分数据很酷？

• PERFORMANCE: When selecting data, we SELECT * FROM statistics WHERE date BETWEEN x and Y, and Postgres only uses the tables, where it makes sense. Eg. SELECT * FROM statistics WHERE date BETWEEN '2010-04-01' AND '2010-04-15' only scans the table statistics_2010_04, all other tables won't get touched - fast!
• Index size: We have no big fat table with a big fat index on column date. We have small tables per month, with small indexes - faster reads.
• Maintenance: We can run vacuum full, reindex, cluster on each month table without locking all other data

• 性能：选择数据时，我们 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，所有其他表都不会被触及 - 快！
• 索引大小：我们没有大的胖表，列日期有大的胖索引。我们每个月都有小表，索引小——读取速度更快。
• 维护：我们可以在每个月的表上运行vacuum full、reindex、cluster，而无需锁定所有其他数据

For the correct use of table inheritance as performance booster, look at the postgresql manual. You need to set CHECK constraints on each table to tell the database, on which key your data gets splitted (partitioned).

要正确使用表继承作为性能助推器，请查看 postgresql 手册。您需要在每个表上设置 CHECK 约束以告诉数据库您的数据在哪个键上被拆分（分区）。

I make heavy use of table inheritance, especially when it comes to store log data grouped by month. Hint: If you store data, which will never change (log data), create or indexes with CREATE INDEX ON () WITH(fillfactor=100); This means no space for updates will be reserved in the index - index is smaller on disk.

我大量使用表继承，尤其是在存储按月分组的日志数据时。提示：如果您存储永远不会更改的数据（日志数据），请使用 CREATE INDEX ON () WITH(fillfactor=100); 创建或索引。这意味着不会在索引中保留更新空间 - 磁盘上的索引较小。

UPDATE: fillfactor default is 100, from http://www.postgresql.org/docs/9.1/static/sql-createtable.html:

更新：填充因子默认值为 100，来自http://www.postgresql.org/docs/9.1/static/sql-createtable.html：

The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default

表的填充因子是 10 到 100 之间的百分比。100（完全填充）是默认值

回答by zxq9

"Table inheritance" meanssomething different than "class inheritance" and they serve different purposes.

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

Postgres is all about data definitions. Sometimes really complex data definitions. OOP (in the common Java-colored sense of things) is about subordinating behaviors to data definitions in a single atomic structure. The purpose and meaning of the word "inheritance" is significantly different here.

Postgres 是关于数据定义的。有时非常复杂的数据定义。OOP（在常见的 Java 颜色意义上）是关于将行为从属于单个原子结构中的数据定义。“继承”这个词的目的和含义在这里有很大的不同。

In OOP land I might define (being very loose with syntax and semantics here):

在 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)

The tables for this might look like:

用于此的表可能如下所示：

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);

But where are the behaviors? They don't fit anywhere. This is not the purpose of "objects" as they are discussed in the database world, because databases are concerned with data, not procedural code. You could write functions in the database to do calculations for you (often a very good idea, but not really something that fits this case) but functions are not the same thing as methods -- methods as understood in the form of OOP you are talking about are deliberately less flexible.

但行为在哪里？它们不适合任何地方。这不是数据库世界中讨论的“对象”的目的，因为数据库与数据有关，而不是过程代码。您可以在数据库中编写函数来为您进行计算（通常是一个很好的主意，但并不是真正适合这种情况的东西）但是函数与方法不同 - 以您正在谈论的 OOP 形式理解的方法大约是故意不那么灵活。

There is one more thing to point out about inheritance as a schematic device: As of Postgres 9.2 there is no way to reference a foreign key constraint across all of the partitions/table family members at once. You can write checks to do this or get around it another way, but its not a built-in feature (it comes down to issues with complex indexing, really, and nobody has written the bits necessary to make that automatic). Instead of using table inheritance for this purpose, often a better match in the database for object inheritance is to make schematic extensions to tables. Something like this:

关于继承作为示意性设备还有一件事要指出：从 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);

Now we have a canonical reference for the instance of the animal that we can reliably use as a foreign key reference, and we have an "ilk" column that references a table of xxx_ilk definitions which points to the "next" table of extended data (or indicates there is none if the ilk is the generic type itself). Writing table functions, views, etc. against this sort of schema is so easy that most ORM frameworks do exactly this sort of thing in the background when you resort to OOP-style class inheritance to create families of object types.

现在我们有一个动物实例的规范引用，我们可以可靠地将其用作外键引用，并且我们有一个“ilk”列，它引用了一个 xxx_ilk 定义表，该表指向扩展数据的“下一个”表（或者如果同类是泛型类型本身，则表示没有）。针对这种模式编写表函数、视图等非常容易，以至于当您求助于 OOP 风格的类继承来创建对象类型系列时，大多数 ORM 框架都会在后台执行此类操作。

回答by grégtheitroade hubert

Inheritance can be used in an OOP paradigm as long as you do not need to create foreign keys on the parent table. By example, if you have an abstract class vehicle stored in a vehicle table and a table car that inherits from it, all cars will be visible in the vehicle table but a foreign key from a driver table on the vehicle table won't match theses records.

只要您不需要在父表上创建外键，就可以在 OOP 范式中使用继承。例如，如果您有一个抽象类车辆存储在车辆表中，并且有一个从它继承的表 car，则所有汽车都将在车辆表中可见，但车辆表上驱动程序表中的外键将不匹配这些记录。

Inheritance can be also used as a partitionningtool. This is especially usefull when you have tables meant to be growing forever (log tables etc).

继承也可以用作分区工具。当您有要永远增长的表（日志表等）时，这尤其有用。

回答by Pavel V.

Main use of inheritance is for partitioning, but sometimes it's useful in other situations. In my database there are many tables differing only in a foreign key. My "abstract class" table "image" contains an "ID" (primary key for it must be in every table) and PostGIS 2.0 raster. Inherited tables such as "site_map" or "artifact_drawing" have a foreign key column ("site_name" text column for "site_map", "artifact_id" integer column for the "artifact_drawing" table etc.) and primary and foreign key constraints; the rest is inherited from the the "image" table. I suspect I might have to add a "description" column to all the image tables in the future, so this might save me quite a lot of work without making real issues (well, the database might run little slower).

继承的主要用途是用于分区，但有时它在其他情况下也很有用。在我的数据库中，有许多表仅在外键上有所不同。我的“抽象类”表“图像”包含一个“ID”（它的主键必须在每个表中）和 PostGIS 2.0 栅格。“site_map”或“artifact_drawing”等继承表具有外键列（“site_map”的“site_name”文本列，“artifact_drawing”表的“artifact_id”整数列等）以及主外键约束；其余部分是从“图像”表继承的。我怀疑我将来可能必须在所有图像表中添加一个“描述”列，因此这可能会为我节省大量工作而不会产生实际问题（嗯，

EDIT: another good use: with two-table handling of unregistered users, other RDBMSs have problems with handling the two tables, but in PostgreSQL it is easy - just add ONLYwhen you are not interrested in data in the inherited "unregistered user" table.

编辑：另一个很好的用途：对于未注册用户的双表处理，其他 RDBMS 在处理这两个表时有问题，但在 PostgreSQL 中很容易 - 只需ONLY在继承的“未注册用户”表中的数据不感兴趣时添加。

回答by Frank Heikens

The only experience I have with inherited tables, is in partioning. It works fine but it's not the most sophisticated and easy to use part of PostgreSQL.

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

Last week we were looking the same OOP issue, but we had too many problems with Hibernate (didn't like our setup), so we didn't use inheritance in PostgreSQL.

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

回答by Maarten

I use inheritance when I have more than 1 on 1 relationships between tables.

当表之间有超过 1 对 1 的关系时，我使用继承。

Example: suppose you want to store object map locations with attributes x, y, rotation, scale.

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

Now suppose you have several different kinds of objects to display on the map and each object has its own map location parameters, and map parameters are never reused.

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

In these cases table inheritance would be quite useful to avoid having to maintain unnormalised tables or having to create location id's and cross referencing it to other tables.

在这些情况下，表继承对于避免必须维护非规范化表或必须创建位置 ID 并将其交叉引用到其他表非常有用。

回答by Leandro

Use it as little as possible. And that usually means never, it boiling down to a way of creating structures that violate the relational model, for instance by breaking the information principle and by creating bags instead of relations.

尽量少用。这通常意味着永远不会，它归结为一种创建违反关系模型的结构的方式，例如通过打破信息原则并创建袋子而不是关系。

Instead, use table partitioning combined with proper relational modelling, including further normal forms.

相反，将表分区与适当的关系建模结合使用，包括进一步的范式。