Composite indexes arrange multiple columns in a defined order. Their usefulness depends on predicates, ordering and planner choices.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: An index on tenant and created_at can support one tenant’s recent records. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Start with query predicates
Identify tenant filtering and recent-record ordering.
Step 2: Choose column order
Evaluate an index beginning with tenant and continuing with the relevant timestamp and tie-breaker.
Step 3: Measure maintenance costs
Include storage and writes as well as read latency.
Worked scenario
An index on tenant and created_at can support one tenant’s recent records.
A listing filters one tenant and orders by created_at plus ID. An index aligned with that pattern may reduce scanning and sorting. Its benefit depends on selectivity and planner choices; an index useful for this query may not efficiently support unrelated queries omitting its leading filter.
Common mistake
Adding every queried column to one giant index increases write and storage costs.
Verify the behavior
Inspect plans on realistic distributions and compare reads and write overhead.
Interview exercise
Choose an index for a listing.
Answer and reasoning
Start from its filter and sort pattern, inspect the plan and measure selectivity and maintenance overhead.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.