Postgres Table Inheritance: Partition via Inheritance (2026)

Postgres’s table inheritance is actually a way to avoid partitioning in most cases, and when you do use it for partitioning, it’s a bit of a hack.

Let’s see it in action. Imagine we have a logs table that stores application events. We want to partition this by date, but keep a single logical view of all logs.

-- The "parent" table. No data goes here.
CREATE TABLE logs (
    log_time timestamp with time zone NOT NULL DEFAULT now(),
    level varchar(10),
    message text
);

-- Child table for today's logs
CREATE TABLE logs_today (
    CHECK (log_time >= '2023-10-27' AND log_time < '2023-10-28')
) INHERITS (logs);

-- Child table for yesterday's logs
CREATE TABLE logs_yesterday (
    CHECK (log_time >= '2023-10-26' AND log_time < '2023-10-27')
) INHERITS (logs);

-- Insert some data
INSERT INTO logs_today (level, message) VALUES ('INFO', 'User logged in');
INSERT INTO logs_yesterday (level, message) VALUES ('ERROR', 'Database connection failed');

-- Query across both
SELECT * FROM logs;

The output of SELECT * FROM logs; will show both the INFO and ERROR log entries, even though they are physically stored in different tables (logs_today and logs_yesterday). This is the magic of INHERITS.

The core problem this solves is managing large tables. As a logs table grows, queries against it will slow down. By splitting it into smaller, more manageable child tables, you can:

Improve Query Performance: When a query targets a specific partition (e.g., SELECT * FROM logs_today), Postgres only needs to scan that smaller table, not the entire massive logs table.
Simplify Maintenance: You can DROP old partitions (e.g., DROP TABLE logs_old_month;) without affecting the rest of the data, or VACUUM individual partitions more efficiently.
Data Archiving: Old data can be moved to less performant storage by simply moving the child table.

Here’s how it works internally:

Parent Table: This is a schema-only table. It defines the columns and data types. No data is ever inserted directly into the parent.
Child Tables: These tables INHERIT columns from the parent. Crucially, they can have their own CHECK constraints that define the range of data they are responsible for.
INHERITS Keyword: This is the core mechanism. When a table inherits from another, it automatically gains all columns from the parent.
CHECK Constraints: These are essential for partitioning via inheritance. They act as the rules for which data belongs in which child table. Postgres doesn’t automatically enforce these at the parent level for inherited tables; you need to ensure your INSERTs go to the right child or use triggers.
Query Routing (Implicit): When you query the parent table (SELECT * FROM logs), Postgres intelligently queries all its children and combines the results. This is where the illusion of a single table comes from.

The levers you control are primarily the CHECK constraints on your child tables and the logic for routing INSERTs. You’ll typically use INSERT statements that target specific child tables, or, more commonly for automatic partitioning, use triggers on the parent table to direct incoming rows to the correct child.

The one thing most people don’t realize is that INHERITS doesn’t automatically enforce the CHECK constraints across parent/child relationships for inserts into the parent. If you INSERT INTO logs (...) directly (which is usually not recommended for partitioned tables, but possible if the parent isn’t schema-only), and don’t have a trigger, the row might not end up in any child table, or worse, a row that violates a child’s CHECK might still be inserted into the parent if the parent itself doesn’t have a restrictive CHECK. This is why triggers are almost always necessary for robust partitioning with inheritance.

The next step is usually managing the creation and dropping of these child tables automatically as time progresses, often using pg_partman or custom cron jobs.