---
title: "PostgreSQL Multitenancy Without RLS: Partition Pruning, Schema Isolation, and pg_bouncer at 10k Tenants"
published: true
description: "Compare schema-per-tenant, partition-based isolation, and separate databases for PostgreSQL SaaS multitenancy. Includes pg_bouncer routing and migration strategies for 10k+ tenants."
tags: postgresql, architecture, api, performance
canonical_url: https://mvpfactory.co/blog/postgresql-multitenancy-partition-schema-pgbouncer
---
## What You Will Build an Understanding Of
By the end of this article, you will know how to choose the right PostgreSQL multitenancy strategy before you have tenants — not after. We will cover the three isolation models that matter in production, show you exactly how partition pruning differs from row filtering at the query planner level, and wire up a pg_bouncer configuration that keeps connection counts sane when you hit 10,000 tenants.
Here is the pattern I use to frame this decision in every SaaS project I touch.
## Prerequisites
- PostgreSQL 11+ (partition pruning is on by default)
- Familiarity with basic SQL DDL
- A rough sense of your tenant count ceiling and traffic distribution
- pg_bouncer installed or available in your stack
---
## The Problem Most Teams Hit Too Late
Teams default to a shared schema with a `tenant_id` column and RLS policies on every table. It works at 50 tenants. At 5,000, you are debugging query planner decisions at 2 AM.
RLS adds predicate evaluation overhead on every row scan. Partition pruning eliminates entire physical segments before the planner touches a single row. That is not a micro-optimization — it is a structural difference in how PostgreSQL executes the query.
Here is the minimal comparison before we go deeper:
| Strategy | Tenant ceiling | Query isolation | Migration complexity | Connection overhead |
|---|---|---|---|---|
| Shared schema + `tenant_id` | ~500 | Low (RLS required) | Low | Low |
| Schema-per-tenant | ~1,000–2,000 | High | Medium | Medium |
| Partition-per-tenant | ~5,000–10,000 | High (planner-level) | Medium | Low |
| Separate database | Unlimited | Complete | High | High |
Pick the row that matches your tenant ceiling. The rest of this article is about implementing it correctly.
---
## Step 1: Schema-Per-Tenant
Each tenant gets its own PostgreSQL schema. The application sets `search_path` at connection time, and queries hit only that tenant's tables — no predicate needed.
sql
-- Connection setup per tenant
SET search_path TO tenant_acme, public;
-- Query hits only tenant_acme.orders — no cross-tenant scanning
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days';
The docs do not emphasize this, but schema proliferation bloats `pg_class` and `pg_attribute`. At 2,000 schemas with 50 tables each, catalog scans slow down DDL operations significantly. Plan your migrations carefully — `ALTER TABLE` across 2,000 schemas requires tooling, not manual SQL.
---
## Step 2: Partition-Based Isolation
Declarative partitioning by `tenant_id` lets the query planner prune irrelevant partitions before execution. With `enable_partition_pruning = on` (the default in PostgreSQL 11+), the planner excludes non-matching partitions entirely.
sql
-- Parent table
CREATE TABLE orders (
id BIGSERIAL,
tenant_id INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
total NUMERIC
) PARTITION BY LIST (tenant_id);
-- Per-tenant partition
CREATE TABLE orders_tenant_42
PARTITION OF orders
FOR VALUES IN (42);
Run `EXPLAIN ANALYZE` on a tenant query. You want to see `Partitions excluded`, not filtered rows. That is the difference between skipping IO entirely and filtering it in memory. For read-heavy SaaS workloads, this matters at scale.
The practical ceiling is roughly 10,000 partitions before the planner's partition selection overhead becomes measurable. PostgreSQL 14+ improved this substantially, but it remains a real consideration.
---
## Step 3: The Connection Routing Layer
Here is the gotcha that will save you hours: pg_bouncer is not optional.
Schema switching and partition routing both require that the right connection reaches the right database context. At 10,000 tenants with burst traffic, native PostgreSQL connections — one process per connection — will exhaust memory well before you hit query bottlenecks.
pg_bouncer in transaction-mode pooling multiplexes thousands of logical client connections onto a small pool of actual PostgreSQL backends.
ini
pgbouncer.ini — transaction pooling for schema-per-tenant
[pgbouncer]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 25
server_reset_query = RESET ALL; SET search_path TO public;
The `server_reset_query` is critical. Without it, a connection released by `tenant_acme` retains that `search_path` and leaks into the next tenant's transaction. This is the class of bug that only surfaces in production under load.
---
## Step 4: Migrations at Scale
Schema-per-tenant migrations require automation. The pattern that holds up in production:
1. Generate migration SQL once
2. Iterate over all schemas in `information_schema.schemata` where `schema_name LIKE 'tenant_%'`
3. Execute in batches with explicit transaction boundaries per schema
4. Track completion state in a separate admin table
Never run schema migrations in the application startup path at this scale. Use a dedicated migration runner with rollback capability and per-tenant status tracking.
---
## Gotchas
**Catalog bloat is real.** At 2,000 schemas × 50 tables, `pg_class` grows large enough to slow DDL measurably. Monitor catalog size as you scale.
**Partition count has a ceiling.** PostgreSQL 14+ raised the practical limit, but planner overhead for partition selection becomes measurable around 10,000 partitions. Benchmark with your actual data, not synthetic uniform distributions.
**Skewed tenant traffic breaks the math.** If 10 accounts drive 80% of traffic, uniform partitioning does not help those accounts. Consider dedicated resources for large tenants and partitioned shared infrastructure for the long tail.
**Do not retrofit pg_bouncer.** Building around a connection pooler from the start is straightforward. Retrofitting it into an existing system under load is a multi-day incident waiting to happen.
---
## Conclusion
Choose your isolation model before you have tenants — migrating from shared-schema to partition-based isolation at 1,000 tenants is a multi-week project. The architectural decision is cheap at day one and expensive at year two.
Deploy pg_bouncer in transaction mode from the start, and benchmark partition pruning with your actual tenant distribution. The right model depends on your tenant count ceiling, traffic shape, and tolerance for migration complexity.
**Further reading:**
- [PostgreSQL Partitioning Docs](https://www.postgresql.org/docs/current/ddl-partitioning.html)
- [pg_bouncer Configuration Reference](https://www.pgbouncer.org/config.html)
- [PostgreSQL 14 Partition Pruning Improvements](https://www.postgresql.org/docs/14/release-14.html)
Top comments (0)