Declarative Partitioning in PostgreSQL

Does your team's event log table have 500 million rows and counting? Are VACUUM runs taking longer than your lunch break, and every DELETE of old data feels like a database-wide emergency? You are not alone. The pain is real: as tables grow, indexes bloat, query plans degrade, and routine maintenance becomes a full-time job. In this lesson, you'll learn how to use declarative partitioning in PostgreSQL to tame that growth, improve query performance, and make data lifecycle management a breeze. Let's dive in.

The problem this lesson solves

Any PostgreSQL table will eventually face the same wall: unbounded growth. Whether it's logs, sensor readings, or audit trails, the data keeps coming. Soon you notice:

• Query slowness: Even with indexes, scanning a 200 GB table is painful.
• Runaway VACUUM: Dead tuples pile up, and autovacuum can't keep up.
• Inefficient deletes: Removing old data forces massive I/O and locks.
• Cache thrashing: The database buffer cache can't hold the working set, so every query hits disk.

Until recently, the only solutions were manual hacks: CHECK constraints, triggers, or partitioning via inheritance. These were clunky, error-prone, and hard to maintain. PostgreSQL 10 changed the game with declarative partitioning — built-in, syntax-native partitioning that solves these problems elegantly. This lesson shows you how to use it today, in PostgreSQL 12+ (and especially 13+ where it matured).

Core concept / mental model

Think of a table as a giant filing cabinet. Without partitioning, every document goes into one drawer. Adding more folders (indexes) helps, but eventually the drawer is so full you can't find anything without digging. Declarative partitioning splits that cabinet into multiple labeled drawers (physical partitions) based on a key value, like a date. The database automatically routes each new row to the correct drawer.

The key terms:

• Partitioned table: The logical parent table you query against. It has no storage of its own.
• Partition: A physical child table, often backed by its own storage and indexes. It inherits the parent's schema.
• Partition key: The column(s) used to decide which partition stores a row.
• Partition bounds: The rules that define which key values map to which partition — e.g., FOR VALUES FROM ('2024-01-01') TO ('2024-02-01').

Mental model: The partitioned table is like a router. It inspects the partition key and forwards each row to the right child table. When you query, PostgreSQL's partition pruning skips partitions that can't possibly match your WHERE clause — that's the performance rocket fuel.

The beauty of declarative partitioning is that the database manages all the routing and constraints for you. You define the partitions once, and from the application's perspective, you're just inserting into one table.

How it works step by step

Here's the lifecycle of working with declarative partitioning:

1. Choose a partition key. Common choices: created_at (date), id (hash-based), or category (list). The key should match your most frequent query filter.

2. Define the parent table with PARTITION BY RANGE, LIST, or HASH. For time-series data, RANGE is almost always the right choice.

3. Create the partitions with CREATE TABLE ... PARTITION OF. Each partition gets its own bounds. For RANGE partitioning, the upper bound is exclusive, the lower is inclusive.

4. Add indexes and constraints on each partition (or use a global index if you're on PostgreSQL 11+ — yes, partition-level indexes are separate).

5. Let the database route data automatically on INSERT. You don't touch partitions directly.

6. Maintain partitions: add new ones ahead of time, drop old ones for archiving.

Optionally, DEFAULT partition can capture rows that don't match any bounds — but be careful, it can hide bugs.

Hands-on walkthrough

Let's build a real example: an events table that logs user actions, partitioned by month.

Step 1: Create the partitioned parent table

CREATE TABLE events (
    id BIGSERIAL,
    user_id INT NOT NULL,
    action TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

Step 2: Create monthly partitions

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

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

Step 3: Add indexes on each partition

CREATE INDEX idx_events_y2024m01_user_id ON events_y2024m01 (user_id);
CREATE INDEX idx_events_y2024m02_user_id ON events_y2024m02 (user_id);

In PostgreSQL 11+, you can create an index on the partitioned table itself, and it will automatically be created on all existing and future partitions. Prefer that for simplicity: sql CREATE INDEX idx_events_user_id ON events (user_id);

Step 4: Insert and query

-- Inserts go to the parent; the router picks the partition
INSERT INTO events (user_id, action, created_at)
VALUES (42, 'login', '2024-01-15 10:23:54+00');

-- Query with a WHERE on the partition key
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

Expected output of EXPLAIN will show only one partition being scanned, e.g.:

Append (cost=0.00..41.88 rows=11 width=220)
  Subplans Removed: 1
  -> Seq Scan on events_y2024m01 ...
        Filter: ((created_at >= '2024-01-01'::date) AND ...)

The line Subplans Removed: 1 is proof that partition pruning skipped the February partition.

Step 5: Add a partition for the next month before you need it

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

Now, any insert with a created_at in March will be routed automatically. If you insert a row without a matching partition, you'll get an error like:

ERROR: no partition of relation "events" found for row
DETAIL: Partition key of the failing row contains (created_at) = (2024-03-15).

That's your cue to create the partition — or use a DEFAULT partition to catch stragglers.

Compare options / when to choose what

When should you use declarative partitioning? If your table is or will be large (say, > 10 million rows), you query mostly by the partition key, and you need data retention (dropping old partitions) — then yes. If your dataset fits comfortably in memory and queries are all over the map, partitioning may add overhead without benefit.

Also consider partition-wise joins (PostgreSQL 14+) for even faster multi-table queries, and partition-wise aggregation to speed up GROUP BY over partitions.

Troubleshooting & edge cases

Here are the classic gotchas you'll hit when using declarative partitioning:

• Missing partition for new data: If you forget to create the next month's partition, inserts fail. Mitigate with a DEFAULT partition or a cron job that creates partitions ahead of time.
• Slow partition creation: Creating many partitions at once can lock the parent table. Do it in batches, and consider using CREATE TABLE ... PARTITION OF outside peak hours.
• Row movement disabled: If you need to move rows between partitions (e.g., UPDATE changes the partition key), you'll get an error: ERROR: new row for relation ... violates partition constraint. As of PostgreSQL 18, row movement is supported, but in earlier versions you must DELETE and re-INSERT. Workaround: update the key via a two-step process.
• Exclusive upper bounds: RANGE bounds are [lower, upper) — the upper bound is exclusive. A common mistake is using TO ('2024-02-01') and then trying to insert a row with created_at = '2024-02-01 00:00:00', which goes to the next partition. That's correct but can surprise you in tests.
• Indexes on partitions: If you create indexes only on the parent, they'll be created on child partitions. But if you add a partition later, the index is created automatically — good. However, if you create an index on a child directly, it won't exist on the parent or other children.
• Primary keys with partitions: The partition key must be part of the primary key on the parent. Otherwise you'll get an error when creating the parent. Example: PRIMARY KEY (id, created_at) is required if you partition by created_at and want a primary key.
• Foreign keys referencing a partitioned table: In PostgreSQL 11+, you can reference a partitioned table, but the referenced table must have the partition key in its unique constraint. This can be limiting.
• When VACUUM still doesn't help: With partitions, each partition gets its own statistics and can be vacuumed independently — that's a win. But if you're still seeing bloated indexes, remember to REINDEX after large deletions.

What you learned & what's next

You've learned how to use declarative partitioning in PostgreSQL to manage large, growing tables. You now understand:

• The syntax for PARTITION BY RANGE, LIST, and HASH.
• How to create partitions and let PostgreSQL route rows automatically.
• How partition pruning makes queries faster by skipping irrelevant partitions.
• The trade-offs between partitioning methods and when to choose each.
• How to troubleshoot common pitfalls like missing partitions and primary key constraints.

With this foundation, you're ready to apply declarative partitioning to your own workloads. But managing partitions manually is still tedious — that's exactly why PostgreSQL 13+ introduced automated partition maintenance features like ALTER TABLE ... ATTACH PARTITION improvements, and why extensions like pg_partman exist. In the next lesson, you'll learn how to use pg_partman to automate partition creation and retention, turning your new partitioning skills into a hands-off operational strategy.

Now go ahead: create a test partitioned table, run some EXPLAIN plans, and see the pruning in action. Your future self — and your database — will thank you.