5 PostgreSQL Queries That Are Secretly Killing Your Node.js API

Every Node.js developer writes these queries. Most don't realize they're leaving 10-100x performance on the table.

1. SELECT * when you only need 3 columns

-- ❌ Transfers entire row over the wire, bloats memory
SELECT * FROM orders WHERE user_id = $1;

-- ✅ Only fetch what you render
SELECT id, status, total FROM orders WHERE user_id = $1;

PostgreSQL still reads full tuples from disk either way, but you save network bandwidth and V8 heap allocation. On a table with 30 columns and JSONB fields, this alone can cut response times by 40%.

2. Pagination with OFFSET

-- ❌ Scans and discards 10,000 rows to show you 20
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

-- ✅ Cursor-based: seeks directly using the index
SELECT * FROM products 
WHERE created_at < $1 
ORDER BY created_at DESC 
LIMIT 20;

OFFSET gets linearly slower as users page deeper. Cursor pagination stays constant-time. If your API has any list endpoint with an offset param, you have a ticking time bomb.

3. N+1 queries in loops

// ❌ 1 query per order = 100 orders = 101 queries
const orders = await db.query('SELECT * FROM orders WHERE user_id = $1', [userId]);
for (const order of orders.rows) {
  const items = await db.query('SELECT * FROM order_items WHERE order_id = $1', [order.id]);
}

// ✅ Single query with JOIN or IN clause
const result = await db.query(`
  SELECT o.*, json_agg(oi.*) as items
  FROM orders o
  LEFT JOIN order_items oi ON oi.order_id = o.id
  WHERE o.user_id = $1
  GROUP BY o.id
`, [userId]);

This is the #1 performance killer I see in Node.js APIs. One JOIN replaces 100 round trips.

4. Missing indexes on foreign keys

-- Check for unindexed foreign keys (run this on your DB right now)
SELECT 
  c.conrelid::regclass AS table_name,
  a.attname AS column_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
AND NOT EXISTS (
  SELECT 1 FROM pg_index i 
  WHERE i.indrelid = c.conrelid 
  AND a.attnum = ANY(i.indkey)
);

PostgreSQL does not auto-index foreign keys. Every JOIN and CASCADE DELETE on an unindexed FK does a sequential scan.

5. Not using connection pooling

// ❌ New connection per query (150ms+ overhead each time)
const { Client } = require('pg');
const client = new Client();
await client.connect();

// ✅ Pool manages connections for you
const { Pool } = require('pg');
const pool = new Pool({ max: 20 });
const result = await pool.query('SELECT 1');

Each new PostgreSQL connection forks a process and does a TLS handshake. A pool reuses existing connections and keeps your p99 latency tight.

---

These 5 patterns are covered in depth — with benchmarks, EXPLAIN analysis, and production-grade solutions — in the full course. We go from PostgreSQL internals all the way through building a capstone e-commerce API that handles thousands of requests per second.

QueryCraft Academy