PostgreSQL Table Inheritance

Tip: Basic Inheritance

CREATE TABLE cities (name text, population float, elevation int);
CREATE TABLE capitals (state text) INHERITS (cities);

capitals has all columns from cities plus state.

Gotcha: Queries Include Child Tables

SELECT * FROM cities;

Returns rows from cities AND capitals. Use ONLY cities to exclude children.

Tip: Separate Indexes per Table

Each table in the inheritance hierarchy needs its own indexes. Parent indexes don't apply to children.

Gotcha: Foreign Keys Don't Work

You can't create a foreign key reference to an inherited table. Use triggers for referential integrity.

Tip: ONLY Keyword

SELECT * FROM ONLY cities;

Returns only rows from the parent table, excluding inherited children.

Gotcha: ALTER TABLE Affects Children

Adding a column to the parent automatically adds it to all child tables.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

PostgreSQL table inheritance is a feature I've used sparingly after inheriting a database where it was abused. Inheritance splits the parent table across multiple child tables, and queries on the parent include all children by default. Indexes created on the parent do NOT apply to children automatically. If you use inheritance, create indexes on each child table explicitly. For most application designs, partitioning is a better alternative.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)