When to use inherited tables in PostgreSQL?

Question

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

I thinked they worked like this:

Table USERS which has all fields required for all user levels. Tables like MODERATORS, ADMINS, BLOGGERS, etc but fields are not checked from parent. For example USERS has email field and inherited BLOGGERS has it now too but it's not unique for both USERS and BLOGGERS at same time. ie. same as I add email field to both tables.

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?

Answer 1

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.

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.

Answer 2

There are some major reasons for using table inheritance in 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.

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

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

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.

Answer 3

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.

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