Use of Postgres table inheritance

Question

Since Postgres also supports partitioned tables, what is the use of child table.

Suppose there is a table of users which has a column created_date. We can store data in 2 ways:

We create many child tables of this user table and distribute the data of users on the basis of created_date (say, one table for every date, like user_jan01_21).
We can create a partitioned table with the partitioning key created_date

Then what is the difference between these solution? Basically, I want to know what problem table inheritance can solve that partitioning cannot.

Another doubt I have: if I follow solution 1, and I query the user table without the ONLY keyword, will it scan all the child tables?

For example:

SELECT * FROM WHERE where created_date = current_date - 10;

The old inheritance based partitioning is deprecated. If you really need partitioning, use the new declarative partitioning. — user330315
– user330315, Commented Feb 9, 2021 at 6:48
Use EXPLAIN(ANALYZE, BUFFERS) to see how the query planner works. No reason to keep your doubt, when you can actually see how it works. — Frank Heikens
– Frank Heikens, Commented Feb 9, 2021 at 12:32

Laurenz Albe · Accepted Answer · 2021-02-09 08:01:49Z

4

If the objective is partitioning, as in your example, then there is no advantage in using table inheritance. Declarative partitioning is far superior in ease of use, performance and available features.

Table inheritance has uses that are unrelated to partitioning. Features that partitioning doesn't offer are:

the child table can have additional columns
a table can inherit from more than one table

With table inheritance, if you select from the parent table, you will also get all results from the child tables, just as if you had used UNION ALL to combine the results.

answered Feb 9, 2021 at 8:01

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bergi Over a year ago

…and with table inheritance, foreign key references to the parent table don't work as one might expect

Collectives™ on Stack Overflow

Use of Postgres table inheritance

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related