Partitions vs Table Inheritance in PostgreSQL

PostgreSQL has two mechanisms for splitting a large table into smaller pieces: declarative partitioning (introduced in PG 10, mature by PG 12) and table inheritance (much older, much more flexible, and much more dangerous).

They look similar from the outside. A query on the parent table returns rows from all the child tables. But they work very differently under the hood, and the difference matters for indexes, foreign keys, performance, and maintenance.

Use declarative partitioning for new work. Use table inheritance if you are maintaining a schema that already uses it and you need to understand what it is doing before you can change it.

Declarative Partitioning

Declarative partitioning is the kind that says "split this table by range" or "split by list of values" and PostgreSQL handles the rest automatically:

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT,
  created_at TIMESTAMP,
  amount NUMERIC
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024_q1 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

When you insert into orders, PostgreSQL automatically routes the row to the correct partition. When you query orders, PostgreSQL runs partition pruning: it looks at the WHERE clause, figures out which partitions could possibly match, and only scans those partitions.

Indexes on the parent table work. Foreign keys work (mostly). Constraints work. Everything you would expect to work on a normal table mostly works, and PostgreSQL handles the distribution transparently.

The catch: partition pruning only works if the planner can prove a partition is irrelevant. If your WHERE clause is WHERE created_at::TEXT LIKE '2024%', the planner cannot prove anything. It scans every partition. This is the common mistake: assuming pruning is automatic, then watching a query on a 100-partition table run forever because the planner could not eliminate any partitions.

Table Inheritance

Table inheritance is the original mechanism. You create a parent table and then create child tables that inherit from it:

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT,
  created_at TIMESTAMP,
  amount NUMERIC
);

CREATE TABLE orders_2024_q1 (
  LIKE orders INCLUDING ALL
) INHERITS (orders);

CREATE TABLE orders_2024_q2 (
  LIKE orders INCLUDING ALL
) INHERITS (orders);

Child tables inherit the columns and constraints from the parent. When you insert into a child, it goes into the child. When you insert into the parent with CONSTRAINT_EXCLUSION enabled, you have to write a trigger or a rule to route the row to the correct child.

Queries on the parent table return rows from the parent and all children. But the planner does not prune automatically. You have to set constraint_exclusion = partition (not just on), and you have to have constraints on the children that the planner can use to prove a child is irrelevant.

It looks like:

ALTER TABLE orders_2024_q1 ADD CONSTRAINT orders_2024_q1_created_at
  CHECK (created_at >= '2024-01-01' AND created_at < '2024-04-01');

If the constraint is correct and the planner can prove it, the planner will skip that table. If the constraint is wrong or the planner is conservative, the planner scans the table anyway.

Indexes are not inherited. You have to create indexes on each child separately. Foreign keys are inherited in a way that is confusing (the foreign key references the parent, and it works, but only if you trust that the constraint on the child is actually correct).

Why This Matters

Declarative partitioning is explicit about its guarantees. The database enforces the partition boundaries. If you query for WHERE created_at = '2025-01-01' on a range partition by quarter, PostgreSQL proves that the row must be in one specific partition and scans only that partition. The proof is built into the partition definition.

Table inheritance relies on you writing correct constraints and the planner being smart enough to use them. If you write the constraint wrong, the planner is wrong. If the planner is conservative, the planner scans more than it needs to. If you drop the constraint by accident, the planner might skip partitions that contain matching rows.

Declarative partitioning also handles DDL better. You can add a new partition without disrupting existing queries. With table inheritance, you have to be careful that your trigger or insert logic handles the new child correctly.

The Practical Guidance

If you are partitioning a new table, use declarative partitioning. It is simpler, safer, and the planner understands it.

If you are working on a schema that already uses table inheritance, understand what it is doing: figure out what the constraints are, verify they are correct, test that the planner is actually pruning. Do not assume the partition boundaries are enforced just because the code says they should be.

I have spent time debugging table inheritance setups where the constraints were off by a second or the planner was conservative and I ended up scanning 12 months of data when I only needed one month. The answer was always "make the constraint explicit and clear" or "rewrite this as declarative partitioning."

Declarative partitioning is newer, simpler, and designed for this specific job. Use it.