The tenant isolation gap that broke our shared database
Published · Updated
We saved weeks by sharing a Postgres instance across SME tenants. Then one customer ran a bulk export, and row-level security locks starved every other query on the server.
When you build SaaS for SMEs, sharing a single Postgres instance feels like the obvious call. You avoid per-tenant infrastructure overhead and keep deployment simple. We did exactly that. It worked until a bulk export from one tenant locked enough rows to starve everyone else.
The illusion of logical isolation
Our platform used Postgres row-level security policies. Every query carried a tenant ID, and RLS filtered the results. In theory, this gave us hard isolation without the cost of separate schemas. In practice, it introduced a bottleneck we never profiled for.
The incident started with a CSV export. A tenant triggered a query selecting 120,000 rows across joined tables. Postgres acquired ShareLocks on the referenced rows to maintain consistency. These locks blocked updates from other tenants on the same tables.
Within 40 seconds, connection pool exhaustion hit our 100-connection limit. Every request waiting on a lock held a connection open. The API started returning 503s for all tenants, not just the one running the export. The shared model collapsed under a single heavy query.
Fixing the lock starvation
Our first fix was pragmatic: we moved long-running reads to a read replica. This prevented write locks from blocking the primary database. It cost an extra 40 euros per month per environment but immediately stopped the cross-tenant lock starvation.
The second fix addressed the RLS overhead. We added the tenant ID to the primary key of every large table. This made partition pruning effective before the RLS filter ran. Query times for targeted reads dropped from 14ms to 3ms.
We also implemented query timeouts specifically for export endpoints. Any read operation holding locks for over 5 seconds gets cancelled. The user receives a notification to retry using an asynchronous background job instead of a synchronous HTTP request.
Planning the physical split
These mitigations work for our current scale of 40 tenants. If we cross 200 tenants, the operational overhead of managing a shared schema will exceed the cost of isolated databases. We set that threshold explicitly in our architecture decision records.
Shared databases with RLS offer compelling cost savings for early-stage SME platforms. But the isolation is logical, not physical. You must design for the day one tenant monopolises a shared resource, because the failure mode impacts everyone simultaneously.
Measure lock contention across tenant boundaries before you commit to RLS. Add tenant IDs to primary keys for partition pruning. Offload heavy reads to replicas. Set aggressive query timeouts on synchronous endpoints. Isolation is a runtime property, not just a schema constraint.
Working on a project where these methods apply?