Learn AI ↗

Query Optimisation

Reading a plan, fixing statistics, and finding the real bottleneck.

1Questions
3Flashcards
2Terms
Drill this topic All of Data Architecture
Questions

1 to work through

  1. 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
Terminology

2 terms in this topic

concept

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.

 concept

Join 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.
Data Architecture

Neighbouring topics

Data Architecture

General material on structuring, storing and governing data.

7 quiz 25 cards 23 terms

Relational Modelling

Normalisation, keys, constraints and the invariants a schema enforces.

1 quiz 4 cards 3 terms

NoSQL Stores

Key-value, document, wide-column and graph — what each buys and forbids.

1 quiz 3 cards 3 terms

Indexing

Designing indexes per query shape, and paying for them on every write.

1 quiz 3 cards 3 terms

Transactions & Isolation

ACID, isolation levels, and the anomalies each level permits.

2 cards 2 terms

Replication

Primaries, replicas, lag, and synchronous versus asynchronous durability.

1 quiz 3 cards 3 terms

Partitioning & Sharding

Splitting data across machines, and the one-way door of a partition key.

2 cards 1 terms

Caching Strategies

Cache-aside, read-through, write-through and where each belongs.

1 cards 1 terms

Cache Invalidation

Stampedes, penetration, staleness windows and versioned keys.

1 cards 2 terms

CQRS

Separating the write model from the read models that serve queries.

1 quiz 2 cards 2 terms

Event Sourcing

Storing the change log as the system of record, and what that costs forever.

1 quiz 3 cards 3 terms

Change Data Capture

Turning a database's replication log into a stream, and its coupling risk.

1 quiz 3 cards 3 terms

Data Warehousing

Dimensional modelling, star schemas and analytical workloads.

1 quiz 3 cards 3 terms

Data Lakes & Lakehouses

Open formats on object storage with transactional metadata on top.

1 quiz 3 cards 3 terms

ETL & ELT

Where transformation happens, and how much raw history you keep.

1 quiz 3 cards 3 terms

Streaming Data

Windowing, watermarks, late arrivals and exactly-once semantics.

1 quiz 4 cards 3 terms

Data Governance

Ownership, lineage, quality, catalogues and who may see what.

1 quiz 3 cards 3 terms

Data Lifecycle & Retention

How long data is kept, where it ages to, and how it is actually deleted.

1 quiz 3 cards 3 terms

Polyglot Persistence

Choosing a store per workload, and the operational cost of variety.

2 quiz 2 cards 2 terms