PostgreSQL at Scale: B-Tree vs BRIN Indexing, PgBouncer & Serverless Branching

PostgreSQL is widely considered the world's most versatile relational database. However, as tables grow from thousands to millions of rows, naive schemas suffer from index bloat, sequential table scans, and connection starvation.

In this guide, we examine index selection, connection pooling mechanics with PgBouncer, and how serverless branching (Neon) transforms database development lifecycles.

Understanding query execution plans and index selectivity is key to predictable database latencies.

Index Selection Matrix

Index Type	Internal Structure	Ideal Use Case	Storage Overhead
B-Tree	Balanced Multi-Way Tree	Equality (`=`), range (`<`, `>`), sorting (`ORDER BY`)	High (stores every key)
GIN	Generalized Inverted Index	JSONB queries (`@>`), Full-Text Search, Array containment	Very High (slow to update)
BRIN	Block Range Index	Huge append-only chronological tables (logs, IoT metrics)	Extremely Low (< 1% of B-Tree)
Partial Index	Filtered B-Tree	Filtering on low-cardinality flags (`WHERE is_active = true`)	Very Low

Partial Indexes: 10x Smaller, 10x Faster

Imagine a users table with 5 million rows, but only 2% are active administrators. A standard index wastes memory indexing 98% irrelevant rows. A partial index indexes only matching records:

sql

-- Bad: Indexes all 5,000,000 rows
CREATE INDEX idx_users_admin ON users(is_admin);

-- Production Best Practice: Indexes only the 100,000 matching rows
CREATE INDEX idx_users_admin_partial ON users(id, email)
WHERE is_admin = true;

Diagnosing Queries with EXPLAIN ANALYZE

Never optimize a query based on intuition. Always inspect the execution planner:

sql

EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT p.id, p.name, count(i.id) as image_count
FROM projects p
LEFT JOIN project_images i ON p.id = i.project_id
WHERE p.is_featured = true
GROUP BY p.id;

Look for these execution red flags:

Seq Scan (Sequential Scan): Postgres is reading the entire table from disk page by page.
Buffers: read=X: Data had to be loaded from cold SSD disk rather than Postgres shared_buffers RAM.
External Sort: The sort operation exceeded work_mem and spilled onto disk.

Connection Pooling with PgBouncer

Each direct Postgres connection forks a backend process on Linux, consuming ~10MB of RAM. If 500 serverless Lambda functions connect simultaneously, Postgres crashes with too many connections.

PgBouncer sits in front of Postgres, multiplexing thousands of incoming client connections onto 25 physical database connections using Transaction Pooling:

ini

[databases]
portfolio_db = host=127.0.0.1 port=5432 dbname=neondb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 25

Database Branching with Neon

Traditional database migrations carry high risk in production. Neon introduces copy-on-write database branching:

Instantly branch production data in 2 seconds without storage duplication.
Run migrations and integration tests against a live replica in CI.
Merge pull requests with zero production downtime risk.