Indexing
25 bites tagged Indexing — interview questions with model answers, and 60-second explainers.
Zero-downtime index migration on a hot table?
Build the index concurrently to avoid table locks, run off-peak with monitoring, and keep it reversible since dropping an index is cheap. Safe schema change at scale. A blocking CREATE INDEX that locks writes on a hot table.
Diagnose database CPU saturation under load
Find the expensive queries via the database's stats, check for missing indexes and full scans, then fix with indexing, query rewrites, caching, or read replicas. DB performance diagnosis.
Loki versus Elasticsearch for logs
Loki indexes only labels and stores raw log chunks, cheap but needs label-scoped brute-force search; Elasticsearch full-text indexes content, fast arbitrary search but costly to store… how indexing choice drives cost and query behavior.
Cutting managed database costs without breaking SLOs
Pool connections, prune and tune indexes, offload reads, tier or partition cold data, right-size storage IOPS. workload-aware cost optimization. only shrinking the instance and ignoring what drives the spend.
Diagnosing and optimizing a slow production query
Read the EXPLAIN ANALYZE plan, find the costly node, then fix via indexing, rewrite, or stats. methodical query-performance debugging. guessing at indexes before reading the actual execution plan.
Optimizing queries on a billion-row fact table
Partition to prune scans, index for selective lookups, materialize views to precompute aggregates; each adds write or maintenance cost. large-table query strategies.
Inverted index in search engines
An inverted index maps each term to the list of documents containing it, making keyword lookup O(1)-ish instead of scanning every document. understanding the core search data structure.
Why developers read query plans
The plan shows the operators the optimizer chose to run a query; developers read it to find why a query is slow; a common thing to look for is a full table scan where an index was expected. basic EXPLAIN literacy.
B+ Tree range queries across pages
Internal nodes are pages of keys guiding the search, all data sits in linked leaf pages; a range query descends to the start key then follows the leaf chain sequentially until the upper bound. how B+ Trees map to disk.
Indexing a low-cardinality status column
With three values each matches a third of rows, so the optimizer prefers a scan over costly random heap fetches; alternatives include partial indexes on rare values and composite indexes leading with status. index selectivity intuition.
B-Tree versus Hash indexes
B-Trees keep keys sorted, supporting equality, range, prefix, and ORDER BY; hash indexes give O(1) equality only, no ranges or ordering. matching index structure to access pattern.
What is a covering index?
A covering index contains every column a query needs so the engine answers from the index alone, skipping the table heap; build it by including filter, join, and selected columns. knowing index-only scans.
Trade-offs of adding indexes to a table
Indexes speed reads but slow writes since every INSERT, UPDATE, and DELETE must maintain them; they consume storage and can be unused on low-selectivity columns. understanding indexes cost more than they give.
What is a query execution plan?
The plan is the optimizer's chosen tree of operators; run EXPLAIN or EXPLAIN ANALYZE; watch for sequential scans, bad row estimates, and costly joins. practical query debugging. confusing estimated cost with actual time.
Clustered versus non-clustered indexes
A clustered index orders the table's actual rows, one per table; a non-clustered index is a separate structure pointing to rows. how index type affects physical row storage. thinking a table can have many clustered indexes.
Composite index column order for multi-column filters
Create one index on (last_name, first_name); order matters because the index serves leftmost-prefix lookups. understanding composite index leftmost-prefix rules.
What a database index is and when it helps
An index is a sorted lookup structure avoiding full scans, helps selective WHERE/JOIN columns, but costs write overhead. basic indexing intuition. indexing everything or ignoring the write and storage cost.
Pandas loc versus iloc indexing
Loc selects by label and is inclusive of both endpoints; iloc selects by integer position and is exclusive of the stop; passing a string label to iloc fails. practical pandas selection fluency.
Add a second access pattern to a key-value store
Add a global secondary index on EmailAddress, weighing extra storage, write amplification, and eventual consistency. secondary indexing in NoSQL. a full scan with a filter, or assuming indexes are free.
Explain database indexes, the classic data structure, and write-heavy trade-offs
Tests the read-write trade-off of indexing. A strong answer names B-Trees, explains they avoid full scans, and notes that inserts, updates, and deletes must update the index, adding write amplification and storage cost. Red flag: claiming indexes are free.
Design a real-time faceted search system for thousands of case studies
Tests inverted index architecture and faceting latency trade-offs. Strong answers compare Elasticsearch and Algolia on indexing speed and query overhead, then outline schema-first ingestion. Red flag: suggesting relational LIKE queries for real-time filtering.
Full-Text Search: Beyond Simple String Matching
Full-text search isn't just string matching; it's a search engine for your data that understands language. Use it for e-commerce search or log analysis. The footgun is thinking a simple `LIKE` query is a substitute for a real search engine like Elasticsearch.
Inverted Index: How Search Engines Find Your Keywords
An inverted index is like a book's index: it maps keywords to the documents containing them. This is the core of full-text search in search engines and databases, allowing instant lookups.
Composite Indexes: One Index for Multiple Columns
A composite index is like a phone book sorted by last name, then first name. It's for queries filtering on multiple columns, like finding a specific person. The footgun is creating separate indexes, which is far less efficient than a single composite one.
Get Indexing bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.