home
›
cheatsheets
›
database_technologies
›
postgresql
Free to read Every card and diagram, and Quiz me
2
Free account Star cards. Your Quiz me marks are kept, so next time you drill just what you missed
3
Pro All 42 answers on this sheet, My sheet across every topic, and PDFs
A free account keeps your stars and Quiz me marks. Pro adds all 42 answers on this sheet, My sheet across every topic, and PDFs.
/
Read
Quiz me
Comfortable
Compact
0
Click a command to copy it
Done 0 /0
To review 0
On this sheet
01 MVCC & VACUUM02 Types, keys & JSONB03 Indexes & performance04 Transactions & locks05 Queries, views & functions06 WAL, replication & backups07 Partitions, shards & pools08 Security, audit & extensions
The model Under multiversion concurrency control (MVCC) , an UPDATE writes a new row version and leaves the old one as a dead row . Each version records xmin and xmax, the transactions that created and deleted it. A snapshot uses them to pick the visible version. VACUUM frees dead rows for reuse and freezes old rows to prevent transaction ID wraparound.Every change goes to the write-ahead log (WAL) first, and COMMIT waits only for that log. Crash recovery and replicas replay it. The planner picks each plan from table statistics . ANALYZE refreshes them. An UPDATE adds v2 and sets xmax on v1. Readers see v1 until the writer commits, and nobody waits. VACUUM frees v1 once no snapshot needs it.
MVCC & VACUUM row versions, and the cleanup they need HOT updates and fillfactor Leave room on each page for new row versions ALTER TABLE accounts SET (fillfactor = 80); It applies to pages filled from now on.
Tip A heap-only tuple (HOT) update adds no index entry. The old version points to the new one on the same page.
Trap Changing any indexed column rules out HOT, except for BRIN indexes (16+). Avoid indexing columns that change often.
VACUUM, ANALYZE and REINDEX Free the space of dead rows for reuse VACUUM orders; It rarely shrinks the file.
Give a bloated table's space back to the operating system VACUUM FULL orders; It rewrites the table under an exclusive lock.
Refresh the planner's statistics ANALYZE orders; Run it after large data changes.
Rebuild a bloated or corrupt index REINDEX INDEX CONCURRENTLY idx_cust; A plain REINDEX blocks writes.
Tip Writers still block writers. A second UPDATE of a row waits for the first to finish.
Trap A long-running transaction stops VACUUM from removing any row that transaction might still see.
Transaction ID wraparound Check how close each database is to wraparound SELECT datname, age(datfrozenxid) FROM pg_database; Autovacuum forces a freeze on any table past 200 million, the autovacuum_freeze_max_age default.
Find what stops freezing A long open transaction, a forgotten prepared transaction or a stale replication slot.
Tip Transaction IDs are 32-bit and compared in a circle. Unfrozen old rows would seem to come from the future.
Trap With 3 million IDs left, PostgreSQL accepts only read-only transactions until a database-wide VACUUM runs.
Types, keys & JSONB column types, constraints, and removing data Column types and auto-numbered keys Store money exactly price numeric(10,2) Not real, which is approximate.
Add an auto-numbered identity column GENERATED ALWAYS AS IDENTITY ALWAYS rejects your own value unless you add OVERRIDING SYSTEM VALUE.
Understand an older SERIAL column id SERIAL PRIMARY KEY It means a separate sequence and a nextval() default.
Generate a UUID key gen_random_uuid() uuidv7() (18+) gives time-ordered values.
Store a file's bytes in the row content bytea TOAST compresses or moves large values out of the row, up to 1 GB each.
Store a file over 1 GB, or read part of one lo_import('/tmp/a.pdf') A large object, up to 4 TB. Deleting its row leaves it behind, so run vacuumlo.
Tip char has no speed advantage in PostgreSQL. Use text or varchar.
Trap Identity and SERIAL columns leave gaps. A value is used up even when its insert rolls back.
Primary, unique and foreign keys Tell PRIMARY KEY from UNIQUE A table has one primary key, which forbids NULL. UNIQUE allows many NULLs, or one with NULLS NOT DISTINCT (15+).
Make a column point at a row in another table REFERENCES departments Deleting a department still in use fails by default.
Delete the referencing rows with their parent ON DELETE CASCADE ON DELETE SET NULL keeps them and clears the key.
Trap PostgreSQL doesn't index a foreign key column. Without that index, each parent delete scans the child table.
DELETE, TRUNCATE or DROP Remove the rows a condition matches DELETE FROM users WHERE age < 18; It fires ON DELETE triggers and leaves dead rows for VACUUM.
Empty the whole table fast TRUNCATE TABLE users; It frees the space at once, but locks out all other access.
Remove the table itself DROP TABLE users;
Tip All three roll back inside a transaction. A ROLLBACK after BEGIN even brings back a dropped table.
JSON and JSONB Read a field, or a nested field, as text data->>'name' data->'specs'->>'cpu' -> returns JSON, and ->> returns text.
Find documents that contain a value or a key WHERE data @> '{"price": 999}' WHERE data ? 'price'
Index every key for @> and ? queries USING GIN (data) Only jsonb supports GIN. An expression index on one field works for json too.
Trap jsonb drops white space and key order, and keeps only the last value of a duplicate key.
Indexes & performance find rows fast, and see why a query is slow Create indexes and build them safely Index a column you filter or join on CREATE INDEX ON users (email); A B-tree by default. Writes wait until the build ends.
Build an index without blocking writes CREATE INDEX CONCURRENTLY It can't run inside a transaction block.
Drop the invalid index a failed build leaves DROP INDEX CONCURRENTLY idx_cust; Queries ignore it, but every write still updates it.
Index two columns together CREATE INDEX ON users (last_name, age); Best when queries filter on last_name, the leading column.
Index only the rows you query CREATE INDEX ON users (email) WHERE active;
Index a computed value CREATE INDEX ON users (lower(email));
Tip PostgreSQL 18 can use a multicolumn B-tree without its leading column, by a skip scan, when that column has few distinct values.
Trap Every index slows writes and takes disk space. Drop the ones pg_stat_user_indexes shows no query uses.
Index types and index-only scans Serve = lookups only USING HASH (email) B-tree, the default, serves = too, plus ranges and sorting.
Search inside arrays, jsonb or full text USING GIN (tags) One entry per element inside a value.
Search geometry, ranges or nearest neighbors USING GIST (location) Lossy for full-text search, so PostgreSQL rechecks each match.
Index a huge table that grows in time order USING BRIN (created_at) One small summary per range of table blocks.
Add the columns a query returns to its index INCLUDE (total) A covering index. INCLUDE columns are stored, not searched.
Tip Index entries carry no visibility information. The scan skips the table only on pages VACUUM marked all-visible.
Trap GIN never supports index-only scans, because its entries hold only parts of each value.
Read a query plan Show the plan without running the query EXPLAIN Each node shows an estimated cost and row count.
Run the query and see real rows and times EXPLAIN (ANALYZE, BUFFERS) BUFFERS counts cache hits and disk reads. It is on by default in 18+.
Read the scan and join nodes A Seq Scan on a selective filter points to a missing index. Hash Join suits larger inputs than Nested Loop.
Tip When estimated and actual rows differ a lot, run ANALYZE first. Stale statistics are the usual cause.
Trap EXPLAIN ANALYZE really runs the statement. Wrap an UPDATE or DELETE in BEGIN and ROLLBACK.
Find slow queries and tune memory List the statements that take the most total time SELECT * FROM pg_stat_statements Sort it by total_exec_time.
See what each session is running SELECT * FROM pg_stat_activity; Look for long queries and idle in transaction sessions.
Cancel a slow query, or end its session pg_cancel_backend(12345) pg_terminate_backend(12345) The second ends the session and rolls back its transaction.
Set the two main memory settings shared_buffers = 4GB work_mem = 64MB Start shared_buffers at a quarter of RAM. work_mem applies per sort or hash step.
Trap A session left idle in transaction holds its locks and stops VACUUM from cleaning up.
Transactions & locks keep data correct when sessions write at once ACID in PostgreSQL Make a transfer all or nothing BEGIN; COMMIT; Atomicity. If any statement fails, the whole transaction rolls back.
Reject data that breaks a rule Consistency, through keys and CHECK constraints.
Hide unfinished changes from other transactions Isolation. Each statement reads an MVCC snapshot.
Keep a committed change through a crash Durability. The WAL reaches disk before COMMIT returns.
Trap Outside a BEGIN block, each statement is its own transaction. A failed second update can't undo the first.
Isolation levels Dirty Re-read New rows Write skew
READ UNCOMMITTED Ask for Read Uncommitted, get Read Committed
READ COMMITTED Use the default, a new snapshot per statement
REPEATABLE READ Keep one snapshot for the whole transaction
SERIALIZABLE Act as if transactions ran one at a time
Tip Write skew means two transactions read the same rows, write different ones, and together break a rule.
Trap Repeatable Read and Serializable can fail with SQLSTATE 40001. The application must retry the whole transaction.
Row locks and deadlocks Lock a row you will update based on what you read FOR UPDATE Other writers wait until you commit. Plain reads don't.
Let workers take jobs from a queue without waiting FOR UPDATE SKIP LOCKED
Prevent deadlocks Lock rows in the same order everywhere, such as by id.
Fail fast instead of waiting on a lock SET lock_timeout = '5s'; Or add NOWAIT to fail at once.
Find what is blocking a session pg_blocking_pids(12345)
Lock a key your application chooses pg_advisory_lock(42) PostgreSQL doesn't enforce it. Release it with pg_advisory_unlock .
Tip The four modes, strongest first, are FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE. A plain UPDATE takes the second.
Trap PostgreSQL checks for a deadlock after deadlock_timeout , 1 second by default. It aborts one transaction with 40P01, so retry it.
Queries, views & functions joins, CTEs, window functions and code in the database Joins and UNION Keep only matches, every left row, or every row INNER JOIN LEFT JOIN FULL OUTER JOIN Where a side has no match, its columns are NULL.
Fetch each user's latest order LEFT JOIN LATERAL Its subquery can read the tables listed before it.
Stack the rows of two queries UNION UNION ALL UNION drops duplicates. UNION ALL keeps them and is faster.
CTEs and window functions Walk a hierarchy, like an org chart WITH RECURSIVE It starts from a base query and adds one level at a time with UNION ALL.
Choose whether a CTE runs on its own AS MATERIALIZED AS NOT MATERIALIZED PostgreSQL folds a plain CTE used once into the main query. These force either choice.
Rank employees within each department RANK() OVER (PARTITION BY dept ORDER BY pay DESC) Ties share a rank. ROW_NUMBER never ties.
Average each row with the two rows before it ROWS BETWEEN 2 PRECEDING AND CURRENT ROW With no frame clause, the frame starts at the first row.
Tip Window functions run after WHERE and GROUP BY. Filter on their result in an outer query.
Views, functions and triggers Store an expensive report's result Use a materialized view. A plain view stores only the query and reruns it on every read.
Refresh a materialized view REFRESH MATERIALIZED VIEW stats; A large refresh blocks reads until it finishes.
Refresh it while reads continue REFRESH MATERIALIZED VIEW CONCURRENTLY stats; It needs a unique index on the materialized view.
Compute a value inside a query SELECT calculate_bonus(42); A function. It can't COMMIT.
Run a batch job that commits in steps CALL update_salaries(); A procedure. It can COMMIT.
Run a function for every changed row EXECUTE FUNCTION audit_trigger() Part of CREATE TRIGGER. A BEFORE trigger can change NEW or skip the row.
Write your own aggregate or operator CREATE AGGREGATE CREATE OPERATOR An aggregate needs a state type and a transition function.
Trap A procedure's COMMIT fails with "invalid transaction termination" when the CALL is inside a BEGIN block.
WAL, replication & backups survive a crash, copy the data, and restore it WAL, backups and recovery Dump one database, then restore chosen objects pg_dump -Fc mydb > mydb.dump pg_restore -d mydb mydb.dump pg_dump doesn't block other users.
Copy the whole cluster's files pg_basebackup -D /backup/base -P Incremental backups need 17+, and pg_combinebackup merges them.
Archive every finished WAL file archive_mode = on archive_command = 'cp %p /backup/wal/%f'
Restore to just before a bad DELETE touch $PGDATA/recovery.signal recovery_target_time = '2026-10-01 12:00' First copy in a base backup and set restore_command.
Tip At COMMIT, PostgreSQL waits for one sequential WAL write. Changed data pages reach disk later, at checkpoints.
Trap A backup you have never restored may not work. Test restores regularly.
Replication and logical decoding Keep an exact copy of every database A streaming standby, asynchronous by default. Synchronous mode loses no commits but slows them.
Copy chosen tables to another server CREATE PUBLICATION pub FOR TABLE orders; Then CREATE SUBSCRIPTION on the other server. It works across major versions.
Stream row changes to a tool like Debezium pg_create_logical_replication_slot('s', 'pgoutput') Logical decoding. It needs wal_level = logical.
Drop a slot nobody reads pg_drop_replication_slot('s')
Tip Logical replication copies no schema changes, and no sequence values before PostgreSQL 19. Automatic failover needs an external tool.
Trap An abandoned slot keeps WAL on disk until someone reads it. Watch pg_replication_slots .
Upgrade to a new major version Upgrade the cluster in place pg_upgrade --check pg_upgrade --link Far faster than a dump and restore. --check only reports problems.
Upgrade with only seconds of downtime Replicate logically to the new version, then switch. Copy the schema first.
Trap Once the new cluster starts after a --link upgrade, you can't go back to the old one.
Partitions, shards & pools grow past one big table or one server Partition a large table Split a table by range, list or hash PARTITION BY RANGE (day) PARTITION BY LIST (region) PARTITION BY HASH (id) Then create each partition with PARTITION OF, such as one table per year.
Read only the partitions a query needs Partition pruning skips those the WHERE clause rules out.
Remove a year of old data fast DROP TABLE sales_2023; Far faster than a bulk DELETE, with no dead rows.
Tip Partition when a table outgrows the server's memory, and queries or deletes follow the partition key.
Trap A primary key or unique constraint must include the partition key. Too many partitions slow planning.
Foreign tables and sharding Query tables on another PostgreSQL server CREATE EXTENSION postgres_fdw; IMPORT FOREIGN SCHEMA public FROM SERVER db2 INTO remote; Create a server and a user mapping first.
Pick a sharding approach Route keys in the application, attach foreign tables as hash partitions, or use the Citus extension.
Trap Joins and transactions across shards are slow and hard to keep atomic. Pick a shard key that keeps most queries on one shard.
Connection pooling and PgBouncer Say why PostgreSQL needs a pool Each connection is a separate server process. max_connections is 100 by default.
Share a few server connections among many clients pool_mode = transaction PgBouncer frees the connection after each transaction. Session mode waits for a disconnect.
Trap Transaction mode drops session state between transactions, so SET, LISTEN and session advisory locks break.
Security, audit & extensions control access, record activity, and add features Roles and privileges Create a group role and a login role CREATE ROLE analysts; CREATE ROLE app LOGIN PASSWORD 'pw';
Give a user a group's privileges GRANT analysts TO app;
Let a role read every table in a schema GRANT USAGE ON SCHEMA public TO analysts; GRANT SELECT ON ALL TABLES IN SCHEMA public TO analysts;
Cover tables created later ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO analysts; Only tables made by the role that runs it, unless you add FOR ROLE.
Trap A role needs USAGE on the schema as well as rights on its tables.
Connections and row-level security Require TLS and strong passwords hostssl all all 10.0.0.0/8 scram-sha-256 A pg_hba.conf line. The first match wins. MD5 passwords are deprecated.
Show each user only their own rows ALTER TABLE docs ENABLE ROW LEVEL SECURITY; CREATE POLICY own ON docs USING (uid = current_setting('app.uid')::int); The app runs SET app.uid = '42' after it connects.
Tip Connect the application as an ordinary role, never a superuser.
Trap Superusers always bypass row-level security. Table owners do too, unless the table has FORCE ROW LEVEL SECURITY.
Extensions and audit logging Add an extension to one database CREATE EXTENSION pgcrypto; Its files must already be on the server. Repeat it in each database.
Name extensions worth knowing PostGIS for geography, pgcrypto for encryption and hstore for key-value pairs.
Log slow statements and connections log_min_duration_statement = 1000 log_connections = on log_statement = 'all' logs requests, not what ran. That isn't a formal audit.
Write formal audit entries shared_preload_libraries = 'pgaudit' Restart, run CREATE EXTENSION pgaudit, then set pgaudit.log.
Trap An extension that needs shared memory, like pg_stat_statements , must be in shared_preload_libraries , then the server restarted.
Nothing matches. Try a shorter word, or clear the search.
Practice 42 interview questions Full answers, from beginner to expert.
Found this useful? Pass it on.
Share on LinkedIn
Share on X
Copy link
This sheet stays free. With Pro , you can:
Every answer on this sheet. Read the full answer to all 42 questions it links, not just the 5 free ones.My sheet. The cards you star and the ones you miss in Quiz me, from every sheet, on one page to quiz and print.PDFs. Download this sheet and My sheet, laid out for A4, with every question a link to its answer.The rest of Pro. Every answer in the question library, and 1,000 AI credits a month.
