Postgres at 100k Queries/sec: Connection Pooling, pgvector & EXPLAIN Hacks

Postgres at Scale: Connection Pooling with PgBouncer, Vector Indexing with pgvector, and Query Optimization

PostgreSQL is the foundational data backbone for the modern technology stack. Its exceptional reliability, rich JSONB support, and extensible ecosystem have made it the default choice for startups and Fortune 500 enterprises alike.

However, as applications scale past tens of millions of records and migrate toward ephemeral serverless runtimes (Next.js, AWS Lambda, Cloudflare Workers), unoptimized PostgreSQL deployments hit steep bottlenecks:

1. Connection Starvation: Each Postgres connection forks a dedicated backend process consuming 5–10 MB of RAM. 500 serverless Lambdas will quickly exhaust database memory and crash the server.
2. Sequential Scans & Stale Statistics: Queries slow to a crawl as data sizes outgrow working memory (work_mem).
3. High-Dimensional Vector Search Latency: Inefficient vector indexing for Retrieval-Augmented Generation (RAG) models brings query times to several seconds.

This guide explores the architectural blueprints and low-level tuning required to scale PostgreSQL to millions of requests with sub-10ms response times.

1. Architectural Anatomy: Why Serverless Kills Vanilla Postgres

PostgreSQL was designed in an era of persistent application servers where long-running processes (e.g., monoliths in Java or Ruby) maintained static pools of 20–50 connections.

In modern edge and serverless architectures:

• Hundreds of stateless Lambda or Vercel functions spawn concurrently.
• Each function attempts to open its own direct TCP connection to port 5432.
• When PostgreSQL reaches max_connections (e.g., 200–500), it rejects new connections with: FATAL: sorry, too many clients already.

Serverless Functions (500 Instances)
  ├── Lambda 1 ──► [Direct TCP Connection] ┐
  ├── Lambda 2 ──► [Direct TCP Connection] ┼──► [PostgreSQL: Process Starvation / Crash]
  └── Lambda N ──► [Direct TCP Connection] ┘

The Solution: Transaction-Level Connection Pooling with PgBouncer

PgBouncer sits as a lightweight proxy between your application tier and the PostgreSQL server. Instead of creating a new backend process for every client, PgBouncer maintains a lean pool of persistent database connections (e.g., 25–50) and multiplexes thousands of incoming client requests through them.

Serverless Tier (5,000 Concurrent Connections)
       │
       ▼
[PgBouncer Proxy (Transaction Pooling Mode)]
       │ (Multiplexes down to 30 persistent connections)
       ▼
[PostgreSQL Primary Database Engine]

Critical Configuration: pgbouncer.ini

[databases]
production_db = host=127.0.0.1 port=5432 dbname=app_prod auth_user=postgres

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# POOL MODE: 'transaction' is critical for serverless scale
pool_mode = transaction

# Connection limits
max_client_conn = 10000
default_pool_size = 40
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 5.0
max_db_connections = 60

# Timeouts
server_idle_timeout = 600
client_idle_timeout = 120
query_timeout = 30

Warning for Transaction Pooling: In pool_mode = transaction, features that depend on session-level state—such as LISTEN/NOTIFY, session-level SET commands, and prepared statements without named caches—are reset between queries. If using Prisma, ensure you connect via the PgBouncer-specific port with ?pgbouncer=true.

2. Advanced Query Optimization: Dissecting EXPLAIN (ANALYZE, BUFFERS)

When a query is slow, running EXPLAIN alone only shows the query planner's hypothetical estimation. You must run EXPLAIN (ANALYZE, BUFFERS) to observe actual disk I/O, cache hit ratios, and execution times:

EXPLAIN (ANALYZE, BUFFERS, TIMING)
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'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 50;

Analyzing the Output:

Limit  (cost=12540.22..12540.35 rows=50 width=48) (actual time=84.321..84.335 rows=50 loops=1)
  Buffers: shared hit=412 read=1892 dirtied=12
  ->  Sort  (cost=12540.22..12585.12 rows=17960 width=48) (actual time=84.318..84.324 rows=50 loops=1)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  HashAggregate  (cost=11420.10..11689.50 rows=17960 width=48) (actual time=72.102..78.431 rows=18450 loops=1)
              Buffers: shared hit=412 read=1892
              ->  Hash Right Join  (cost=1204.30..9870.20 rows=103326 width=24) (actual time=12.430..54.120 rows=98432 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    ->  Seq Scan on orders o  (cost=0.00..6420.30 rows=184320 width=20) (actual time=0.012..21.432 rows=184320 loops=1)
                          Buffers: shared hit=210 read=1450

What to Look For:

1. Buffers: shared read: High values mean pages had to be fetched from physical disk (NVMe SSD). Aim for shared hit (data retrieved from memory/cache).
2. Seq Scan: Indicates PostgreSQL had to scan every single table row sequentially because no applicable index existed.
3. Sort Method: external merge Disk: Indicates that the query exceeded work_mem and had to spill sorting data to temporary files on disk. Increase work_mem for the query session:
   sql

   SET work_mem = '64MB';

3. High-Performance Indexing Strategies

Multi-Column Composite Indexing with Equality-Range Ordering

When queries filter on multiple columns, column order in composite indexes matters immensely. Follow the Equality First, Range Second principle:

-- Query:
SELECT * FROM audit_logs
WHERE organization_id = 'org_987'
  AND status = 'FAILED'
  AND created_at >= NOW() - INTERVAL '7 days';

-- Optimal Index: Equality columns FIRST, range column LAST
CREATE INDEX idx_audit_org_status_date
ON audit_logs (organization_id, status, created_at DESC);

Partial Indexes for High-Skew Workflows

If 95% of orders are COMPLETED and only 5% require active operational inspection (PENDING, DISPUTED), indexing the entire table wastes RAM and write performance. Create a Partial Index:

-- Zero storage overhead for the 95% inactive rows
CREATE INDEX idx_orders_unresolved
ON orders (created_at ASC)
WHERE status IN ('PENDING', 'DISPUTED');

4. Vector Search Tuning with pgvector: HNSW vs. IVFFlat

Retrieval-Augmented Generation (RAG) and semantic recommendation engines rely on pgvector for embedding similarity search. Choosing the correct index architecture determines whether search latency is 5ms or 800ms.

1. IVFFlat (Inverted File Flat):
   - Clusters vectors into Voronoi cells.
   - Fast build time, low memory footprint.
   - Sub-optimal recall accuracy under high-dimensional (1536-dim) models.

2. HNSW (Hierarchical Navigable Small World):
   - Constructs a multi-layer geometric graph.
   - Unmatched query throughput (10x faster than IVFFlat).
   - Higher memory usage during index construction.

Creating an Optimized HNSW Index in pgvector:

-- Enable the vector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create table with 1536-dimensional embeddings (e.g., text-embedding-3-small)
CREATE TABLE document_embeddings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID NOT NULL,
  content TEXT NOT NULL,
  embedding vector(1536) NOT NULL
);

-- Build HNSW index utilizing Cosine Distance (vector_cosine_ops)
CREATE INDEX idx_document_embedding_hnsw
ON document_embeddings
USING hnsw (embedding vector_cosine_ops)
WITH (
  m = 16,               -- Max bidirectional links per node (16-32 is optimal)
  ef_construction = 64  -- Dynamic candidate list size during graph build
);

Production Vector Query with Cosine Similarity:

-- Set ef_search for runtime query accuracy tuning (higher = better recall, slightly slower)
SET hnsw.ef_search = 40;

SELECT
  id,
  content,
  1 - (embedding <=> '[0.012, -0.043, ...]'::vector) AS cosine_similarity
FROM document_embeddings
ORDER BY embedding <=> '[0.012, -0.043, ...]'::vector
LIMIT 5;

Benchmark: Direct Postgres vs. PgBouncer + HNSW Tuning

To test resilience, we ran a synthetic concurrent workload of 2,000 serverless clients executing concurrent reads and vector searches:

Frequently Asked Questions (FAQ)

What is the difference between AWS RDS Proxy and PgBouncer?

AWS RDS Proxy is a fully managed connection pooler tightly integrated with AWS IAM and Secrets Manager. Under the hood, it fulfills the same core role as PgBouncer. Self-hosted PgBouncer provides lower latency, zero AWS lock-in, and granular configuration controls at lower cost.

How often should I run VACUUM ANALYZE?

PostgreSQL's built-in autovacuum daemon handles dead tuple cleanup in 99% of cases. However, after bulk database migrations or importing millions of records, manually running VACUUM (ANALYZE, VERBOSE) immediately updates query planner statistics, preventing sub-optimal sequential scan choices.

How much shared_buffers should I allocate to PostgreSQL?

As a rule of thumb for dedicated database servers, allocate 25% of total system RAM to shared_buffers. The remaining 75% is used by the operating system page cache and per-query work_mem.

Key Takeaways

1. Never connect serverless runtimes directly to Postgres: Deploy PgBouncer in transaction mode to protect connection limits and memory.
2. Always profile with EXPLAIN (ANALYZE, BUFFERS): Target shared hit and eliminate disk spill sorts.
3. Use HNSW for vector workloads: HNSW delivers order-of-magnitude faster vector searches for modern AI and RAG applications.