SQL

Folders

• SQLAlchemy 16

Notes

• SQL fundamentals 6 Joins, grouping, subqueries and normalization are asked at every level.
• How a B-tree index works 8 "An index makes lookups O(log n) instead of O(n)" is true and explains almost nothing you actually need.
• How a query is processed 6 Two orders matter and they are different: the order the engine does its work in before executing anything, and the logical order the clauses evaluate in,…
• Transactions and ACID 6 Everyone can expand the acronym. The senior answer explains how the engine delivers each letter, because the mechanism is what tells you the limits: why a…
• Relationships and Keys in PostgreSQL 4 In relational databases like PostgreSQL, relationships describe how tables are linked to each other.
• SQLAlchemy 3 The de facto Python SQL toolkit. Two layers in one library: Core (a SQL-builder / expression language) and ORM (objects-to-rows mapping on top of Core).
• N+1 query problem The classic ORM trap: one query loads N parent rows, then N more queries load each parent's children.
• Transaction isolation levels 6 Isolation levels decide which interleavings of concurrent transactions you are allowed to observe.
• Connection pooling 4 A connection pool keeps a set of authenticated database connections open and lends them to requests, so a query does not pay a TCP handshake, TLS and…
• Reading a query plan 8 A query is slow. You have its plan.
• Index types 7 PostgreSQL has six built-in index access methods: B-tree, Hash, GIN, GiST, SP-GiST and BRIN.
• Window functions Window functions compute across a set of rows without collapsing them - a rank beside each row, a running total, the previous row's value.
• Zero-downtime migrations The goal: change schema while keeping the app serving traffic, with old and new code running simultaneously during the deploy window.
• Sharding and Partitioning 10 Both split a table into smaller pieces. Partitioning keeps the pieces within one database.
• CAP, ACID, BASE, PACELC 6 Vocabulary for talking about distributed-system trade-offs.
• Alembic 6 Schema migrations for SQLAlchemy. Interview-relevant because migration mistakes cause outages, and the questions are about the failure modes rather than the…
• MySQL 5 Most of this repo assumes Postgres. This file covers what differs, because "we're on MySQL" is a common interview follow-up and the differences are real.
• SQLite 5 The most deployed database in the world, and the one people underestimate.
• VACUUM, autovacuum, table bloat, and transaction wraparound 7 Postgres MVCC keeps old row versions for transactions that might still be reading them.
• PostgreSQL Advisory Locks 6 A native Postgres locking primitive separate from row-level locks.
• Replication and Logical Decoding 6 Postgres replicates via the write-ahead log (WAL).
• WAL, Checkpoints, and fsync 6 Postgres' durability story. The write-ahead log is the source of truth; checkpoints flush data; fsync makes it survive a crash.
• Isolation anomalies across databases 5 REPEATABLE READ in PostgreSQL and REPEATABLE READ in MySQL are different guarantees with the same name.
• Triggers in practice 7 Every team eventually needs a change log: who touched this invoice, when, and what did it look like before.
• CTEs and recursive queries 7 A common table expression names an intermediate result so a query reads as a sequence of steps instead of a nest of subqueries.
• Functions and procedures 5 Postgres will run your code. The question an interviewer is really asking is whether you know what the planner does with it — because a function is not a…
• Views and materialized views 4 The two share a keyword and almost nothing else.
• Why your index is not being used 6 You added the index. The query is still slow.
• Partition lifecycle: retention without bloat 5 14_sharding_partitioning.md covers what partitioning is and when to reach for it.
• PostgreSQL architecture 5 "Why PostgreSQL?" is easy to answer with a feature list.
• pg_stat_statements 4 EXPLAIN answers "why is this query slow?".