PostgreSQL Best Practices for Node.js

Connection pooling is not optional

Postgres creates a process per connection. 200 connections = 200 processes. Without pooling, your Node app will open more connections than Postgres can efficiently handle. Use PgBouncer for production, or configure your ORM's pool:

// Prisma datasource pool
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  connectionLimit = 10  // per process
}

// pg (node-postgres)
const pool = new Pool({
  max: 10,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

Index what you query

A missing index on a frequently queried column is the most common Postgres performance issue. Rule: add an index for every column that appears in a WHERE clause with cardinality > 100 rows, and every foreign key column.

-- Find slow queries (enable pg_stat_statements first)
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Add index for common query pattern
CREATE INDEX CONCURRENTLY idx_projects_user_created
ON projects(user_id, created_at DESC);

-- For full-text search
CREATE INDEX CONCURRENTLY idx_projects_name_search
ON projects USING gin(to_tsvector('english', name));

EXPLAIN ANALYZE before every optimization

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.*, u.email
FROM projects p
JOIN users u ON p.user_id = u.id
WHERE p.status = 'active'
ORDER BY p.created_at DESC
LIMIT 20;

-- Look for:
-- Seq Scan on large tables (add an index)
-- Hash Join with large hash batches (memory pressure)
-- High actual_rows vs estimated_rows (stale statistics — run ANALYZE)

Use transactions correctly

// Wrap related mutations in a transaction
const result = await prisma.$transaction(async (tx) => {
  const project = await tx.project.create({ data: projectData });
  await tx.billingAccount.update({
    where: { orgId: project.orgId },
    data: { projectCount: { increment: 1 } },
  });
  return project;
});

// Use READ COMMITTED for most reads (default)
// Use REPEATABLE READ for reports/analytics that must be consistent
// Use SERIALIZABLE only when truly needed — it's expensive

Postgres features worth using

JSONB columns — Store flexible schema data with full indexing support. Better than a separate NoSQL database for semi-structured data.

Generated columns — Computed columns stored on disk. Great for full-text search vectors.

Table partitioning — Partition large tables by date for dramatically faster time-range queries and easy data archival.

pg_cron — Schedule database jobs directly in Postgres. Useful for data cleanup and aggregation.

Advisory locks — Distributed locks stored in Postgres. Use for long-running jobs where you want exactly-once execution across replicas.

VACUUM and ANALYZE

Postgres's MVCC model means deleted/updated rows aren't immediately removed — they're dead tuples that VACUUM reclaims. Autovacuum handles this automatically, but for high-write tables, tune autovacuum aggressiveness or run VACUUM ANALYZE manually after large data imports.

Read replicas

For read-heavy workloads, direct analytics and reporting queries to a read replica. Don't let long-running OLAP queries compete with your OLTP traffic on the primary.

const primaryDb = new Pool({ connectionString: process.env.DATABASE_PRIMARY_URL });
const replicaDb = new Pool({ connectionString: process.env.DATABASE_REPLICA_URL });

// Route reads to replica, writes to primary
const db = {
  query: (sql, params) => primaryDb.query(sql, params),
  readQuery: (sql, params) => replicaDb.query(sql, params),
};