PostgreSQL Table Inheritance vs Partitioning: When to Use Each

Two Features That Look Alike

PostgreSQL has table inheritance and declarative partitioning, and newcomers conflate them because for years inheritance was how you partitioned. Since PostgreSQL 10 that’s no longer true, and picking the right one matters — especially migrating Oracle partitioned tables.

Inheritance: the older, general mechanism

A child table inherits its parent’s columns; a query on the parent sees rows from all children. Powerful, but full of sharp edges for partitioning use: unique constraints and foreign keys are not enforced across children, and indexes don’t cascade. It’s a data-modelling tool, not a performance one.

Declarative partitioning: the modern answer

For large tables — time-series, logs, archival-by-range — this is almost always what you want: real partitions, automatic partition pruning, and clean syntax.

CREATE TABLE events (
  id     bigint,
  ts     timestamptz NOT NULL,
  payload jsonb
) PARTITION BY RANGE (ts);

CREATE TABLE events_2026_07 PARTITION OF events
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

-- the planner prunes to just the July partition:
SELECT count(*) FROM events WHERE ts >= '2026-07-10' AND ts < '2026-07-11';

For an Oracle DBA the mapping is direct: Oracle RANGE/LIST/HASH partitioned tables become PostgreSQL declarative partitions — not inheritance.

The gotchas that bite

• The partition key must be in every primary/unique key. There are no global unique indexes across partitions, so a PK must include the partition column.
• Indexes are per-partition. Create an index on the parent (PostgreSQL 11+) and it cascades to each partition; there’s no single global index.
• Pruning needs the key in your WHERE. Queries that don’t filter on the partition column scan every partition — the opposite of the win you wanted.
• Plan partition maintenance. New ranges must be created ahead of time; pg_partman automates it.

So when is plain inheritance right?

Rarely, and deliberately: modelling a genuine sub-type hierarchy, or a specific data-organization pattern where you want the parent/child relationship itself — not partition-by-value performance. If your goal is “this table is too big,” the answer is declarative partitioning.