Partitioning gets pitched as a performance feature, but the honest first question is whether you need it at all. A well-indexed table with a few hundred million rows on decent hardware is often still perfectly fast. Partitioning earns its complexity when you have a specific operational problem it solves — usually bulk deletes, index bloat on a huge table, or query patterns that only ever touch a narrow slice of the data — not as a default for "the table got big."
Range partitioning: the common case
Range partitioning splits a table by a value range, almost always a timestamp, and it solves a problem that indexes alone can't: cheap deletion of old data. Deleting a year-old partition is a metadata operation — DROP TABLE on the partition, effectively instant — versus a DELETE statement that has to find and remove millions of rows, generate that many WAL records, and leave behind dead tuples for autovacuum to clean up.
CREATE TABLE events (
id bigint,
event_type text,
created_at timestamptz NOT NULL,
payload jsonb
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2025_01 PARTITION OF events
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- Instant, not a scan-and-delete
DROP TABLE events_2025_01;The other benefit is partition pruning: a query with a created_at predicate that the planner can statically match to specific partitions will skip scanning the rest entirely, which keeps query performance flat as the table grows, as long as queries actually filter on the partition key.
List and hash partitioning: narrower use cases
List partitioning splits by discrete values — a region or tenant_id column, for example — which is useful when different partitions genuinely have different access patterns or lifecycle rules, like archiving one region's data on a different schedule. Hash partitioning distributes rows pseudo-randomly across a fixed number of partitions based on a hash of the key, which is useful purely for spreading write load and index size evenly when there's no natural range or list boundary, but it gives up the pruning benefit for range queries entirely since hash values don't preserve order.
CREATE TABLE user_sessions (
user_id bigint,
session_id uuid,
started_at timestamptz
) PARTITION BY HASH (user_id);
CREATE TABLE user_sessions_p0 PARTITION OF user_sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- ... p1, p2, p3What partitioning doesn't fix
Partitioning is not a substitute for indexing — each partition needs its own indexes (or you define them on the parent and Postgres propagates them), and a query that doesn't filter on the partition key still has to scan every partition, which can be slower than scanning one large table if the planner has to open dozens of partitions to satisfy a single query. It also doesn't reduce total storage or total row count; it reorganizes the same data into smaller physical units. Foreign keys referencing a partitioned table, and unique constraints that don't include the partition key, come with real restrictions in Postgres that are easy to discover only after the migration is already in production.
A reasonable threshold
In practice, the trigger for partitioning is rarely a row count in isolation — it's a specific pain point: VACUUM taking hours on one enormous table, bulk deletes timing out or bloating the table, or a clear, stable access pattern where 95% of queries only touch the last 30 days. If none of those are true yet, adding partitioning early mostly adds migration risk and query-planning edge cases without a payoff to show for it.
A worked failure mode
A table is partitioned by hash of id because "scale." Every query is WHERE created_at > now()-7d without id, so all partitions are scanned. Maintenance is worse than a single table. Another monthly range partition is never dropped; 400 partitions make planning slow. The failure is partitioning for the wrong key and never aging data. Partition on the column you prune, cap partition count, and automate drop/detach.
When this is the wrong tool
Partitioning is the wrong tool under a few tens of millions of rows that already index well. It will not replace a missing index. Do not hash-partition a time-series you always query by time. Use partitioning for retention and partition pruning you can show in EXPLAIN.
The wrong-tool test is easier with a concrete customer. If a user can lose money, lose access, or see someone else's data when "Table Partitioning Strategies That Actually Pay Off" is slightly misapplied, do not let the pattern ride on defaults. Tighten the API, add an assertion in CI, and refuse silent fallbacks that look like success. Most production failures here are not exotic; they are a missing bound, a missing key, or a missing check that the original paper assumed a careful operator would have.