PostgreSQL Cheatsheet — 80+ Commands & Functions with Real Pitfalls (psql, JSONB, CTE, Window, Index)

List every database on the cluster, with owner, encoding, collation, and access privileges. The first command you run after \c-ing into psql blind.

⚠ Common pitfall: Add `+` for size info (`\l+`). On a busy cluster with hundreds of databases, the size column requires a scan and can stall the connection — fall back to `\l` if it hangs.

\l

\l+

\l app_*  -- pattern match

Connect to a different database (and optionally a different user) inside the same psql session. Cheaper than quitting and re-launching.

⚠ Common pitfall: Switching DBs drops your session state — temp tables, prepared statements, search_path settings all go with the old connection.

\c production

\c app_db readonly_user

\c - postgres  -- same db, new user

List all tables in the current database (or matching a pattern). `\dt *.*` includes system tables; `\dt+` adds size and description.

\dt

\dt+

\dt public.user*

\dt myschema.*

Describe a table: columns, types, defaults, indexes, foreign keys, triggers, check constraints, inheritance. The single most-used psql command.

⚠ Common pitfall: `\d` (no arg) lists every relation including sequences and views — that is rarely what you want. Use `\dt` for just tables.

\d users

\d+ users  -- with storage details

\d users_id_seq  -- describe a sequence

List all roles (users + groups) and their attributes: superuser, can-create-db, can-create-role, can-login, replication, password-set, valid-until.

\du

\du+  -- include description

\du app_*

Quit psql. Equivalent to `exit`, Ctrl-D on a fresh line, or `\quit`.

\q

Execute SQL commands from a file. The path is resolved on the CLIENT side (the machine running psql), not the server.

⚠ Common pitfall: `\i` aborts on the first error unless you SET `ON_ERROR_STOP off`. For production scripts, use `\set ON_ERROR_STOP on` and wrap in BEGIN / COMMIT.

\i schema.sql

\ir relative/migration.sql  -- relative to current file

\set ON_ERROR_STOP on
\i deploy.sql

Toggle per-statement wall-clock timing. Indispensable for quick "is this query slow?" checks without breaking out EXPLAIN.

\timing on

\timing off

\timing  -- toggle

Expanded display: pivot wide rows so each column prints on its own line. `\x auto` toggles based on terminal width — keeps narrow rows tabular but flips wide ones automatically.

\x

\x on

\x auto

Run a shell command from inside psql without leaving the session. `\!` alone drops you to a temporary subshell.

\! ls -la

\! pwd

\!  -- enter subshell, type exit to return

Print details of the current connection: user, database, host, port, SSL mode. Use this before running anything destructive — confirm you are on the right cluster.

\conninfo

Bulk import / export CSV via the CLIENT (not the server). Unlike server-side COPY, `\copy` does not require superuser and reads files on the machine running psql.

⚠ Common pitfall: Server-side `COPY` is faster (no network round-trip per row) but requires superuser AND the file must be on the DB server. `\copy` is what you actually want from a laptop.

\copy users FROM 'users.csv' WITH CSV HEADER

\copy (SELECT * FROM orders WHERE status='paid') TO 'paid.csv' WITH CSV HEADER

`\dn` lists schemas (namespaces). `\df` lists user-defined functions (add `+` for source, add a pattern to filter). `\df S` includes system functions.

\dn

\df

\df+ my_func

\df *json*

Open the last query (`\e`) or a function body (`\ef name`) in your $EDITOR. Save and close to run it. The fastest way to edit a long multi-line statement you mistyped.

⚠ Common pitfall: Your `$EDITOR` must exit cleanly for psql to read the buffer back. Editors that fork into the background (some GUI ones) leave psql hanging. Set `\setenv PSQL_EDITOR vim` if the default misbehaves.

\e

\ef my_function

\ev my_view  -- edit a view definition

Re-run the last query every N seconds (default 2). A poor-man's live dashboard for watching replication lag, active connections, or a long-running job's progress straight from psql.

SELECT count(*) FROM pg_stat_activity;
\watch 5

SELECT now(), pg_size_pretty(pg_database_size('app'));
\watch 10

`\set` defines psql variables and behavior flags (ON_ERROR_STOP, AUTOCOMMIT). `\pset` controls output formatting (format, border, null string, pager). Put your favourites in `~/.psqlrc`.

\set ON_ERROR_STOP on

\pset null '∅'  -- show NULL distinctly

\pset format wrapped

`\gx` runs the current query buffer in expanded mode for just this one execution. `\g file` sends the result to a file instead of the screen. Both avoid permanently toggling display settings.

SELECT * FROM users WHERE id = 1 \gx

SELECT * FROM orders \g /tmp/orders.txt

Object listers: `\dx` installed extensions, `\di` indexes, `\ds` sequences, `\dv` views, `\dm` materialized views, `\dp` table privileges. Append `+` for size/extra detail and a pattern to filter.

\dx

\di+ users*

\dp orders  -- who can do what on orders

Take each row of the current query result as a SQL command and execute it. The idiomatic way to generate-and-run DDL in bulk (drop every table, reindex everything) without a scripting language.

⚠ Common pitfall: Whatever the query produces runs verbatim — a stray quote or injection-prone string is a foot-gun. Inspect the SELECT output first, then add `\gexec` once you trust it.

SELECT format('REINDEX INDEX CONCURRENTLY %I;', indexname)
  FROM pg_indexes WHERE schemaname = 'public'
\gexec

After a statement fails, `\errverbose` reprints the most recent error with full detail: SQLSTATE code, schema/table/column/constraint names, and the server source location. Invaluable for decoding cryptic constraint violations.

\errverbose

Three string types. `text` is the only one you should use 99% of the time — no length limit, same storage and performance as varchar. `varchar(n)` only adds a length check; `char(n)` blank-pads (almost never what you want).

⚠ Common pitfall: Changing `varchar(20)` to `varchar(40)` is metadata-only and instant. Changing `varchar(20)` to `varchar(15)` requires a full table scan. Just use `text` and constrain in app code or with a CHECK.

CREATE TABLE users (id bigserial, name text, email text);

ALTER TABLE users ALTER COLUMN bio TYPE text;

Fixed-width integers: smallint 2B (-32K..32K), int 4B (±2.1B), bigint 8B (±9.2 quintillion). Default to `int` for normal numbers, `bigint` for any ID column on a table that might grow.

⚠ Common pitfall: Using `int` for IDs is the #1 production outage waiting to happen — overflow at 2.1B rows kills writes. Use `bigint` (or `bigserial` / `bigint generated as identity`) for every primary key from day one.

age smallint

user_id bigint

CREATE TABLE orders (id bigserial PRIMARY KEY);

Auto-incrementing integer. `serial` = int + sequence + default. `bigserial` = bigint version. PG 10+ added `GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY` which is the SQL-standard replacement.

⚠ Common pitfall: Sequences are NOT transactional. Even a rolled-back INSERT burns an ID, so never assume serial values are contiguous. Use a synthesizer if you need true gap-less IDs (e.g. invoices).

id bigserial PRIMARY KEY

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY  -- SQL standard

128-bit universally unique ID, 16 bytes on disk. Use `gen_random_uuid()` (built-in since PG 13) or install `uuid-ossp` for older versions. Great for distributed systems where IDs must be generated client-side.

⚠ Common pitfall: UUIDs as primary keys hurt B-tree cache locality (random insert order = page splits). UUID v7 (time-ordered) fixes this — coming in PG 18; today, prefix with a timestamp manually.

id uuid DEFAULT gen_random_uuid() PRIMARY KEY

SELECT gen_random_uuid();

`timestamp` (a.k.a. `timestamp without time zone`) stores a naive datetime — no zone info, no conversion. `timestamptz` stores UTC internally and converts to / from session timezone on I/O. ALWAYS use `timestamptz`.

⚠ Common pitfall: Storing `timestamp` and computing offsets in app code is THE classic timezone bug. Use `timestamptz` everywhere; set the session `TIME ZONE` only at the display edge.

created_at timestamptz NOT NULL DEFAULT now()

SELECT created_at AT TIME ZONE 'Asia/Shanghai' FROM orders;

A duration of time (months, days, microseconds). Composable arithmetic on timestamps: `now() + interval '7 days'`, `ts1 - ts2`.

⚠ Common pitfall: Intervals carry months and days separately because their length varies (Feb is 28/29 days). Avoid `interval '1 month'` for billing logic — use date arithmetic with explicit days.

SELECT now() + interval '1 hour';

SELECT now() - interval '7 days';

DELETE FROM sessions WHERE created_at < now() - interval '30 days';

Both store JSON. `jsonb` is the binary, indexed, deduplicated, key-ordered form — use this 99% of the time. `json` preserves the literal input bytes and whitespace (for audit logs where exact input matters).

⚠ Common pitfall: jsonb does NOT preserve key order or whitespace. `json` does not support most operators (no @>, no GIN index). Default to `jsonb` unless you have a specific reason for `json`.

config jsonb NOT NULL DEFAULT '{}'

INSERT INTO events (payload) VALUES ('{"type":"click","x":42}'::jsonb);

PostgreSQL natively supports arrays of any type. Use for small, ordered, append-mostly sets where a join table is overkill (tags, role lists, ordered preferences).

⚠ Common pitfall: Arrays are NOT a substitute for a many-to-many table for anything you will query frequently. Cannot foreign-key into them; updates rewrite the entire array; GIN indexes work for membership but not order.

tags text[] DEFAULT '{}'

SELECT * FROM posts WHERE tags @> ARRAY['rust'];

UPDATE posts SET tags = array_append(tags, 'wasm') WHERE id = 1;

Fixed labeled values. More space-efficient and self-documenting than a text+CHECK, with native ordering. `CREATE TYPE status AS ENUM (...)`.

⚠ Common pitfall: Removing an enum value is impossible without recreating the type — design the value set carefully. `ALTER TYPE ... ADD VALUE` is one-way.

CREATE TYPE order_status AS ENUM ('pending','paid','shipped','refunded');

ALTER TYPE order_status ADD VALUE 'cancelled' AFTER 'pending';

Exact arbitrary-precision decimal. The ONLY correct type for money — `float` rounds and loses cents. `numeric(precision, scale)` caps total and fractional digits; bare `numeric` is unbounded.

⚠ Common pitfall: numeric arithmetic is slow (software, not CPU). For heavy aggregation on money, store integer cents (bigint) and divide at the display edge. Never use `real`/`double precision` for currency.

price numeric(12, 2) NOT NULL

SELECT 0.1::numeric + 0.2::numeric;  -- exactly 0.3

SELECT round(amount, 2) FROM invoices;

True / false / NULL (three-valued). Accepts many literals on input: TRUE/FALSE, 't'/'f', 'yes'/'no', '1'/'0', 'on'/'off'. Stored as a single byte.

⚠ Common pitfall: A nullable boolean has THREE states. `WHERE active` silently drops NULL rows (NULL is not true). Use `WHERE active IS NOT FALSE` or `coalesce(active, false)` to be explicit.

is_active boolean NOT NULL DEFAULT true

SELECT * FROM users WHERE is_active IS NOT FALSE;

`date` is a calendar day (no time, no zone). `time` is a wall-clock time of day. `time with time zone` exists but is almost useless (a time without a date has no real offset). Prefer `date` + `timestamptz`.

birth_date date

SELECT current_date, current_time;

SELECT date_trunc('month', created_at)::date FROM orders;

Built-in range types store a contiguous span with inclusive/exclusive bounds. Operators: `@>` contains, `&&` overlaps, `-|-` adjacent. Pair with an EXCLUDE constraint to prevent overlapping bookings.

SELECT '[2026-05-01,2026-06-01)'::daterange @> '2026-05-15'::date;

CREATE TABLE bookings (
  room_id int,
  during tsrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

Network address types. `inet` holds an IPv4/IPv6 host (optionally with netmask), `cidr` a network block, `macaddr` a hardware address. Support containment (`<<=`), masking, and abbreviation natively.

client_ip inet

SELECT * FROM logs WHERE client_ip <<= '10.0.0.0/8';  -- inside subnet

SELECT '192.168.1.5/24'::inet + 10;

Raw binary blob. The default output format is `hex` (\x...). Use it for small binaries (thumbnails, hashes, encrypted blobs); for large files, store in object storage and keep a URL.

⚠ Common pitfall: bytea has no streaming — the whole value loads into memory on read. Multi-MB blobs bloat your row, slow VACUUM, and balloon backups. Keep bytea values small.

SELECT digest('hello', 'sha256');  -- needs pgcrypto, returns bytea

SELECT encode(digest('hello', 'sha256'), 'hex');

Full-text search types. `tsvector` is a document parsed into normalized lexemes with positions; `tsquery` is a search expression. Match with `@@`. Back it with a GIN index for fast search.

SELECT to_tsvector('english', 'The quick brown fox') @@ to_tsquery('english', 'quick & fox');

CREATE INDEX docs_fts ON docs USING GIN (to_tsvector('english', body));

SELECT * FROM docs WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'brown fox');

Define a new table. Columns get type + optional default + constraints (NOT NULL, UNIQUE, PRIMARY KEY, REFERENCES, CHECK). Always declare a primary key; PG does not require one but you will regret skipping it.

CREATE TABLE users (
  id bigserial PRIMARY KEY,
  email text UNIQUE NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Idempotent table creation — succeeds silently if the table already exists. Standard for migration scripts that may re-run.

⚠ Common pitfall: Only the table existence is checked, not the schema. If columns drifted, you get the OLD table with no warning. Use a real migration tool for production.

CREATE TABLE IF NOT EXISTS audit_log (id bigserial PRIMARY KEY, action text);

Modify an existing table: ADD / DROP / ALTER COLUMN, ADD / DROP CONSTRAINT, RENAME. Each sub-action takes its own lock; many actions can be batched in one statement for fewer locks.

⚠ Common pitfall: `ALTER TABLE ... ADD COLUMN col text DEFAULT 'x'` rewrites the whole table on PG ≤10. On PG 11+, non-volatile defaults are metadata-only and instant. Volatile defaults (like `gen_random_uuid()`) still rewrite.

ALTER TABLE users ADD COLUMN avatar_url text;

ALTER TABLE users DROP COLUMN deprecated_field;

ALTER TABLE users RENAME COLUMN nickname TO display_name;

ALTER TABLE users ADD CONSTRAINT users_email_lower CHECK (email = lower(email)) NOT VALID;

Remove a table. `IF EXISTS` makes it safe to re-run. `CASCADE` also drops dependent objects (views, foreign keys pointing in) — without it, PG refuses if anything depends.

⚠ Common pitfall: `CASCADE` is irreversible and silently drops every dependent view, function, and FK. Always run `DROP ... RESTRICT` first (the default) to see what would break before you go nuclear.

DROP TABLE IF EXISTS old_users;

DROP TABLE users CASCADE;  -- careful

Build an index. Plain `CREATE INDEX` takes an ACCESS EXCLUSIVE lock — blocks writes (and on some old PG versions reads) for the duration. `CONCURRENTLY` builds it without blocking writes; takes 2-3x longer but is the only safe production option.

⚠ Common pitfall: `CREATE INDEX CONCURRENTLY` can FAIL silently (leaves an INVALID index). Always check: `SELECT * FROM pg_indexes WHERE indexdef LIKE '%INVALID%'`. If invalid, DROP and rebuild.

CREATE INDEX CONCURRENTLY users_email_idx ON users (email);

CREATE UNIQUE INDEX CONCURRENTLY orders_uuid_uidx ON orders (uuid);

View = saved query, re-run on every SELECT. Materialized view = query result stored on disk, refreshed manually with `REFRESH MATERIALIZED VIEW`. Materialized is great for expensive aggregates that can be stale.

⚠ Common pitfall: `REFRESH MATERIALIZED VIEW` locks the view for reads. Use `REFRESH MATERIALIZED VIEW CONCURRENTLY` (requires a UNIQUE index on the view) to refresh without blocking readers.

CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;

CREATE MATERIALIZED VIEW daily_revenue AS
  SELECT date_trunc('day', created_at) d, sum(amount) FROM orders GROUP BY 1;
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

A column whose value is computed from other columns. `STORED` writes the value to disk; `VIRTUAL` is not yet supported in PG (use a view).

ALTER TABLE products
  ADD COLUMN search_text text
  GENERATED ALWAYS AS (lower(name) || ' ' || lower(coalesce(brand, ''))) STORED;

Enforce referential integrity. `ON DELETE CASCADE` deletes children, `SET NULL` nulls the FK, `RESTRICT`/`NO ACTION` blocks. Always index the referencing column — PG does NOT auto-index FK columns.

⚠ Common pitfall: A FK with no index on the child side turns every parent DELETE/UPDATE into a sequential scan of the child table. This is the #1 silent performance killer in normalized schemas.

ALTER TABLE orders ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

CREATE INDEX orders_user_id_idx ON orders (user_id);  -- index the FK!

Add a CHECK or FK constraint without scanning existing rows (instant, only an ACCESS EXCLUSIVE flash). It applies to new/changed rows immediately; validate the backlog later with `VALIDATE CONSTRAINT` (takes only a SHARE UPDATE EXCLUSIVE lock).

⚠ Common pitfall: The two-step (NOT VALID then VALIDATE) is THE way to add constraints to a big live table without a long write lock. Skipping VALIDATE leaves pre-existing rows unchecked forever.

ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT amount_positive;

Attach documentation to any object: table, column, function, index, constraint. Comments show up in `\d+`, in tooling, and in `pg_description`. Self-documenting schemas pay off years later.

COMMENT ON COLUMN users.status IS 'one of: pending, active, banned (see app enum)';

COMMENT ON TABLE audit_log IS 'append-only; never UPDATE rows here';

A standalone counter. `nextval()` advances it, `currval()` reads your session's last value, `setval()` resets it. Use OWNED BY to tie a sequence's lifecycle to a column.

⚠ Common pitfall: Sequences cache values per session (`CACHE n`) for speed — cached-but-unused values are lost on disconnect, widening gaps. Set `CACHE 1` if you (mistakenly) need near-contiguous values.

CREATE SEQUENCE invoice_no_seq START 1000 INCREMENT 1;

SELECT nextval('invoice_no_seq');

CREATE SEQUENCE s OWNED BY mytable.id;

A namespace for tables/views/functions inside one database. Use schemas for multi-tenant isolation, per-environment separation, or grouping (e.g. `analytics.*`, `staging.*`). Set `search_path` to control unqualified name resolution.

CREATE SCHEMA IF NOT EXISTS analytics AUTHORIZATION analyst;

SET search_path TO analytics, public;

CREATE TABLE analytics.events (id bigserial PRIMARY KEY);

Rename a table/column/index or move an object to another schema — both are metadata-only and instant (a brief ACCESS EXCLUSIVE lock). Great for the expand/contract migration pattern.

⚠ Common pitfall: Renaming a column breaks every view, function, and app query that references the old name with NO compile-time warning (PG resolves names lazily). Grep your codebase before renaming.

ALTER TABLE users RENAME TO app_users;

ALTER INDEX users_email_idx RENAME TO app_users_email_idx;

ALTER TABLE events SET SCHEMA analytics;

UNIQUE enforces no duplicates (NULLs are allowed and distinct by default; PG 15+ adds `NULLS NOT DISTINCT`). EXCLUDE is the generalized form: "no two rows where these expressions conflict under these operators" — the way to forbid overlapping time ranges.

ALTER TABLE users ADD CONSTRAINT users_email_uq UNIQUE (email);

CREATE UNIQUE INDEX ON tags (lower(name)) NULLS NOT DISTINCT;  -- PG 15+

ALTER TABLE reservations ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room WITH =, during WITH &&);

Add one or more rows. Multi-row INSERT is dramatically faster than one statement per row (one network round-trip, one WAL flush).

INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada');

INSERT INTO users (email, name) VALUES ('a@x.com','Ada'), ('b@x.com','Bob'), ('c@x.com','Cy');

Get back the rows you just inserted, including server-generated columns (serial IDs, defaults, generated). Saves a separate SELECT round-trip.

INSERT INTO users (email) VALUES ('a@x.com') RETURNING id, created_at;

INSERT INTO orders (user_id, amount) SELECT id, 0 FROM users WHERE created_at > now() - interval '1 day' RETURNING *;

Atomic upsert. On conflict with the named UNIQUE constraint, `DO UPDATE SET col = EXCLUDED.col` (use the would-be-inserted values), or `DO NOTHING` to skip.

⚠ Common pitfall: The conflict target MUST be a UNIQUE constraint or PRIMARY KEY — a plain index does not count. Sequences are still advanced on conflict, leaving gaps in serial IDs.

INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

INSERT INTO event_dedupe (event_id) VALUES ('e123')
ON CONFLICT DO NOTHING;

Modify rows and return the new values. `FROM` clause lets you join other tables into the UPDATE (PostgreSQL extension to standard SQL).

⚠ Common pitfall: `UPDATE` without WHERE updates every row in the table. Always run the equivalent SELECT first to count affected rows, or wrap in BEGIN / ROLLBACK while testing.

UPDATE users SET name = 'Ada Lovelace' WHERE id = 1 RETURNING *;

UPDATE orders o SET status = 'paid' FROM payments p
WHERE o.id = p.order_id AND p.confirmed_at IS NOT NULL
RETURNING o.id;

Remove rows and return what was deleted (great for audit logs or "undo" tooling).

⚠ Common pitfall: `DELETE` without WHERE wipes the table. For large deletes, batch them (DELETE WHERE id IN (...) LIMIT N pattern via CTE) — a single huge DELETE generates massive WAL and bloats the table.

DELETE FROM sessions WHERE expires_at < now() RETURNING id;

WITH victims AS (
  SELECT id FROM sessions WHERE expires_at < now() ORDER BY id LIMIT 10000
)
DELETE FROM sessions WHERE id IN (SELECT id FROM victims) RETURNING id;

Empty a table instantly, no per-row WAL. Resets sequences only with `RESTART IDENTITY`. CASCADE truncates dependent FK tables too.

⚠ Common pitfall: TRUNCATE takes ACCESS EXCLUSIVE — blocks everything. Cannot be selectively rolled back to a savepoint in some replication topologies. Triggers do NOT fire unless you specify `BEFORE TRUNCATE`.

TRUNCATE TABLE staging_imports;

TRUNCATE TABLE users RESTART IDENTITY CASCADE;

Bulk-insert the result of a query. The fast way to backfill, copy a filtered subset, or denormalize into a reporting table — all server-side, no rows crossing the wire.

INSERT INTO users_archive (id, email, archived_at)
  SELECT id, email, now() FROM users WHERE deleted_at < now() - interval '90 days';

The fastest bulk load/unload path — a single statement streams rows with no per-row parsing overhead. Server-side `COPY ... FROM 'file'` needs superuser/pg_read_server_files and the file on the DB host. `COPY ... FROM STDIN` is what drivers use under the hood.

⚠ Common pitfall: COPY is all-or-nothing within its transaction. For 10x the speed of row-by-row INSERT, drop non-essential indexes, COPY, then rebuild them. Note it bypasses rules and only fires triggers that are explicitly defined.

COPY users (email, name) FROM '/data/users.csv' WITH (FORMAT csv, HEADER true);

COPY (SELECT * FROM orders WHERE created_at >= '2026-01-01') TO STDOUT WITH (FORMAT csv, HEADER true);

SQL-standard multi-action upsert (PG 15+). One statement with WHEN MATCHED / WHEN NOT MATCHED branches to UPDATE, DELETE, or INSERT. More expressive than ON CONFLICT when the match key is not a unique constraint.

⚠ Common pitfall: MERGE does NOT have the atomic insert-or-update concurrency guarantee that ON CONFLICT gives you. Under concurrent writers it can still raise a unique violation. For pure upsert on a unique key, prefer ON CONFLICT.

MERGE INTO inventory i
USING shipments s ON i.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = i.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);

Row-level locking inside a transaction. `FOR UPDATE` locks selected rows so concurrent writers wait. `SKIP LOCKED` skips already-locked rows — the canonical pattern for a Postgres-backed job queue where N workers each grab a different row.

⚠ Common pitfall: `FOR UPDATE` with an ORDER BY across many workers causes lock-wait pileups; add `SKIP LOCKED` so each worker grabs the next free row instead of queuing. `NOWAIT` errors immediately instead of waiting.

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

SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

Produce multiple aggregation levels in one pass. ROLLUP gives subtotals + grand total along a hierarchy; CUBE gives every combination; GROUPING SETS lets you pick exactly which groupings. Use `GROUPING()` to tell subtotal rows from real ones.

SELECT region, product, sum(amount)
  FROM sales GROUP BY ROLLUP (region, product);

SELECT region, product, sum(amount), grouping(region, product) AS g
  FROM sales GROUP BY GROUPING SETS ((region), (product), ());

PostgreSQL extension: keep the first row per distinct key according to ORDER BY. The cleanest "latest row per group" — shorter than a window-function subquery, and often faster.

⚠ Common pitfall: The leftmost ORDER BY expressions MUST match the DISTINCT ON columns, or you get an error. Add any tiebreaker columns after them. Without an explicit ORDER BY the kept row is arbitrary.

SELECT DISTINCT ON (user_id) user_id, amount, created_at
  FROM orders ORDER BY user_id, created_at DESC;

OFFSET paginates by skipping rows — simple but O(offset): page 10000 scans and throws away a million rows. Keyset (seek) pagination uses `WHERE (sort_key) > (last_seen)` and stays O(page size) at any depth.

⚠ Common pitfall: OFFSET pagination on a frequently-changing table also shows duplicates/skips as rows shift between requests. Keyset pagination is stable AND fast — prefer it for infinite scroll and deep pages.

SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 40;  -- page 3

SELECT * FROM posts WHERE id > 1234 ORDER BY id LIMIT 20;  -- keyset, fast

Extract a JSONB value: `->` returns jsonb (keep typing), `->>` returns text. Use `->>` when you want the value as a string for comparison / display.

⚠ Common pitfall: Comparing a `->>` text result to a number requires casting: `(data->>'count')::int > 5`. Without the cast you get a slow text comparison and no index usage.

SELECT data->'user'->>'email' FROM events;

SELECT * FROM events WHERE (data->>'count')::int > 5;

Path extraction: `#>` returns jsonb at a path, `#>>` returns text. Path is an array literal — `'{user,name}'` or `ARRAY['user','name']`.

SELECT data #> '{user,address,city}' FROM events;

SELECT data #>> '{user,name}' FROM events;

Containment: does the left JSONB contain the right? GIN-indexable and the fastest way to filter on nested keys. Backbone of any JSONB query workload.

SELECT * FROM events WHERE data @> '{"type":"click"}';

SELECT * FROM users WHERE prefs @> '{"theme":"dark","lang":"zh"}';

`?` does the key exist at the top level. `?|` any of these keys. `?&` all of these keys. GIN-indexable with `jsonb_path_ops`.

SELECT * FROM events WHERE data ? 'user_id';

SELECT * FROM events WHERE data ?| ARRAY['email','phone'];

SELECT * FROM events WHERE data ?& ARRAY['ip','ua'];

Construct a JSONB object from alternating key/value pairs. Cleaner than `jsonb_set` for building from scratch in a query.

SELECT jsonb_build_object('id', id, 'email', email, 'created', created_at) FROM users;

Update a value at a JSONB path. Last arg controls create-if-missing behavior (default true). Returns the new JSONB.

⚠ Common pitfall: jsonb_set on a NULL JSONB returns NULL silently. `coalesce(col, '{}')` before setting to avoid losing the update.

UPDATE users SET prefs = jsonb_set(prefs, '{theme}', '"dark"') WHERE id = 1;

UPDATE users SET prefs = jsonb_set(coalesce(prefs, '{}'::jsonb), '{lang}', '"zh"', true);

SQL/JSON path queries (PG 12+): a mini language for navigating and filtering JSON, similar to JSONPath. Returns a set of jsonb values.

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

SELECT * FROM orders WHERE jsonb_path_exists(data, '$.items[*] ? (@.sku == "X1")');

Unnest a JSONB array into one row per element. `jsonb_array_elements` returns jsonb, the `_text` variant returns text. The standard way to join into a JSON array as if it were a table.

SELECT e.value->>'sku' AS sku
  FROM orders, jsonb_array_elements(data->'items') AS e;

SELECT jsonb_array_elements_text('["a","b","c"]'::jsonb);

`jsonb_each` expands a JSONB object into (key, value) rows; `jsonb_object_keys` returns just the keys. Use to iterate over dynamic objects whose keys you do not know in advance.

SELECT key, value FROM events, jsonb_each(data) WHERE id = 1;

SELECT jsonb_object_keys(prefs) FROM users WHERE id = 1;

Aggregate rows back INTO JSONB. `jsonb_agg` builds an array, `jsonb_object_agg(k, v)` builds an object. The way to return a parent with its children nested as JSON in one query.

SELECT u.id, jsonb_agg(o.* ORDER BY o.created_at) AS orders
  FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.id;

SELECT jsonb_object_agg(code, name) FROM countries;

JSONB modification operators: `||` merges/concatenates (right wins on key clash, shallow merge only), `-` removes a key or array element, `#-` removes at a path. None mutate — each returns a new value.

⚠ Common pitfall: `||` is a SHALLOW merge — nested objects are replaced wholesale, not deep-merged. For deep merge you must recurse (or use a helper function). Easy to lose nested data unintentionally.

SELECT '{"a":1,"b":2}'::jsonb || '{"b":9,"c":3}'::jsonb;  -- {a:1,b:9,c:3}

UPDATE users SET prefs = prefs - 'legacy_flag' WHERE id = 1;

SELECT data #- '{user,temp}' FROM events;

Convert SQL values into JSON. `to_jsonb(anyvalue)` is the modern catch-all (handles rows, arrays, scalars, preserves types). `row_to_json` is the older row-only form. Backbone of building API payloads in SQL.

SELECT to_jsonb(u) FROM users u WHERE id = 1;

SELECT to_jsonb(array_agg(name)) FROM tags;

Common Table Expression: name a subquery and reuse it. PG 12+ inlines CTEs by default (faster); add `MATERIALIZED` to force the old "execute once and cache" semantics.

⚠ Common pitfall: Pre-PG 12, every CTE acted as an "optimization fence" — the planner could not push predicates through. Many old PG codebases use CTEs as a hint; on PG 12+ that hint is gone, queries can change plan.

WITH recent AS (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT user_id, count(*) FROM recent GROUP BY user_id;

Recursive CTE: traverse trees and graphs. The anchor SELECT bootstraps; the recursive SELECT references the CTE itself. UNION ALL keeps duplicates; UNION dedupes.

⚠ Common pitfall: Always have a termination condition. An unbounded recursive CTE on a cyclic graph runs until it OOMs the server. Add `WHERE depth < 100` or track visited nodes.

WITH RECURSIVE org AS (
  SELECT id, manager_id, name, 1 AS depth FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.manager_id, e.name, org.depth + 1
    FROM employees e JOIN org ON e.manager_id = org.id
    WHERE org.depth < 100
)
SELECT * FROM org;

CTEs can wrap INSERT / UPDATE / DELETE with RETURNING — useful for "move rows from one table to another in one statement" patterns.

WITH moved AS (
  DELETE FROM events WHERE created_at < now() - interval '30 days' RETURNING *
)
INSERT INTO events_archive SELECT * FROM moved;

Wrap a self-limiting subquery in a CTE to process huge tables in bounded chunks, keeping each transaction short and WAL flat. Re-run until zero rows affected. The safe way to purge or backfill billions of rows online.

⚠ Common pitfall: Without `ORDER BY ... LIMIT` inside the CTE, a single statement still touches the whole table. The LIMIT is what bounds each pass — never omit it on large tables.

WITH batch AS (
  SELECT id FROM events
    WHERE processed = false ORDER BY id LIMIT 5000 FOR UPDATE SKIP LOCKED
)
UPDATE events SET processed = true
  WHERE id IN (SELECT id FROM batch) RETURNING id;

Explicit optimization-fence control (PG 12+). `MATERIALIZED` forces the CTE to execute once and cache (useful when it is expensive and referenced many times, or has side effects). `NOT MATERIALIZED` lets the planner inline and push predicates down.

WITH heavy AS MATERIALIZED (
  SELECT * FROM big_join_view
)
SELECT * FROM heavy WHERE x = 1
UNION ALL
SELECT * FROM heavy WHERE y = 2;

Track the visited path in a recursive CTE to stop on cycles. PG 14+ adds native `CYCLE col SET is_cycle USING path` syntax; before that, carry an array of visited ids and filter `WHERE NOT id = ANY(path)`.

WITH RECURSIVE g AS (
  SELECT id, parent_id, ARRAY[id] AS path FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id, n.parent_id, g.path || n.id
    FROM nodes n JOIN g ON n.parent_id = g.id
    WHERE NOT n.id = ANY(g.path)
)
SELECT * FROM g;

Assign a unique sequential number within each partition, ordered by the ORDER BY. The classic "top N per group" pattern: filter to rn <= N in an outer query.

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
) t WHERE rn <= 3;

Both rank rows within a partition. `RANK` skips numbers after a tie (1, 1, 3); `DENSE_RANK` does not (1, 1, 2). Use DENSE for "top 3 distinct scores".

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

SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) FROM players;

Reference the previous (LAG) or next (LEAD) row in the same partition without a self-join. Optional offset (default 1) and default value.

SELECT day, revenue,
       revenue - LAG(revenue, 1, 0) OVER (ORDER BY day) AS day_over_day
  FROM daily_revenue;

Bucket rows into n approximately-equal-sized groups by the ORDER BY. Used for quartiles, percentile bands, A/B test arms.

SELECT user_id, spend, NTILE(4) OVER (ORDER BY spend DESC) AS quartile FROM users;

Frame clause: which rows in the partition the aggregate sees. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW = running total; ROWS BETWEEN 6 PRECEDING AND CURRENT ROW = 7-day moving sum.

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
  FROM daily_revenue;

Pluck the first or last value from the window frame. LAST_VALUE needs an explicit frame `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` — the default frame stops at current row.

SELECT user_id,
       FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS first_order,
       LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order
  FROM orders;

Named window: define the OVER clause once and reuse it across multiple aggregates. Cleaner than copy-pasting `PARTITION BY x ORDER BY y` three times.

SELECT user_id,
       SUM(amount) OVER w,
       AVG(amount) OVER w,
       COUNT(*)   OVER w
  FROM orders
  WINDOW w AS (PARTITION BY user_id ORDER BY created_at);

Relative-position window functions. `PERCENT_RANK` = (rank - 1) / (total - 1), ranging 0..1. `CUME_DIST` = fraction of rows at or below the current value. Use for percentile dashboards without bucketing.

SELECT name, score, round((percent_rank() OVER (ORDER BY score))::numeric, 3)
  FROM students;

Ordered-set aggregates for percentiles. `percentile_cont(0.5)` interpolates (true median, may not exist in data); `percentile_disc(0.5)` returns an actual value from the set. The right way to get p50/p95/p99 latencies.

SELECT
  percentile_cont(0.5)  WITHIN GROUP (ORDER BY latency_ms) AS p50,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95,
  percentile_cont(0.99) WITHIN GROUP (ORDER BY latency_ms) AS p99
FROM requests;

Collapse consecutive runs into ranges using the "row_number minus value" trick: rows in one unbroken streak share a constant difference, which becomes the grouping key. The canonical pattern for streaks, sessions, and contiguous ranges.

SELECT user_id, min(day) AS streak_start, max(day) AS streak_end, count(*) AS len
FROM (
  SELECT user_id, day,
    day - (row_number() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
  FROM active_days
) t
GROUP BY user_id, grp;

Conditional aggregation without CASE. `count(*) FILTER (WHERE status = 'paid')` counts only matching rows. Cleaner and faster than `sum(CASE WHEN ... THEN 1 ELSE 0 END)`, and works as a window aggregate too.

SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'paid')     AS paid,
  count(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders;

Default index type. Best for equality and range queries on scalars. Multi-column B-tree respects leftmost-prefix: index (a, b) helps WHERE a=? and WHERE a=? AND b=? but NOT WHERE b=?.

CREATE INDEX users_email_idx ON users (email);

CREATE INDEX orders_user_created_idx ON orders (user_id, created_at DESC);

Generalized Inverted Index — for "the data is composite, I want to query its parts" cases. JSONB containment, full-text tsvector, array membership, pg_trgm fuzzy matching.

⚠ Common pitfall: GIN indexes are slow to write (rewrite full posting lists). For write-heavy JSONB columns, consider partial GIN or a functional index on hot keys only.

CREATE INDEX events_data_gin ON events USING GIN (data jsonb_path_ops);

CREATE INDEX posts_tags_gin ON posts USING GIN (tags);

CREATE INDEX users_name_trgm ON users USING GIN (name gin_trgm_ops);  -- needs pg_trgm

Generalized Search Tree — for geometric and range types. PostGIS spatial indexes, range exclusion constraints, similarity search.

CREATE INDEX locations_geom_gist ON locations USING GiST (geom);

CREATE INDEX bookings_period_gist ON bookings USING GiST (period);  -- daterange

Only index rows matching a predicate. Massively smaller and faster than indexing every row when most queries hit a subset (active rows, recent rows, status='pending').

CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';

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

Index the result of an expression, not a raw column. Required when queries wrap columns in functions (`WHERE lower(email) = ?` needs index on `lower(email)`).

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

CREATE INDEX events_date_idx ON events (date_trunc('day', created_at));

PG 11+ — store extra non-key columns in the index leaf so the planner can answer the query from the index alone (index-only scan), no heap fetch.

⚠ Common pitfall: Index-only scans skip the heap, so they require an up-to-date visibility map. Run VACUUM after big bulk loads or you will not see the speedup.

CREATE INDEX users_email_inc_idx ON users (email) INCLUDE (name, created_at);

Block Range INdex — tiny index that stores min/max per block range. Trivially cheap for very large tables with natural ordering (time-series, append-only logs).

CREATE INDEX events_created_brin ON events USING BRIN (created_at);

Run the query and show the actual plan with real row counts, timing, and buffer hits. The single most useful tool when you ask "why is this slow?" — look for Seq Scan on big tables, big rows-removed-by-filter, and high read counts.

⚠ Common pitfall: `EXPLAIN ANALYZE` on UPDATE / DELETE / INSERT actually executes the write. Wrap in `BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;` to inspect the plan without changing data.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = $1;

BEGIN;
EXPLAIN (ANALYZE) UPDATE users SET name = 'x' WHERE id = 1;
ROLLBACK;

Hash index — equality only (`=`), no ranges, no ordering. Since PG 10 it is crash-safe and WAL-logged. Occasionally smaller/faster than B-tree for large random keys, but B-tree is the safer default 99% of the time.

CREATE INDEX sessions_token_hash ON sessions USING hash (token);

Rebuild a bloated or corrupt index. `REINDEX INDEX CONCURRENTLY` (PG 12+) rebuilds without an exclusive lock — the production-safe option. Use after heavy churn shrinks index efficiency, or to fix an INVALID index.

⚠ Common pitfall: Plain `REINDEX` takes an ACCESS EXCLUSIVE lock on the table — never run it on a live table without CONCURRENTLY. The concurrent form leaves a leftover invalid index if it fails; drop it and retry.

REINDEX INDEX CONCURRENTLY users_email_idx;

REINDEX TABLE CONCURRENTLY orders;

Every index slows writes and costs disk. This view shows `idx_scan` (times the planner used each index). Indexes with `idx_scan = 0` after a full traffic cycle are dead weight — drop them.

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes
  WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

`EXPLAIN (FORMAT JSON)` emits a machine-readable plan for tooling. `EXPLAIN (GENERIC_PLAN)` (PG 16+) plans a parameterized query with `$1` placeholders WITHOUT executing it — finally lets you EXPLAIN a prepared statement directly.

EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE id = 1;

EXPLAIN (GENERIC_PLAN) SELECT * FROM users WHERE email = $1;

The workflow that actually finds your bottleneck: order pg_stat_statements by total time, not mean time. A query taking 5 ms called 10 million times hurts more than a 2-second report run once. `mean_exec_time` finds the worst single query; `total_exec_time` finds the worst aggregate load.

SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms,
       round(total_exec_time::numeric, 0) AS total_ms, query
  FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

SELECT pg_stat_statements_reset();  -- zero the counters before a test

Split a table by a range of values, typically a date. Each partition is a separate table — drop a partition to delete a month of data in milliseconds.

CREATE TABLE events (id bigserial, created_at timestamptz NOT NULL, payload jsonb)
  PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_05 PARTITION OF events
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

Split by an explicit list of values. Use for multi-tenant per-customer partitioning or per-region tables.

CREATE TABLE orders (id bigserial, region text, amount numeric)
  PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us-east','us-west');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('eu-west','eu-central');

Hash the partition key to spread rows evenly across N partitions. Use when no natural range / list exists but you want write contention reduced.

CREATE TABLE messages (id bigint, user_id bigint, body text)
  PARTITION BY HASH (user_id);

CREATE TABLE messages_p0 PARTITION OF messages FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE messages_p1 PARTITION OF messages FOR VALUES WITH (MODULUS 4, REMAINDER 1);

Add or remove a partition from a partitioned table. DETACH is the cheap "delete a month of data" operation. CONCURRENTLY (PG 14+) avoids a brief access exclusive lock.

⚠ Common pitfall: ATTACH PARTITION runs a full table scan to validate the partition bound unless the table already has a matching CHECK constraint. Add the CHECK first, then ATTACH, to skip the scan.

ALTER TABLE events ATTACH PARTITION events_2026_06 FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

ALTER TABLE events DETACH PARTITION events_2025_01 CONCURRENTLY;

A catch-all partition for rows that match no other partition's bounds. Without it, an INSERT outside every defined range fails with a "no partition found" error. Add one as a safety net, but monitor its size.

⚠ Common pitfall: Attaching a NEW partition whose range overlaps rows already sitting in the DEFAULT partition scans the default to move them and fails if any conflict. Keep the default near-empty by defining partitions ahead of time.

CREATE TABLE events_default PARTITION OF events DEFAULT;

A partition can itself be partitioned, giving two-level schemes (e.g. RANGE by month, then HASH by tenant inside each month). Useful for very large multi-tenant time-series, but adds planning overhead — keep total partition count in the low thousands.

CREATE TABLE metrics (tenant_id int, ts timestamptz, val numeric)
  PARTITION BY RANGE (ts);

CREATE TABLE metrics_2026_05 PARTITION OF metrics
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01')
  PARTITION BY HASH (tenant_id);

The planner skips partitions that cannot match the WHERE clause — the whole point of partitioning. Confirm it works by checking the EXPLAIN plan touches only the relevant partitions. Runtime pruning (PG 11+) also prunes on parameter values at execution.

⚠ Common pitfall: Pruning only works if the WHERE clause references the partition key directly. Wrapping it in a function (`WHERE date_trunc('day', ts) = ...`) or comparing across types defeats pruning and scans every partition.

EXPLAIN SELECT * FROM events WHERE created_at >= '2026-05-15' AND created_at < '2026-05-16';

Logical backup of a database: SQL text by default, or custom (`-Fc`) / directory (`-Fd`) / tar (`-Ft`) for parallel restore. Run from any client; respects --no-owner / --no-privileges for cross-cluster moves.

⚠ Common pitfall: pg_dump is single-version-snapshot consistent BUT slow on multi-TB databases. For very large clusters use `pg_basebackup` (physical) + WAL archiving, not pg_dump.

pg_dump -Fc -f app.dump app

pg_dump -Fd -j 4 -f app_dir app  # 4-way parallel dump

pg_dump --schema-only -f schema.sql app

Restore from a pg_dump archive (-Fc / -Fd / -Ft formats). Supports parallel restore with `-j`, table-level filtering with `-t`, and `--no-owner` for cross-user moves.

pg_restore -d app_new -j 4 app.dump

pg_restore -d app -t users app.dump

pg_restore --schema-only -d app schema.dump

Physical base backup of an entire cluster — bit-for-bit copy of the data directory while the server keeps running. Foundation for streaming replicas and PITR.

pg_basebackup -h primary.db -D /var/lib/postgresql/replica -U replicator -R -P

Standby server connects to primary, replays WAL in real time. Configure `primary_conninfo` on the standby and a replication slot on the primary so WAL is retained until the standby has it.

⚠ Common pitfall: Without a replication slot, the primary can recycle WAL before the standby reads it — replica falls behind permanently. WITH a slot, a dead standby fills the primary disk. Monitor `pg_replication_slots.confirmed_flush_lsn`.

SELECT pg_create_physical_replication_slot('replica1');

SELECT slot_name, active, confirmed_flush_lsn FROM pg_replication_slots;

Replicate selected tables (not the whole cluster) between PG versions. Built around publications (source) and subscriptions (target). Required for online major-version upgrades.

CREATE PUBLICATION mypub FOR TABLE users, orders;

CREATE SUBSCRIPTION mysub CONNECTION 'host=src dbname=app user=repl' PUBLICATION mypub;

Dump the WHOLE cluster including global objects pg_dump skips: roles, tablespaces, and grants. `--globals-only` exports just roles/tablespaces — pair it with per-database pg_dump for a complete, parallelizable backup.

pg_dumpall --globals-only -f globals.sql

pg_dumpall -f full_cluster.sql

Restore to any exact moment by replaying archived WAL on top of a base backup. Requires `archive_mode = on` + a working `archive_command`. Set `recovery_target_time` in the recovery config; PG stops replay there.

⚠ Common pitfall: PITR is only as good as your archive_command. If WAL archiving has been silently failing, your recovery window has a hole. Monitor `pg_stat_archiver.last_failed_time` and test restores regularly.

# in postgresql.conf
archive_mode = on
archive_command = 'test ! -f /arch/%f && cp %p /arch/%f'

# in recovery target config
recovery_target_time = '2026-05-29 14:30:00+00'

`synchronous_commit` trades durability for latency. `on` (default) flushes WAL to disk before COMMIT returns. `remote_apply` waits for a sync standby to apply. `off` returns before the local flush — fast but a crash loses recent commits.

⚠ Common pitfall: Setting `synchronous_commit = off` globally to speed up writes silently risks data loss on crash. Better: set it per-transaction for non-critical bulk loads only, leaving critical writes durable.

SET LOCAL synchronous_commit = off;  -- this transaction only

SELECT application_name, state, sync_state FROM pg_stat_replication;

On the primary, measure how far each standby trails in bytes; on a standby, measure lag in seconds. Byte lag spots a slow network; time lag (`now() - last replay`) spots a stalled apply. Alert on both.

SELECT application_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
  FROM pg_stat_replication;

SELECT now() - pg_last_xact_replay_timestamp() AS replay_lag;  -- run on the standby

In PG, USER and GROUP are the same thing: a ROLE. `CREATE USER` = `CREATE ROLE ... LOGIN`. Roles can be granted to other roles (Postgres-style group membership).

CREATE ROLE app_readonly NOLOGIN;

CREATE USER app_service WITH PASSWORD 'xxx';

GRANT app_readonly TO app_service;

Hand out (or take back) privileges on databases, schemas, tables, columns, sequences, functions. New tables do NOT inherit privileges — set `ALTER DEFAULT PRIVILEGES` to make grants apply to future objects.

⚠ Common pitfall: GRANT SELECT ON ALL TABLES IN SCHEMA only covers tables that exist NOW. New tables get nothing. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly fixes future tables too.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;

REVOKE ALL ON SCHEMA public FROM PUBLIC;

Host-based authentication config: maps (connection type, database, user, address) → auth method. Order matters — first match wins. Methods: trust (no password!), md5, scram-sha-256, peer, cert, ldap.

⚠ Common pitfall: A stray `host all all 0.0.0.0/0 trust` line lets anyone on the internet log in as anyone. Audit pg_hba.conf on every cluster — never use `trust` on a network-reachable line.

# TYPE  DATABASE  USER  ADDRESS         METHOD

host    all       all   10.0.0.0/8      scram-sha-256

hostssl app       app   0.0.0.0/0       scram-sha-256

local   all       all                   peer

Per-row access policies — the database itself enforces "user X only sees their own rows". Enable on a table, then `CREATE POLICY`.

⚠ Common pitfall: RLS does NOT apply to the table owner or superusers by default. Set `FORCE ROW LEVEL SECURITY` to apply policies even to the owner. Otherwise migrations / app-as-owner bypass your security.

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

ALTER TABLE notes FORCE ROW LEVEL SECURITY;

CREATE POLICY my_notes ON notes
  USING (user_id = current_setting('app.user_id')::bigint);

`SET ROLE` switches the current privilege context to a role you are a member of (reversible with `RESET ROLE`). The backbone of secure connection-pooling: connect as a trusted pooler role, then `SET ROLE` to the per-request user.

SET ROLE app_readonly;

RESET ROLE;

SET SESSION AUTHORIZATION 'reporting';

Defensive role/session settings: `CONNECTION LIMIT` caps concurrent connections per role; `statement_timeout` kills runaway queries; `idle_in_transaction_session_timeout` kills connections holding a transaction open and blocking VACUUM.

⚠ Common pitfall: A forgotten `BEGIN` in an interactive session can block autovacuum cluster-wide for hours. Set `idle_in_transaction_session_timeout` on every interactive/admin role to bound the damage.

ALTER ROLE app_service CONNECTION LIMIT 50;

ALTER ROLE app_service SET statement_timeout = '30s';

ALTER ROLE admin SET idle_in_transaction_session_timeout = '60s';

PG ships built-in roles you GRANT instead of handing out superuser: `pg_read_all_data` / `pg_write_all_data` (PG 14+), `pg_monitor` (view stats), `pg_signal_backend` (cancel/terminate others' queries without being superuser).

GRANT pg_read_all_data TO analyst;

GRANT pg_monitor TO observability;

Per-query execution statistics: total time, mean time, calls, rows returned, I/O. The first extension you install on any production cluster — without it, "find the slow query" is guessing.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- needs shared_preload_libraries

SELECT query, calls, mean_exec_time, total_exec_time
  FROM pg_stat_statements
  ORDER BY total_exec_time DESC LIMIT 20;

Trigram-based fuzzy text matching. Makes `LIKE '%foo%'`, `ILIKE`, and similarity search (`%` operator) indexable via GIN. Game-changer for search-as-you-type UIs.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX users_name_trgm ON users USING GIN (name gin_trgm_ops);

SELECT * FROM users WHERE name % 'Adda';  -- fuzzy match

Geospatial data and queries: geometry / geography types, spatial indexes, distance / contains / intersects operators. The reason serious GIS workloads run on PostgreSQL.

CREATE EXTENSION IF NOT EXISTS postgis;

SELECT name FROM places
  WHERE ST_DWithin(geog, ST_MakePoint(-122.4, 37.8)::geography, 1000);  -- within 1km

UUID generation functions (v1, v3, v4, v5). On PG 13+ the built-in `gen_random_uuid()` covers v4 — only install uuid-ossp if you specifically need v1 (timestamp-based) or v5 (namespace).

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

SELECT uuid_generate_v1();

SELECT uuid_generate_v5(uuid_ns_url(), 'https://x.com/user/42');

hstore: simple key-value (predates jsonb, prefer jsonb for new code). pgcrypto: crypt(), gen_salt(), digest(), pgp_sym_encrypt(). citext: case-insensitive text type — index-friendly alternative to lower() everywhere.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

INSERT INTO users (password) VALUES (crypt('hunter2', gen_salt('bf')));

SELECT * FROM users WHERE password = crypt('hunter2', password);

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE accounts (email citext UNIQUE);

Vector similarity search for embeddings (the `vector` type). Supports L2 (`<->`), inner product (`<#>`), and cosine (`<=>`) distance, with HNSW and IVFFlat approximate-nearest-neighbor indexes. The reason Postgres is a viable vector DB.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (id bigserial, embedding vector(1536));

CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);

SELECT id FROM docs ORDER BY embedding <=> $1 LIMIT 5;

Foreign Data Wrapper: query tables in ANOTHER PostgreSQL server as if they were local. Define a server + user mapping, import a foreign schema, then JOIN across databases. The built-in way to federate.

⚠ Common pitfall: Cross-server JOINs can pull entire foreign tables over the network if the planner cannot push the filter down. Check the EXPLAIN for "Foreign Scan" with a pushed-down WHERE; otherwise add `use_remote_estimate` and analyze the foreign table.

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER src FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db2', dbname 'app');

IMPORT FOREIGN SCHEMA public LIMIT TO (orders) FROM SERVER src INTO remote;

Add B-tree-style operators (`=`, `<`, `>`) to GiST/GIN indexes. The key enabler for multi-column EXCLUDE constraints (e.g. `room WITH =, during WITH &&`) and for combining a scalar equality with a JSONB/array search in one GIN index.

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings ADD CONSTRAINT no_double_book
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

Extension lifecycle. `CREATE EXTENSION ... WITH SCHEMA x` installs objects into a chosen schema. `ALTER EXTENSION ... UPDATE` bumps to a newer version. List available ones in `pg_available_extensions`, installed ones in `pg_extension`.

SELECT name, default_version, installed_version FROM pg_available_extensions WHERE installed_version IS NOT NULL;

ALTER EXTENSION postgis UPDATE;

DROP EXTENSION IF EXISTS hstore CASCADE;

In SQL, NULL means "unknown". NULL = NULL is NULL (not TRUE), NULL != NULL is also NULL. Use IS NULL / IS NOT NULL, or `IS DISTINCT FROM` to treat NULLs as comparable.

⚠ Common pitfall: `WHERE col = NULL` always returns zero rows. `NOT IN (subquery)` returns zero rows if the subquery has even ONE NULL. Use `NOT EXISTS` or filter NULLs out first.

SELECT * FROM users WHERE deleted_at IS NULL;

SELECT * FROM users WHERE id IS DISTINCT FROM other_id;  -- treats NULL as a value

SELECT * FROM orders WHERE NOT EXISTS (SELECT 1 FROM refunds WHERE refunds.order_id = orders.id);

PG uses MVCC: UPDATE = insert new row + mark old row dead. VACUUM reclaims dead rows; without it, table size grows and queries slow down. Autovacuum is on by default — but high-write tables may need tuning.

⚠ Common pitfall: A long-running transaction (forgotten psql session, hung connection) BLOCKS vacuum cluster-wide. Check `SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction'` and kill old sessions.

VACUUM ANALYZE users;

VACUUM FULL users;  -- rewrites table, exclusive lock — avoid in production

SELECT pid, age(now(), xact_start) AS age, query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY age DESC;

Most ALTERs take ACCESS EXCLUSIVE — blocks every read AND write for the duration. On a busy production table, a one-second hold cascades into thousands of stalled queries. Always plan the lock.

⚠ Common pitfall: ADD COLUMN with a non-volatile default is metadata-only since PG 11. ADD COLUMN with a VOLATILE default (gen_random_uuid()) still rewrites the table. ALTER COLUMN TYPE almost always rewrites. Plan accordingly.

SET lock_timeout = '5s';  -- never block forever

ALTER TABLE users ADD COLUMN x text;  -- safe, metadata only

ALTER TABLE users ADD COLUMN id_v2 uuid DEFAULT gen_random_uuid();  -- REWRITES TABLE — danger

Sequences live outside transactions. A rolled-back INSERT still burns the ID. Bulk loads via COPY can also leave the sequence value behind the actual max(id) — then the next INSERT throws a duplicate-key error.

⚠ Common pitfall: After bulk-loading rows that set the id column explicitly, run `SELECT setval('users_id_seq', (SELECT max(id) FROM users))` to realign the sequence.

SELECT setval('users_id_seq', (SELECT max(id) FROM users));

SELECT last_value, is_called FROM users_id_seq;

WAL (write-ahead log) accumulates if archive_command fails, a replication slot has no consumer, or wal_keep_size is too high. A full WAL volume crashes the server. Monitor `pg_wal` directory size in alerts.

⚠ Common pitfall: NEVER `rm` files from pg_wal manually — you will corrupt the database. Use `pg_archivecleanup` or drop the offending replication slot (`SELECT pg_drop_replication_slot('slot_name')`).

SELECT slot_name, active, restart_lsn, pg_size_pretty(pg_current_wal_lsn() - restart_lsn) AS lag FROM pg_replication_slots;

SELECT pg_drop_replication_slot('dead_replica');

`timestamp without time zone` stores the literal y-m-d h:m:s, NO zone info. When the app and DB are in different zones, every read silently misinterprets the time. Use `timestamptz` everywhere; only `SET TIME ZONE` at the presentation edge.

SHOW timezone;

SET TIME ZONE 'Asia/Shanghai';

SELECT created_at AT TIME ZONE 'UTC' FROM orders;  -- explicit conversion

PostgreSQL resolves unqualified names against `search_path`. If `search_path` includes `public` and an attacker can create objects in `public`, they can shadow your functions / tables. Always set `search_path = "$user", pg_catalog` and qualify names in SECURITY DEFINER functions.

⚠ Common pitfall: SECURITY DEFINER functions are CVE generators if `search_path` is not pinned. Always: `CREATE FUNCTION ... SECURITY DEFINER SET search_path = pg_catalog, pg_temp`.

SHOW search_path;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

CREATE FUNCTION sensitive() RETURNS void
  LANGUAGE plpgsql
  SECURITY DEFINER SET search_path = pg_catalog, pg_temp
AS $$ BEGIN /* ... */ END $$;

Comparing a column to a value of a different type can force an implicit cast that disables the index. A `bigint` column compared to a text literal, or `varchar` vs `text` mismatches, silently turn an index scan into a seq scan.

⚠ Common pitfall: Always pass parameters in the column's exact type. In app code, bind an integer as an integer, not a string. Check the EXPLAIN for an unexpected `Seq Scan` or a cast wrapped around your indexed column.

-- bad: '5' is text, may skip a bigint index
SELECT * FROM orders WHERE id = '5';

SELECT * FROM orders WHERE id = 5;  -- typed correctly

Two transactions that lock the same rows in opposite order deadlock; PG detects it and kills one with a "deadlock detected" error. The fix is discipline: always acquire locks (update rows) in a consistent order, e.g. sorted by primary key.

⚠ Common pitfall: Deadlocks are an application-logic bug, not a PG bug. Retrying the killed transaction usually succeeds, but the real fix is to order your writes. Sort the ids before a multi-row UPDATE.

UPDATE accounts SET bal = bal - 10 WHERE id = LEAST(1, 2);
UPDATE accounts SET bal = bal + 10 WHERE id = GREATEST(1, 2);

PG's MVCC means `SELECT count(*)` must scan visible rows — it is NOT a stored counter like in MyISAM. On a big table this is slow. For an approximate live count, read `reltuples` from pg_class (updated by ANALYZE/VACUUM).

⚠ Common pitfall: Building a UI that shows an exact total count on a huge, busy table will hammer the DB. Use an approximate count, a cached counter table, or `count(*)` only on an indexed filtered subset.

SELECT reltuples::bigint AS approx_rows FROM pg_class WHERE relname = 'events';

SELECT count(*) FROM orders WHERE status = 'open';  -- ok if status is indexed & selective

PG transaction IDs are 32-bit and wrap around. If autovacuum cannot freeze old rows fast enough (blocked by long transactions or stuck on a huge table), the cluster approaches wraparound and PG will force a shutdown to protect data.

⚠ Common pitfall: Monitor `age(datfrozenxid)` per database against `autovacuum_freeze_max_age` (default 200M). A database approaching that number is an emergency — find what is blocking vacuum (long transactions, unconsumed replication slots, failed autovacuum).

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC;

Each PG connection is a full OS process with its own memory — a few hundred is the practical ceiling on most hardware. Apps that open a connection per request exhaust `max_connections` under load. Put PgBouncer (transaction pooling) in front.

⚠ Common pitfall: Bumping `max_connections` to thousands does NOT scale — it just moves the bottleneck to memory and context-switching. The right answer is a pooler, not a bigger number. Serverless/Lambda especially needs transaction-mode pooling.

SELECT count(*), state FROM pg_stat_activity GROUP BY state;

SHOW max_connections;

An `OR` across different columns often forces a sequential scan because no single index covers both branches. Rewrite as `UNION` of two indexed queries, or as `col = ANY(ARRAY[...])` when it is the same column.

⚠ Common pitfall: PG 14+ can sometimes use a Bitmap Or across two indexes, but it is not guaranteed. Check the EXPLAIN — if you see a Seq Scan where you expected index usage, the OR is the usual culprit.

-- may seq scan:
SELECT * FROM users WHERE email = $1 OR phone = $2;

SELECT * FROM users WHERE email = $1
UNION
SELECT * FROM users WHERE phone = $2;