PostgreSQL Partitioning: Table Inheritance and Declarative

Key Insights

Declarative partitioning (PostgreSQL 10+) eliminates the need for manual triggers and constraint management, reducing complexity and improving maintainability compared to legacy table inheritance.
Partition pruning automatically excludes irrelevant partitions during query execution, but only when queries filter on the partition key—choosing the right partition strategy is critical for performance gains.
Migrating from inheritance-based to declarative partitioning requires careful planning but can be done with minimal downtime using partition attachment and detachment operations.

Introduction to PostgreSQL Partitioning

Partitioning splits large tables into smaller, more manageable pieces while maintaining the illusion of a single table to applications. The benefits are substantial: queries that filter on the partition key scan only relevant partitions, bulk deletion becomes instant (just drop a partition), and maintenance operations like VACUUM run faster on smaller chunks.

You should consider partitioning when tables exceed several gigabytes and exhibit clear access patterns. Time-series data is the classic use case—application logs, sensor readings, financial transactions. Geographic data also partitions well when queries consistently filter by region or country.

PostgreSQL offers two partitioning approaches: the legacy inheritance-based method (pre-10) and modern declarative partitioning (10+). If you’re starting fresh, use declarative partitioning. It’s simpler, faster, and better integrated with PostgreSQL’s query planner.

Here’s a quick performance comparison:

-- Non-partitioned table query
EXPLAIN ANALYZE SELECT * FROM events 
WHERE event_date >= '2024-01-01' AND event_date < '2024-02-01';
-- Seq Scan on events (cost=0.00..180000.00 rows=100000) (actual time=450.123..892.456)

-- Partitioned table query (same data)
EXPLAIN ANALYZE SELECT * FROM events_partitioned 
WHERE event_date >= '2024-01-01' AND event_date < '2024-02-01';
-- Seq Scan on events_partitioned_2024_01 (cost=0.00..1500.00 rows=100000) (actual time=5.234..45.678)

The partitioned query scans one partition instead of the entire table—a 20x performance improvement.

Table Inheritance Partitioning (Legacy Method)

Before PostgreSQL 10, partitioning relied on table inheritance, check constraints, and triggers. While functional, this approach requires significant manual setup and maintenance.

The pattern involves creating a parent table and child tables that inherit from it:

-- Parent table (empty, just structure)
CREATE TABLE events (
    id BIGSERIAL,
    event_date DATE NOT NULL,
    event_type VARCHAR(50),
    data JSONB
);

-- Child tables for specific date ranges
CREATE TABLE events_2024_01 (
    CHECK (event_date >= '2024-01-01' AND event_date < '2024-02-01')
) INHERITS (events);

CREATE TABLE events_2024_02 (
    CHECK (event_date >= '2024-02-01' AND event_date < '2024-03-01')
) INHERITS (events);

-- Indexes on child tables
CREATE INDEX idx_events_2024_01_date ON events_2024_01(event_date);
CREATE INDEX idx_events_2024_02_date ON events_2024_02(event_date);

Check constraints enable constraint exclusion—the planner skips partitions that can’t contain matching rows. But data doesn’t automatically route to the correct partition. You need a trigger function:

CREATE OR REPLACE FUNCTION events_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF (NEW.event_date >= '2024-01-01' AND NEW.event_date < '2024-02-01') THEN
        INSERT INTO events_2024_01 VALUES (NEW.*);
    ELSIF (NEW.event_date >= '2024-02-01' AND NEW.event_date < '2024-03-01') THEN
        INSERT INTO events_2024_02 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range. Create appropriate partition.';
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_events_trigger
    BEFORE INSERT ON events
    FOR EACH ROW EXECUTE FUNCTION events_insert_trigger();

This works, but maintaining trigger functions as you add partitions is tedious and error-prone. Every new month requires updating the trigger function. Not ideal.

Declarative Partitioning (Modern Method)

Declarative partitioning simplifies everything. You declare the partitioning strategy, and PostgreSQL handles routing and constraint management automatically.

PostgreSQL supports three partition types: RANGE (for continuous values like dates), LIST (for discrete values like regions), and HASH (for even distribution).

Here’s a RANGE-partitioned table for time-series data:

CREATE TABLE events (
    id BIGSERIAL,
    event_date DATE NOT NULL,
    event_type VARCHAR(50),
    data JSONB
) PARTITION BY RANGE (event_date);

-- Create partitions
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE events_2024_03 PARTITION OF events
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

No triggers, no check constraints. INSERT statements automatically route to the correct partition.

For discrete categorical data, use LIST partitioning:

CREATE TABLE user_data (
    user_id BIGINT,
    region VARCHAR(10),
    data JSONB
) PARTITION BY LIST (region);

CREATE TABLE user_data_us PARTITION OF user_data
    FOR VALUES IN ('US', 'USA');

CREATE TABLE user_data_eu PARTITION OF user_data
    FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE user_data_asia PARTITION OF user_data
    FOR VALUES IN ('JP', 'CN', 'IN');

HASH partitioning distributes rows evenly across partitions—useful when you don’t have a natural partitioning key:

CREATE TABLE user_sessions (
    session_id UUID,
    user_id BIGINT,
    created_at TIMESTAMP
) PARTITION BY HASH (user_id);

CREATE TABLE user_sessions_0 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE user_sessions_2 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE user_sessions_3 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Managing Partitions

Partition management is straightforward with declarative partitioning. Adding a new partition for upcoming data:

-- Add April partition before month starts
CREATE TABLE events_2024_04 PARTITION OF events
    FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

Detaching old partitions for archival or deletion:

-- Detach old partition (keeps data, removes from parent)
ALTER TABLE events DETACH PARTITION events_2024_01;

-- Now you can archive it
pg_dump -t events_2024_01 > events_2024_01.sql

-- Or drop it entirely
DROP TABLE events_2024_01;

Default partitions catch rows that don’t match any partition:

CREATE TABLE events_default PARTITION OF events DEFAULT;

Be cautious with default partitions—they can hide data quality issues. I prefer raising errors for out-of-range values.

Sub-partitioning enables multi-level partitioning strategies:

CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')
    PARTITION BY LIST (event_type);

CREATE TABLE events_2024_01_login PARTITION OF events_2024_01
    FOR VALUES IN ('login');

CREATE TABLE events_2024_01_purchase PARTITION OF events_2024_01
    FOR VALUES IN ('purchase');

Indexes and Constraints on Partitioned Tables

Indexes created on the parent table automatically cascade to all partitions:

CREATE INDEX idx_events_date ON events(event_date);
CREATE INDEX idx_events_type ON events(event_type);

This creates indexes on every partition. You can also create partition-specific indexes:

CREATE INDEX idx_events_2024_01_data ON events_2024_01 USING GIN(data);

Unique constraints require the partition key:

-- This works (includes partition key)
ALTER TABLE events ADD CONSTRAINT events_unique 
    UNIQUE (id, event_date);

-- This fails (missing partition key)
ALTER TABLE events ADD CONSTRAINT events_unique_id 
    UNIQUE (id);
-- ERROR: unique constraint on partitioned table must include all partitioning columns

This limitation exists because PostgreSQL can’t efficiently enforce uniqueness across partitions. If you need a unique ID without the partition key, consider a separate sequence table or use partition-local uniqueness.

Migration from Inheritance to Declarative

Migrating from inheritance-based to declarative partitioning requires careful execution but offers significant benefits. Here’s the strategy:

-- 1. Create new declarative partitioned table
CREATE TABLE events_new (
    id BIGSERIAL,
    event_date DATE NOT NULL,
    event_type VARCHAR(50),
    data JSONB
) PARTITION BY RANGE (event_date);

-- 2. Attach existing child tables as partitions
ALTER TABLE events_2024_01 NO INHERIT events;
ALTER TABLE events_new ATTACH PARTITION events_2024_01
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- 3. Repeat for all partitions
ALTER TABLE events_2024_02 NO INHERIT events;
ALTER TABLE events_new ATTACH PARTITION events_2024_02
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 4. Rename tables
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_new RENAME TO events;

-- 5. Drop old parent and triggers
DROP TRIGGER insert_events_trigger ON events_old;
DROP FUNCTION events_insert_trigger();
DROP TABLE events_old;

This approach minimizes downtime. The ATTACH PARTITION operation validates that existing data matches the partition constraint—ensure your check constraints align with the partition bounds.

Performance Considerations and Best Practices

Partition pruning is the primary performance benefit. Verify it’s working with EXPLAIN:

EXPLAIN SELECT * FROM events 
WHERE event_date = '2024-01-15';

-- Should show:
-- Seq Scan on events_2024_01
-- (only one partition scanned)

If you see all partitions scanned, your WHERE clause doesn’t match the partition key, or you’re using a function that prevents pruning.

Choose partition keys based on query patterns. If 90% of queries filter by date, partition by date. Aim for 10-100 partitions—too many partitions increase planning overhead.

Keep partition sizes between 1GB and 100GB. Smaller partitions increase metadata overhead; larger partitions reduce the benefits of partitioning.

Monitor partition sizes:

SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE tablename LIKE 'events_%'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Partition-wise joins (PostgreSQL 11+) enable joining partitioned tables partition-by-partition, dramatically improving performance for large joins. Enable with enable_partitionwise_join = on.

The bottom line: declarative partitioning is simpler, faster, and better integrated than inheritance-based partitioning. Use it for new projects, and migrate legacy systems when feasible. Your future self will thank you.