Learn AI ↗

Indexing

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

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

1 to work through

  1. intermediate

    A table has 14 indexes and writes have become slow. How do you decide which to remove?

    2 min answer
Terminology

3 terms in this topic

concept

Covering Index

An index that contains every column a query needs, so the query is answered from the index without reading the table at all.

 metric

Index Selectivity

The fraction of rows a predicate eliminates — the property that determines whether an index is worth using at all.

 concept

Partial Index

An index built over only the rows matching a predicate, so it is far smaller and cheaper to maintain than a full index.
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

Query Optimisation

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

1 quiz 3 cards 2 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