Akusara Digital
Architecture & Engineering2026-09-02•7 min read

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.

A

Akusara Engineering Team

Core Platform Engineers

#PostgreSQL#Database#Backend#Performance
Index Strategies and Connection Pooling in Multi-Tenant PostgreSQL
Key Takeaway

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:

sql
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:

sql
-- 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:

  1. PostgreSQL jumps directly to the matching tenant_id partition.
  2. It filters exact status values without reading table heap blocks.
  3. The records are already stored in descending created_at order, completely avoiding an in-memory Sort node.

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_connections conservatively (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.

Tags:#PostgreSQL#Database#Backend#Performance
A

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 Articles