PostgreSQL Table Partitioning in Multi-Tenant SaaS: When It Helps and When It Doesn't

Table partitioning is one of those PostgreSQL features that solves a real problem but creates a different one if applied in the wrong situation. Every time a SaaS team's audit log table grows past a hundred million rows, someone suggests partitioning. Sometimes that is exactly right. Sometimes it makes everything worse. The difference comes down to understanding what the query planner actually does with partitions, and more importantly, what it does not do.

What PostgreSQL Table Partitioning Actually Does

PostgreSQL supports three partitioning strategies: range, list, and hash.

Range partitioning divides rows by a continuous range of values, most commonly a timestamp. An audit_events table partitioned by created_at might have one partition per month: audit_events_2026_01, audit_events_2026_02, and so on.

List partitioning divides rows by explicit discrete values. You could partition by region where each partition holds rows for a specific geographic region. In a MENA deployment you might have events_lebanon, events_uae, events_saudi.

Hash partitioning distributes rows evenly across a fixed number of partitions based on a hash of one or more columns. It is useful when you have no natural range key but want to spread write load.

The planner benefit is partition pruning: when a query includes a filter on the partition key, the planner skips partitions whose ranges cannot contain matching rows. A query filtering WHERE created_at >= '2026-05-01' on a monthly-partitioned table only scans the May and subsequent partitions. For a table with three years of data, that might mean scanning 2 out of 36 partitions.

But partition pruning only fires when the filter is on the partition key itself. A query filtering by tenant_id alone on a date-partitioned table gets no benefit. It scans every partition.

What partitioning does NOT help with:

• Connection overhead per query (unchanged)
• Index lookups on non-partition-key columns
• INSERT throughput (marginal improvement at best)
• Query latency when queries span many partitions

The Multi-Tenant SaaS Use Case for Partitioning

The scenario where range partitioning on time genuinely helps in SaaS is append-heavy time-series tables that grow without bound and are queried by recent time ranges.

Audit logs are the canonical case. Every tenant action writes an event. A modest SaaS with 500 active tenants generates tens of millions of rows per month. Queries against audit logs almost always include a time filter: "show me all events for tenant X in the last 30 days". With a monthly partition, that query touches at most two partitions.

CREATE TABLE audit_events (
    id          BIGSERIAL,
    tenant_id   UUID        NOT NULL,
    user_id     UUID        NOT NULL,
    action      TEXT        NOT NULL,
    resource_id UUID,
    metadata    JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

CREATE TABLE audit_events_2026_05
    PARTITION OF audit_events
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE audit_events_2026_06
    PARTITION OF audit_events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

-- Create indexes on each partition
CREATE INDEX ON audit_events_2026_05 (tenant_id, created_at);
CREATE INDEX ON audit_events_2026_06 (tenant_id, created_at);

The composite index on (tenant_id, created_at) within each partition is what makes the per-tenant time-range query fast. Partitioning reduces the index size that needs to be scanned by limiting which partitions participate.

Where Partitioning Hurts in SaaS Systems

Cross-partition queries. Any query that does not filter on the partition key must scan all partitions. In a multi-tenant system, if you ever need to run operations across all tenants (billing reconciliation, data export, compliance reporting), a date-partitioned table forces a full scan across every partition. That is often slower than a properly indexed single table because the planner must open each partition, check its index, and merge results.

Partition maintenance. Partitioned tables require someone or something to create future partitions ahead of time. If you have a monthly partition strategy and nobody creates the July partition before July 1st, inserts fail. You need a cron job, a pg_partman setup, or a deploy step that creates N months ahead. This operational burden is easy to underestimate.

Foreign key constraints do not cross partition boundaries. If audit_events has a foreign key referencing users, and users is partitioned, the foreign key constraint becomes significantly more complex. In practice, many teams drop cross-table FK constraints when they partition, accepting that referential integrity must be enforced at the application layer.

Backfilling is painful. If you decide to partition an existing table with 200 million rows, you cannot add partitioning to an existing table with a simple ALTER TABLE. You create a new partitioned table, migrate data partition by partition, swap the table name, and rebuild all foreign key references. On a live production system serving customers across Lebanon, UAE, and Saudi Arabia, this is a multi-day operation that requires careful zero-downtime execution.

Wrong partition key choice. Partitioning by tenant_id with list partitioning sounds appealing but means adding a new partition every time a new tenant signs up. At scale, having thousands of partitions degrades planner performance because the planner must evaluate partition constraints for every query.

The Alternative: Proper Indexing Before Partitioning

Before reaching for partitioning, exhaust your indexing options. For the typical multi-tenant time-range query pattern, a well-designed index on a single table beats a poorly designed partition scheme every time.

Partial indexes on a non-partitioned table can be remarkably effective:

-- Index only recent audit events, which are queried most frequently
CREATE INDEX idx_audit_events_recent
    ON audit_events (tenant_id, created_at)
    WHERE created_at >= '2026-01-01';

This index is small because it excludes historical data. Queries filtered to recent events hit this index and run fast. Rebuilding it covers only the filtered subset of rows.

BRIN indexes (Block Range INdexes) are designed for large append-only tables where physical row order correlates with the indexed column. An audit log where rows are always inserted with increasing created_at values is a textbook BRIN use case:

CREATE INDEX idx_audit_brin ON audit_events USING BRIN (created_at);

A BRIN index is orders of magnitude smaller than a B-tree index on the same column. It works by storing min/max values for blocks of pages rather than individual row pointers. For table scans filtered by a range of recent dates, it dramatically reduces the pages that need to be read.

Benchmarking: on a 10 million row audit events table, a well-placed composite B-tree index on (tenant_id, created_at) consistently achieves sub-10ms query times for the common case. Partitioning that same table without improving the index strategy produces worse performance because the overhead of opening multiple partitions exceeds the benefit of partition pruning at that scale.

Run VACUUM ANALYZE regularly on high-write tables. A stale table statistics picture causes the planner to make poor decisions regardless of whether partitioning is in play.

When Partitioning Is the Right Answer

Partitioning earns its complexity overhead in specific situations:

Retention policies. If you need to delete audit events older than 12 months, DELETE FROM audit_events WHERE created_at < NOW() - INTERVAL '12 months' on a 2 billion row table takes hours, creates enormous WAL, and holds locks that disrupt normal operations. DROP TABLE audit_events_2025_04 completes in milliseconds. This is the single most compelling reason to partition audit and event tables.

Archival workflows. Similarly, moving old data to a cheaper storage tier (cold tablespace, S3 via foreign data wrapper) is trivially easy with partitions: detach the old partition, attach it to a new parent table on the archive tablespace. On a single table, this requires copying rows.

True high-volume time-series data. If a single tenant generates millions of rows per day (IoT sensor data, transaction streams, POS event logs from hundreds of restaurant locations), and every query filters by a recent time window, partition pruning delivers real query plan improvements because each partition's index fits comfortably in shared_buffers.

Key Lessons from Production

• Partition pruning only fires on the partition key. A multi-tenant system querying by tenant_id on a date-partitioned table gets no benefit unless the query also filters by date.
• Index first. A composite index on (tenant_id, created_at) solves the common case at a fraction of the operational complexity.
• The best use case for range partitioning is retention: DROP PARTITION instead of DELETE WHERE created_at < on a 2 billion row table.
• Never partition an existing large table without a zero-downtime migration plan. The partition swap operation is non-trivial.
• Create future partitions ahead of time. Missing a partition means insert failures in production.
• Avoid list partitioning by tenant ID unless your tenant count is small and stable.
• BRIN indexes are underused and often the right tool for append-only time-series data.

Not sure where to start?

If your PostgreSQL tables are growing fast and you are trying to decide whether partitioning, indexing, or a schema redesign is the right move for your SaaS, we can run a query analysis and schema review with your team. https://voxire.com/get-a-quote/