Route here for: schema/table/key design, index decisions, query writing and
optimization, transaction/concurrency behavior.
Match your situation to a "load when" line; load only matching pages.
| Page |
Load when |
| index-selection |
Deciding whether a column/query deserves an index; a query is slow and you suspect a missing index |
| composite-index-column-order |
Creating a multi-column index; choosing column order for equality + range/sort queries |
| covering-indexes |
A query already served by an index still reads the table (heap) heavily; deciding whether to add INCLUDE/covering columns |
| partial-and-expression-indexes |
Queries always filter a fixed rare condition (status, deleted_at) or a function of a column (lower(email)); a uniqueness rule applies only to a subset of rows (e.g. live rows) |
| index-write-cost |
Adding indexes to write-heavy tables; bulk loads; auditing for unused/redundant indexes |
| Page |
Load when |
| reading-execution-plans |
A single query/statement is slow; verifying an index/query change with EXPLAIN before shipping (endpoint slow because it runs many fast queries → n-plus-one-queries) |
| keyset-pagination |
Implementing pagination, infinite scroll, or batch table walks |
| streaming-large-result-sets |
Exporting/reading a very large single-query result into the app; process memory peaks on fetchall or building a big file; server-side cursor blocked by autocommit or a read-only proxy |
| large-in-lists |
Building IN (...) queries whose list size can grow (batch lookups, fetch-by-ids) |
| n-plus-one-queries |
Loading a list plus per-row associations via an ORM; query count scales with result size |
| existence-and-count-checks |
Writing "is there any…", counts, badges, or gating logic on row presence |
| Page |
Load when |
| requirements-to-tables |
Turning feature requirements into tables, columns, and relationships |
| naming-conventions |
Creating or renaming a table/column/index/constraint and picking its name; setting conventions for a new project (skip when only changing existing objects' behavior) |
| primary-key-choice |
Choosing PK type (sequence vs UUID); ids exposed publicly; MySQL clustered-index concerns |
| foreign-keys-and-referential-actions |
Declaring FKs; choosing ON DELETE behavior; polymorphic/circular references; bulk loads under FKs |
| column-data-types |
Picking column types: money, time, text, enums, JSON, binary; changing a type on a live table |
| nullability-and-defaults |
Declaring column nullability/defaults; queries dropping rows around NULLs |
| soft-delete |
Deleted records themselves must be restorable or kept (deleted_at schemas); deciding what a parent's deletion does to children that must survive (for who-changed-what history → requirements-to-tables) |
| online-schema-changes |
Running ALTER TABLE / CREATE INDEX on a large table under live traffic; a migration blocks reads/writes (ACCESS EXCLUSIVE); adding a column/constraint/NOT NULL/index/type change safely; expand-and-contract to decouple DB migration from app deploy |
| Page |
Load when |
| autovacuum-and-wraparound |
A write-heavy table bloats or slows over time; tuning autovacuum for a hot table; monitoring/preventing transaction-ID wraparound (age(datfrozenxid)); the database starts refusing writes to avoid wraparound; deciding VACUUM vs VACUUM FULL vs pg_repack |
| Page |
Load when |
| concurrent-access-for-a-read-api |
Using SQLite as the store for an HTTP API that serves reads while a background job writes; database is locked under load; readers stalling during an import; choosing WAL vs rollback journal, busy_timeout, single-writer, per-connection pragma cost; scaling SQLite-backed reads across worker processes |
| Page |
Load when |
| isolation-level-selection |
Check-then-act writes, lost updates, duplicate bookings, choosing isolation/locking; deadlock-detected errors; oversell despite @Transactional |
| optimistic-vs-pessimistic-locking |
Multi-step read-modify-write that cannot fold into one UPDATE — choosing version-column optimistic vs FOR UPDATE by conflict frequency; stale form submits; retry storms on hot rows |