PostgreSQL 18 adds planner gains for many partitions, skip scans, and AIO, reshaping multi-tenant scale design for SaaS teams on 18.3. Read now.
Why multi-tenant SaaS designs hit partition limits
Most multi-tenant Postgres layouts put tenant identity on a partition key—by tenant id, region, or a composite that keeps hot accounts isolated. That works until the catalog and planner cost of “many partitions” starts to dominate query planning and maintenance. On earlier generations of the same pattern, teams often over-sharded early, under-partitioned hot tenants, or split into separate databases before the single-cluster design had run out of headroom.
PostgreSQL 18’s planner work for large partition sets, skip scans, and asynchronous I/O (AIO) change that calculus for SaaS teams targeting 18.3. You still need a clear tenancy model, but you can keep more tenants in one controlled partition tree longer—if you design for how the planner and storage paths actually behave under concurrent, tenant-scoped traffic.
Planner gains, skip scans, and AIO in practice
Planner improvements for many partitions matter when almost every request carries a tenant predicate and touches only a slice of the tree. Cheaper partition pruning and plan construction reduce the fixed cost of “open a connection, plan a tenant-scoped query, return a small result.” That cost shows up less as a single slow query and more as latency variance under connection churn and mixed tenant sizes.
Skip scans help when indexes are composite and the leading column is not always in the filter—common in multi-tenant schemas that index (tenant_id, …) but also serve global admin or cross-tenant ops jobs that filter only on secondary columns. They do not replace a correct tenant-leading index for the hot path; they reduce the penalty when secondary access patterns still need the same tables.
AIO shifts how sequential and bulk reads interact with the OS and storage stack. For multi-tenant systems, the wins show up in maintenance and analytical side paths—partition-wise scans, backfills, reporting jobs—more than in single-row OLTP. Design so tenant OLTP stays index-driven and partition-pruned, and let AIO absorb the cost of the jobs that must touch wider ranges without blocking the hot path.
Partitioning first, sharding when tenancy demands it
Treat declarative partitioning as the default scale lever inside one primary. Choose a key that matches almost every query’s tenant filter, keep partition counts high enough to isolate noisy neighbors but low enough that attach/detach, vacuum, and monitoring stay operable, and avoid hash-only schemes that scatter a single tenant across many partitions unless you have a proven reason.
- One tenant → one or few partitions when a few accounts dominate load.
- Shared partitions for the long tail of small tenants, with clear move-out criteria for “graduate to dedicated.”
- Cross-tenant constraints and global uniqueness only where the product truly needs them; they fight partition locality.
Sharding—splitting tenants across databases or clusters—still belongs in the design when compliance boundaries, per-tenant failover, or write throughput exceed what one primary can absorb. PostgreSQL 18’s partition and I/O improvements delay that split for many SaaS shapes; they do not remove the need for a tenant→shard map, idempotent migrations, and dual-write or cutover plans when you do fan out.
A practical path on 18.3 for multi-tenant scale
Start from the request shape: every tenant-scoped API path should prune to a known partition set before join and sort work. Index as (tenant_id, …) for those paths, keep skip-scan-friendly composites only where secondary filters are real, and size partitions so attach, detach, and rebalance of a single large tenant are routine ops—not emergencies.
Measure plan time and partition prune behavior under realistic tenant mix, not only steady single-tenant load. Use AIO-sensitive workloads (bulk export, partition maintenance, historical scans) to validate storage configuration, but keep the SaaS core path simple: prune early, touch few partitions, avoid global scans in the request path. When a subset of tenants still overwhelms shared capacity, graduate those tenants to dedicated partitions or shards with the same schema—so sharding is a placement decision, not a rewrite of the product data model.