PostgreSQL Performance Engineering: Query Optimization, Indexing Strategies & PgBouncer

The Silent Killer of Scaling Applications

1. Deconstructing EXPLAIN (ANALYZE, BUFFERS)

sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT u.id, u.email, count(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= '2026-01-01' AND u.status = 'ACTIVE'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 20;

Key Metrics to Analyze in the Output:

1. Seq Scan vs Index Scan / Bitmap Index Scan:
   • Seq Scan reads every single row on disk sequentially. On a table with 2,000,000 rows, this kills throughput.
   • Index Scan traverses a B-tree structure, fetching only matching row pointers (TIDs).
   • Index Only Scan is the fastest possible scan: all requested columns exist in the index itself, completely bypassing the table heap.
2. Buffers: shared hit=... read=...:
   • shared hit means data blocks were found directly in RAM (shared_buffers).
   • shared read means PostgreSQL had to issue physical I/O requests to SSD/NVMe disk.
   • If read is consistently high, your working data set exceeds allocated buffer memory or queries lack selective indexing.
3. Execution Time vs Planning Time:
   • Compares the milliseconds spent formulating the plan versus executing it across partitions and disk blocks.

2. Advanced Indexing: Choosing the Right Tool for the Job

┌─────────────────────────────────────────────────────────────┐
│                 PostgreSQL Index Arsenal                    │
├──────────────┬──────────────────────────────┬───────────────┤
│ Index Type   │ Best Suited For              │ Disk Overhead │
├──────────────┼──────────────────────────────┼───────────────┤
│ B-Tree       │ Equality (=), Range (<, >)   │ Moderate      │
│ Partial      │ Skewed data (status='ACTIVE')│ Minimal       │
│ Covering     │ Read-heavy, Index-Only Scans │ Moderate      │
│ GIN          │ JSONB, Full-Text, Arrays     │ High          │
│ BRIN         │ Append-only time-series data │ Microscopic   │
└──────────────┴──────────────────────────────┴───────────────┘

A. Partial Indexes (Save 90% Disk & Memory)

sql
-- ❌ Inefficient: Indexes all 10M rows (even completed ones)
CREATE INDEX idx_audit_status ON audit_records(status);

--  Ultra-efficient Partial Index: Only indexes the ~50,000 pending rows
CREATE INDEX idx_audit_pending 
ON audit_records(created_at, user_id) 
WHERE status = 'PENDING';

B. GIN (Generalized Inverted Index) for JSONB

sql
-- Query searching within JSON attributes
SELECT * FROM events 
WHERE metadata @> '{"event_type": "checkout_completed", "env": "prod"}';

-- Create specialized jsonb_path_ops GIN index
CREATE INDEX idx_events_metadata_gin 
ON events USING GIN (metadata jsonb_path_ops);

C. BRIN (Block Range Index) for Massive Time-Series

sql
CREATE INDEX idx_logs_created_at_brin 
ON system_logs USING BRIN (created_at) 
WITH (pages_per_range = 32);

3. Eliminating ORM N+1 Queries in Django and SQLAlchemy

python
# ❌ The N+1 Catastrophe: 1 query for users + 100 queries for orders!
users = User.objects.filter(is_active=True)[:100]
for user in users:
    print(user.profile.bio)  # Hits database 100 times!

The Solution: select_related and prefetch_related

python
#  Fixed in Django: Executes exactly 1 single JOIN query
users = (
    User.objects.filter(is_active=True)
    .select_related("profile")             # SQL INNER/LEFT JOIN
    .prefetch_related("recent_orders")      # Executed in a single IN (...) query
)[:100]

python
#  Eager loading in SQLAlchemy
stmt = (
    select(User)
    .options(joinedload(User.profile), selectinload(User.orders))
    .where(User.is_active == True)
    .limit(100)
)
results = await session.scalars(stmt)

4. Scaling Connection Saturation with PgBouncer

• PostgreSQL spends 60% of CPU time on context switching and memory allocation instead of executing queries.
• Memory consumption spikes, risking OS Out-Of-Memory (OOM-killer) shutdowns.

[1,000 Web Workers / Lambda Functions]
                 │
                 ▼
      [ PgBouncer Pooler ] (Maintains 50 persistent connections)
                 │
                 ▼
       [ PostgreSQL Database ]

Deploying PgBouncer in Transaction Pooling Mode

ini
; pgbouncer.ini
[databases]
app_db = host=127.0.0.1 port=5432 dbname=app_db

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 40
min_pool_size = 10
reserve_pool_size = 5

Share this article

Comments