Partial Index Concept

An index over only some of the rows

A partial index (sometimes called a "filtered index") is an index that covers only a subset of a table's rows — those matching a specified condition — rather than every row in the table.

sql
CREATE INDEX idx_active_students ON students (department) WHERE gpa >= 2.0;

Suppose in our 1,000,000-row students table, 950,000 students have gpa >= 2.0 ("in good standing") and 50,000 do not. If the application very frequently runs queries like:

sql
SELECT * FROM students WHERE department = 'CS' AND gpa >= 2.0;

...a partial index built with the WHERE gpa >= 2.0 condition only ever needs to store entries for the 950,000 qualifying rows relevant to that condition — wait, actually the more valuable scenario is the reverse: if only a small subset of rows commonly matter (e.g., WHERE status = 'pending' on a mostly-'completed' orders table), the partial index would be tiny and fast compared to indexing the entire table.

Why smaller can be better

A partial index restricted to a condition that only a fraction of rows satisfy is:

• Smaller — less disk space, since only qualifying rows have entries.
• Faster to scan — fewer entries to search through even in the index itself.
• Cheaper to maintain — writes to rows that don't match the partial condition never touch this index at all, reducing write overhead (20.14) compared to a full index on the same column.

This makes partial indexes ideal when queries consistently filter on the same subset — e.g., "active" records, "pending" orders, non-deleted rows in a soft-delete scheme — rather than needing the whole table indexed.

Not universally supported

Dialect support varies significantly:

• PostgreSQL: full native support via the WHERE clause on CREATE INDEX.
• SQL Server: supports a similar concept called "filtered indexes."
• MySQL: as of most versions, has no native support for partial/filtered indexes — this is a real, practical dialect gap developers must work around (e.g., using generated/virtual columns or restructuring the schema).

Edge cases

• A query must match the partial index's condition (or a subset of it) for the optimizer to even consider using it — WHERE gpa >= 2.0 doesn't help a query filtering WHERE gpa < 2.0, since none of the required rows are in the index at all.
• Partial indexes are commonly used to enforce a uniqueness rule over only "active" rows in soft-delete schemes (e.g., UNIQUE (email) WHERE deleted_at IS NULL), which combines the ideas from 20.9 and 20.10.
• If application query patterns shift and the filtered condition no longer matches common queries, the partial index quietly becomes useless without any error — it just stops being chosen by the optimizer.

Interview-style Q&A

Q: What is a partial index, and why would you use one? A: It's an index built over only the rows matching a specific condition, useful when queries consistently filter to that same subset — it's smaller, faster to scan, and cheaper to maintain than indexing the whole table.

Q: Does every SQL dialect support partial indexes? A: No — PostgreSQL and SQL Server support the concept (partial/filtered indexes), but MySQL has no native equivalent.

Key takeaway: a partial index narrows the "index everything" trade-off down to just the rows that matter for a known, consistent query pattern.