What is a partial index — an index created with a WHERE predicate so it contains only a subset of a table's rows — and what problem does it solve? Give a workload where it clearly pays off.
answer
- index with a WHERE clause = subset only
- tiny index → cached, shallow, precise estimates
- non-matching rows cost zero writes
- queue: status = pending; soft delete: deleted_at IS NULL
- query must imply the index predicate
basics
~20 sA partial index indexes only rows matching a predicate, so it is smaller, cheaper to maintain, and better cached. It pays off when queries always target a small slice of the table — for example only unprocessed jobs in a queue table where most rows are already done.
solid answer
~50 sA partial index carries a filter condition in its definition, so only rows satisfying that condition get index entries. Everything else — usually the overwhelming majority — is simply absent. The payoff is threefold. **Size**: an index over 1% of a billion-row table is a hundredth the size, so it fits in cache and its tree is shallower. **Write cost**: rows outside the predicate cause no index maintenance at all on insert, and a row that moves out of the predicate causes a delete from the index rather than an update, so hot rows stop touching it. **Plan quality**: because every entry qualifies, the planner's estimate for the filtered query is sharper. The classic case is a queue or state column with heavy skew: a `jobs` table where 99.9% of rows are `done` and queries only ever look for `pending`. Related cases are soft-delete flags and "only rows where a nullable column has a value".
code
sql · 9 linesCREATE INDEX idx_jobs_pending
ON jobs (enqueued_at)
WHERE status = 'pending';
-- uses the index: the filter implies the index predicate
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY enqueued_at
LIMIT 10;go deeper
Define it, say it is smaller and cheaper to maintain, and give the queue or soft-delete example.
Add the three savings explicitly, explain that the query must constrain the predicate column, and choose key columns that serve the ordering the query needs.
Discuss churn on the hot subset, when to keep a full index alongside, and how estimation improves for skewed predicates.
Treat it as index-portfolio design: total write amplification across all indexes, which access paths must survive future query changes, and whether a partial index is masking a table that should be split or archived.
## Definition A **partial index** (some engines call it a filtered index) is an index whose definition includes a predicate. Only rows for which the predicate evaluates true are represented in the index; the rest have no entry. Everything else about the index — its structure, its column list, its uniqueness — works normally. Contrast this with an ordinary index, which necessarily has one entry per row of the table (per non-null key, depending on engine rules). ## Why a smaller index is not a marginal win Three independent effects compound. **Storage and cache residency.** If 0.1% of rows qualify, the index is roughly 0.1% of the size. A full index that spills to disk becomes one that lives permanently in the buffer cache, which changes lookup cost from physical reads to memory hits. Fewer levels in the tree also means fewer page accesses per probe. **Write amplification.** Every index on a table is work on every write: inserting a row inserts into every index; updating an indexed column deletes and reinserts an entry; deleting a row removes entries everywhere. With a partial index, rows that never satisfy the predicate cost nothing. In the queue example, the enormous stream of already-completed rows sitting in the table imposes zero maintenance, and a job leaving the pending state removes one index entry and is thereafter free. **Estimation quality.** Optimisers estimate row counts from statistics. On a full index over a heavily skewed column, an estimate for the rare value can be wrong by orders of magnitude, producing bad join orders. A partial index restricted to that value gives a small, precisely-scoped structure; entries are all matches, so scanning it is pure signal with no discarded rows. ## The canonical workloads - **Queue / state tables.** `WHERE status = 'pending'` on a table where finished rows dominate. Queries never look at completed work, so indexing it is pure overhead. - **Soft deletes.** `WHERE deleted_at IS NULL`. Application queries almost always exclude deleted rows; the archive is only touched by rare audits. - **Sparse attributes.** A nullable column that is populated for a small minority of rows — index only the populated rows. - **Multi-tenant hot spots.** One tenant, or one small set of high-value rows, that is queried very differently from the rest. - **Conditional uniqueness**, covered separately — a unique index with a predicate enforces uniqueness only within the subset. ## The requirement that catches people A partial index is only usable when the optimiser can prove the query's own filter implies the index predicate. If the index says `WHERE status = 'pending'` and the query does not constrain `status` at all, the index cannot be used — it does not contain the rows the query might need, and the engine has no way to know that. So the predicate column typically must appear in the query, even when it feels redundant to the application developer. A second consequence: the predicate must be checkable against the query at planning time, which is why comparisons to constants work reliably and comparisons involving values not known until execution may not. That is worth its own study. ## When it is the wrong tool - **When queries span the whole table.** If some reports need completed rows too, you either keep a full index as well — giving up the write-cost saving — or accept scans for those reports. - **When the predicate value keeps changing.** Rows flipping in and out of the predicate cause index inserts and deletes, and heavy churn on a small hot index can create contention and bloat on those pages. - **When the subset is not actually small.** A predicate matching 60% of rows saves little and adds a usability constraint. The rule of thumb is that the subset should be a small minority. - **When you need one index to serve many predicates.** Ten partial indexes for ten predicate variants cost more in aggregate than one general index. ## Choosing the columns A subtle design point: once the predicate pins a column to a constant, that column usually does not need to be in the index key as well. If the index filters to pending rows, the keys should be whatever the query orders or filters by next — for example the enqueue timestamp — so that the index directly serves "oldest pending job first". This is where partial indexes deliver their sharpest results: a tiny structure whose every entry is a candidate, in exactly the order the query wants. ## Summary for an interview Say what it is, name the three savings (size, write cost, estimate quality), give the queue or soft-delete example, and then volunteer the catch: the query must constrain the predicate column so the planner can prove the index is safe to use.
- Does the predicate column still need to be in the index key list?Usually not. If the predicate pins status to a single constant, every entry has the same status value, so storing it adds bytes without adding selectivity. Spend the key columns on what the query filters, joins, or orders by next — typically a timestamp or an identifier. The exception is a predicate that still allows several values, such as a set or an inequality, where the column can remain useful as a key.
- What happens to a row that stops satisfying the index predicate?Its entry is removed from the partial index as part of the update, exactly as if the row had been deleted from the index's point of view. That is why queue workloads suit partial indexes so well: a job leaving the pending state shrinks the index rather than growing it, and afterwards that row imposes no further maintenance cost.
Instead of indexing every book in a warehouse, you keep a card catalogue only for the books currently on loan. It stays pocket-sized and instantly searchable, because 99% of the stock never gets a card.
saying these in an interview costs you the question
- Believing a partial index still contains all rows and just filters them on read
- Expecting the planner to use it for a query that does not constrain the predicate column
- Using a predicate that matches most of the table and calling it a partial index win
- Assuming a partial index removes the need for statistics or that estimates are always exact
- Adding many overlapping partial indexes and ignoring their combined write cost