Why should you care?

PostgresVerse has 50M orders but APIs only query status='pending' — index pending rows alone, not the whole table.

See it live — copy this example

Run in pgAdmin or psql.

CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

SELECT order_id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

What happened?

• Index entries exist only for pending orders.
• Query predicate must match the index predicate (or imply it) for the planner to use the index.

Practice next

1. Compare index size partial vs full on orders(status, created_at).
2. EXPLAIN query with status pending — confirm Partial Index Scan.
3. Query status completed and see different plan.
4. CREATE UNIQUE INDEX ON customers(email) WHERE deleted_at IS NULL;
5. Index only last 90 days: WHERE created_at > now() - interval '90 days'.

Remember

Predicate shrinks index to relevant rows. Query filter must align with index predicate. Great for sparse flags like is_active=true.

PostgresVerse ops queue

Kitchen display polls pending orders every second with tiny partial index.

Outcome: Write amplification drops because completed orders are not indexed.