Why This Matters
Partial indexes only include rows matching a WHERE condition, making them smaller and faster when queries consistently filter on the same condition. This is perfect for:
- Soft deletes: Only index active (non-deleted) records
- Status columns: Only index pending orders, not completed ones
- Null handling: Only index rows where a column is NOT NULL
The Problem
Incorrect - Full index includes irrelevant rows
-- Index includes all rows, even soft-deleted ones
CREATE INDEX users_email_idx ON users (email);
-- Query always filters active users
SELECT * FROM users
WHERE email = '[email protected]' AND deleted_at IS NULL;The Solution
Correct - Partial index matches query filter
-- Index only includes active users
CREATE INDEX users_active_email_idx ON users (email)
WHERE deleted_at IS NULL;
-- Query uses the smaller, faster index
SELECT * FROM users
WHERE email = '[email protected]' AND deleted_at IS NULL;Key insight: The query's WHERE clause must match or be more restrictive than the index's WHERE clause for Postgres to use the partial index.
Common Use Cases
-- Only pending orders (status rarely changes once completed)
CREATE INDEX orders_pending_idx ON orders (created_at)
WHERE status = 'pending';
-- Only non-null values
CREATE INDEX products_sku_idx ON products (sku)
WHERE sku IS NOT NULL;
-- Only recent data
CREATE INDEX logs_recent_idx ON logs (user_id)
WHERE created_at > '2024-01-01';Check Your Project Automatically
Connect your Supabase project to identify opportunities for partial indexes.
Start Supabase AuditCommon Mistakes
- Query doesn't match index condition:
WHERE email = ?won't use an index that hasWHERE deleted_at IS NULL- you must include the same filter in your query. - Condition changes frequently: If most rows match the condition, a partial index provides little benefit.
- Forgetting about unique constraints: You can create partial unique indexes too:
CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL