I run a lot of Node.js applications backed by PostgreSQL. Most of them started fast. Then traffic grew, dashboards slowed down, and suddenly a query that used to take 200ms was hanging at 5 seconds. I've been there.
PostgreSQL is powerful, but it doesn't optimize itself. If you're building a SaaS product or any data-heavy Node.js app, you need to understand how Postgres handles your queries, manages connections, and uses indexes. This guide walks through everything I've learned optimizing production databases â from connection pooling to query rewrites to monitoring setups that catch problems before users do.
If you're running Postgres on a budget VPS (like the 2GB DigitalOcean droplets I use in Dhaka), this matters even more. Memory constraints amplify bad query patterns. I've avoided multiple VPS upgrades just by tuning Postgres correctly.
Understanding PostgreSQL Performance Bottlenecks
Postgres performance breaks down into a few core bottlenecks.
Query execution time. Slow queries usually mean sequential scans instead of index usage, or inefficient joins. You see this when a single request hangs.
Connection overhead. Opening a new Postgres connection takes 1-3ms. At 50 connections per second, that's 50-150ms of pure overhead. Without connection pooling, your database spends more time on handshakes than queries.
Index usage and table scans. If Postgres can't find a matching index, it scans the entire table. On a 10-million-row table, that's a disaster.
Memory and disk I/O. Postgres caches data in shared_buffers. If your working set doesn't fit, Postgres hits disk for every query. On a 2GB VPS, this happens fast. Disk I/O is 100x slower than memory.
Lock contention. Concurrent writes to the same rows cause lock waits. Common in high-write workloads like real-time dashboards.
The fix depends on the bottleneck. I usually start with connection pooling and query optimization because they're the easiest wins. Database optimization is just one part of overall Node.js performance â but in my experience, it's often the highest-impact lever when your app slows down under load.
Connection Pooling in Node.js
Connection pooling is the highest-leverage optimization for Node.js + Postgres. Without it, every request opens a new connection, waits 1-3ms for handshake, runs the query, then closes. With pooling, you reuse a fixed number of connections across all requests.
A REST API handling 100 req/sec without pooling means 100-300ms of connection overhead per second. With a 10-connection pool, that overhead drops to zero.
Configuring pg (node-postgres)
const { Pool } = require('pg');
const pool = new Pool({
host: process.env.DB_HOST,
database: process.env.DB_NAME,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
max: 20, // Maximum pool size
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
module.exports = { pool };
Pool sizing: I use max: 20 for most apps. The formula is (core_count à 2) + effective_spindle_count. On a 2-core VPS, that's minimum 5 connections. I bump to 10-20 based on concurrency. Too low and requests queue; too high and you overwhelm Postgres.
Prisma Connection Pooling
Prisma handles pooling internally. Default connection_limit is 10, which works for most apps. Add it to your DATABASE_URL:
postgresql://user:password@host:5432/dbname?connection_limit=10&pool_timeout=20
For serverless (Lambda), use Prisma Data Proxy or PgBouncer to avoid opening connections on every cold start.
PgBouncer for External Pooling
For high-traffic or serverless apps, I use PgBouncer between the app and Postgres. It multiplexes client connections onto a fixed pool of Postgres connections. I set pool_mode = transaction to release connections after each transaction instead of holding them for the full session.
Connection Leak Detection
Leaks happen when code forgets to release connections. Monitor with:
setInterval(() => {
console.log('Pool:', {
total: pool.totalCount,
idle: pool.idleCount,
waiting: pool.waitingCount,
});
}, 10000);
If waiting climbs or idle stays at zero, look for queries that throw errors without releasing, or uncommitted transactions.
Query Optimization Fundamentals
Most slow queries come down to one thing: Postgres is scanning the entire table instead of using an index. The fix is either adding an index or rewriting the query to use an existing one.
EXPLAIN ANALYZE: Your Best Friend
EXPLAIN ANALYZE shows you exactly what Postgres is doing for a query. Here's an example from a slow dashboard query I optimized last month:
EXPLAIN ANALYZE
SELECT 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 > '2025-01-01'
GROUP BY u.email
ORDER BY order_count DESC
LIMIT 20;
The output:
Seq Scan on users u (cost=0.00..2845.00 rows=5000 width=45) (actual time=0.045..4832.123 rows=4823 loops=1)
Filter: (created_at > '2025-01-01'::date)
Rows Removed by Filter: 45177
Hash Join (cost=120.00..3456.78 rows=5000 width=53) (actual time=245.123..4987.456 rows=4823 loops=1)
...
Planning Time: 2.456 ms
Execution Time: 5023.789 ms
Key things I look for:
- Seq Scan â means it's scanning the entire table. If you see this on a large table, you need an index.
- Rows Removed by Filter â means it scanned 45,177 rows and threw most of them away. Wasteful.
- Execution Time â 5 seconds. Unacceptable for a dashboard.
The fix was adding an index on users.created_at:
CREATE INDEX idx_users_created_at ON users(created_at);
After the index, the same query dropped to 150ms. The EXPLAIN ANALYZE output changed to:
Index Scan using idx_users_created_at on users u (cost=0.42..234.56 rows=4823 width=45) (actual time=0.023..85.234 rows=4823 loops=1)
Index Cond: (created_at > '2025-01-01'::date)
No more sequential scan. Postgres goes straight to the rows it needs using the index.
Index Strategies
B-tree (default): For equality and range queries (=, <, >, BETWEEN). Most common.
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);
GIN: For full-text search, JSONB queries, and arrays.
CREATE INDEX idx_products_tags ON products USING GIN(tags);
When NOT to index: Indexes slow down writes and take disk space. Skip them on write-heavy tables or low-cardinality columns (booleans).
Query Rewriting Patterns
Use specific columns instead of SELECT *: Fetching unused columns wastes bandwidth, especially on wide tables.
// Bad: SELECT *
// Good:
const users = await pool.query('SELECT id, email, name FROM users WHERE id = $1', [userId]);
Avoid OR across different columns: Postgres can only use one index per table. Rewrite as UNION:
SELECT * FROM users WHERE email = 'asif@example.com'
UNION
SELECT * FROM users WHERE username = 'asif';
Always LIMIT result sets: Use cursor-based pagination with indexed columns when possible.
N+1 Query Detection and Fixes
N+1: fetch a list, then loop and query each item separately. With 100 users, that's 101 queries instead of 1.
// N+1 problem: 101 queries
const users = await prisma.user.findMany();
for (const user of users) {
const orders = await prisma.order.findMany({ where: { userId: user.id } });
}
// Fixed: 1 query
const users = await prisma.user.findMany({
include: { orders: true },
});
Prisma-Specific Optimizations
Prisma makes database access easier but hides performance footguns.
Relation Loading Strategies
Use include for eager loading when you know you need related data. If you only need a count, use _count:
const users = await prisma.user.findMany({
select: {
id: true,
email: true,
_count: { select: { orders: true } },
},
});
This runs a COUNT subquery instead of fetching all orders.
Select Field Optimization
Prisma fetches all fields by default. Use select to fetch only what you need:
const users = await prisma.user.findMany({
select: { id: true, email: true },
});
Matters on tables with large text or JSONB columns.
Raw Queries When Needed
For complex aggregations, use $queryRaw:
const result = await prisma.$queryRaw`
SELECT DATE(created_at) as date, COUNT(*) as count
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at)
`;
Batch Operations
Use createMany for bulk inserts. It's 10-50x faster than looping individual creates:
await prisma.user.createMany({ data: users });
Database Configuration Tuning
Out-of-the-box Postgres is configured for a server with 128MB of RAM. If you're running on a modern VPS (especially in a Docker container), you need to tune postgresql.conf to actually use your available memory.
Key Settings for a 2GB VPS
These are the settings I use on a DigitalOcean droplet with 2GB RAM:
# /etc/postgresql/14/main/postgresql.conf
# Memory
shared_buffers = 512MB # 25% of RAM
effective_cache_size = 1536MB # 75% of RAM
work_mem = 16MB # Per-query sort/hash memory
maintenance_work_mem = 128MB # For VACUUM, CREATE INDEX
# Checkpoints
checkpoint_completion_target = 0.9
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB
# Connections
max_connections = 100
# Query Planner
random_page_cost = 1.1 # Lower for SSD (default is 4.0 for spinning disks)
effective_io_concurrency = 200 # Higher for SSD
shared_buffers. This is how much RAM Postgres uses to cache data. The rule of thumb is 25% of total RAM. On a 2GB VPS, that's 512MB. Going higher doesn't always help because the OS also caches files, and you want to leave room for that.
effective_cache_size. This tells the query planner how much memory is available for caching (both Postgres's shared_buffers and the OS page cache). Set this to 75% of RAM. It doesn't actually allocate memory; it just influences the planner's decisions.
work_mem. This is the amount of memory each query operation (like a sort or hash join) can use before spilling to disk. I set this to 16MB. If you have queries doing large sorts, you can bump this, but be careful: if you have 10 concurrent queries, they could use 10 Ã work_mem, so don't set it too high.
random_page_cost. This tells Postgres how expensive it is to fetch a random page from disk. The default is 4.0, which assumes spinning hard drives. On SSD, random access is much faster, so I set this to 1.1. This makes Postgres more likely to choose index scans over sequential scans.
After changing these settings, reload Postgres:
sudo systemctl reload postgresql
Checkpoint and WAL Tuning
Postgres writes changes to the Write-Ahead Log (WAL) before committing. Checkpoints flush WAL to disk. I set checkpoint_completion_target = 0.9 to spread checkpoint writes over 90% of the interval, smoothing I/O spikes.
Autovacuum Configuration
For high-write tables, make autovacuum run more frequently:
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);
This triggers when 5% of the table changes instead of the default 20%.
Monitoring and Diagnostics
You can't optimize what you don't measure.
pg_stat_statements Setup
Enable this extension to track query execution stats:
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
After restart:
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Slow Query Logging
Log queries slower than 500ms:
log_min_duration_statement = 500
Connection and Lock Monitoring
Check active connections:
SELECT pid, usename, state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle';
If you see many idle in transaction connections, that's a leak or uncommitted transactions. For lock contention, query pg_locks joined with pg_stat_activity to see which queries are blocking others.
Production Case Study
This is a real optimization I did last quarter. Names and numbers are slightly fictionalized, but the problem and solution are accurate.
Baseline: Slow Dashboard Query (5s)
I was building a SaaS dashboard that showed recent user activity. The query looked like this:
const activities = await prisma.activity.findMany({
where: { createdAt: { gte: thirtyDaysAgo } },
include: { user: true },
orderBy: { createdAt: 'desc' },
take: 50,
});
When the activity table hit 500,000 rows, this query slowed to 5 seconds. Users complained that the dashboard was "broken."
EXPLAIN ANALYZE Output
I ran EXPLAIN ANALYZE on the generated SQL:
EXPLAIN ANALYZE
SELECT a.*, u.*
FROM activity a
LEFT JOIN users u ON u.id = a.user_id
WHERE a.created_at >= '2026-04-08'
ORDER BY a.created_at DESC
LIMIT 50;
The output showed a sequential scan on activity:
Seq Scan on activity a (cost=0.00..8234.56 rows=12345 width=120) (actual time=0.045..4823.123 rows=12234 loops=1)
Filter: (created_at >= '2026-04-08'::date)
Rows Removed by Filter: 487766
Sort (cost=8456.78..8489.12 rows=12345 width=140) (actual time=4987.234..4989.456 rows=50 loops=1)
Sort Key: created_at DESC
...
Execution Time: 5012.789 ms
Postgres was scanning all 500,000 rows, filtering down to 12,000, then sorting them to get the top 50. Disaster.
Applied Optimizations
1. Added an index on created_at:
CREATE INDEX idx_activity_created_at ON activity(created_at DESC);
The DESC keyword tells Postgres to store the index in descending order, which matches the ORDER BY clause. After this, the query dropped to 1.2 seconds.
2. Optimized the Prisma query to only fetch needed fields:
const activities = await prisma.activity.findMany({
where: { createdAt: { gte: thirtyDaysAgo } },
select: {
id: true,
type: true,
createdAt: true,
user: { select: { id: true, email: true, name: true } },
},
orderBy: { createdAt: 'desc' },
take: 50,
});
This cut data transfer and dropped the query to 600ms.
3. Increased connection pool size from 5 to 20.
Under load, requests were queuing up waiting for a free connection. Bumping the pool size eliminated the wait time. Query time stayed at 600ms, but the P99 latency (99th percentile) dropped from 2 seconds to 650ms because requests stopped queuing.
4. Enabled connection pooling with PgBouncer.
The app was deployed on AWS Lambda, which opens a new connection on every cold start. I added PgBouncer in front of Postgres to multiplex Lambda connections. This dropped connection overhead from 50ms per request to near-zero.
After: Query Time Reduced to 150ms
Final EXPLAIN ANALYZE:
Index Scan Backward using idx_activity_created_at on activity a (cost=0.42..145.67 rows=50 width=120) (actual time=0.023..78.234 rows=50 loops=1)
Index Cond: (created_at >= '2026-04-08'::date)
Nested Loop Left Join (cost=0.85..189.45 rows=50 width=140) (actual time=0.045..125.678 rows=50 loops=1)
...
Execution Time: 148.234 ms
Query time dropped from 5 seconds to 150ms. The dashboard felt instant again.
Cost Impact: Avoided VPS Upgrade
Before optimization, I was planning to upgrade from a $24/month 2GB VPS to a $48/month 4GB instance. After tuning, the 2GB instance handled 3x more traffic without breaking a sweat. Saved $24/month, or $288/year.
That's the return on learning query optimization.
Performance Checklist
Here's the checklist I run through on every production Postgres setup:
Pre-Production Audit
- Connection pooling enabled (pg pool, Prisma pool, or PgBouncer)
- Pool size set to
(core_count à 2) + 1or higher based on concurrency -
shared_buffersset to 25% of RAM -
effective_cache_sizeset to 75% of RAM -
random_page_costset to 1.1 for SSD -
work_memset to 16MB or higher for sort-heavy queries -
pg_stat_statementsextension enabled - Slow query logging enabled (500ms threshold)
Index Coverage Analysis
- All foreign keys have indexes (e.g.,
orders.user_id) - Commonly filtered columns have indexes (e.g.,
created_at,status) - Full-text search fields use GIN indexes
- JSONB query fields use GIN indexes
- No unused indexes (check with
pg_stat_user_indexes)
Connection Pool Health Checks
- Monitor pool utilization (total, idle, waiting connections)
- Set up alerts for
waiting > 5(connection starvation) - Check for connection leaks (idle connections that never close)
Monitoring Setup
-
pg_stat_statementsqueries reviewed weekly - Slow query logs monitored (or forwarded to log aggregator)
- Connection count tracked (with alerts for >80% of
max_connections) - Cache hit ratio tracked (should be >99%)
- Lock contention monitored with
pg_locksqueries
Backup Performance Considerations
-
pg_dumpruns during low-traffic windows - Backups don't block writes (use
--no-acl --no-ownerfor faster restores) - WAL archiving enabled for point-in-time recovery
If you check off everything on this list, your Postgres setup is production-ready.
Tested environment: Node.js 20 LTS, PostgreSQL 14.x, Docker 24.x on Ubuntu 22.04 LTS.
This is the workflow I use on every Node.js + Postgres project. Connection pooling, query optimization, and monitoring aren't optional if you're building for production. I learned most of this the hard way, debugging slow queries at 2am when a dashboard hit the front page of Hacker News.
If you're deploying Node.js apps with Docker, check out my guide on Deploying Node.js Apps with Docker and Nginx on a VPS â it covers the full production setup including Postgres in Docker. And if you're building a SaaS product on a budget, the techniques here will save you from costly VPS upgrades and keep your app fast as you scale.