PostgreSQL is the database most Node.js backends should default to. It handles relational data, JSON documents, full-text search, geospatial queries, and concurrent writes without breaking a sweat. But connecting Node.js to Postgres the right way requires more than pg.query(). Connection pooling, migrations, transactions, parameterized queries, and indexing strategy are the difference between a prototype that works and a production system that stays fast under load.
Connecting with a pool, not a client
A single database connection handles one query at a time. Under concurrent requests, everything queues behind a single bottleneck. A connection pool maintains a set of reusable connections and hands them out as needed.
import { Pool } from 'pg';
export const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 5000,
});export async function findUserByEmail(email: string) {
const result = await pool.query('SELECT id, name, email, role FROM users WHERE email = $1', [
email,
]);
return result.rows[0] ?? null;
}The max: 20 setting means up to 20 concurrent queries can run without waiting. idleTimeoutMillis closes connections that sit unused, preventing resource leaks. connectionTimeoutMillis fails fast instead of hanging when the database is unreachable. Always use parameterized queries ($1, $2) instead of string interpolation to prevent SQL injection.
Migrations: version-controlled schema changes
Schema changes should never be applied by hand. Migrations track every CREATE TABLE, ALTER COLUMN, and CREATE INDEX as versioned files that run in order and can be rolled back.
import { Pool } from 'pg';
export async function up(pool: Pool) {
await pool.query(`
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
role VARCHAR(50) NOT NULL DEFAULT 'user',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
)
`);
}
export async function down(pool: Pool) {
await pool.query('DROP TABLE IF EXISTS users');
}import { Pool } from 'pg';
export async function up(pool: Pool) {
await pool.query(`
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
total_cents INTEGER NOT NULL,
status VARCHAR(50) NOT NULL DEFAULT 'pending',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
)
`);
await pool.query(`
CREATE INDEX idx_orders_user_id ON orders(user_id)
`);
}
export async function down(pool: Pool) {
await pool.query('DROP TABLE IF EXISTS orders');
}Every migration has an up and a down. The runner tracks which migrations have been applied in a migrations table and only executes new ones. Adding a column, renaming a table, creating an index, all versioned and reproducible across every environment.
Transactions: all or nothing
When multiple queries must succeed together, a transaction ensures atomicity. If any step fails, everything rolls back and the database stays consistent.
export async function createOrder(userId: string, items: CartItem[]) {
const client = await pool.connect();
try {
await client.query('BEGIN');
const orderResult = await client.query(
`INSERT INTO orders (user_id, total_cents, status)
VALUES ($1, $2, 'confirmed')
RETURNING id`,
[userId, calculateTotal(items)]
);
const orderId = orderResult.rows[0].id;
for (const item of items) {
await client.query(
`INSERT INTO order_items (order_id, product_id, quantity, price_cents)
VALUES ($1, $2, $3, $4)`,
[orderId, item.productId, item.quantity, item.priceCents]
);
await client.query(`UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1`, [
item.quantity,
item.productId,
]);
}
await client.query('COMMIT');
return orderId;
} catch (error) {
await client.query('ROLLBACK');
throw error;
} finally {
client.release();
}
}The client.release() in finally is critical. Without it, the connection stays checked out of the pool forever, eventually exhausting all available connections. The transaction guarantees that if stock deduction fails, the order and its items are not created either.
A helper function removes the boilerplate from every transaction.
export async function withTransaction<T>(fn: (client: PoolClient) => Promise<T>): Promise<T> {
const client = await pool.connect();
try {
await client.query('BEGIN');
const result = await fn(client);
await client.query('COMMIT');
return result;
} catch (error) {
await client.query('ROLLBACK');
throw error;
} finally {
client.release();
}
}export async function createOrder(userId: string, items: CartItem[]) {
return withTransaction(async (client) => {
const { rows } = await client.query(
`INSERT INTO orders (user_id, total_cents) VALUES ($1, $2) RETURNING id`,
[userId, calculateTotal(items)]
);
// ... rest of the logic
return rows[0].id;
});
}Indexing: the single biggest performance lever
A query without a matching index scans every row in the table. On 10,000 rows, nobody notices. On 10 million, the query takes seconds instead of milliseconds. Indexes solve this, but the wrong indexes waste disk space and slow down writes.
-- Exact match lookups
CREATE INDEX idx_users_email ON users(email);
-- Range queries and sorting
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
-- Composite index for filtered + sorted queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Partial index: only index what matters
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';The composite index on (user_id, status) serves queries that filter by user and status, or filter by user alone. It does not help queries that filter by status alone, because composite indexes work left to right.
Partial indexes are a PostgreSQL specialty. The pending orders index is smaller and faster than a full index because it only includes rows matching the condition. If 95% of orders are completed, this index is 20x smaller than a full one.
Use EXPLAIN ANALYZE to verify that queries actually use your indexes.
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = '...' AND status = 'pending'
ORDER BY created_at DESC LIMIT 10;The output shows whether PostgreSQL chose an index scan or a sequential scan, how many rows it examined, and how long each step took. If you see Seq Scan on a large table, you’re missing an index.
JSON columns: the best of both worlds
PostgreSQL’s jsonb type lets you store flexible data alongside structured columns. It supports indexing, querying, and partial updates without sacrificing the relational model.
await pool.query(
`INSERT INTO products (name, price_cents, metadata)
VALUES ($1, $2, $3)`,
[
'Wireless Keyboard',
7999,
JSON.stringify({
brand: 'Acme',
weight: '450g',
connectivity: ['bluetooth', 'usb-c'],
}),
]
);-- Query inside JSON
SELECT * FROM products
WHERE metadata->>'brand' = 'Acme';
-- Filter on nested array
SELECT * FROM products
WHERE metadata->'connectivity' ? 'bluetooth';
-- Index JSON fields for performance
CREATE INDEX idx_products_brand ON products ((metadata->>'brand'));The jsonb column handles product attributes that vary by category without requiring a separate attributes table or an EAV pattern. Keyboards have connectivity and weight. T-shirts have size and color. The structured columns (name, price_cents) stay relational and indexed, while the flexible data lives in JSON.
Full-text search without Elasticsearch
PostgreSQL has built-in full-text search that handles most use cases without adding another service to your stack.
ALTER TABLE products ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
setweight(to_tsvector('english', coalesce(description, '')), 'B')
) STORED;
CREATE INDEX idx_products_search ON products USING GIN(search_vector);export async function searchProducts(query: string) {
const result = await pool.query(
`SELECT id, name, description,
ts_rank(search_vector, websearch_to_tsquery('english', $1)) AS rank
FROM products
WHERE search_vector @@ websearch_to_tsquery('english', $1)
ORDER BY rank DESC
LIMIT 20`,
[query]
);
return result.rows;
}The generated column automatically maintains the search vector when data changes. setweight gives the product name higher relevance than the description. websearch_to_tsquery parses natural search queries like “wireless keyboard bluetooth” into a proper search expression. The GIN index makes this fast even on millions of rows.
Conclusion
PostgreSQL handles far more than simple CRUD. Transactions keep data consistent. Indexes make queries fast. JSON columns add flexibility without abandoning the relational model. Full-text search eliminates an external dependency. Combined with proper connection pooling and migration discipline, PostgreSQL is the backbone that lets Node.js backends scale from prototype to production without switching databases halfway through.