Alright, let’s pull back the curtain on one of PostgreSQL’s most seductively “clever” features: table inheritance. It feels like you’ve just been handed a superpower. You can create a master table and have child tables that automatically inherit its columns. Need to model different types of vehicles? A base vehicles table with children cars, trucks, and boats seems so elegant. But here’s the brutal truth, straight from the trenches: for the use case you’re probably thinking of—partitioning your data for performance—native inheritance is often a trap dressed up as a solution. It’s the coding equivalent of a beautifully designed sports car with a lawnmower engine under the hood.

Let’s be clear: table inheritance is fantastic for certain modeling problems, like when you have truly disparate entity types that share a common core set of attributes. But the moment you start thinking, “I’ll use this to split my data across physical tables for easier management and faster queries,” you’re venturing into its limitations. The designers gave us a chisel when what we needed was a power drill.

The Constraint Exclusion Gambit

The biggest gotcha is that the query planner is not automatically your friend here. Let’s set up a classic example.

CREATE TABLE sensor_data (
    id SERIAL PRIMARY KEY,
    sensor_id INTEGER NOT NULL,
    captured_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    reading NUMERIC NOT NULL
);

CREATE TABLE sensor_data_2023 () INHERITS (sensor_data);
CREATE TABLE sensor_data_2024 () INHERITS (sensor_data);

You insert 2023 data into sensor_data_2023 and 2024 data into sensor_data_2024. Now, let’s run a query for a specific sensor in 2024.

EXPLAIN ANALYZE
SELECT * FROM sensor_data
WHERE sensor_id = 42
AND captured_at >= '2024-01-01';

You’d hope the planner would be smart enough to only scan sensor_data_2024. But hope is not a strategy. By default, it will scan every single child table because it has no built-in way to know which table might contain sensor_id = 42. It’s terrified of missing data. To make this work, you must manually add constraints to every child table and enable a specific setting.

-- You MUST do this for each child table. Forget one, and it's a full table scan.
ALTER TABLE sensor_data_2023
    ADD CONSTRAINT captured_at_2023
    CHECK (captured_at >= '2023-01-01' AND captured_at < '2024-01-01');

ALTER TABLE sensor_data_2024
    ADD CONSTRAINT captured_at_2024
    CHECK (captured_at >= '2024-01-01' AND captured_at < '2025-01-01');

-- And you MUST enable this magic setting for your session or in postgresql.conf
SET enable_partition_pruning = on;
SET constraint_exclusion = partition; -- (On older versions < 11)

Now the planner can use those CHECK constraints to exclude irrelevant tables. This is a manual, error-prone process. Native partitioning, introduced in PostgreSQL 10, handles this automagically. Why the inheritance feature didn’t get this right from the start is a mystery best discussed over a strong drink.

The Primary Key Catastrophe

Here’s the one that makes me laugh every time. Remember that PRIMARY KEY we defined on the parent sensor_data table? It’s a lie. A complete and total fiction.

A primary key implies uniqueness across the entire table family. But PostgreSQL does not enforce uniqueness across inherited tables. The PRIMARY KEY constraint on the parent only applies to the parent table itself. Each child table can have its own unique primary key, but there’s nothing stopping sensor_data_2023 and sensor_data_2024 from both having a row with id = 12345.

Let that sink in. Your “primary key” is no longer a guarantee of uniqueness. It’s a suggestion. This isn’t a minor oversight; it’s a fundamental design flaw for any partitioning scheme. You are now wholly responsible for ensuring global uniqueness, probably with a complex trigger or by using an external ID generator. It’s madness. Native partitioning doesn’t have this problem because there is only one actual table; the partitions are physically linked to it.

The Foreign Key Fiasco

If the primary key situation is bad, foreign keys are a non-starter. You cannot create a foreign key constraint that references the parent table. Think about it: how could another table possibly reference a row if the database can’t even guarantee where a unique ID lives or if it’s unique at all? The system rightly throws its hands up and says, “I can’t promise you anything, pal.” This makes inheritance utterly useless for any schema that relies on relational integrity, which is, you know, most of them. Native partitioning, again, sidesteps this entirely because foreign keys can reference the partitioned table just like any other.

The DML Annoyance

Inheritance requires you to be hyper-aware of where you’re putting data. INSERT INTO sensor_data ... will put data only into the sensor_data parent table. To insert into a specific child, you must name it directly: INSERT INTO sensor_data_2024 .... To automate this, you’re back to writing triggers. Native partitioning, with its dedicated CREATE TABLE ... PARTITION BY syntax, handles this seamlessly. You insert into the parent table, and the database routes the row to the correct partition based on the partitioning key. It just works.

The bottom line? Table inheritance is a powerful tool for creating a hierarchy of tables where each child is a distinct type of entity. It is not, and was never designed to be, a robust data partitioning system. For that, you want native declarative partitioning (PG10+). It handles all the nasty bits—constraint exclusion, unique indexes across all partitions, and DML routing—for you. Use inheritance for its intended purpose: clever modeling. Use partitioning for performance. Don’t mix them up, unless you enjoy building intricate Rube Goldberg machines that occasionally lose your data.