SQL, indexing and PostgreSQL cheat sheet

How a B-tree index speeds up a query

A B-tree index keeps the indexed values sorted in a shallow, balanced tree of pages, each pointing to the table row's location. A lookup walks from the root through a few levels to the leaf that holds the value, then fetches the row, so cost grows with the tree's height, not the table's size. On a 1,000,000-row table in our PostgreSQL 17 run, a primary key lookup read 4 pages, while a filter on an unindexed column read all 7,353 table pages.

SET max_parallel_workers_per_gather = 0;
CREATE TABLE orders (id int PRIMARY KEY, customer_id int);
INSERT INTO orders
SELECT g, g % 1000 FROM generate_series(1, 100000) g;
VACUUM ANALYZE orders;
EXPLAIN (COSTS OFF) SELECT * FROM orders WHERE customer_id = 42;
EXPLAIN (COSTS OFF) SELECT * FROM orders WHERE id = 450;

Column order in a composite index

A composite B-tree is sorted by the first column, then by the second within each first value, like a phone book by last name then first name. It serves filters on customer_id alone, customer_id plus created_at, and WHERE customer_id = ? ORDER BY created_at with no sort. It can't seek on created_at alone. Put equality columns first and the range or sort column last.

Index-only scans and INCLUDE

If every column a query needs is in the index, PostgreSQL can answer from the index without reading table rows: an index-only scan. It still checks the visibility map, so it only skips the heap for pages VACUUM has marked all-visible. CREATE INDEX ... (customer_id) INCLUDE (total) adds payload columns to the leaf entries without making them part of the sort key, so they don't affect uniqueness or ordering.

SET max_parallel_workers_per_gather = 0;
CREATE TABLE orders (
  id int PRIMARY KEY, customer_id int, total numeric
);
INSERT INTO orders
SELECT g, g % 1000, g FROM generate_series(1, 100000) g;
CREATE INDEX orders_cust ON orders (customer_id);
VACUUM ANALYZE orders;
EXPLAIN (COSTS OFF)
SELECT customer_id, total FROM orders WHERE id = 450;
CREATE INDEX orders_cust_total ON orders (customer_id) INCLUDE (total);
VACUUM ANALYZE orders;
EXPLAIN (COSTS OFF)
SELECT customer_id, total FROM orders WHERE customer_id = 7;

Reading EXPLAIN ANALYZE

Run EXPLAIN (ANALYZE, BUFFERS) and read the plan tree from the innermost nodes outward. For each node compare estimated rows with actual rows: a big mismatch means bad statistics, and the planner picked a join or scan method for the wrong size. Then look for where the time and buffers go: a sequential scan that removes most rows, a nested loop with many loops, or a sort that spills to disk.

CREATE TABLE t (id int PRIMARY KEY, v int);
INSERT INTO t SELECT g, g % 10 FROM generate_series(1, 10000) g;
ANALYZE t;
EXPLAIN (COSTS OFF) SELECT * FROM t WHERE id = 5;

B-tree, GIN, GiST, BRIN and hash

B-tree is the default for equality, ranges and sorting. GIN indexes the elements inside a value, so it suits jsonb containment (@>), arrays and full-text search. GiST supports overlap and nearest-neighbour queries: geometry, ranges, exclusion constraints. BRIN stores a summary per block range and is tiny, which works when values follow the physical order, like timestamps in an append-only log. Hash indexes only do equality.

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
  room int NOT NULL,
  during tstzrange NOT NULL,
  EXCLUDE USING gist (room WITH =, during WITH &&)
);
INSERT INTO bookings VALUES (1, '[2026-11-01 10:00, 2026-11-01 11:00)');
INSERT INTO bookings VALUES (1, '[2026-11-01 10:30, 2026-11-01 12:00)');

Partial and expression indexes

A partial index covers only rows matching a WHERE clause, such as WHERE deleted_at IS NULL or WHERE status = 'pending', so it stays small and fast when queries only ever look at that slice. An expression index stores a computed value, like lower(email), so a query that filters on exactly that expression can use it. A partial unique index enforces rules like 'one active subscription per user'.

CREATE TABLE users (id int PRIMARY KEY, email text NOT NULL);
CREATE UNIQUE INDEX users_email_lower ON users (lower(email));
INSERT INTO users VALUES (1, 'Ana@Example.com');
INSERT INTO users VALUES (2, 'ana@example.com');

The N+1 query problem

One query fetches the parents and then a separate query runs for every parent to get its children, so 500 orders cost 501 round trips. Each query is fast, so it hides in development, but with 500 rows and 1 ms of network latency it adds half a second. Fix it by loading children for all parents in one query, with a JOIN or WHERE parent_id = ANY(%s), or with the ORM's eager loading.

Upserts with ON CONFLICT

Use INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col. The database checks the unique index and either inserts or updates atomically, so two concurrent requests can't both insert. Checking with a SELECT first and then inserting has a race: both requests see no row and one fails with a unique violation. DO NOTHING skips duplicates instead.

OFFSET vs keyset pagination

OFFSET makes the database produce and throw away every skipped row, so page 5,000 costs as much as reading 100,000 rows, and rows shift between pages when data changes. Keyset (cursor) pagination remembers the last row's sort key and asks for rows after it: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20. With a matching index each page costs the same.

NULL, three-valued logic and NOT IN

In SQL, NULL means unknown, so any comparison with it is unknown, not true or false, and WHERE keeps only true rows. Use IS NULL or IS DISTINCT FROM instead. x NOT IN (1, NULL) means x <> 1 AND x <> NULL; the second part is unknown, so the whole condition is never true and the query returns nothing. Use NOT EXISTS, which isn't affected by NULLs in the subquery.

CREATE TABLE users (id int);
CREATE TABLE banned (user_id int);
INSERT INTO users VALUES (1), (2), (3);
INSERT INTO banned VALUES (2), (NULL);
SELECT count(*) FROM users u
WHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.user_id = u.id);

Window functions

A window function computes a value across a set of rows related to the current row without collapsing them like GROUP BY does. row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) numbers each customer's orders from newest; keep rows where it's 1. Others include rank, dense_rank, lag, lead and running totals with sum(...) OVER (ORDER BY ...).

CREATE TABLE orders (id int, customer_id int, created_at date);
INSERT INTO orders VALUES
  (1, 1, '2026-10-01'), (2, 1, '2026-10-05'), (3, 2, '2026-10-03');
SELECT customer_id, id FROM (
  SELECT customer_id, id, row_number() OVER (
    PARTITION BY customer_id ORDER BY created_at DESC
  ) AS rn
  FROM orders
) t WHERE rn = 1 ORDER BY customer_id;

Normalization and when to denormalize

Normalizing (up to third normal form, roughly 'every column depends on the key, the whole key and nothing but the key') stores each fact once, so updates can't leave copies disagreeing. Denormalizing copies or precomputes data to make hot reads cheaper, at the cost of keeping copies in sync. Start normalized, measure, then denormalize specific read paths with a clear owner for the copy: a trigger, the same transaction, or an event.

Connection pooling and PgBouncer

Each PostgreSQL connection is a separate server process with its own memory, and throughput peaks at a fairly small number of active connections, roughly a few per CPU core, then falls as they compete. A pool reuses a fixed set of connections across requests. With many app instances, add PgBouncer: in transaction mode it lends a server connection only for the length of a transaction, so thousands of clients can share a few dozen.

SELECT count(*) > 0 FROM pg_stat_activity
WHERE backend_type = 'client backend';
SHOW max_connections;

Schema changes without downtime

Use expand and contract: add new things compatibly, move code and data over, then remove the old things in a later release. Know which statements take heavy locks: creating an index blocks writes unless you use CREATE INDEX CONCURRENTLY; adding a CHECK or foreign key scans the table unless you add it NOT VALID and VALIDATE it later. Always set a short lock_timeout, because even a quick ALTER queues every query behind it while it waits.

CREATE TABLE customers (id int PRIMARY KEY);
CREATE TABLE orders (id int PRIMARY KEY, customer_id int);
INSERT INTO customers VALUES (1);
INSERT INTO orders VALUES (10, 1);
SET lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);
SELECT convalidated FROM pg_constraint
WHERE conname = 'orders_customer_fk';