Skip to content

Partial Index

1. What is it? (2-3 lines)

A Partial Index (or Filtered Index) is an index built over a subset of rows in a table defined by a conditional WHERE clause. Only table rows that satisfy the index predicate are included in the B+ tree index structure.

2. Why does it exist?

In large tables, indexing all rows is wasteful when 95% of queries filter for a tiny subset of rows (e.g. WHERE status = 'UNPROCESSED' or WHERE is_deleted = false). A full table index wastes massive disk space and RAM caching un-queried rows. Partial Indexes shrink index size by 90%+, drastically speeding up writes and cache hit ratios.

3. Where is it used?

  • PostgreSQL: Native CREATE INDEX idx ON tbl(col) WHERE predicate.
  • SQL Server: Filtered Indexes (WHERE predicate).
  • MongoDB: Partial Indexes (partialFilterExpression).

4. How does it work? (simple flow)

  1. Index Creation: Developer creates CREATE INDEX idx_unprocessed_orders ON orders (created_at) WHERE status = 'UNPROCESSED';.
  2. Row Ingestion (Write): When a row is inserted/updated, engine checks the WHERE status = 'UNPROCESSED' predicate.
  3. If status == 'PROCESSED': Row is NOT added to index tree! (Zero write amplification penalty).
  4. If status == 'UNPROCESSED': Row key is inserted into the partial B+ Tree index.
  5. Query Matching: Query SELECT * FROM orders WHERE status = 'UNPROCESSED' AND created_at > ... matches the partial index predicate and uses the index.
  6. Predicate Mismatch: Query SELECT * FROM orders WHERE status = 'COMPLETED' skips the index because the query predicate doesn't match the index predicate.

5. Advantages

  • Dramatic reduction in index storage footprint on disk and Buffer Pool RAM.
  • Zero write amplification for updates/inserts on rows outside the index predicate.
  • Allows enforcing unique constraints on a row subset (e.g., unique active email per tenant: UNIQUE(email) WHERE status = 'ACTIVE').

6. Limitations

  • Query optimizer will ONLY use the partial index if the query WHERE clause explicitly contains or implies the partial index predicate.
  • Parameterized queries / Prepared statements with dynamic variables may fail to leverage partial indexes if the optimizer cannot guarantee predicate alignment at plan time.
  • Not supported natively in MySQL (requires generated virtual columns workarounds).

7. Common interview questions (10)

  1. How does a Partial Index differ from a Full Table Secondary Index?
  2. How do Partial Indexes drastically reduce write amplification for high-velocity insert tables?
  3. How do you implement a Unique Constraint that applies ONLY to active non-deleted rows using a Partial Index?
  4. Why will SELECT * FROM orders WHERE status = $1 fail to use a partial index created WHERE status = 'PENDING'?
  5. How does a Partial Index improve Buffer Pool RAM cache efficiency?
  6. How can you emulate Partial Indexes in MySQL InnoDB using Virtual Columns?
  7. What happens when an updated row's status changes from UNPROCESSED to PROCESSED in a partial index?
  8. How does a Partial Index interact with VACUUM and index maintenance in PostgreSQL?
  9. When should you use a Partial Index vs Partitioning a database table?
  10. How does a Partial Composite Index optimize multi-tenant queue processing systems?

8. Common misconceptions

  • Misconception: Partial indexes can index only specific columns. Fact: Indexing specific columns is a standard index. A Partial Index indexes a subset of ROWS based on a boolean condition.
  • Misconception: Queries using static constants automatically match partial index predicates. Fact: The query predicate must mathematically imply the partial index predicate.

10. 30-second cheat sheet

  • DDL Syntax: CREATE INDEX idx ON orders (created_at) WHERE status = 'PENDING';.
  • Core Benefit: Indexes ROW SUBSETS only. Cuts index disk size and write overhead by 90%+.
  • Top Use Cases: Active queues (status='PENDING'), soft deletes (is_deleted=false), partial unique constraints.
  • Gotcha: Query WHERE clause MUST explicitly include the exact partial index predicate.