Indexing
Designing indexes per query shape, and paying for them on every write.
5 to work through
-
intermediate Multiple choice
A query filters on `lower(email)` and there is an index on `email`. The query is slow and the plan shows a sequential scan. Why, and what are the fixes?
2 min answer -
intermediate
A table has 14 indexes and writes have become slow. How do you decide which to remove?
2 min answer -
advanced
A search platform receives continuous document updates while users expect newly published content to appear quickly. How should indexing pipelines, replicas, refresh intervals, cache invalidation and query serving be separated?
2 min answer -
advanced
A source-code platform must make code across millions of repositories searchable, where repositories differ enormously in size and activity. Which indexing decisions dominate, and what makes this different from indexing documents?
2 min answer -
advanced
Prices change continuously based on demand and dates. Search must filter and sort by price over millions of listings. How?
2 min answer
6 terms in this topic
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.
patternFreshness Tiering
Splitting a search corpus into a small aggressively-refreshed index of recent documents and a large lazily-refreshed main index, so sub-second freshn…
metricIndex Selectivity
The fraction of rows a predicate eliminates — the property that determines whether an index is worth using at all.
conceptIndexing
Auxiliary structures that turn a scan into a lookup — the highest-leverage database intervention and the one most often applied blindly.
conceptPartial Index
An index built over only the rows matching a predicate, so it is far smaller and cheaper to maintain than a full index.
case-studyUber H3: Hexagonal Spatial Indexing
Uber built and open-sourced a hexagonal grid index because uniform neighbour distance matters when you are analysing supply and demand across space.
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.
Query Optimisation
Reading a plan, fixing statistics, and finding the real bottleneck.
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.