Databases
Table partitioning: interview questions and how to answer them
Partitioning splits one logical table into physical pieces within a single database, so queries can skip pieces and maintenance can work on one at a time.
Written and reviewed by Sahil Srivastav
What it actually is
Partitioning divides a table into child tables by a declared key — commonly a time range, sometimes a list of values, sometimes a hash. Queries and writes address the parent table and the engine routes them, so the application sees one table while storage sees many.
The benefit people expect is query speed, through partition pruning: if the planner can prove from the WHERE clause that a partition cannot contain matching rows, it skips it entirely. The benefit that more reliably materialises is operational. Dropping last year's data becomes DETACH/DROP PARTITION — a metadata operation — instead of a DELETE of a hundred million rows that bloats the table and saturates vacuum.
The critical constraint, and the one that decides whether partitioning helps at all, is that pruning requires the partition key in the query predicate. A query that filters only on something else must touch every partition, and has now acquired per-partition planning overhead on top of the same work. Partitioning the wrong table on the wrong key makes things slower.
Why it matters in production
Because at large table sizes, maintenance rather than query time becomes the binding constraint. Vacuum on a one-billion-row table takes hours; index rebuilds need a maintenance window; a bulk delete leaves bloat the autovacuum cannot keep up with. Partitioning makes all of these per-partition — bounded, schedulable, and interruptible.
And because retention is the use case where it is almost unambiguously right. Time-series and event data with a retention policy is the canonical fit: new data goes to the current partition, old partitions are dropped instantly, and indexes stay small because each partition has its own. Teams that implement retention as a nightly DELETE on a huge table usually end up partitioning eventually, after fighting bloat for a year.
How it works
Pruning is a planner proof, not a runtime filter
The planner eliminates partitions it can prove are irrelevant from the predicate. Static pruning happens at planning time from literals; execution-time pruning handles parameters and some joins. If the predicate does not constrain the partition key — or wraps it in a function the planner cannot reason about — nothing is pruned and every partition is scanned.
The key must match how you actually query
This is the design decision, and it is not reversible cheaply. Time-range keys suit retention and recent-data access. A tenant key suits multi-tenant isolation, if queries always carry the tenant. Hash partitioning spreads writes evenly but prunes only for equality on the key. Choosing a key your queries do not filter on gives you all the overhead and none of the benefit.
Indexes are per-partition
Each partition has its own indexes, which is why they stay small and why index maintenance is bounded. A unique constraint, though, can only be enforced within a partition unless the partition key is part of the unique key — so global uniqueness across partitions is not something partitioning gives you for free.
Per-partition overhead is real
Planning considers every partition it cannot prune, locks are taken per partition, and each has its own metadata and vacuum. Hundreds of partitions on a modest table can cost more in planning than the scan saves. The right count is usually tens, not thousands.
Retention becomes metadata
The decisive operational win: ALTER TABLE ... DETACH PARTITION followed by DROP TABLE removes a month of data almost instantly, with no row-by-row delete, no bloat and no vacuum backlog. Compared with deleting a hundred million rows, this is the difference between a scheduled task and a recurring incident.
Implementing it
Partition when the table is genuinely large and either has a retention policy or a dominant access pattern that always carries the same key. Do not partition a table because it feels big — verify you have a pruning predicate first.
Confirm pruning with EXPLAIN before concluding it works. The plan shows which partitions were scanned; seeing all of them means the key or the query is wrong, not that partitioning failed.
Automate partition creation ahead of time. A missing future partition means inserts fail or land in a default partition that quietly grows into the problem you were avoiding.
Keep the partition count moderate and the boundaries aligned to how you drop data — monthly partitions for monthly retention. Misaligned boundaries mean you still need row-level deletes.
-- Range partitioning by time: suits retention and recent-data queries.
CREATE TABLE events (
id bigserial,
tenant_id bigint NOT NULL,
occurred_at timestamptz NOT NULL,
payload jsonb NOT NULL
) PARTITION BY RANGE (occurred_at);
CREATE TABLE events_2026_10 PARTITION OF events
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
-- Prunes: the predicate constrains the partition key.
SELECT * FROM events WHERE occurred_at >= '2026-10-01' AND tenant_id = $1;
-- Does NOT prune: no constraint on occurred_at, so every partition is scanned.
SELECT * FROM events WHERE tenant_id = $1;
-- Retention as metadata, not as a hundred-million-row DELETE
ALTER TABLE events DETACH PARTITION events_2025_10;
DROP TABLE events_2025_10;Interview questions and how to answer them
What does partitioning actually buy you?
Primarily operations: retention becomes a metadata drop instead of a mass delete, and vacuum, index builds and maintenance become per-partition and bounded. Query speed is a secondary benefit and only materialises when the planner can prune, which requires the partition key in the predicate. Leading with the operational answer signals you have run one of these rather than read about it.
How do you choose the partition key?
From the dominant query predicate and the retention policy, which usually agree for event data — time. For multi-tenant data, the tenant key works only if every query carries the tenant. The decision is effectively permanent, so the test is: can I name a predicate present in nearly every query that constrains this key?
A query against a partitioned table got slower. Why?
Most likely it is not pruning, so it reads every partition plus per-partition planning and locking overhead that the unpartitioned table did not have. Check EXPLAIN for which partitions were scanned. Either the predicate does not constrain the key, or it wraps the key in something the planner cannot reason through.
Can you enforce uniqueness across partitions?
Not with a plain unique constraint — it is enforced per partition. To get global uniqueness, the partition key must be part of the unique key, which changes what uniqueness means. If you need a globally unique natural key independent of the partition key, partitioning is working against you and that is worth surfacing early.
How is this different from sharding?
Partitioning is within one database and one engine: transactions, joins and constraints all still work normally, and it is largely transparent to the application. Sharding spreads data across separate databases, which gives write and storage scaling but gives up cross-shard transactions and joins. Partitioning is the much cheaper tool and should be exhausted first.
Answers that lose the round
- Partitioning on a key the queries do not filter on, so nothing ever prunes
- Expecting a query-speed win when the real benefit is operational
- Creating hundreds or thousands of partitions and paying more in planning than is saved
- Forgetting to create future partitions, so inserts fail or pile into a default partition
- Assuming a unique constraint holds globally when it only holds within each partition
- Partition boundaries that do not align with the retention period, so deletes are still needed
- Wrapping the partition key in a function in the predicate, defeating pruning
Practise table partitioning in a real repository
Gronex ships the case where logical filtering is correct but the physical plan still touches every partition, so one high-volume tenant degrades reads for everyone. The tests separate result correctness from workload isolation, which is exactly the distinction pruning turns on.
FAQ
How many partitions is reasonable?
Tens is comfortable, low hundreds is workable, thousands usually costs more in planning and lock overhead than it saves. Size them so each is independently manageable and the count stays bounded as data grows — monthly partitions with a retention policy self-limit, daily ones over several years do not.
Can I partition an existing large table in place?
Not directly — you create a partitioned table and migrate. The usual approach is to attach the existing table as one partition and partition new data going forward, or to copy in batches with dual writes. Either way it is a migration project, not an ALTER.
Does partitioning help with write throughput?
Modestly, by reducing index size per partition and spreading contention on index pages. It does not add write capacity — all partitions share the same server, disks and WAL. If you need genuine write scaling, that is sharding.
What is a default partition for?
Catching rows that match no defined range so inserts do not fail. Useful as a safety net, dangerous as a habit: it quietly accumulates the data your partition scheme failed to anticipate, cannot be pruned, and eventually becomes the large unmanaged table you were trying to avoid. Alarm on its row count.