Speeding Up Write‑Heavy Workloads with PostgreSQL Declarative Partitioning

Problem: Write‑Heavy Tables, Table Bloat, and Slow Queries

In many applications, a single table can swell to tens of millions of rows. Every INSERT touches one large heap, every UPDATE rewrites a row in that heap, and a simple SELECT that filters on a date column still has to scan the entire table because the planner has no clue which rows belong where. The result is high I/O, long vacuum times, and query plans that touch far more data than necessary.

Typical symptoms:

• INSERT latency climbs as the table grows.
• SELECTs that should hit a single month take seconds because they read every partition.
• VACUUM and ANALYZE lock the whole table for minutes, blocking writes.
• Buffer cache is overwhelmed, causing cache misses and higher disk traffic.

Thesis: Declarative Partitioning Cuts the Problem in Half

PostgreSQL’s declarative partitioning (introduced in 10 and refined in 11‑15) lets you split a logical table into child tables—called partitions—based on a key. The key can be a range (e.g., dates), a list of discrete values, or a hash of a column. Partitioning has three core benefits for write‑heavy workloads:

• Smaller heaps – each partition is a separate table, so inserts and updates touch only a tiny file on disk.
• Partition pruning – the planner can skip entire partitions when a query’s WHERE clause references the partition key, drastically reducing I/O.
• Targeted maintenance – VACUUM, ANALYZE, and even ALTER TABLE can run on a single partition, keeping the rest of the database free from lock contention.

Because the partitioned table is still a single logical object, you can keep foreign keys, triggers, and indexes just as you would on a normal table. The engine stitches the pieces together automatically.

Worked Example: Partitioning a Large Events Table by Date

Below is a minimal, reproducible example. Assume a PostgreSQL 15 cluster, a database named appdb, and a user appuser with the necessary privileges.

1. Create the master table with a partition key.

-- Connect to the database
\c appdb appuser

-- Create the master table; it will hold the partition key but no rows itself
CREATE TABLE events (
  event_id BIGSERIAL PRIMARY KEY,
  event_time TIMESTAMPTZ NOT NULL,
  event_type TEXT NOT NULL,
  payload JSONB
) PARTITION BY RANGE (event_time);

Key points:

• The PARTITION BY RANGE (event_time) clause tells PostgreSQL to split the table by the event_time column.
• No rows are stored in events itself; all data lives in child partitions.
• Indexes defined on events will be automatically created on each partition.

1. Create a partition for a single month.

CREATE TABLE events_2024_10 PARTITION OF events
  FOR VALUES FROM ('2024-10-01 00:00:00+00') TO ('2024-11-01 00:00:00+00');

Repeat this step for each month you expect to store. You can script it or use a cron job that adds a new partition at the start of each month.

1. Insert data.

INSERT INTO events (event_time, event_type, payload)
VALUES
  ('2024-10-15 12:34:56+00', 'click', '{"page": "/home"}'),
  ('2024-10-20 08:20:00+00', 'purchase', '{"item_id": 123}')
;

PostgreSQL automatically routes each row to the correct partition based on event_time. No trigger or extra logic is needed.

1. Query with a date range.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE event_time BETWEEN '2024-10-01' AND '2024-10-31';

In the output you should see that only the events_2024_10 partition is scanned. The planner will not touch events_2024_11 or any other partition. Buffer statistics will confirm fewer pages are read compared to a non‑partitioned table.

1. Maintenance on a single partition.

VACUUM (ANALYZE) events_2024_10;

This command cleans up dead tuples in just that month’s heap, leaving other partitions untouched and minimizing write stalls.

Trade‑offs and Limitations

• Query design matters. Partition pruning only happens when the planner can match a query’s WHERE clause to the partition key. A query that filters on a different column will still scan all partitions.
• Aggregations over many partitions. Some aggregate functions (e.g., array_agg) can force a full table scan if the planner cannot guarantee that the result is confined to a subset of partitions.
• Complex joins. Joining a partitioned table to another large table can sometimes bypass pruning if the join condition does not reference the partition key in a way the planner understands.
• DDL overhead. Adding or dropping partitions is cheap, but migrating an existing large table to a partitioned layout requires a one‑off data load that can be time‑consuming.
• Index maintenance. Every index you create on the master table is duplicated on each partition. Managing many indices can increase storage and maintenance overhead.

Actionable Next Steps

• Audit your most frequent queries. If >70% filter on a candidate key, partitioning is likely beneficial.
• Start with a single range partition (e.g., by month) and monitor pg_stat_user_tables for I/O and vacuum times.
• Automate partition creation with a lightweight script that runs CREATE TABLE … PARTITION OF … at the start of each period.
• Schedule per‑partition VACUUM (ANALYZE) during low‑traffic windows to keep dead tuples from piling up.
• Consider a hybrid approach: keep hot data in a non‑partitioned “current” table and archive older data into a partitioned schema.

By following these steps, you can turn a sluggish, write‑heavy table into a fast, scalable structure that still feels like a single logical object to your application.