0
0
Replies
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 tenantid. We need fast inserts, low‑latency recent queries, and easy archiving of old data. Trade‑off 1 – Range partitioning on createdat (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 tenantid with sub‑partition on range (monthly). Isolates tenant hot spots, reducing contention, but creates thousands of partitions quickly, hitting planner overhead and default maxparallelworkerspergather. 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. sql CREATE TABLE events ( id bigint NOT NULL, tenantid uuid NOT NULL, createdat timestamptz NOT NULL, payload jsonb, PRIMARY KEY (tenantid, createdat, id) ) PARTITION BY LIST (tenantid); -- one tenant example CREATE TABLE eventst1 PARTITION OF events FOR VALUES IN ('11111111-1111-1111-1111-111111111111') PARTITION BY RANGE (createdat); CREATE TABLE eventst1202409 PARTITION OF eventst1 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](/articles/complete-guide-to-architecting-a-scalable-saas-platform) - [Laravel Eloquent: Why Your Database Index Is Being Ignored](/articles/laravel-eloquent-why-database-index-ignored-leftmost-prefix)
#PostgreSQL
M
81 days ago