PostgreSQL quick reference

CREATE TABLE t (... NOT NULL, CHECK (...), REFERENCES ...)

PostgreSQL checks every declared constraint — types, NOT NULL, CHECK, foreign keys — before a write is allowed to become a row.

INSERT INTO orders (customer_id) VALUES (999); -- rejected if 999 doesn't exist

database → schema → table → row / column

The containment chain every PostgreSQL object lives inside. Role, extension and tablespace sit outside it — they answer who/what/where, not containment.

CREATE SCHEMA sales; CREATE TABLE sales.orders (id INT, total NUMERIC);

psql -h HOST -U USER -d DATABASE

A connection targets exactly one database on one server. Reach a sibling database by opening a new connection, not by schema-qualifying across it.

psql -d storefront # this session only ever sees storefront

INSERT/UPDATE/DELETE ... RETURNING col, ...

Hands back the affected rows in the same statement — the only way to see a row DELETE just removed, since a later SELECT can never find it again.

DELETE FROM tasks WHERE done = true RETURNING id, title;

WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ... OFFSET ...

WHERE filters rows before grouping; HAVING filters groups after. ORDER BY sorts the final result; LIMIT/OFFSET slice it.

SELECT customer_id, count(*) FROM orders GROUP BY customer_id HAVING count(*) > 2;

COALESCE(a, b, ...) · NULLIF(a, b) · x::type

COALESCE returns the first non-NULL argument. NULLIF returns NULL when its two arguments are equal. :: (or CAST) converts between types, raising an error if the value cannot convert.

COALESCE(NULLIF(discount_code, ''), 'NONE')

expr AS alias · scalar_fn(col) · agg_fn(col)

AS names a column or table for the rest of the query. A scalar function returns one value per row; an aggregate returns one value per group.

SELECT upper(status), count(*) FROM orders GROUP BY status;

col IS NULL · col IS NOT NULL

NULL = NULL is NULL, never true — = can never test for NULL. IS NULL/IS NOT NULL are the only operators built to test it, returning true or false.

SELECT * FROM customers WHERE email IS NULL;

(a AND b) OR c ≠ a AND (b OR c)

AND binds tighter than OR by default. Parentheses always override the default and never change meaning when added redundantly.

WHERE status = 'completed' AND (total > 100 OR status = 'refunded')

a [INNER|LEFT|RIGHT|FULL OUTER] JOIN b ON a.key = b.key

Combines rows from two tables; the join type decides what happens to a row with no match on the other side.

SELECT c.name, o.total FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;

CROSS JOIN · FROM t a JOIN t b ON a.x = b.y

CROSS JOIN pairs every row with every row deliberately; a self join uses two aliases of the same table to compare its own rows.

ON <indexed, correctly-typed, unique-on-one-side column>

The ON clause is both a correctness statement and a performance lever — a wrapped or mismatched-type predicate often can't use an index.

1:1 = UNIQUE FK · 1:N = plain FK · N:N = junction table

The relationship shape lives in the schema; a join just reveals the row-count effect the foreign keys already imply.

two 1:N joins multiply rows (3 × 2), not add (3 + 2)

Aggregate each child down to one row per parent before joining, rather than patching a multiplied SUM with DISTINCT.

predicate · relationship shape · join type — check COUNT(*) after each

A join's row count changes for exactly one of three reasons — isolate which one by adding joins one at a time.

(SELECT ... FROM t WHERE t.col = outer.col)

A correlated scalar subquery — references an outer column, returns exactly one value per outer row.

SELECT c.name, (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) FROM customers c;

WHERE EXISTS (SELECT 1 FROM ... WHERE correlated)

A pure yes/no test on row existence — never inspects the subquery's selected values, sidestepping NULL comparison pitfalls.

NOT EXISTS (SELECT 1 FROM t WHERE t.fk = outer.id)

The NULL-safe way to find rows with no match — NOT IN silently returns zero rows if the subquery result contains any NULL.

SELECT c.* FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

WITH name AS (SELECT ...) SELECT ... FROM name

Names a subquery for the duration of one statement — reference it like a table, but it does not persist afterward.

WITH big_orders AS (SELECT * FROM orders WHERE total > 100) SELECT count(*) FROM big_orders;

WITH RECURSIVE cte AS (base UNION [ALL] recursive)

Runs the base case once, then repeatedly joins against only the previous round's output — cyclic graphs need a visited-node guard.

WITH x AS [MATERIALIZED | NOT MATERIALIZED] (...)

PG 12+ usually inlines a single-reference CTE like a subquery; a multi-reference CTE is usually computed once — check EXPLAIN.

fn(...) OVER (PARTITION BY col ORDER BY col)

Computes a value across a partition of related rows without collapsing them, unlike GROUP BY.

SELECT name, dept, avg(salary) OVER (PARTITION BY dept) FROM employees;

RANK() OVER (ORDER BY col DESC), LAG(col) OVER (ORDER BY col)

RANK/DENSE_RANK number rows by value (gaps vs no gaps on ties); LAG/LEAD read a neighboring row's value.

SELECT name, RANK() OVER (ORDER BY score DESC) FROM results;

SUM() OVER (ORDER BY ...) · ROW_NUMBER() OVER (PARTITION BY ...)

Running total: SUM() with ORDER BY. Top-N-per-group: ROW_NUMBER() partitioned by group, filtered in an outer query.

ROWS BETWEEN N PRECEDING AND CURRENT ROW

Default frame (with ORDER BY) is "start of partition through current row" — an explicit ROWS frame gives a fixed-size moving window.

window function: one pass vs. self join/subquery: reprocesses rows

A window function expresses "this row vs its own group" in one pass — reach for a self join only when two independently-filterable shapes are needed.

NUMERIC(precision, scale)

Exact decimal storage with no floating-point rounding error — the correct type family for money.

CREATE TABLE accounts (balance NUMERIC(12, 2));

text · varchar(n) · char(n)

text and varchar(n) share identical storage — only a length check differs. char(n) pads with trailing spaces and is rarely the right choice.

boolean · bytea · uuid · jsonb · array[] · enum · daterange

Each specialized type replaces a specific relational workaround — reach for it when the workaround is genuinely worse.

TIMESTAMPTZ

Stores a date/time normalized to UTC internally — an unambiguous real-world instant, unlike plain timestamp.

happened_at TIMESTAMPTZ NOT NULL DEFAULT now()

numeric(p,s) + currency_code — not MONEY

money's formatting and rounding depend on server locale; numeric(p,s) gives portable, schema-defined precision instead.

value::type · CAST(value AS type)

Implicit casts happen only when a conversion is safe and unambiguous; anything ambiguous or lossy needs an explicit cast.

JSONB, arrays, enums, ranges vs. integer, numeric, varchar

PostgreSQL-specific types trade portability for expressiveness — weigh the trade-off explicitly if a multi-database future is realistic.

NULL ≠ 0, '', or false — SUM/AVG/COUNT(col) skip NULL rows

NULL is the absence of a known value, not a zero-like sentinel — aggregates skip it entirely rather than treating it as zero.

col IS NULL · col IS NOT NULL · a IS NOT DISTINCT FROM b

The only operators guaranteed to return true or false, never NULL, when testing for a missing value.

SELECT * FROM customers WHERE phone IS NULL;

NULL comparison → unknown → not-true (WHERE, JOIN ON); GROUP BY is the exception

One rule explains WHERE, JOIN ON and booleans; GROUP BY deliberately groups all NULLs together instead.

COALESCE(a, b, ...) · NULLIF(a, b)

COALESCE returns the first non-NULL argument; NULLIF returns NULL when the two arguments are equal.

SELECT COALESCE(NULLIF(status, 'unset'), 'pending') FROM tasks;

x NOT IN (a, b, NULL) → always NULL

NOT IN against any list containing a NULL always returns unknown/zero rows — check for NULLs in the list, whether literal or from a subquery.

SELECT * FROM products WHERE category_id NOT IN (1, 2) OR category_id IS NULL;

NULL-able only with an articulable reason — else NOT NULL

A NULL-able column should answer "why might this legitimately have no value?" — otherwise gaps are a data bug, not a schema feature.

PRIMARY KEY · FOREIGN KEY ... REFERENCES ... · UNIQUE · NOT NULL · CHECK (expr)

The five core constraint types — each rejects a write that violates a specific invariant, enforced from any code path.

CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC NOT NULL CHECK (total >= 0));

PRIMARY KEY (col_a, col_b) · UNIQUE (col_a, col_b)

Enforces uniqueness on the combination of columns, not each column individually — the standard shape for a junction table.

UNIQUE (col) DEFERRABLE INITIALLY DEFERRED

Checks the constraint at COMMIT instead of after each statement — lets a multi-step swap pass through a temporarily invalid state.

REFERENCES parent(id) ON DELETE {CASCADE|RESTRICT|SET NULL|SET DEFAULT|NO ACTION}

Decides what happens to a child row when the parent it references is deleted — NO ACTION (default) blocks it, CASCADE propagates it.

order_id INT REFERENCES orders(id) ON DELETE CASCADE

CHECK (balance >= 0) — enforced for every write, any code path

Application validation only protects the code path it is written into; a database constraint protects all of them, including future ones.

duplicate → UNIQUE · orphan → FOREIGN KEY · invalid → CHECK

Each data-quality problem maps to a distinct constraint, enforced atomically by the database.

1NF: atomic values · 2NF: depends on whole key · 3NF: depends only on the key

The three practical normal forms — each eliminates a specific kind of redundancy that can cause an update anomaly.

CREATE TABLE zip_codes (zip TEXT PRIMARY KEY, city TEXT NOT NULL);

cardinality → which table holds the FK; optionality → NOT NULL or not

Cardinality (how many) and optionality (must it exist) are independent questions about the same relationship.

SERIAL id PRIMARY KEY + UNIQUE(natural_key)

A surrogate key is stable and meaningless by design; a natural key can change — the common default combines both.

bigint (SERIAL/IDENTITY) vs. UUID (gen_random_uuid())

bigint is smaller and database-assigned; UUID can be generated before insertion, at roughly double the storage cost.

created_at · updated_at · created_by · deleted_at

created_at sets itself via DEFAULT; updated_at needs an active mechanism (trigger or app code) since DEFAULT only fires on INSERT.

CREATE UNIQUE INDEX ... ON t (col) WHERE deleted_at IS NULL

A partial unique index scoping uniqueness to only active (non-soft-deleted) rows.

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

ALTER TABLE ... ADD COLUMN new_field TYPE — cheap

Adding a nullable column is cheap in Postgres — the argument against premature generalization (EAV tables) for speculative flexibility.

col INT REFERENCES parent(id)

Enforces that every value in col genuinely references an existing row in parent — the mechanism behind referential integrity.

customer_id INT REFERENCES customers(id)

1:1 = FK + UNIQUE · 1:N = FK · N:N = junction table

One-to-one needs the same foreign key as one-to-many, plus a UNIQUE constraint — without it, it's actually one-to-many.

PRIMARY KEY (a_id, b_id) — two FKs, composite key

A pure association needs only its two foreign keys; once the relationship carries data, it becomes a real entity with its own key.

FOREIGN KEY (a, b) REFERENCES t(a, b)

References a composite primary/unique key as one unit — the referencing table must supply the exact combination.

ON DELETE CASCADE | RESTRICT | SET NULL | SET DEFAULT

CASCADE composes across the whole foreign key chain — its blast radius is a property of the schema's reference graph, not one constraint.

SELECT * FROM pg_constraint WHERE contype = 'f' AND confdeltype = 'c'

Every CASCADE foreign key in the schema — the starting point for tracing a table's full delete blast radius.

SELECT conrelid::regclass, confrelid::regclass FROM pg_constraint WHERE confrelid = 'orders'::regclass;

CREATE VIEW name AS SELECT ...

A stored, named query — no data of its own, always reflects current data, pays the underlying query's cost on every read.

CREATE VIEW active_customers AS SELECT * FROM customers WHERE deleted_at IS NULL;

CREATE MATERIALIZED VIEW name AS SELECT ... · REFRESH MATERIALIZED VIEW name

Physically stores a query's result — fast reads, but stale until explicitly refreshed. Nothing refreshes it automatically.

CREATE MATERIALIZED VIEW customer_totals AS SELECT customer_id, sum(total) FROM orders GROUP BY customer_id;

SELECT * FROM some_view — cost is invisible from the syntax

A view's calling syntax gives no signal of its cost; check EXPLAIN before trusting a view is cheap in a hot path.

GRANT SELECT ON view TO role — runs with owner's privileges

A view executes with its owner's privileges to read underlying tables — a structural way to expose a restricted data slice.

REFRESH MATERIALIZED VIEW CONCURRENTLY name

Refreshes a materialized view without locking it against reads — requires a unique index on the view.

REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals;

CREATE SEQUENCE name · nextval('name')

A standalone object generating incrementing integers atomically — the mechanism SERIAL/IDENTITY build on.

CREATE SEQUENCE orders_id_seq; SELECT nextval('orders_id_seq');

col INT GENERATED ALWAYS AS IDENTITY

Standard-SQL sequence-backed column — GENERATED ALWAYS rejects explicit values unless OVERRIDING SYSTEM VALUE is used.

CREATE TABLE orders (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

sequence: unique + increasing — never contiguous

Gaps from rollbacks, failed inserts, or direct nextval() calls are normal, not corruption — never infer row count from ids.

nextval() is never rolled back, even inside a rolled-back transaction

Deliberately exempt from transactional rollback — making sequences transactional would serialize concurrent nextval() calls.

SELECT max(id) + 1 FROM t → unsafe · nextval('seq') → safe

max(id)+1 has a read-then-write race under concurrency; a sequence's nextval() is atomic and race-free.

CREATE TABLE orders (id SERIAL PRIMARY KEY);

CREATE FUNCTION f(...) RETURNS ... LANGUAGE sql|plpgsql AS $$ ... $$;

SQL function body = one statement, inlineable. PL/pgSQL function body = a procedural block with control flow.

CREATE FUNCTION full_name(a text, b text) RETURNS text LANGUAGE sql AS $$ SELECT a || ' ' || b $$;

DECLARE v type; BEGIN ... EXCEPTION WHEN cond THEN ... END;

PL/pgSQL local variables, control flow and exception handling — each EXCEPTION block is its own subtransaction.

EXCEPTION WHEN division_by_zero THEN RETURN NULL;

CREATE PROCEDURE p(...) LANGUAGE plpgsql AS $$ ... COMMIT; ... $$; CALL p(...);

Procedures are invoked with CALL and, unlike functions, may COMMIT/ROLLBACK inside their body.

CALL archive_old_orders();

invariant/bulk-set-op → database · business rule/external call → application

Database-side logic fits invariants and set-based operations; application logic fits frequently-changing rules and external systems.

ALTER TABLE accounts ADD CONSTRAINT balance_non_negative CHECK (balance >= 0);

a few targeted functions/triggers, not the application's primary logic layer

Database-side logic scales worst for deep trigger chains, external I/O, and business rules that need application-level tooling.

-- prefer pg_notify() + application-owned workflow over a trigger calling an external API

CREATE TRIGGER t BEFORE|AFTER|INSTEAD OF INSERT|UPDATE|DELETE ON tbl FOR EACH ROW EXECUTE FUNCTION fn();

BEFORE can modify/veto a row; AFTER only observes an already-committed write; INSTEAD OF replaces an operation on a view.

CREATE TRIGGER guard_price BEFORE INSERT ON products FOR EACH ROW EXECUTE FUNCTION reject_negative_price();

CREATE TRIGGER t ... FOR EACH ROW|STATEMENT EXECUTE FUNCTION fn();

Row-level fires once per row with OLD/NEW; statement-level fires once per statement regardless of row count.

CREATE TRIGGER refresh_stats AFTER DELETE ON orders FOR EACH STATEMENT EXECUTE FUNCTION refresh_order_stats();

CREATE TRIGGER t AFTER ... FOR EACH ROW WHEN (...) EXECUTE FUNCTION fn();

Good trigger fits: audit trails, cross-row/cross-table invariants, derived values that must hold for every writer.

CREATE TRIGGER audit_order_status AFTER UPDATE OF status ON orders FOR EACH ROW EXECUTE FUNCTION log_order_change();

multiple triggers, same timing/event → alphabetical by name

Trigger fire order is alphabetical by name, not creation order; trigger writes can cascade and even recurse with no built-in depth limit.

CREATE TRIGGER "01_validate" BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION validate_order();

\d+ tablename

The direct way to surface a table's hidden trigger-driven behavior before it causes a confusing debugging session.

\d+ orders -- lists every trigger attached to the table

BEGIN; ...; COMMIT; | ROLLBACK;

BEGIN opens a transaction block; COMMIT makes every change since BEGIN permanent; ROLLBACK discards all of it.

BEGIN; UPDATE accounts ...; UPDATE accounts ...; COMMIT;

BEGIN; -- exactly the statements that must succeed/fail together --; COMMIT;

A transaction boundary should match the real invariant exactly — not wider, not narrower.

BEGIN; UPDATE accounts SET balance = balance - 100 ...; UPDATE accounts SET balance = balance + 100 ...; COMMIT;

no BEGIN → each statement is its own transaction

Without an explicit BEGIN, each statement gets its own implicit BEGIN/COMMIT — related statements need an explicit transaction to be atomic together.

BEGIN; UPDATE ...; UPDATE ...; COMMIT; -- explicit, to make two statements atomic together

SAVEPOINT name; ...; ROLLBACK TO SAVEPOINT name;

A savepoint lets a transaction recover from one failed step without losing everything committed before it.

SAVEPOINT before_risky; ...; ROLLBACK TO SAVEPOINT before_risky;

BEGIN ISOLATION LEVEL READ COMMITTED|REPEATABLE READ|SERIALIZABLE;

Read Committed (default, per-statement snapshot), Repeatable Read (one snapshot per transaction), Serializable (strongest, may require retry).

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT pid, now() - xact_start, state FROM pg_stat_activity WHERE state = 'idle in transaction';

Locks are held for a transaction's full duration; long/idle transactions also block VACUUM from reclaiming dead tuples database-wide.

SELECT pid, xact_start FROM pg_stat_activity WHERE state = 'idle in transaction';

SELECT pid, age(backend_xmin) FROM pg_stat_activity ORDER BY 2 DESC;

Production risk from long transactions compounds under load: lock contention, bloat, connection exhaustion, and (extreme cases) wraparound protection.

SELECT pid, now() - xact_start AS duration FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY duration DESC;

dirty read < non-repeatable read < phantom read < serialization anomaly

Four concurrency phenomena, in escalating subtlety — PostgreSQL never allows dirty reads; Repeatable Read prevents the next two; only Serializable prevents all four.

BEGIN ISOLATION LEVEL REPEATABLE READ; -- freezes the snapshot for the whole transaction

SHOW transaction_isolation;

PostgreSQL: 3 distinct levels (Read Uncommitted == Read Committed); Repeatable Read exceeds the SQL standard by also blocking phantom reads.

BEGIN ISOLATION LEVEL REPEATABLE READ; -- already blocks phantom reads on PostgreSQL

pessimistic: SELECT ... FOR UPDATE · optimistic: UPDATE ... WHERE version = ?

Pessimistic locks upfront (good under high contention); optimistic checks at write time via affected row count (good under low contention).

UPDATE accounts SET balance = 900, version = 8 WHERE id = 1 AND version = 7;

BEGIN; SELECT ... FOR UPDATE; -- read-then-write --; COMMIT;

Locks the returned rows against other lockers/writers for the rest of the transaction — must be used inside an explicit BEGIN to have any effect.

SELECT quantity FROM inventory WHERE product_id = 42 FOR UPDATE;

FOR UPDATE | FOR NO KEY UPDATE | FOR SHARE | FOR KEY SHARE

A strength ladder of row locks, strongest to weakest — pick the weakest one that expresses the actual requirement.

SELECT * FROM products WHERE id = 42 FOR KEY SHARE; -- just prevent deletion

SELECT ... FOR UPDATE NOWAIT | SKIP LOCKED

NOWAIT fails fast instead of waiting; SKIP LOCKED silently skips locked rows — the standard job-queue claim pattern.

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

UPDATE t SET n = n - 1 WHERE id = ? AND n > 0 RETURNING n;

Inventory/payments/counters/job-claiming/uniqueness races are all the same read-then-write gap — fix with one atomic statement, FOR UPDATE, a UNIQUE constraint, or SKIP LOCKED.

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42 AND quantity > 0 RETURNING quantity;

reading never blocks writing, writing never blocks reading

MVCC gives every statement a consistent snapshot by versioning rows instead of locking readers against writers.

BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT ...; -- sees a stable snapshot, blocks nothing

SELECT xmin, xmax, * FROM t WHERE ...;

UPDATE creates a new row version (new xmin) and sets xmax on the old one — never an in-place overwrite.

UPDATE accounts SET balance = 900 WHERE id = 1; -- old version superseded, new version created

visible if: xmin committed as-of snapshot, xmax not committed as-of snapshot

A snapshot is the rule deciding which row version is visible — fresh per statement (Read Committed) or once per transaction (Repeatable Read/Serializable).

BEGIN ISOLATION LEVEL REPEATABLE READ; -- one snapshot, reused for every statement in this transaction

SELECT never blocks UPDATE, UPDATE never blocks SELECT (same row) — DDL is the exception

MVCC's reader/writer non-blocking guarantee applies to ordinary DML — DDL (ALTER TABLE, etc.) still takes locks that can block readers.

SELECT count(*) FROM accounts; -- does not block a concurrent UPDATE, or vice versa

SELECT relname, n_dead_tup FROM pg_stat_user_tables;

A dead tuple is a superseded row version no open snapshot could still need — still occupies space until VACUUM reclaims it.

SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'accounts';

VACUUM t; vs VACUUM FULL t;

VACUUM reclaims dead tuple space for reuse, no exclusive lock. VACUUM FULL rewrites the table smaller and returns space to the OS, but needs ACCESS EXCLUSIVE.

SELECT pg_size_pretty(pg_relation_size('accounts'));

VACUUM t; · ANALYZE t; · VACUUM ANALYZE t; · VACUUM FULL t;

VACUUM reclaims space for reuse; ANALYZE refreshes planner statistics — independent concerns, commonly run together.

VACUUM ANALYZE accounts;

SELECT relname, last_autovacuum FROM pg_stat_user_tables;

Autovacuum runs VACUUM/ANALYZE automatically per table, based on that table's own accumulated changes — essential infrastructure, not optional tuning.

SHOW autovacuum_max_workers;

n_dead_tup / n_live_tup, tracked over time

Routine VACUUM (autovacuum) is the steady-state goal; VACUUM FULL is rare recovery, not scheduled maintenance.

SELECT relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = ?;

Bloat is the accumulated gap between dead tuples created and dead tuples reclaimed — check it before assuming an index/schema fix is needed.

SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

SELECT pid, age(backend_xmin) FROM pg_stat_activity ORDER BY 2 DESC;

A long-running or idle-in-transaction session holds back the vacuum horizon database-wide, regardless of which tables it actually touched.

SELECT pid, state, age(backend_xmin) FROM pg_stat_activity WHERE backend_xmin IS NOT NULL;

SELECT last_analyze, last_autoanalyze FROM pg_stat_user_tables;

Stale planner statistics (not the schema or indexes) are a common, easily-checked cause of a query plan degrading after a large data change.

ANALYZE orders; -- refresh statistics explicitly after a large bulk change

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

Autovacuum's per-table trigger point — default 50 + 0.1 × row count — tune per table via storage parameters, never globally for one table's needs.

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);

SELECT ctid, * FROM t; -- exposes physical row location

A heap table stores rows in no guaranteed order — physical layout depends on free space, not any key value.

SELECT * FROM orders ORDER BY id; -- explicit order, never assumed from heap layout

SELECT ctid, xmin, xmax, * FROM t;

A page (8 KB default) holds many tuples; each tuple's header carries xmin/xmax, the actual visibility information a snapshot checks.

SHOW block_size; -- 8192

SELECT reltoastrelid::regclass FROM pg_class WHERE relname = ?;

TOAST compresses, then relocates, oversized field values into a separate table once a row exceeds ~2 KB — transparent to ordinary SQL.

SELECT id, title FROM articles; -- avoid SELECT * to skip detoasting an unused large column

SELECT count(*), pg_size_pretty(pg_relation_size('t')) FROM t;

A table can grow in physical size from UPDATE activity alone, with row count completely unchanged — dead tuples, not new rows, are the cause.

SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'accounts';

SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables;

A HOT update skips new index entries when no indexed column changed and the page has room — cheaper, and cleanable before VACUUM.

UPDATE accounts SET notes = 'reviewed' WHERE id = 1; -- HOT-eligible if notes is unindexed

CREATE TABLE t (...) WITH (fillfactor = 70);

A lower fillfactor reserves per-page free space so future UPDATEs can qualify as HOT — worth it only for high-churn tables.

ALTER TABLE sessions SET (fillfactor = 70); VACUUM FULL sessions;

SELECT pg_size_pretty(pg_indexes_size('t'));

An index speeds reads but costs storage and write throughput — every relevant write must also update every applicable index.

SELECT pg_size_pretty(pg_relation_size('accounts')), pg_size_pretty(pg_indexes_size('accounts'));

EXPLAIN SELECT ...;

Seq Scan (most rows match), Index Scan (few rows match), Bitmap Index+Heap Scan (moderate rows match) — chosen by the planner based on selectivity.

EXPLAIN SELECT * FROM accounts WHERE balance BETWEEN 500 AND 600;

SELECT attname, n_distinct FROM pg_stats WHERE tablename = ?;

Selectivity (fraction of rows matched) is usually driven by cardinality (distinct value count) — low-cardinality columns rarely benefit from a plain index on equality.

EXPLAIN SELECT * FROM accounts WHERE is_active = true; -- check the rows estimate

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;

Every index is a standing write/storage cost — audit idx_scan to find indexes paying that cost with zero observed read benefit.

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'accounts' ORDER BY idx_scan;

CREATE INDEX idx ON t (col_a, col_b); -- sorted by col_a, then col_b within it

A composite index is one structure sorted primarily by its first column — column order must match how real queries narrow down.

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

index (a, b, c) → usable prefixes: a | (a,b) | (a,b,c)

A composite index only narrows a scan starting from its leftmost column — a constraint on a later column alone cannot use it.

EXPLAIN SELECT * FROM orders WHERE status = 'pending'; -- won't use an index on (customer_id, status)

CREATE INDEX idx ON t (key_col) INCLUDE (payload_col);

An index-only scan skips the heap when every needed column is in the index and the visibility map confirms the page is all-visible.

EXPLAIN SELECT x, y FROM tab WHERE x = ?; -- look for "Index Only Scan"

CREATE INDEX idx ON t (col); -- defaults to USING btree

B-tree is PostgreSQL's default, general-purpose index type — covers equality, range, and ORDER BY from one structure.

SELECT indexdef FROM pg_indexes WHERE indexname = 'idx_email';

LIKE 'prefix%' → usable · LIKE '%suffix' → not usable, by a B-tree

B-tree covers equality, range, and ORDER BY directly. A prefix LIKE pattern is a range query in disguise; a suffix pattern is not.

EXPLAIN SELECT * FROM accounts WHERE name LIKE 'Al%'; -- Index Cond shows a rewritten range

CREATE INDEX idx ON t (col_a, col_b); -- defaults to a composite B-tree

Composite (multicolumn) indexes are specifically a B-tree capability — see Index Fundamentals for the full ordering mechanics.

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

CREATE INDEX idx ON t (col) WHERE cond; · CREATE INDEX idx ON t (fn(col));

Partial indexes cover a row subset; expression indexes cover a transformed value — both need exact query-to-definition matching to be used.

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

CREATE UNIQUE INDEX idx ON t (col) [NULLS NOT DISTINCT];

A unique index rejects duplicate values across all its columns together; NULLs are distinct from each other by default.

CREATE UNIQUE INDEX idx_email ON accounts (email);

B-tree: always eligible · GiST/SP-GiST: sometimes · GIN: never

Index-only scan support depends on the index TYPE — B-tree always qualifies (given the general requirements); GIN never does.

EXPLAIN SELECT status FROM orders WHERE status = 'pending'; -- look for 'Index Only Scan'

CREATE INDEX idx ON t USING hash (col); -- equality only

A Hash index supports only equality — B-tree already covers that plus range/sort, which is why B-tree remains the default even for equality-only workloads.

EXPLAIN SELECT * FROM orders WHERE status = 'pending'; -- Hash index usable here, nowhere else

CREATE INDEX idx ON t USING gist (col); · ORDER BY col <-> point;

GiST/SP-GiST support geometric data and nearest-neighbor (distance-ordered) queries — a capability B-tree structurally cannot express.

SELECT name FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

CREATE INDEX idx ON t USING gin (col);

GIN indexes components (array elements, JSONB keys, lexemes) rather than whole values — makes @>/<@/&& containment queries and full-text search fast.

CREATE INDEX idx_tags ON products USING gin (tags); SELECT * FROM products WHERE tags @> ARRAY['sale'];

CREATE INDEX idx ON t USING brin (col);

BRIN summarizes min/max per page range instead of per row — tiny, but only useful when the column correlates with physical row order.

CREATE INDEX idx_brin_created_at ON event_log USING brin (created_at);

operator needed → supporting index type(s) → workload fit

Choose an index type by the operator the query actually needs and the real workload — never by habit or convention alone.

CREATE INDEX idx_tags ON products USING gin (tags); -- @> requires GIN, not a default B-tree