PostgreSQL Partitioned Tables: A Practical Guide
A deep dive into PostgreSQL table partitioning — how it works under the hood, when it makes sense, the trade-offs involved, and what to plan for before migrating a large table.
When a table grows large enough that queries start to slow down despite proper indexing, and VACUUM takes noticeably longer with each passing week, partitioning is worth considering. PostgreSQL partitioning is one of the most effective tools available for managing large tables — but it comes with trade-offs and migration complexity that deserve careful thought before committing to it.
This post covers what partitioned tables actually are, how PostgreSQL implements them internally, the three partitioning strategies available, the performance benefits and the caveats, and finally what you need to plan for when migrating an existing table.
What Is a Partitioned Table?
Conceptually, a partitioned table functions as a single logical table, but its data is physically stored across multiple individual tables called partitions. To the application, it looks like one table — you query it, insert into it, and delete from it like any other. Under the hood, PostgreSQL routes each inserted row to the correct physical partition based on the value of a designated column or set of columns, and transparently reads from the relevant partitions when you query.
To understand how PostgreSQL manages this routing and transparency, it helps to know the three components involved:
- Parent table (the partitioned table): The main table definition. It holds no data itself — it defines the table structure and the partitioning scheme (e.g.,
PARTITION BY RANGE). All queries and DML operations are typically directed at this table. - Child tables (the partitions): The individual physical tables that actually store the data. Each partition is constrained to hold only a specific subset of the parent table's data, as defined by its partition bounds.
- Partition key: One or more columns used to determine which partition a given row belongs to.
-- Example: creating a range-partitioned table by date
CREATE TABLE events (
id BIGSERIAL,
occurred_at TIMESTAMP WITH TIME ZONE NOT NULL,
payload JSONB
) PARTITION BY RANGE (occurred_at);
-- Creating individual monthly partitions
CREATE TABLE events_2026_01
PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02
PARTITION OF events
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');With this setup, any row inserted into events is automatically routed to the correct child table, and any query against events transparently reads from the relevant partitions.
Partitioning Strategies
PostgreSQL supports three partitioning strategies, each suited to a different data distribution pattern. Choosing the right one depends on how your data is structured and how your queries filter it.
Range Partitioning
The table is divided based on non-overlapping ranges of the partition key's values and is the most common partitioning strategy, particularly for time-series data.
Best for: Time-series data — logs, events, audit trails, metrics — where you partition by date, month, or year. Also works well for monotonically increasing numeric IDs.
In a sales platform, for example, partitioning by sale_date means a monthly report query only touches the relevant partition rather than scanning years of data.
-- Partition a sales table by month
CREATE TABLE sales (
id BIGSERIAL,
sale_date DATE NOT NULL,
amount NUMERIC
) PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2026_01
PARTITION OF sales
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');List Partitioning
The table is divided by explicitly listing which key values belong to each partition. Every possible value must be accounted for, or PostgreSQL will reject the insert with an error. A default partition can be defined to catch any values not explicitly mapped, acting as a fallback for unexpected or future categories.
Best for: Data with discrete, well-known categories such as region, country code, or status.
In a multi-region deployment, for example, partitioning customers by region_id keeps each region's data physically separate, making region-scoped queries and maintenance operations more efficient.
CREATE TABLE customers (
id BIGSERIAL,
region_id INT NOT NULL,
name TEXT
) PARTITION BY LIST (region_id);
CREATE TABLE customers_emea PARTITION OF customers FOR VALUES IN (1, 2, 3);
CREATE TABLE customers_apac PARTITION OF customers FOR VALUES IN (4, 5, 6);
CREATE TABLE customers_amer PARTITION OF customers FOR VALUES IN (7, 8, 9);Hash Partitioning
The table is divided by computing a hash of the partition key and distributing rows evenly across a predefined number of partitions using a modulus and remainder.
Best for: Even load distribution when data has no natural range or category structure, and when the goal is to spread write pressure across partitions.
A common real-world case is an events table partitioned by workspace_id. When a row is inserted, PostgreSQL computes a hash of the workspace_id value and applies a modulo operation against the number of partitions to determine where the row lands. Every event belonging to the same workspace always hashes to the same remainder, so all of that workspace's data ends up in the same partition. This gives you two benefits at once: writes are distributed evenly across partitions (assuming a reasonable spread of workspace IDs), and queries filtered by workspace_id are pruned to a single partition rather than scanning all of them.
CREATE TABLE events (
event_id UUID NOT NULL,
workspace_id UUID NOT NULL,
occurred_at TIMESTAMP WITH TIME ZONE NOT NULL,
payload JSONB
) PARTITION BY HASH (workspace_id);
CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3);With four partitions, workspace IDs whose hash value mod 4 equals 0 go to events_p0, those with remainder 1 go to events_p1, and so on. The distribution is determined entirely by the hash function — there is no manual mapping of values to partitions, and no new partitions need to be created as new workspaces are added.
How PostgreSQL Routes Data
With the three partitioning strategies in mind, it's worth understanding what actually happens when data is written to or read from a partitioned table.
When you insert a row into the parent table, PostgreSQL evaluates the partition key value against each partition's constraint and writes the row directly to the matching child table. No data is ever stored in the parent itself.
When you query the parent table, PostgreSQL's query planner examines the WHERE clause and determines which child tables could possibly contain matching rows, skipping those that provably cannot. This mechanism is called partition pruning and is covered in detail in the Performance Benefits section below.
You can verify pruning is working as expected using EXPLAIN:
EXPLAIN SELECT * FROM events
WHERE occurred_at >= '2026-01-01' AND occurred_at < '2026-02-01';If pruning is effective, only the relevant partition(s) appear in the plan. If all partitions appear, the query's filter either does not reference the partition key or uses it in a way the planner cannot statically evaluate.
Performance Benefits
Partitioning delivers benefits across query performance, memory efficiency, and table maintenance. The impact becomes most significant once a table grows beyond what the server can comfortably hold in memory, but some benefits — particularly around maintenance and data lifecycle — apply regardless of table size.
Partition Pruning
Of all the performance benefits partitioning offers, pruning is the most impactful. It is what allows queries against a very large table to execute as if most of the data does not exist.
When the WHERE clause filters on the partition key, the planner skips irrelevant partitions entirely before execution begins. For a table partitioned by month with five years of data (60 partitions), a query for a single month scans 1/60th of the storage — regardless of table size:
-- PostgreSQL only scans events_2026_01, not every other monthly partition
SELECT * FROM events
WHERE occurred_at >= '2026-01-01' AND occurred_at < '2026-02-01';The same applies to hash-partitioned tables. If the table is partitioned by workspace_id, a workspace-scoped query lands on a single partition:
-- PostgreSQL hashes the workspace_id and scans only the matching partition
SELECT * FROM events
WHERE workspace_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';The benefit compounds as the table grows. On a non-partitioned table, a query must traverse the full index tree and potentially read many disk pages even when returning a small result set. With pruning, the planner eliminates entire partitions before execution begins — not just fewer rows, but fewer index lookups, fewer buffer pool reads, and less I/O pressure overall. The query operates as if the table only contains the data it actually needs.
Pruning also applies at runtime for queries using parameters that are not known at plan time, such as prepared statements with bind parameters. PostgreSQL evaluates the partition key condition when the query is executed and prunes accordingly, so the benefit is not limited to queries with hardcoded literals.
It is worth noting that pruning only works when the filter references the partition key directly. A query that filters on a non-key column — or uses the partition key inside a function call such as WHERE date_trunc('month', occurred_at) = '2026-01-01' — prevents the planner from statically determining which partitions are relevant, and all partitions will be scanned.
Smaller, More Cache-Friendly Indexes
Each partition has its own independent indexes, sized only for the rows that partition holds. This matters because PostgreSQL caches index pages in shared_buffers, and a single large index on a 10 million row table is unlikely to fit entirely in memory — so lookups frequently spill to disk. Split across 12 partitions of ~830,000 rows each, the indexes are individually much smaller. The active partitions' indexes are far more likely to stay cached, meaning most index lookups are served from memory rather than triggering a disk read.
This benefit extends to writes as well. Every insert or update that touches an indexed column must update the relevant indexes. On a large non-partitioned table, those index structures are bigger and less likely to be in memory, making each write more expensive. With partitioning, each write only touches the indexes of a single partition — which are smaller and more likely to be cached — reducing the per-write cost of index maintenance.
More Efficient Maintenance
On large non-partitioned tables, VACUUM has to scan the entire table to reclaim space from dead tuples — a process that grows more expensive as the table grows. With partitioning, VACUUM operates on each partition independently. Active partitions, where most writes happen, are smaller and quicker to process. Older partitions that are rarely or never modified accumulate almost no dead tuples, so VACUUM on them becomes nearly instantaneous.
The same applies to index bloat. On a non-partitioned table, deleted rows leave behind dead index entries that accumulate across the entire index until VACUUM reclaims them. With partitioning, that bloat is contained within each partition's own indexes, keeping them leaner and easier to maintain over time.
Partition Detach and Drop
When data has a clear retention policy — for example, keeping only the last 12 months of events — partitioning makes enforcing that policy trivial. Rather than running a DELETE statement that scans and removes millions of rows one by one, leaving behind dead tuples that VACUUM then has to clean up, you simply detach or drop the entire partition. PostgreSQL treats it as dropping a table, so the operation completes almost instantly regardless of how many rows the partition contains.
Detaching is useful when you want to remove data from the live table but keep it available for archiving or analysis:
-- Detach a partition without deleting it (fast, no full-table lock)
ALTER TABLE events DETACH PARTITION events_2025_01;
-- Or drop it entirely
DROP TABLE events_2025_01;The difference in practice is significant: a DELETE on a partition with tens of millions of rows can run for minutes and cause noticeable I/O pressure. Dropping the partition takes milliseconds.
Caveats and Constraints
Partitioning introduces real complexity — in schema design, query planning, ongoing partition management, and especially in migrating an existing table. None of these should be taken lightly, but each can be planned for before committing to a partitioned design.
Partition Management Overhead
For range and list partitioning, new partitions must be created before data arrives. If you partition by month and forget to create next month's partition, inserts will fail with a constraint violation error (or fall into a default partition if you have one). Tools like pg_partman automate partition creation and maintenance, but they add a dependency.
Hash partitioning avoids this problem entirely — the number of partitions is fixed at creation time and no new ones ever need to be added.
Query Planning Overhead
With a very large number of partitions (hundreds or more), the query planner itself takes longer to evaluate which partitions to prune. For very fast, simple queries where planning time is a significant fraction of execution time, this overhead can negate the benefit of pruning. This is less of a concern for long-running analytical queries and more of a concern for high-throughput OLTP workloads hitting simple point lookups.
Primary Keys and Unique Constraints Must Include the Partition Key
PostgreSQL cannot enforce global uniqueness across all partitions unless the partition key is included in the unique index or primary key. This is a fundamental limitation of the architecture — each partition is a separate physical table with its own indexes.
-- This will fail: cannot enforce uniqueness of 'event_id' alone across partitions
ALTER TABLE events ADD PRIMARY KEY (event_id);
-- This works: the partition key is included in the constraint
-- For a hash-partitioned-by-workspace_id table:
ALTER TABLE events ADD PRIMARY KEY (event_id, workspace_id);Foreign Key Limitations
Foreign keys work in all directions with partitioned tables — a regular table can reference a partitioned table, a partitioned table can reference a regular table, and two partitioned tables can reference each other. There are no restrictions on which side of the relationship is partitioned.
The one thing to be aware of is that any primary key or unique constraint on a partitioned table must include all partition key columns. Since a foreign key must reference a primary key or unique constraint, the referenced columns will always end up including the partition key as a side effect. This is not a foreign key rule specifically, but a consequence of how uniqueness works across partitions.
For example, on a table partitioned by workspace_id, the primary key must include workspace_id. Any foreign key referencing that table must therefore also include workspace_id:
-- The primary key must include the partition key
ALTER TABLE events ADD PRIMARY KEY (event_id, workspace_id);
-- A foreign key referencing events must include both columns
ALTER TABLE audit_log ADD CONSTRAINT fk_event
FOREIGN KEY (event_id, workspace_id) REFERENCES events (event_id, workspace_id);If your schema has complex referential integrity between large tables, audit this carefully before partitioning.
No Concurrent Index Creation on the Parent Table
CREATE INDEX CONCURRENTLY — which builds an index without locking out writes — cannot be used on the parent table to propagate an index to all partitions in one step. You must create the index on each partition individually, running concurrent index creation partition by partition. On a busy table with many partitions, coordinating this safely requires a deliberate rollout plan.
Before You Migrate: What to Consider
Migrating an existing non-partitioned table to a partitioned one requires careful planning. There is no in-place conversion; PostgreSQL does not support a one-step ALTER TABLE for this, so the process involves moving data while keeping the system running.
Is the Table Actually Large Enough to Benefit?
Partitioning adds complexity. For tables that comfortably fit in memory or where queries are already fast, the overhead — in management, schema constraints, and query planning — outweighs the gain. A reasonable threshold to start evaluating partitioning is when the table's indexes no longer fit in shared_buffers and query performance degrades under normal load.
Choose the Partition Key Carefully
The partition key should reflect your most common query filter. If 90% of your queries filter by created_at, partition by date. If they filter by region_id, consider list partitioning by region. A poorly chosen partition key means queries scan every partition anyway and pruning provides no benefit, while all the management overhead remains.
The Migration Approach
Broadly, the steps look like this:
- Create the new partitioned table with the desired schema and partition structure.
- Backfill data from the old table into the partitioned one, typically in batches to avoid locking and excessive I/O.
- Keep both tables in sync during the migration window — either using triggers on the old table or by running dual-writes at the application level.
- Perform a cutover by renaming the tables, updating the application connection, or redirecting traffic.
- Validate and clean up the old table once confidence is established.
For very large tables (hundreds of GB to TB range), this process may span days and require dedicated tooling.
Plan for Schema Constraint Changes
Before starting, audit every unique constraint, primary key, and foreign key on the table. Any unique constraint that does not include the partition key must be redesigned. If other tables have foreign keys referencing this table, each will need to be evaluated against the partitioned table's constraints.
Maintenance Automation
If partitioning by time, set up automated partition creation before the migration is live. An undetected failure to create next month's partition is a production incident waiting to happen.
When Does Partitioning Make Sense?
Not every large table is a good candidate. Partitioning pays off most clearly in a few specific scenarios:
- Time-series data with a retention policy: Logs, events, metrics, audit trails where old data is regularly archived or deleted. Dropping old partitions is instant and produces no bloat.
- Tables that no longer fit in memory: When the working set of data is a small recent slice of a very large table (e.g., last 30 days of a multi-year log table), partitioning lets PostgreSQL read only that slice into memory.
- High write throughput with predictable key distribution: Hash partitioning can distribute write pressure across partitions, reducing contention on hot pages and indexes.
Partitioning is harder to justify for tables that are large but accessed uniformly across all data, for tables with complex cross-partition uniqueness requirements, or for workloads dominated by fast point lookups where query planning overhead starts to matter.
Summary
PostgreSQL partitioning is a powerful tool when applied to the right problem. The core benefits — partition pruning, smaller indexes, faster maintenance, and instant partition drops — are real and significant for large time-series or categorically segmented data. But the constraints around primary keys, foreign keys, unique indexes, and concurrent operations are equally real, and the migration from a non-partitioned table is a significant operational effort that requires careful upfront planning.
Before committing, verify that the partition key matches your query patterns, that your uniqueness and foreign key constraints are compatible with the partitioned model, and that you have an operational plan for managing partition lifecycle from day one.
Published by...
George Andrinopoulos
Visit author page