Index Strategies and Connection Pooling in Multi-Tenant PostgreSQL
Why naive single-column indexes break under multi-tenant tenant_id scoping. Practical composite index patterns, JSONB query boundaries, and connection pooling with pgbouncer.
Akusara Engineering Team
Core Platform Engineers
Why naive single-column indexes break under multi-tenant tenant_id scoping. Practical composite index patterns, JSONB query boundaries, and connection pooling with pgbouncer.
The Reality of Multi-Tenant Queries
In multi-tenant SaaS applications, virtually every query includes a tenant identifier clause:
SELECT * FROM orders
WHERE tenant_id = 'tenant_456'
AND status = 'completed'
ORDER BY created_at DESC
LIMIT 50;If your table has independent single-column indexes on tenant_id, status, and created_at, the PostgreSQL query planner must either perform a bitmap index scan merging three separate index trees or scan an index with high selectivity loss. Under millions of rows, query latency spikes.
Composite Index Ordering Matters
A B-tree composite index must be ordered based on equality predicates first, followed by range or ordering predicates:
-- Correct composite index for tenant filtering + sorting
CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);With this structure:
- PostgreSQL jumps directly to the matching
tenant_idpartition. - It filters exact
statusvalues without reading table heap blocks. - The records are already stored in descending
created_atorder, completely avoiding an in-memorySortnode.
Preventing Connection Exhaustion with Connection Poolers
Serverless edge environments (such as Vercel or Cloudflare Workers) spin up dynamic instances on demand. If 500 concurrent requests open 500 direct PostgreSQL connections, PostgreSQL memory consumption spikes and backend processes begin stalling.
We enforce transaction-mode connection pooling (e.g. pgbouncer or Neon connection pooler):
- Set
max_connectionsconservatively (e.g. 50-100 on the database instance). - Use transaction pooling so that idle client connections hold zero database backend workers between SQL queries.
- Keep prepared statements managed within pooler guidelines.
The Rule of Thumb
Never create indexes speculatively. Inspect production slow query logs using pg_stat_statements, run EXPLAIN (ANALYZE, BUFFERS), and index the exact predicate patterns your software uses.
Published by Akusara Engineering Team
Core Platform Engineers
Akusara Digital is an engineering and product studio building mission-critical software, custom SaaS platforms, and developer tooling.
Read Next from Akusara
All ArticlesPractical Next.js 16 Upgrades: What Turbopack, React 19, and Server Actions Actually Change
Lessons learned migrating production client applications to Next.js 16. Handling asynchronous request APIs, replacing client mutation state with Server Actions, and Turbopack build nuances.

How We Architected Aksora: A Real-Time QA Platform on Next.js 16 and PostgreSQL
Behind the scenes of building our flagship test management workspace. Why we chose Next.js 16 Turbopack, strict PostgreSQL schemas, and end-to-end type safety.
