Skip to content

Relational databases: PostgreSQL in depth

PostgreSQL in depth: indexes and query plans, N+1 queries, window functions, MVCC and isolation, locking, schema design, migrations and scaling.

17 lessons

Indexes, plans and queries

  1. 1B-tree indexes, pages and what indexes costA lookup costs one page per level, and levels grow by hundreds; every index you add is written on every insert.
  2. 2Composite indexes, column order and coveringColumn order decides which questions the index answers in one stretch: equality columns first, then one range or sort.
  3. 3Partial, expression, covering (INCLUDE), GIN and BRIN indexesShape the index to the query, and know what it still has to read.
  4. 4Reading EXPLAIN (ANALYZE, BUFFERS): scans, joins, estimates vs actualsRead a plan from the bottom: the lowest node whose estimate misses its actual rows is where the planner went wrong.
  5. 5N+1 queries, joins and batchingN+1 is a count that grows with the list, not one slow query. Count statements per request, and load each relation level once.
  6. 6SQL fluency: GROUP BY vs window functions (top-N per group, ranking, running totals)A window computes across related rows and keeps every row; it runs after WHERE, so filter on it one query out.

Transactions and concurrency

  1. 7How PostgreSQL delivers ACID: WAL and MVCC row versionsAn UPDATE adds a row version and never overwrites one; snapshots let readers and writers pass, and VACUUM clears only versions nobody can still see.
  2. 8Isolation levels and anomalies in PostgresPostgres never shows uncommitted rows; the level decides when your snapshot is taken, and whether anyone checks what you decided from it.
  3. 9Preventing lost updatesHand the change to the database: a waiting UPDATE applies its arithmetic and its WHERE to the newest committed row, while app-computed values go stale.
  4. 10Row locks, SKIP LOCKED, lock timeouts and deadlocksA lock is a wait until commit; only a circle of waits is a deadlock, so take shared rows in one order.

Schema design and evolution

  1. 11Normalization vs deliberate denormalizationStore each fact once so no write can leave two versions; copy a fact for reads only with a path that keeps the copy right.
  2. 12Keys and constraints: UUIDv4, UUIDv7 or bigint, and constraints as the last line of defencePick a key that sorts by time; let constraints refuse bad rows, and know where NULLs, row-by-row checks and range ends slip through.
  3. 13Zero-downtime schema migrations: expand, migrate, contractChange a live schema only in steps both running versions survive, and never let DDL wait in front of your traffic.
  4. 14Prisma and ORM pitfalls: transactions, raw SQL, over-fetching, connectionsAn ORM call is SQL on a borrowed connection: see the SQL, select what you need, and keep slow work outside the transaction.

Scaling the database

  1. 15Replication and read replicasA replica is the primary some way in the past: fine for reads that tolerate that, wrong for users who must see their writes.
  2. 16Partitioning and sharding: pruning, retention, shard keysPartitions are skipped only by conditions on the partition key; a shard is reached directly only by a query that carries the shard key.
  3. 17MySQL/InnoDB against PostgreSQL: clustered keys, gap locks, DDL commits and undo logsSame SQL, different engine: InnoDB keeps rows in key order, locks the gaps it scans, commits on DDL and rebuilds old versions from undo logs.