Cheatsheets

PostgreSQL

EXPLAIN, index types, transactions & isolation, JSONB, VACUUM, and query tuning.

Visualize

Postgres — index vs sequential scan

A sequential scan checks every row (O(n)); a B-tree index takes a few guided hops (O(log n)).

Seq ScanO(n)

row 1id = 7

row 2id = 13

row 3id = 4

row 4id = 56

row 5id = 21

row 6id = 8

row 7id = 42

row 8id = 30

checked 0 of 8 rows

Index ScanO(log n)

root1 – 60

branch30 – 60

leaf30 – 49

took 0 of 3 hops

Press play — both race to find id=42.

68 entries

EXPLAIN & reading a plan7

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Show the planner's chosen plan WITHOUT running the query

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE customer_id = 42;

Actually run the query and report real time, rows, and I/O

Seq Scan on orders (cost=0.00..18584.00 rows=1000000 ...)

Sequential Scan — read every row in the table, no index used

Index Scan using idx_x on t ... Index Cond: (id = 5)

Index Scan — walk the B-tree, then fetch matching heap rows

Bitmap Heap Scan ... -> Bitmap Index Scan on idx_x

Bitmap scan — collect row locations, sort, then read heap in order

Nested Loop / Hash Join / Merge Join

The three join strategies — and when each wins

SET track_io_timing = on; -- then EXPLAIN (ANALYZE, BUFFERS)

Add real I/O timing to BUFFERS so you see disk vs cache cost

Index types10

CREATE INDEX idx ON t (col); -- B-tree (default)

B-tree — the default; equality, range, sorting, prefix LIKE

CREATE INDEX idx_doc ON t USING gin (data); -- jsonb / arrays / FTS

GIN — inverted index for containment in jsonb, arrays, and full-text

CREATE INDEX idx_geo ON t USING gist (geom); -- ranges, geometry, KNN

GiST — extensible tree for geometry, ranges, and nearest-neighbour

CREATE INDEX idx_ts ON events USING brin (created_at);

BRIN — tiny index for huge, naturally-ordered append-only tables

CREATE INDEX idx_active ON users (email) WHERE deleted_at IS NULL;

Partial index — index only the rows matching a predicate

CREATE INDEX idx_lower_email ON users (lower(email));

Expression index — index the result of a function or expression

CREATE INDEX idx_ab ON t (a, b, c); -- leftmost-prefix rule

Composite index — column order matters (leftmost prefix)

CREATE INDEX idx_cov ON orders (customer_id) INCLUDE (status, total);

Covering index (INCLUDE) — enable an index-only scan

CREATE INDEX CONCURRENTLY idx ON big_table (col);

Build an index WITHOUT locking writes — for production tables

CREATE INDEX idx_hash ON t USING hash (col);

Hash index — equality only, niche since B-tree usually wins

Query tuning recipes6

-- Make predicates sargable (Search ARGument ABLE)

Don't wrap the indexed column in a function — it disables the index

ANALYZE orders; -- refresh planner statistics

Refresh statistics so the planner estimates rows correctly

-- SELECT only the columns you need (avoid SELECT *)

Narrow column lists enable index-only scans and cut I/O

... ORDER BY id LIMIT 20 -- keyset, not OFFSET

Keyset pagination beats OFFSET on large tables

-- Add an FK column index (Postgres does NOT create one)

Foreign keys are not auto-indexed — index the referencing column

pg_stat_statements: ORDER BY total_exec_time DESC

Find the queries that actually cost the most across the whole DB

Transactions & isolation7

SHOW transaction_isolation; -- default: read committed

READ COMMITTED (default) — each statement sees the latest committed data

BEGIN ISOLATION LEVEL REPEATABLE READ;

REPEATABLE READ — one stable snapshot for the whole transaction

BEGIN ISOLATION LEVEL SERIALIZABLE;

SERIALIZABLE — as if transactions ran one at a time

SELECT ... FROM jobs
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 1;

Work-queue pattern — lock a row and let other workers skip it

SELECT ... FOR UPDATE; -- vs FOR SHARE / FOR NO KEY UPDATE

Row-level locking modes — block writers while you decide

-- Deadlock: txn A locks row 1 then 2; txn B locks 2 then 1

Deadlocks — Postgres detects and aborts one victim automatically

SAVEPOINT sp1; ... ROLLBACK TO sp1;

Savepoints — partial rollback within a transaction

MVCC & maintenance6

-- MVCC: every UPDATE/DELETE creates a dead tuple

How MVCC works — old row versions linger until VACUUM removes them

VACUUM (ANALYZE, VERBOSE) orders;

VACUUM — reclaim dead tuples and update the visibility map

-- autovacuum: the background daemon (leave it ON)

Autovacuum — automatic VACUUM/ANALYZE triggered by churn

VACUUM FULL orders; -- ACQUIRES AN EXCLUSIVE LOCK

VACUUM FULL — rewrites the table to shrink it, but locks everything

-- Bloat: dead tuples + empty space inflate table/index size

Table & index bloat — wasted space from churn and stalled VACUUM

SELECT datname, age(datfrozenxid) FROM pg_database;

Transaction-ID wraparound — the silent cluster-killer VACUUM prevents

JSONB7

data -> 'user'        -- returns jsonb
data ->> 'name'       -- returns text

-> keeps JSON, ->> extracts text — the core access operators

data #> '{a,b,0}'      -- jsonb at path
data #>> '{a,b,0}'     -- text at path

#> / #>> — extract by a path array (deep navigation)

data @> '{"paid": true}'

Containment — does the JSON contain this sub-document? (GIN-indexable)

data ? 'key' data ?| array['a','b'] data ?& array['a','b']

Key-existence operators — does this top-level key exist?

SELECT jsonb_build_object('id', id, 'name', name) FROM users;

Build JSON from rows — object / array / agg constructors

SELECT jsonb_path_query(data, '$.items[*] ? (@.qty > 1)') FROM orders;

JSONPath — query and filter inside a JSON document (SQL/JSON path)

-- JSONB column vs real columns: choose deliberately

When to use JSONB vs normalized columns

Constraints6

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Primary key — the modern IDENTITY way to auto-generate it

customer_id bigint REFERENCES customers (id) ON DELETE CASCADE

Foreign key — referential integrity, with an ON DELETE policy

email text NOT NULL UNIQUE

UNIQUE — enforce no duplicates (backed by a unique index)

CHECK (price >= 0)

CHECK — enforce a boolean invariant on each row

EXCLUDE USING gist (room_id WITH =, during WITH &&)

EXCLUSION constraint — generalize UNIQUE to 'no two rows overlap'

ALTER TABLE t ADD CONSTRAINT fk ... DEFERRABLE INITIALLY DEFERRED;

Deferrable constraints — postpone the check until COMMIT

Upsert & RETURNING4

INSERT INTO t (id, n) VALUES (1, 'a')
ON CONFLICT (id) DO UPDATE SET n = EXCLUDED.n;

Upsert — insert, or update the existing row on a unique conflict

INSERT INTO t (...) VALUES (...) ON CONFLICT DO NOTHING;

Insert-if-absent — skip silently when the row already exists

UPDATE t SET status = 'done' WHERE id = 5 RETURNING *;

RETURNING — get the affected rows back from a write, no second query

WITH moved AS (
  DELETE FROM inbox WHERE id = 5 RETURNING *
)
INSERT INTO archive SELECT * FROM moved;

Data-modifying CTE — chain writes atomically in one statement

Postgres features8

WITH RECURSIVE tree AS (
  SELECT id, parent_id FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id, n.parent_id FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;

Recursive CTE — traverse trees and graphs (hierarchies)

SELECT *, sum(amt) OVER (ORDER BY day
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM ledger;

Window frames — running totals and moving windows (frame clause)

SELECT *
FROM customers c,
LATERAL (SELECT * FROM orders o
         WHERE o.customer_id = c.id
         ORDER BY o.created_at DESC LIMIT 3) recent;

LATERAL join — a subquery that references the row to its left

area numeric GENERATED ALWAYS AS (w * h) STORED

Generated columns — a column computed from other columns

tags text[] -- '{a,b,c}' tags @> '{a}' array_length(tags,1)

Array columns — first-class arrays with operators and GIN indexing

CREATE TYPE mood AS ENUM ('low','ok','high');

ENUM types — a fixed, ordered set of named values

id uuid PRIMARY KEY DEFAULT gen_random_uuid()

UUID keys — gen_random_uuid() built into Postgres 13+

to_tsvector('english', body) @@ to_tsquery('english', 'cat & dog')

Full-text search — tsvector/tsquery with a GIN index

Operations & psql7

-- Postgres connections are heavy: ~1 process + memory each

Connection pooling — why you need PgBouncer in front

SELECT pid, state, wait_event_type, query
FROM pg_stat_activity WHERE state <> 'idle';

pg_stat_activity — see every connection and what it's doing right now

SELECT pg_cancel_backend(pid);    -- cancel
SELECT pg_terminate_backend(pid); -- kill the connection

Cancel or kill a query/connection by pid

SELECT * FROM pg_locks bl JOIN pg_stat_activity a USING (pid);

pg_locks — find what is blocking what

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Roles & GRANT — least-privilege access control

\d table \dt \di \l \timing \x

Essential psql meta-commands — inspect schema and tune the prompt

\copy orders TO 'orders.csv' WITH (FORMAT csv, HEADER);

\copy — fast bulk import/export through the psql client