Partial indexes: indexing only what matters

Capsule description

Up to here, all your indexes have indexed the whole table. Each row has its entry in each index, regardless of whether the query will look for it or not.

But think about these real cases:

• A users table with 10 million rows, 9.5 million soft-deleted (deleted_at IS NOT NULL). Every useful query filters WHERE deleted_at IS NULL — you care about 5%.
• An orders table with status IN ('pending', 'completed', 'cancelled'). 95% are completed. The app's queries almost always look for the pending ones (4%).
• An events table partitioned by tenant with multi-tenancy: each tenant only searches their own events. The global index includes events from all 1,000 tenants.

In all three cases, indexing the whole table is a waste. Most of the index is never used because the queries always filter by a condition that discards the majority.

Partial indexes solve exactly this. Simple syntax:

CREATE INDEX idx_users_active
ON users(id)
WHERE deleted_at IS NULL;

The index contains only the rows that meet the condition. For 10M rows with 5% active, the index has ~500k entries — 20x smaller than a traditional index. Smaller = faster to maintain, more fits in cache, faster queries.

This capsule teaches you when to apply partial indexes, their exact syntax, and the most common trap: the partial's condition must match the query's WHERE, or the planner ignores the index. A subtle detail that decides whether the optimization works or not.

Concrete objective: you'll be able to identify when a table is a candidate for a partial index, design it correctly, and validate with EXPLAIN that the planner chooses it over a full index.

Mental model: the "filtered" index

A partial index is a normal index with a WHERE clause that limits which rows are included. Only the rows that meet that condition have an entry in the index.

Traditional index over 10M rows, 95% deleted:
  → 10M entries in the tree
  → takes 600 MB on disk
  → each UPDATE/INSERT/DELETE updates the index
  → queries with WHERE deleted_at IS NULL read the tree and filter out 95%

Partial index WHERE deleted_at IS NULL:
  → 500k entries in the tree
  → takes 30 MB on disk (20x less)
  → updates to deleted rows don't touch the index
  → queries with WHERE deleted_at IS NULL access the relevant rows directly

Basic syntax

CREATE INDEX idx_name
ON table(column_to_index)
WHERE condition;

The condition can be any predicate:

• WHERE deleted_at IS NULL
• WHERE status = 'pending'
• WHERE active = true AND tenant_id = 42
• WHERE created_at > '2026-01-01' (careful: static, doesn't update itself)
• WHERE amount > 1000

And it can be combined with composite and INCLUDE:

CREATE INDEX idx_orders_pending
ON orders(customer_id, created_at)
INCLUDE (total)
WHERE status = 'pending';

Composite + covering + partial. All together.

The main trap: predicate matching

A partial index is only used if the query's WHERE includes a condition compatible (equal or subset) with the index's WHERE.

Example that works

Index:

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

Queries that do use it:

-- ✅ Exact predicate
SELECT * FROM orders
WHERE status = 'pending' AND customer_id = 42;

-- ✅ Equal predicate + additional filters
SELECT * FROM orders
WHERE status = 'pending' AND customer_id = 42 AND total > 100;

-- ✅ Exact predicate with no more filters
SELECT * FROM orders WHERE status = 'pending';

Example that does NOT work

Same index. Queries that don't use it:

-- ❌ Without the partial's condition
SELECT * FROM orders WHERE customer_id = 42;
-- The planner can't assume that customer_id = 42 implies status = 'pending'.
-- It goes to Seq Scan or uses another index.

-- ❌ Different predicate
SELECT * FROM orders WHERE status = 'completed' AND customer_id = 42;

-- ❌ Broader predicate (superset)
SELECT * FROM orders WHERE status IN ('pending', 'completed');
-- 'completed' isn't in the partial. The planner discards the index.

-- ❌ Predicate with an expression the planner doesn't equate
SELECT * FROM orders WHERE status LIKE 'pend%';
-- LIKE isn't strictly equivalent to = 'pending' for the planner,
-- even if conceptually they may be equivalent.

Key point: the planner is literal. If your partial says WHERE status = 'pending', the query must include WHERE ... status = 'pending' ... (it can have more AND conditions, but that exact one must be there). Syntactic variations can break the matching.

Subtle case: a subset but the planner doesn't "see" it

CREATE INDEX idx_orders_active_year
ON orders(customer_id)
WHERE status = 'pending' AND created_at >= '2026-01-01';

Query:

SELECT * FROM orders
WHERE status = 'pending'
  AND created_at >= '2026-06-01'
  AND customer_id = 42;

Does it use it? Sometimes yes, sometimes no. The planner has to deduce that created_at >= '2026-06-01' implies created_at >= '2026-01-01'. For simple constants, it usually deduces it. For more complex expressions, it doesn't.

Practical rule: the partial is safest when its WHERE is as simple as possible and the query replicates it almost verbatim. The more subtle the expected deduction, the greater the risk of the planner ignoring the index.

Classic use cases

Case 1: soft delete

A very common pattern. Apps don't physically delete, they mark deleted_at with a timestamp.

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT UNIQUE,
    name        TEXT,
    deleted_at  TIMESTAMPTZ
);

After months, 80-95% of the rows are soft-deleted. Every useful query does:

SELECT * FROM users WHERE deleted_at IS NULL AND email = ?;

Design:

-- Replaces the original UNIQUE
DROP INDEX users_email_key;
CREATE UNIQUE INDEX idx_users_email_active
ON users(email)
WHERE deleted_at IS NULL;

Benefits:

• Index 5-20x smaller (only active ones).
• UNIQUE applies only to active users: you can "delete" a user with email foo@bar.com and then register another one with the same email.
• Active queries are faster and use less cache.

Case 2: skewed enum

status with values ('completed', 'pending', 'cancelled'). Distribution 95/4/1. Queries almost always look for pending.

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

Pending are 4% of the table. The index is 25x smaller. Queries that filter by pending are much faster.

For cancelled (1%): if you also search for it often, another partial WHERE status = 'cancelled'. If very rare, don't index it.

For completed (95%): it doesn't deserve a partial — it covers almost everything. If you need to filter by completed, the traditional index or a composite that includes status may be better. Or no index and Seq Scan (it's 95% of the table, reading it whole isn't absurd).

Case 3: a common business condition

A column that's always filtered with the same condition:

CREATE INDEX idx_products_in_stock
ON products(category_id, name)
WHERE in_stock = true;

If your app never shows out-of-stock products, the index only needs the in_stock = true ones. Smaller, faster.

Case 4: multi-tenancy with a small tenant

You have a table shared between tenants, but a giant tenant (Enterprise Customer) represents 80% of the data. For the small tenants, a global index is inefficient.

CREATE INDEX idx_events_small_tenants
ON events(user_id, created_at)
WHERE tenant_id != 1;  -- or WHERE tenant_id IN (list of small tenants)

It works for the small tenants' queries without polluting the index with the large tenant's millions of rows.

(This is an advanced scenario. Serious multi-tenancy is covered in guide #13 or by using partitioning, guide #14. Here we only point out the pattern.)

Case 5: queries by recent ranges

CREATE INDEX idx_orders_recent
ON orders(customer_id, created_at)
WHERE created_at >= '2026-01-01';

Careful: the condition is static. As the months pass and most queries still filter "from the last year", the partial's condition becomes obsolete. You'd need to recreate the index periodically with the new cutoff date. That's why this case requires explicit maintenance (see module 7 / capsule 07).

For genuinely "recent" queries, BRIN indexes or partitioning are usually better long-term options (guide #14).

Worked example: table with soft delete

You'll set up a table with 90% deleted and compare a traditional index vs a partial.

Setup

DROP TABLE IF EXISTS demo_users;
CREATE TABLE demo_users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL,
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at  TIMESTAMPTZ
);

-- 1M rows, 90% deleted
INSERT INTO demo_users (email, name, created_at, deleted_at)
SELECT
    'user' || g || '@example.com',
    'User ' || g,
    NOW() - (random() * INTERVAL '730 days'),
    CASE WHEN random() < 0.9
         THEN NOW() - (random() * INTERVAL '180 days')
         ELSE NULL
    END
FROM generate_series(1, 1000000) g;

ANALYZE demo_users;

Verification:

SELECT
  COUNT(*) FILTER (WHERE deleted_at IS NULL) AS active,
  COUNT(*) FILTER (WHERE deleted_at IS NOT NULL) AS deleted,
  COUNT(*) AS total
FROM demo_users;

Expected output:

active  | deleted | total
--------+---------+--------
~100000 | ~900000 | 1000000

Version 1: traditional index

CREATE INDEX idx_users_email ON demo_users(email);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name FROM demo_users
WHERE deleted_at IS NULL AND email = 'user12345@example.com';

Expected output:

Index Scan using idx_users_email on demo_users
  Index Cond: (email = 'user12345@example.com'::text)
  Filter: (deleted_at IS NULL)
  Rows Removed by Filter: 0 (or 1 if the row is deleted)
  Buffers: shared hit=4
Execution Time: 0.234 ms

It works. But the index has 1M entries. Size:

SELECT pg_size_pretty(pg_relation_size('idx_users_email'));
-- ~50 MB

Version 2: partial index

DROP INDEX idx_users_email;
CREATE INDEX idx_users_email_active
ON demo_users(email)
WHERE deleted_at IS NULL;

ANALYZE demo_users;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name FROM demo_users
WHERE deleted_at IS NULL AND email = 'user12345@example.com';

Expected output:

Index Scan using idx_users_email_active on demo_users
  Index Cond: (email = 'user12345@example.com'::text)
  Buffers: shared hit=3
Execution Time: 0.187 ms

Differences:

For this individual query, the time improvement is modest (it was already fast). The big benefit is in:

1. Size on disk (10x less).
2. Maintenance: deletes/updates to already-deleted rows don't touch this index.
3. Cache: the smaller index fits in shared_buffers, a better cache hit ratio.
4. Bulk operations: if you do UPDATE demo_users SET ... WHERE deleted_at IS NOT NULL (over the 900k deleted), you don't touch this index. With the traditional one you would.

Verification: query WITHOUT the partial's condition

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name FROM demo_users
WHERE email = 'user12345@example.com';

Expected output:

Seq Scan on demo_users
  Filter: (email = 'user12345@example.com'::text)
  Rows Removed by Filter: 999999
  Buffers: shared hit=...
Execution Time: 80ms

Seq Scan. The planner doesn't use the partial because the query doesn't include deleted_at IS NULL. The searched-for row could be deleted, and the partial wouldn't index it — the planner has to assume the worst.

Lesson: if your app sometimes searches by email without filtering deleted, you'll need two indexes (one partial for active, one full for "anyone"), or rewrite the query to include WHERE deleted_at IS NULL whenever possible.

Partial UNIQUE: pedagogical bonus

DROP INDEX idx_users_email_active;
CREATE UNIQUE INDEX idx_users_email_active
ON demo_users(email)
WHERE deleted_at IS NULL;

Now you can have:

-- This works, even if there's a "deleted" user with the same email
INSERT INTO demo_users (email, name) VALUES ('user12345@example.com', 'New User');

If you had had a global UNIQUE on email, you couldn't reuse the email after a soft delete. The partial UNIQUE is the idiomatic solution for this pattern.

Why does this matter in real work?

1. Large tables with "dead" data become manageable.

Apps with years of operation accumulate old data: deleted users, cancelled orders, archived events. Without a partial, the indexes grow indefinitely and all the queries pay that weight. With a partial, the indexes are the size of the "live" data.

2. Partial UNIQUE enables idiomatic soft delete patterns.

Without a partial, soft delete with UNIQUE on email is a pain: you have to do tricks like adding deleted_at to the UNIQUE composite. With a partial, it's trivial.

3. You reduce storage cost.

In the cloud (RDS, GCP, etc.), space costs. An index 20x smaller is 20x less disk.

4. You improve EXPLAIN reading.

A plan where idx_orders_pending appears tells you immediately what the query does: it filters pending. Much more expressive than idx_orders_status_customer_created that covers everything.

Traps and common mistakes

Mistake 1 (conceptual): the query doesn't include the partial's condition

Symptom: you create the partial, capture the plan, it still shows Seq Scan or uses another index.

Why it happens: the query doesn't include a condition compatible with the partial's WHERE. The planner can't use the index.

How to detect: check that the query's WHERE literally contains the partial's condition (or a subset the planner can deduce).

How to fix it: two options:

1. Rewrite the query to include the condition: WHERE deleted_at IS NULL AND .... If it's an endpoint of your app, adding the filter is usually correct (queries that do NOT filter soft-deleted are probably bugs).
2. If you can't change the query, don't use a partial — use a full or composite index that includes the condition's column.

Mistake 2 (practical): the partial's condition is too complex

Symptom: you create a partial with WHERE col_a = X AND col_b > Y AND col_c IS NOT NULL, queries with those exact predicates do use it, queries with a subtle variation don't.

Why it happens: the planner is literal. Each additional condition in the partial is a condition the query must replicate exactly.

How to fix it: keep the partial's WHERE as simple as possible — ideally a single condition with a constant value (status = 'pending', deleted_at IS NULL, active = true).

Mistake 3 (conceptual): a partial with a condition that changes over time

Symptom: you create WHERE created_at >= '2026-01-01'. In January 2027, queries with WHERE created_at >= '2027-01-01' no longer use the partial.

Why it happens: the partial filters by a constant. When the "window of interest" moves, the partial falls behind.

How to fix it:

• Recreate the partial periodically with the new date (a scheduled maintenance process).
• Use BRIN indexes (efficient for columns correlated with the physical order, like timestamps).
• Partition the table by date range (guide #14).

Each solution has trade-offs. For apps with a clear window of interest and monthly movement, partitioning is usually the right answer.

Mistake 4 (conceptual): assuming a partial UNIQUE replaces a full UNIQUE

Symptom: you create CREATE UNIQUE INDEX ... ON users(email) WHERE deleted_at IS NULL and assume email is unique globally.

Why it's wrong: the partial UNIQUE only guarantees uniqueness among rows that meet the condition. You can have multiple rows with the same email if they're deleted.

How to fix it: understand that this is exactly what you want in soft delete. If you need global uniqueness (even among soft-deleted), don't use a partial — but then you have the problem of not being able to reuse emails after a delete.

Mistake 5 (practical): not recreating the partial after schema changes

Symptom: you add a new column (is_premium), and all the critical queries now filter by is_premium = true. The partial WHERE deleted_at IS NULL is no longer optimal: it indexes all the active ones, but the queries only want the active premium ones.

How to fix it: create a more specific partial: WHERE deleted_at IS NULL AND is_premium = true. Re-evaluating indexes when the query patterns change is a recurring discipline.

Mistake 6 (conceptual): a partial on a low-cardinality column with no condition

Symptom: you create CREATE INDEX ON users(active) WHERE active = true thinking the partial helps.

Why it's sub-optimal: the partial WHERE active = true already filters to the active rows, so the active column always equals true in the index. Indexing active adds nothing — every entry has the same value.

How to fix it: index another column that does discriminate between the active rows. For example, CREATE INDEX ON users(email) WHERE active = true.

Exercises

Exercise 1: predict the partial's usage

You have the partial:

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

For each query, predict whether it uses it.

1. WHERE customer_id = 42 AND status = 'pending'
2. WHERE customer_id = 42
3. WHERE status = 'pending' AND created_at > '2026-01-01'
4. WHERE status IN ('pending', 'completed') AND customer_id = 42
5. WHERE customer_id = 42 AND status = 'pending' AND total > 100
6. WHERE status != 'completed' AND customer_id = 42

Exercise 2: design a partial for soft delete

You have a comments table with 5M rows. 4M are soft-deleted (deleted_at IS NOT NULL). The active queries are:

• WHERE deleted_at IS NULL AND post_id = X (a post's list of comments)
• WHERE deleted_at IS NULL AND user_id = X ORDER BY created_at DESC LIMIT 50 (a user's recent comments)

Design the indexes.

-- For queries by post
CREATE INDEX idx_comments_post_active
ON comments(post_id)
WHERE deleted_at IS NULL;

-- For queries by user with ordering by date
CREATE INDEX idx_comments_user_created_active
ON comments(user_id, created_at DESC)
WHERE deleted_at IS NULL;

CREATE INDEX idx_comments_post_active
ON comments(post_id)
INCLUDE (content, user_name)
WHERE deleted_at IS NULL;

Exercise 3: detect a poorly designed partial

You're given this partial:

CREATE INDEX idx_orders_high_value
ON orders(customer_id)
WHERE total > 1000 AND status = 'completed' AND created_at >= '2026-01-01';

For queries:

• WHERE customer_id = 42 → not used.
• WHERE customer_id = 42 AND total > 1000 → not used.
• WHERE customer_id = 42 AND total > 1000 AND status = 'completed' AND created_at >= '2026-01-01' → used.

What problem do you see with this partial? How would you rethink it?

-- For "high value pending/completed" regardless of date
CREATE INDEX idx_orders_high_value
ON orders(customer_id)
WHERE total > 1000;

CREATE INDEX idx_orders_value_status
ON orders(customer_id, status, total);

-- Partial on the "completed" subset (assuming it's a minority)
CREATE INDEX idx_orders_completed
ON orders(customer_id, total)
WHERE status = 'completed';

Exercise 4: partial UNIQUE for soft delete

You have an accounts table with (id, username, deleted_at). You want:

1. username unique among active accounts (without deleted_at).
2. To allow reusing a username if the original account was soft-deleted.
3. Searching WHERE deleted_at IS NULL AND username = ? to be fast.

Design the solution.

CREATE UNIQUE INDEX idx_accounts_username_active
ON accounts(username)
WHERE deleted_at IS NULL;

INSERT INTO accounts (username, deleted_at) VALUES ('juan', NULL);  -- OK
INSERT INTO accounts (username, deleted_at) VALUES ('juan', NULL);  -- ERROR: duplicate
UPDATE accounts SET deleted_at = NOW() WHERE username = 'juan';     -- soft delete
INSERT INTO accounts (username, deleted_at) VALUES ('juan', NULL);  -- OK now

Exercise 5: when NOT to use a partial

For each case, decide whether a partial is appropriate or whether another strategy is preferable.

1. events table with event_type IN ('click', 'view', 'purchase'). Distribution 60/35/5. You want queries by event_type.
2. users table with country (200 countries). Uniform distribution (~0.5% each country). Queries by country.
3. orders table with status 95% completed, 5% pending. You want queries by pending.
4. posts table with published. 50% published, 50% drafts. Queries only of published ones.

Exercise 6: apply it to your own case

Take a table in your project that has at least one of these patterns:

• Soft delete (deleted_at).
• A skewed status enum (one value covers <20% of the table).
• A boolean that almost all the queries filter by (active, published, verified).

Follow:

1. Count how many rows meet the condition (SELECT COUNT(*) WHERE ...).
2. Capture the plan of a typical query with a traditional index.
3. Create the partial.
4. Capture the plan afterward.
5. Measure the size of each index.
6. Conclude: is it worth it for your case?

## Table: orders, condition: status = 'pending'

**Distribution:**
- Total rows: 5,200,000
- pending: 240,000 (~4.6%)

**Plan BEFORE** (with a traditional `idx_orders_status_created`):

**Change:**
```sql
CREATE INDEX idx_orders_pending_customer
ON orders(customer_id)
WHERE status = 'pending';

ANALYZE orders;

Index Scan using idx_orders_pending_customer on orders
  Index Cond: (customer_id = 42)
  Buffers: shared hit=8
Execution Time: 0.4ms

</details>

---

## Summary and next step

In this capsule you learned:

- A **partial index** indexes only the rows that meet a `WHERE` condition. Smaller, faster to maintain.
- Classic cases: **soft delete** (`WHERE deleted_at IS NULL`), **skewed enum** (`WHERE status = 'pending'`), **business conditions** (`WHERE in_stock = true`).
- **Predicate matching:** the query must include a condition compatible with the partial's `WHERE`. If not, the planner ignores the index.
- **Partial UNIQUE** enables patterns like reusing emails after a soft delete.
- **Simple partials win**. The more complex the `WHERE`, the greater the risk of a mismatch.
- **Heuristic**: a partial shines when the condition filters <30% of the table. Below 5%, it's almost always worth it.

Before moving on, you should be able to:

- Identify tables that are candidates for a partial (soft delete, skewed enum, filtering boolean).
- Design the `WHERE` condition simple and compatible with real queries.
- Validate with `EXPLAIN` that the planner uses the partial.
- Combine a partial with composite and INCLUDE when it helps.

**Next capsule — Expression indexes and a JSONB/GIN preview.** Up to here we've indexed raw columns. What about queries that apply functions? `WHERE lower(email) = ...`, `WHERE to_char(created_at, 'YYYY-MM') = ...`. Your traditional index isn't used because the function wraps the column. The solution: **expression indexes**, which index the function's result. You'll learn the syntax, the `IMMUTABLE` requirement (the rule that confuses people most), and the preview of when you need GIN for JSONB and arrays — without going deep (that's guide #14).

---

## Resources

1. [Markus Winand — Use The Index, Luke! — "Partial Indexes"](https://use-the-index-luke.com/sql/where-clause/null/partial-indexes) — the canonical explanation with visual use cases.
2. [PostgreSQL Documentation — Partial Indexes](https://www.postgresql.org/docs/16/indexes-partial.html) — the official chapter. Includes use cases, predicate matching, and restrictions.
3. [Hubert "depesz" Lubaczewski — "Partial indexes"](https://www.depesz.com/2010/02/24/partial-indexes-explained/) — a real case with before/after numbers.
4. [PostgreSQL Wiki — Partial Indexes](https://wiki.postgresql.org/wiki/PartialIndexes) — the community's idiomatic patterns.
5. [Bruce Momjian — "Indexing Best Practices" (partial section)](https://momjian.us/main/presentations/performance.html) — slides with examples of when a partial is preferable vs alternatives.
6. [Tomas Vondra — "Partial indexes for performance"](https://www.2ndquadrant.com/en/blog/partial-indexes/) — a technical analysis of how the planner decides to use partials, with benchmarks.
7. [PostgreSQL Documentation — Predicate Locking and Partial Indexes](https://www.postgresql.org/docs/16/transaction-iso.html#XACT-SERIALIZABLE) — an edge case about the interaction with isolation levels.

---

*Module 3 — Database Performance & Query Tuning Guide*