Partitioning large PostgreSQL tables: events, logs and more
Short answer: Tables that only ever grow, such as activity events, audit logs, sensor readings or sent-email records, eventually make queries slow and clean-up painful. PostgreSQL's built-in partitioning splits one logical table into smaller physical pieces, usually by month, so queries touching recent data scan only recent partitions and old data can be removed by dropping a partition instead of a huge delete. It is worth it for genuinely large, time-based tables, not for every table, and it needs planning around keys, indexes and the creation of future partitions.
The problem with one ever-growing table
Most business apps have one or two tables that grow far faster than the rest. A booking app logs every status change. A SaaS product records every user event. A delivery platform stores GPS points. After a few years:
- Queries for "last 30 days" get slower, because indexes cover years of data.
- Deleting old rows to meet your retention policy takes hours, locks things up and leaves the table bloated afterwards.
- Backups and maintenance take longer every month.
PostgreSQL's partitioning documentation describes exactly these benefits. Query performance can improve dramatically when most of the heavily accessed rows are in a single partition or a small number of partitions. And dropping a partition with DROP TABLE, or detaching it with ALTER TABLE DETACH PARTITION, is far faster than a bulk operation and entirely avoids the VACUUM overhead caused by a bulk DELETE. Seldom-used data can also be moved to cheaper storage.
When partitioning is worth it
The documentation is clear that benefits will normally be worthwhile only when a table would otherwise be very large, and gives a rule of thumb that the table's size should exceed the physical memory of the database server. For most small business apps, that means partitioning a handful of event or log tables, not customers, products or orders.
Signs you may need it:
- One table is many times bigger than all others combined.
- Almost every query on it filters by date.
- You delete or archive old rows on a schedule.
If queries are slow but the table is moderate in size, better indexes or query changes usually help more.
Choosing how to split
PostgreSQL supports range, list and hash partitioning:
- Range, for example by month on a created_at column, is the natural choice for time-based data.
- List, by explicit values such as region or tenant type, suits data with a small number of fixed categories.
- Hash, spreading rows evenly by a key, suits cases where there is no natural range but the table is still huge.
Pick a partition size that matches how you query and delete. Monthly partitions are common for events kept for a year or two. The documentation warns that too many partitions can mean longer query planning times and higher memory consumption, so daily partitions for data kept for years are usually a mistake.
Partition pruning: why queries get faster
PostgreSQL can skip partitions that cannot contain matching rows. The documentation describes the planner proving that a partition need not be scanned because it could not contain any rows meeting the query's WHERE clause, and excluding, or pruning, it from the plan. This only works if your queries filter on the partition key. A query for one customer's events across all time still visits every partition, so check your common queries with EXPLAIN before and after.
Constraints and keys to plan for
Partitioning changes some rules:
- Unique constraints and primary keys on a partitioned table must include all partition key columns, because each partition's index can only enforce uniqueness within itself. An events table with a primary key of just id may need to become id plus created_at.
- You cannot convert in place. The documentation states it is not possible to turn a regular table into a partitioned table or vice versa. Migration usually means creating a new partitioned table, copying data across in batches, and switching over.
- Foreign keys pointing to the partitioned table need care; check what your PostgreSQL version supports.
- A default partition catches rows that fit no other partition, but it can slow down adding new partitions later, so avoid letting it fill up.
Creating future partitions automatically
Inserts fail if no partition exists for the new rows. Create partitions ahead of time, for example the next three months, with a scheduled job or an extension that manages partitions. Alert if the next month's partition is missing a week before it is needed.
Retention made easy
With monthly partitions, your retention job becomes: detach or drop the partitions older than your policy allows, optionally exporting them to cheap storage first. Recent PostgreSQL versions also accept DETACH PARTITION with a CONCURRENTLY option in ALTER TABLE, which reduces locking on busy tables. It runs in seconds rather than hours and leaves no bloat behind.
A worked example
Picture a hypothetical Christchurch fleet-tracking company whose PostgreSQL database stored vehicle location points. The location table had grown to several hundred gigabytes, far larger than the server's memory, and the nightly job deleting points older than 13 months ran for hours and slowed the app.
The developers created a new location table partitioned by month on recorded_at, changed the primary key to include recorded_at, copied data in batches over a week and switched the app over during a quiet period. A scheduled job now creates partitions three months ahead and drops those past retention. Queries for recent trips touch only one or two partitions, and the nightly delete is gone.
FAQ
Will partitioning speed up every query?
No. Only queries that filter on the partition key benefit. Others may be slightly slower.
Is sharding the same thing?
No. Partitioning splits a table within one database server; sharding spreads data across servers.
Can managed database services do this?
Yes. Partitioning is built into PostgreSQL, so managed services generally support it.
If one table in your app keeps growing and slowing everything down, tell us what it stores and how long you keep it.