Query Optimisation
Reading a plan, fixing statistics, and finding the real bottleneck.
3 to work through
-
intermediate Multiple choice
An endpoint's p99 is 4 seconds. The database's slow query log is empty and CPU is low. What is happening?
2 min answer -
advanced
A query that ran in 50ms for two years now takes 90 seconds. Nothing was deployed and the data volume grew normally. What happened?
2 min answer -
advanced
In a multi-tenant platform where every tenant's data shares the same physical tables and tenants differ in size by orders of magnitude, why do query plans that are optimal for one tenant become pathological for another - and what is the fix?
2 min answer
4 terms in this topic
Cardinality Estimation
The planner's prediction of how many rows each step of a query will produce — the input that determines every other choice it makes.
conceptJoin Strategies
The three ways a database combines two row sets — nested loop, hash join and merge join — and the conditions under which each is correct.
practiceQuery Optimisation
Making the database do less work — usually by removing round trips and rows rather than by rewriting clever SQL.
practiceWrite-Amplification Audit
Accounting for everything a single logical write actually costs - indexes, replication, triggers, deletion of old rows - before concluding that a dat…
Neighbouring topics
Data Architecture
General material on structuring, storing and governing data.
Relational Modelling
Normalisation, keys, constraints and the invariants a schema enforces.
NoSQL Stores
Key-value, document, wide-column and graph — what each buys and forbids.
Indexing
Designing indexes per query shape, and paying for them on every write.
Transactions & Isolation
ACID, isolation levels, and the anomalies each level permits.
Replication
Primaries, replicas, lag, and synchronous versus asynchronous durability.
Partitioning & Sharding
Splitting data across machines, and the one-way door of a partition key.
Caching Strategies
Cache-aside, read-through, write-through and where each belongs.
Cache Invalidation
Stampedes, penetration, staleness windows and versioned keys.
CQRS
Separating the write model from the read models that serve queries.
Event Sourcing
Storing the change log as the system of record, and what that costs forever.
Change Data Capture
Turning a database's replication log into a stream, and its coupling risk.
Data Warehousing
Dimensional modelling, star schemas and analytical workloads.
Data Lakes & Lakehouses
Open formats on object storage with transactional metadata on top.
ETL & ELT
Where transformation happens, and how much raw history you keep.
Streaming Data
Windowing, watermarks, late arrivals and exactly-once semantics.
Data Governance
Ownership, lineage, quality, catalogues and who may see what.
Data Lifecycle & Retention
How long data is kept, where it ages to, and how it is actually deleted.
Polyglot Persistence
Choosing a store per workload, and the operational cost of variety.