Choosing Partitioning Strategy for High‑Write SaaS on PostgreSQL

Context: We're building a multi‑tenant SaaS that ingests ~10k rows/sec per tenant, total 200k rows/sec. Data is time‑series plus tenant_id. We need fast inserts, low‑latency recent queries, and easy archiving of old data.

Trade‑off 1 – Range partitioning on created_at (daily). Simple to drop old partitions, but each insert must be routed through the parent planner; with many tenants the planner cache grows, and hot partitions can cause lock contention on the partition key. Indexes need to be recreated per partition.

Trade‑off 2 – List partitioning on tenant_id with sub‑partition on range (monthly). Isolates tenant hot spots, reducing contention, but creates thousands of partitions quickly, hitting planner overhead and default max_parallel_workers_per_gather. Maintenance scripts become more complex.

Trade‑off 3 – Declarative partitioning with a default partition for overflow. Allows fallback for unexpected dates or new tenants, but queries that span the default partition may suffer performance penalties.

CREATE TABLE events (
    id bigint NOT NULL,
    tenant_id uuid NOT NULL,
    created_at timestamptz NOT NULL,
    payload jsonb,
    PRIMARY KEY (tenant_id, created_at, id)
) PARTITION BY LIST (tenant_id);

-- one tenant example
CREATE TABLE events_t1 PARTITION OF events
    FOR VALUES IN ('11111111-1111-1111-1111-111111111111')
    PARTITION BY RANGE (created_at);

CREATE TABLE events_t1_2024_09 PARTITION OF events_t1
    FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');

Open questions:

1. In your production workloads, how have you balanced the number of partitions vs planner overhead when using list + range partitioning?
2. What automated VACUUM/ANALYZE strategy do you use to keep insert latency low across thousands of partitions?

Related Engineering Guides

• Complete Guide to Architecting a Scalable SaaS Platform
• Laravel Eloquent: Why Your Database Index Is Being Ignored