SQL

Deep dive

Decision table

How it works

• Parse → rewrite → plan (cost model from statistics: row counts, histograms, most-common values) → execute (iterator or vectorized operators).
• Access paths: sequential scan, B-tree index scan, index-only scan (covering index, needs visibility map), bitmap scan.
• Joins: nested loop (small outer, indexed inner), hash join (big unsorted inputs), merge join (sorted inputs).
• Concurrency: MVCC keeps old row versions so readers don't block writers; VACUUM reclaims them in Postgres.
• Composite index (a, b, c) serves filters on a, a,b, a,b,c and sorts on that prefix, not on b alone.

Code

PostgreSQL patterns.

-- Schema: constraints are cheap, bugs are not.
CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   bigint      NOT NULL,
  customer_id bigint      NOT NULL REFERENCES customers(id),
  status      text        NOT NULL CHECK (status IN ('open', 'paid', 'shipped', 'cancelled')),
  total_cents bigint      NOT NULL CHECK (total_cents >= 0),   -- money as integers
  created_at  timestamptz NOT NULL DEFAULT now()
);
-- Index matches the hot query: equality columns first, then the sort column.
CREATE INDEX orders_tenant_status_created ON orders (tenant_id, status, created_at DESC, id DESC);
CREATE INDEX orders_customer ON orders (customer_id);         -- FKs are not auto-indexed

-- Keyset pagination: O(page) instead of OFFSET's O(offset + page).
SELECT id, total_cents, created_at
FROM orders
WHERE tenant_id = $1 AND status = 'paid'
  AND (created_at, id) < ($2, $3)            -- last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Upsert.
INSERT INTO daily_revenue (tenant_id, day, cents)
VALUES ($1, $2, $3)
ON CONFLICT (tenant_id, day) DO UPDATE SET cents = daily_revenue.cents + EXCLUDED.cents;

-- Window function: running total and rank per customer, no self-join.
SELECT customer_id, id, total_cents,
       sum(total_cents) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_cents,
       rank()           OVER (PARTITION BY customer_id ORDER BY total_cents DESC) AS size_rank
FROM orders
WHERE tenant_id = $1;

-- Job queue: many workers, no double processing.
WITH next AS (
  SELECT id FROM jobs
  WHERE state = 'ready'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE jobs SET state = 'running', started_at = now()
FROM next WHERE jobs.id = next.id
RETURNING jobs.id, jobs.payload;

-- Read the plan with real timings and buffer counts.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE tenant_id = 7 AND status = 'open' ORDER BY created_at DESC LIMIT 20;

Transfer with explicit isolation (retry on serialization failure, SQLSTATE 40001):

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 500 WHERE id = 1 AND balance >= 500;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

Efficiency

• Cost: a query costs what its plan reads: an index lookup touches a few pages, a sequential scan reads the whole table, so the same query can differ by orders of magnitude (order of). Loading rows with COPY has significantly less overhead than a series of INSERTs (PostgreSQL docs). Each Postgres connection is a backend process, and max_connections sizes shared resources (PostgreSQL docs). Bound: I/O and buffer-cache hits for OLTP; memory bandwidth for analytics (order of).
• Levers:
  • an index that matches the filter and sort — turns a scan into a few page reads (order of; PostgreSQL docs on indexes) — selective filters on large tables
  • covering index with INCLUDE — index-only scan, no heap fetch when the visibility map allows (PostgreSQL docs) — hot read paths
  • COPY or multi-row INSERT — much lower per-row overhead (PostgreSQL docs, "Populating a Database") — bulk loads
  • connection pooling (PgBouncer, app pool) — fewer backend processes (PostgreSQL docs) — many short-lived clients
  • columnar vectorized engine (DuckDB, ClickHouse) — scans only needed columns in batches (DuckDB paper) — analytics over many rows
• Naive → standard → state of the art:
  • SELECT * with N+1 queries, no index · scan per row, many round trips
  • one joined query, matching composite and partial indexes, pooled connections · few page reads per request
  • OLTP on Postgres plus a columnar engine for analytics, batched writes with COPY · each workload on a suitable layout

Performance notes

• Indexes speed reads and slow writes (each index is another B-tree update); drop unused ones (pg_stat_user_indexes).
• Partial indexes (WHERE status = 'open') and covering indexes (INCLUDE (total_cents)) for hot paths.
• Batch writes: multi-row INSERT or COPY is order of 10-100x faster than row-at-a-time round trips.
• Connection pooling (PgBouncer, or the app's pool) is mandatory: each Postgres connection is a process with order of MBs of memory.
• Large tables: partition by time for retention and pruning; keep transactions short so VACUUM can work.

Pitfalls

• N+1 queries from ORMs: one query per row in a loop. Use joins or WHERE id = ANY($1).
• OFFSET 100000 pagination: scans and discards all skipped rows.
• Functions or casts on indexed columns (WHERE lower(email) = …, created_at::date = …) disable the index; use an expression index or range predicates.
• NOT IN (subquery) with a NULL in the subquery returns no rows; use NOT EXISTS.
• NULL comparisons: x = NULL is never true; use IS NULL / IS DISTINCT FROM.
• Read-modify-write in Read Committed (lost updates): use UPDATE … SET x = x + 1, SELECT … FOR UPDATE, or Serializable with retries.
• Long-running transactions (or idle-in-transaction sessions) block VACUUM and bloat tables.
• SELECT * in application queries: breaks index-only scans and couples code to schema.
• Floating-point money; timestamps without time zone.

Implementations

Sources

• PostgreSQL Global Development Group, PostgreSQL Documentation: Indexes docs, Concurrency Control docs, Using EXPLAIN docs, Populating a Database docs.
• Winand, SQL Performance Explained, 2012. site
• Berenson et al., A Critique of ANSI SQL Isolation Levels, SIGMOD 1995. paper
• Raasveldt, Mühleisen, DuckDB: an Embeddable Analytical Database, SIGMOD 2019. paper
• SQLite, Appropriate Uses For SQLite. docs