PostgreSQL Partitioning for High-Volume SaaS: When to Add It and How to Get It Right

Every SaaS backend that survives long enough grows a monster table. Usually it is events, audit logs, or something like message deliveries: append-heavy, rarely updated, and queried almost exclusively by time range. One day a dashboard query that used to take 40 ms takes 4 seconds, autovacuum falls behind, and someone on the team says the words "we should partition that."

PostgreSQL table partitioning is a good tool. It is also one of the most misapplied ones. Partitioning gets recommended on forums the way antibiotics used to be prescribed: for everything, whether or not it can possibly help. Teams spend weeks migrating a table into partitions, see zero improvement, and end up with a more complicated schema than before. This post covers what partitioning does for you, what it cannot do, and the implementation details that decide whether it pays off.

What partitioning actually solves

Declarative partitioning splits one logical table into several physical child tables behind a single name. Postgres routes each row to the right partition on insert and, when the query allows it, skips irrelevant partitions entirely at query time. That second part is called partition pruning, and it is where the value lives.

Concretely, partitioning helps with three kinds of pain.

First, time-range scans on huge tables. If your events table holds three years of data but 95 percent of queries touch the last 30 days, a monthly range partition means those queries scan one or two partitions instead of the whole heap. Indexes on a 20 GB partition are shallower and stay hot in memory in a way that indexes on a 900 GB table do not.

Second, maintenance operations. VACUUM, ANALYZE, and REINDEX all run per partition. On an unpartitioned table of several hundred gigabytes, a single VACUUM can run for hours and lose the race against your write rate. Split across partitions, the same work happens in smaller chunks, and old partitions that no longer receive writes barely need vacuuming at all.

Third, data lifecycle. Retention policies stop being a problem. Deleting one month of data from a big table means a long DELETE, millions of dead tuples, and a vacuum bill afterwards. With partitions you run DETACH or DROP on one child table. It completes in milliseconds and produces no bloat.

What it does not solve

Partitioning does nothing for write throughput. An INSERT still goes to exactly one partition, through the same WAL, onto the same disks. If your bottleneck is ingest rate, look at batching, unlogged staging tables, or hardware before you look at partitioning.

It also does nothing for random-access reads. A lookup like SELECT * FROM orders WHERE id = 48291 was already fast through the primary key index, and after partitioning it may get slightly slower, because the planner has more relations to consider and, unless the id encodes the partition key, no partition can be pruned. The same goes for most single-row UPDATE traffic.

A useful rule: partitioning rewards queries that align with the partition key and taxes queries that ignore it. If your workload has no dominant access pattern, partitioning gives you the tax without the reward.

When to add PostgreSQL table partitioning

There is no hard threshold, but a practical one: think about partitioning when a table passes roughly 50 to 100 GB and keeps growing, and only when you can name the partition key that most queries filter on. For SaaS workloads that key is almost always a timestamp.

Before committing, verify the theory with EXPLAIN ANALYZE on your slowest real queries. What you want to see is Postgres scanning large ranges of the table, either as sequential scans or as index scans that touch millions of rows across the whole time span. If EXPLAIN shows tight index scans returning a few hundred rows in milliseconds, your problem is elsewhere and partitioning will not move the needle. We see this regularly in performance audits: the table is big, the instinct says partition, and the plan says the actual cost is a missing composite index or a bloated one.

Partitioning an existing large table is also not free. Postgres has no ALTER TABLE that converts a plain table in place. You create a new partitioned table, backfill it, and swap, typically with a dual-write phase or a trigger to keep the two in sync during the migration. Budget real engineering time for that step.

Range partitioning for event and audit tables

For append-only, time-keyed tables, monthly range partitioning is the default that fits most SaaS products:

CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    tenant_id   bigint NOT NULL,
    payload     jsonb NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_08 PARTITION OF events
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

Note the primary key. Every unique constraint on a partitioned table must include the partition key, which is why the key above is (id, created_at) rather than plain id. This constraint surprises teams during migration planning more than anything else, because it can ripple into foreign keys and ORM mappings.

Pick granularity from your retention policy and volume, in that order. If you keep 13 months of data and drop the rest, monthly partitions make retention a matter of dropping one partition per month. Daily partitions only make sense at very high volume; thousands of partitions slow down planning, and Postgres handles dozens far more gracefully than tens of thousands.

Hash partitioning for high-write tenant tables

Range partitioning assumes a time axis. Some tables have none: a per-tenant settings or usage table with heavy writes across the whole key space, for example. There hash partitioning can spread the table into fixed buckets:

CREATE TABLE tenant_usage (
    tenant_id  bigint NOT NULL,
    metric     text NOT NULL,
    value      bigint NOT NULL,
    PRIMARY KEY (tenant_id, metric)
) PARTITION BY HASH (tenant_id);

CREATE TABLE tenant_usage_0 PARTITION OF tenant_usage
    FOR VALUES WITH (MODULUS 8, REMAINDER 0);

The wins here are smaller and mostly operational: vacuum runs in parallel across buckets, indexes stay smaller, and one pathological tenant cannot bloat the entire table. Be honest about whether you need it. Hash partitioning cannot be used for retention, gives you no pruning on time queries, and the bucket count is painful to change later. Many teams reaching for it would be better served by row-level tenancy design work on the schema itself.

Pruning, indexes, and the settings that matter

On any supported Postgres version, declarative partitioning relies on enable_partition_pruning, which is on by default. The older constraint_exclusion setting only matters for legacy inheritance-based setups; if a blog post tells you to tune it for declarative partitions, the post is dated.

Indexes declared on the parent table propagate to every partition automatically, including future ones. Define them once at the parent level and resist adding per-partition extras unless a specific partition has a proven need. Every index is multiplied by the partition count, in disk, in write amplification, and in planning time.

Verify pruning the same way you verified the problem: EXPLAIN. A pruned plan lists only the matching partitions. If you see every partition in the plan for a query that filters on the partition key, something is wrong, and it is usually one of the gotchas below.

The gotchas that bite in production

Parameterized queries can defeat pruning. After a prepared statement runs five times, Postgres may switch to a generic plan in which the parameter value is unknown at plan time. Runtime pruning usually rescues this, and EXPLAIN shows it as "Subplans Removed", but expressions on the partition key, casts between timestamp types, or stable functions like now() in unfortunate places can leave you scanning everything. If a query is fast in psql and slow from the application, test with plan_cache_mode = force_custom_plan before blaming the driver.

Foreign keys need version awareness. Foreign keys from a partitioned table to a normal table have worked since Postgres 11, and foreign keys referencing a partitioned table since Postgres 12. On anything older, or on schemas migrated from inheritance-based partitioning, referential integrity toward the partitioned side may be missing and nobody noticed. Legacy systems carry exactly this kind of quiet debt, which is a large part of what modernization projects end up untangling.

Default partitions can become a trap. A DEFAULT partition catches rows that match no range, which prevents insert errors but also collects garbage silently, and its existence can block adding new partitions that overlap data already sitting in it. If you use one, monitor its row count and treat anything nonzero as an incident.

Automating partitions with pg_partman

Manual partition management fails in a predictable way: someone forgets to create next month's partition, and at midnight on the first, inserts start failing or piling into the default partition. The pg_partman extension exists to remove that failure mode. You register the parent table once, and its maintenance procedure pre-creates future partitions and detaches or drops expired ones according to your retention setting. Schedule run_maintenance_proc() with pg_cron or any external scheduler, and alert if it has not run in a day. This is not optional tooling for production use; treat a partitioned table without automated maintenance as unfinished work.

Partitioning done at the right time, on the right table, for the right query pattern is one of the highest-leverage changes available to a growing SaaS on Postgres. Done speculatively, it is schema complexity with no payoff. Measure first, partition second.

If you are staring at a table that has outgrown its design and want a second opinion on whether PostgreSQL table partitioning is the right move, we do this work regularly at wolf-tech.io. Get in touch at hello@wolf-tech.io and tell us what EXPLAIN ANALYZE is showing you.