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*

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';

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;

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;

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")');

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;

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);

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;

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;

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;

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);

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);

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 $$;