A query that feels instant with 500 rows can crawl once a table hits a few million. Here's how I actually go about diagnosing and fixing that in PostgreSQL.
1. Analyzing Query Execution with EXPLAIN ANALYZE
Before adding indexes, it is vital to know how PostgreSQL reads your tables. Running a query with EXPLAIN ANALYZE displays the execution planner output:
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'bablusingh.dev@zohomail.in';
Look closely for Seq Scan (Sequential Scan), which indicates a full table scan. Our goal is to achieve Index Scan or Index Only Scan.
2. Choosing the Right Index Type
PostgreSQL offers several index types:
B-Tree: The default. Perfect for equality and range queries (
<, <=, =, >=, >).Hash: Only handles simple equality operators.
GIN (Generalized Inverted Index): Essential for searching arrays or JSONB columns.
To index our email column:
CREATE UNIQUE INDEX idx_users_email ON users(email);
3. Connection Pooling: pg-pool vs. PgBouncer
Every client connection to PostgreSQL spins up a separate backend OS process. Under heavy load, setting up and tearing down TCP connections drains system resources.
pg-pool: Useful for client-side pooling within your Node.js application process.
PgBouncer: A lightweight connection pooler that sits between Node.js and PostgreSQL, saving massive quantities of RAM by reuse.
Rule of thumb: If your application scales past 50 containerized instances, use PgBouncer to manage the connection limits of your DB instance.
