A Database Index Interview Exercise With a Write-Heavy Table
Evaluate a database index for a write-heavy table using a concrete tenant/status query and measured read, write and storage tradeoffs.
TL;DR
- Practice this fictional prompt: an events table receives frequent inserts and status updates.
- Compare the read improvement with write latency, resource use and storage under a realistic mixture of operations.
- Define the observation period and the signals that would make you reconsider the index.
Start with a workload, not a column name
Practice this fictional prompt: an events table receives frequent inserts and status updates. A dashboard repeatedly asks for the newest pending events for one tenant. Someone proposes indexing every filtered column. Explain which index you would investigate and what costs you would measure before adopting it.
Clarify the query, data distribution, write rate and required response time. A table being large does not by itself identify the right index. The exercise should show that you can connect an access pattern with an index candidate and then test the tradeoff under representative conditions.
Write the query you intend to support
Assume the dashboard filters by tenant and status, orders newest first and returns a small page. A simplified query is:
SELECT id, created_at, status
FROM events
WHERE tenant_id = :tenant
AND status = 'pending'
ORDER BY created_at DESC, id DESC
LIMIT 50;The unique identifier breaks ties in creation time. Ask whether the application really needs only these columns or retrieves a large payload too. Also ask how many rows are pending for a typical tenant and whether a few tenants dominate the table. Those details affect the likely benefit.
Propose one candidate and explain its shape
A candidate multicolumn index could begin with tenant and status, followed by the ordering columns. This is a hypothesis to test, not a universal prescription. PostgreSQL's multicolumn-index documentation explains how column order affects B-tree use; apply that behavior to the actual predicates and ordering.
| Query need | Candidate index consideration |
|---|---|
| Select one tenant | Tenant as a leading equality condition |
| Select pending status | Additional equality condition or a suitable partial-index design |
| Read newest first | Ordering columns in compatible order |
| Stable ties | Include the identifier in the order |
| Small result set | Check how much work precedes the limit |
A partial index limited to pending rows might be another candidate if that subset and the query pattern justify it. Explain the conditions under which it can be used rather than assuming every status query benefits equally.
Account for the write side
Indexes occupy storage and must be maintained as indexed data changes. The PostgreSQL index introduction explicitly discusses their overhead. In this exercise, status updates matter because rows move through the workflow and may change index membership or entries.
Do not say that reads matter more simply because the dashboard is visible. Inserts and updates may be part of the application's core service. Compare the read improvement with write latency, resource use and storage under a realistic mixture of operations.
If several existing indexes already cover related access paths, inspect whether the new one is redundant or whether an old one can eventually be removed after verification. Adding an index is easier to propose than maintaining an unexplained collection indefinitely.
Design a representative measurement
Use a staging or controlled test dataset that reflects important distribution features, including a large tenant and a small tenant. Inspect the query plan and actual work where safe, then compare the workload before and after the candidate index. Keep the database version and relevant settings consistent.
Do not run a tiny uniformly distributed fixture and claim a production speedup. The fixture can verify syntax and intended ordering, but performance needs representative evidence. Similarly, one fast cached execution does not establish behavior under the expected load.
Our system design frameworks guide can help keep the measurement tied to requirements. In an interview, it is acceptable to say which evidence you would collect rather than invent a benchmark number.
Ask whether the dashboard’s pending filter matches the application’s real status values and parameterization. A proposed partial index can be attractive on paper yet fail to support the query as planned. Verify the actual generated statement and plan rather than a hand-written approximation. Also include the cost of maintaining statistics and ordinary operational monitoring in the discussion, without assuming those tasks make every index prohibitively expensive.
Plan creation and rollback as operational work
Index creation itself has operational implications. Check the database's supported creation modes, locking behavior and limitations for the deployed version. Do not promise a lock-free or cost-free operation merely because a command includes a concurrency option.
Define the observation period and the signals that would make you reconsider the index. If read benefit is small while write cost rises materially, you may choose another query shape, a narrower index or a different dashboard requirement. A rollback can involve removing the candidate index after confirming it is not enforcing an essential constraint.
Use the mock interview strategy guide to retry with a query that filters across all tenants. Your candidate should change when the workload changes. A strong answer proposes an index for a specific access path, measures both sides of the tradeoff and avoids treating more indexes as automatic optimization.