One page
Databases cheat sheet
What to keep at hand. If a line is not clear, go back to the matching lesson in the course.
Open the course: Databases: going deeperPostgreSQL isolation levels
| What | How |
|---|---|
| Read Committed (default) | each statement sees a fresh snapshot; non-repeatable read, phantom, lost update are possible |
| Repeatable Read | one snapshot per transaction; no phantoms; a write conflict gives 40001 |
| Serializable | SSI, as if run one by one; catches write skew; transactions must be retried |
| Set the level | BEGIN ISOLATION LEVEL REPEATABLE READ; |
| Protection from lost update | UPDATE t SET n = n - 1 ... | SELECT ... FOR UPDATE | version column |
Index types
| What | How |
|---|---|
| B-tree | =, <, >, BETWEEN, ORDER BY, LIKE 'abc%' |
| Composite | CREATE INDEX ON orders (user_id, created_at); |
| Covering | CREATE INDEX ON orders (user_id) INCLUDE (total); |
| Expression | CREATE INDEX ON users (lower(email)); |
| Partial | CREATE INDEX ON orders (created_at) WHERE status = 'pending'; |
| GIN | jsonb, arrays, full-text search, pg_trgm |
| GiST | geodata, ranges, nearest neighbor search |
| BRIN | huge tables that grow over time |
| Hash | only = |
Scaling PostgreSQL
| What | How |
|---|---|
| Replica lag | SELECT replay_lag FROM pg_stat_replication; |
| Partitioned table | CREATE TABLE events (...) PARTITION BY RANGE (created_at); |
| Partition | CREATE TABLE events_2026_11 PARTITION OF events FOR VALUES FROM (...) TO (...); |
| Logical replication | CREATE PUBLICATION pub FOR TABLE orders; |
| Sharding | Citus or a shard key at the application level |
Which model to choose
| What | How |
|---|---|
| Relational (PostgreSQL) | transactions, JOINs, reports; the default choice |
| Document (MongoDB) | the entity is read as a whole, flexible structure |
| Key-value (Redis, DynamoDB) | cache, sessions, counters, access by key |
| Wide-column (Cassandra) | very many writes, queries known in advance |
| Graph (Neo4j) | queries that walk several hops of relationships |
PostgreSQL in production
| What | How |
|---|---|
| Real plan | EXPLAIN (ANALYZE, BUFFERS) SELECT ...; |
| Statistics for dependent columns | CREATE STATISTICS s (dependencies) ON city, country FROM addresses; |
| Job queue | SELECT ... FOR UPDATE SKIP LOCKED LIMIT 10; |
| Who is blocking | SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity; |
| Timeout before DDL | SET lock_timeout = '3s'; |
| PgBouncer | pool_mode = transaction |
| Logical backup | pg_dump -Fc -d app -f app.dump |
| Index without blocking | CREATE INDEX CONCURRENTLY ... |
| Constraint without blocking | ADD CONSTRAINT ... NOT VALID; VALIDATE CONSTRAINT ... |