Examining PostgreSQL 9.4 – A first look

When we start to have slightly larger databases (multi terabyte) then delayed replicas become a very useful backup and recovery strategy. Typically we need to recover databases when there has either been human error (someone deletes a bunch of data they should not have) or a compromise. In these situations rather than restoring for a nightly backup it is _always_ faster to roll a delayed replica forward and promote it to be master then resync all the normal replicas.

What it works for really well

Table inheritance works really, really well for enforcing consistent interfaces to repeatedly used pieces of information which are independent for referential integrity purposes. For example, we've all seen horrors involving global notes tables with umpteen join tables.... Inheritance provides a very clean solution to that problem: have an abstract notes table (which can double for query purposes as a global notes table) and worker tables which have foreign keys which attach specifically to other tables.

For example, in LedgerSMB we have a note table, an invoice_note table a eca_note table (notes for customer/vendor agreements), and more. The nice thing is, the tables all have the same structure and can be managed structure-wise as if they were a single table.

For example, an alter table statement on note can affect all sub-tables in many cases (other than unique constraints, primary or foreign keys, etc). If I want to add a virtual column for full text searching, I can do this with a single function as follows:

    CREATE OR REPLACE FUNCTION tsvector(note) 
    RETURNS tsvector
    language sql immutable as
    $$ SELECT to_tsvector($1.subject || ' ' || $1.note); $$;

What It Does Not Work For

So inheritance is often sold as a way of tracking part/whole relationships and other type/subtype problems. The problem is that these currently break down where you need referential integrity enforcement across an inheritance tree. Consequently, while I see inheritance as a really, really useful feature, it is a feature that is largely useless for the problems it was originally intended to solve. Now, it is getting better (9.2 added NOINHERIT constraints, which allow you to apply different check constraints to parent and child tables, useful if you want to forbid all inserts to the parent table), but the really big problems have to do with the inability to properly inherit unique indexes, and therefore not to referential integrity enforcement against a whole inheritance tree.

In general in these cases, you are better off with a single table, and designing a structure without table inheritance to model the information.