Partial Indexes - When Less Is More

Day 6 taught you composite indexes. Day 20 taught you covering indexes - put everything in the index. Today: what if you don’t index everything, but only the rows you actually query? A partial index uses a WHERE clause to index a subset of rows. Smaller, faster, and sometimes the only way to make a unique constraint make sense.

Day 20’s covering index was about adding columns to the index - making it wider so PostgreSQL never touches the table. Today we go the other direction: making the index narrower by including fewer rows. A partial index adds a WHERE clause to CREATE INDEX, and only rows matching the predicate get indexed.

Why would you want fewer rows in an index? Because if 95% of your queries filter on WHERE status = ‘pending’, indexing all 10 million rows - including the 9.9 million that are ‘completed’ - is wasted space and wasted I/O. The partial index on WHERE status = ‘pending’ is 31× smaller and fits in cache.

The problem: most rows are irrelevant to most queries

Consider an orders table with 10 million rows:

CREATE TABLE orders (
  id           BIGSERIAL PRIMARY KEY,
  customer_id  BIGINT NOT NULL,
  status       TEXT NOT NULL DEFAULT 'pending',
  total        NUMERIC(10,2) NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Distribution of status:
-- pending:    100,000   (1%)
-- processing:  50,000   (0.5%)
-- shipped:   1,850,000  (18.5%)
-- completed: 8,000,000  (80%)

Your application has a dashboard that queries pending orders:

SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

Without an index on status, this query scans all 10 million rows. So you create a regular index:

CREATE INDEX idx_orders_status ON orders (status);

-- Index size: ~67 MB
-- Indexes ALL 10 million rows, including 8M 'completed' rows
-- that the dashboard never queries.

The index works - PostgreSQL finds the 100,000 pending rows fast. But the index is bloated with 9.9 million rows your dashboard never needs. Those extra index entries consume disk space, cache space, and slow down every INSERT, UPDATE, and DELETE that has to maintain them.

The fix: add a WHERE clause to CREATE INDEX

PostgreSQL lets you add a WHERE clause to CREATE INDEX. Only rows matching the predicate are indexed:

-- Drop the bloated full index
DROP INDEX idx_orders_status;

-- Create a partial index - only pending orders
CREATE INDEX idx_orders_pending
  ON orders (created_at DESC)
  WHERE status = 'pending';

-- Index size: ~2.2 MB (31× smaller!)
-- Indexes only 100,000 rows (the pending ones)

Notice something important: the index key is (created_at DESC), not (status). The WHERE clause filters which rows go into the index; the key columns determine how they’re sorted. Since every row in the partial index has status = 'pending', there’s no need to include status in the key - it’s implied by the predicate.

This can become a covering index (Day 20): include every selected payload column so an index-only scan is possible when visibility-map conditions permit.

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

This index combines an ordered key, INCLUDE columns (Day 20, all selected payload in the index), and partial indexing (only pending rows). If pagination needs a stable order when timestamps tie, choose a definition with the primary key as the final key instead:

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

The matching query must then use ORDER BY created_at DESC, id DESC. That is a genuine two-column composite key and a covering candidate; PostgreSQL may still need heap visibility checks.

How PostgreSQL uses the partial index

Run the dashboard query again with EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

-- PLAN:
--   Limit  (cost=0.42..52.05 rows=20 width=30) (actual time=0.057..0.131 rows=20)
--     ->  Index Scan using idx_orders_pending_covering on orders
--           Index Searches: 1
--           Buffers: shared hit=20 read=3
--   Planning Time: 0.617 ms
--   Execution Time: 0.159 ms

PostgreSQL recognizes that the query’s WHERE status = 'pending' matches the index’s predicate, so it can use the partial index. The index is tiny (100K rows, 2.2 MB), reads only 23 buffer pages, and returns 20 rows in 0.16ms. The index is on (created_at DESC), so no sort step is needed - PostgreSQL reads the first 20 entries and stops.

The planner is smart - but not magic

The planner can only use a partial index if the query’s WHERE clause is provably implied by the index’s predicate. This means:

-- ✅ Can use the partial index:
SELECT * FROM orders WHERE status = 'pending';

-- ❌ Cannot use the partial index:
SELECT * FROM orders WHERE status IN ('pending', 'processing');

-- ❌ Cannot use the partial index:
SELECT * FROM orders WHERE status != 'pending';

-- ✅ Can use the partial index:
SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2026-01-01';

The key insight: the query’s predicate must be at least as restrictive as the index’s predicate. If the query could return rows that aren’t in the index, PostgreSQL can’t use it.

Real benchmark: full vs partial index

Let’s measure the difference. Using PostgreSQL 18 with 10 million rows in the orders table (100,000 pending):

Scenario	Median	Buffers	Index size
Seq Scan (no index)	452.6ms	91,548	-
Full index on (status)	37.4ms	929	67 MB
Partial index (pending only)	1.9ms	22	2.2 MB

Comparison	Result
Partial vs full index	19.5× faster
Partial vs seq scan	236× faster
Index size reduction	31× smaller (67 MB → 2.2 MB)
Buffer reduction	42× fewer pages (929 → 22)

The partial index is 19.5× faster than the full index - not because the B-tree algorithm is different, but because of two factors. First, the index is 31× smaller: fewer pages to read, fewer cache misses. Second, the partial index is on (created_at DESC) - it’s pre-sorted. The full index on (status) finds 100,000 pending rows but then needs to sort them by created_at. The partial index is already sorted, so PostgreSQL reads the first 20 rows and stops.

The buffer count tells the story: 929 buffers (full index - read 100K rows + sort) vs 22 buffers (partial index - read 20 rows, already sorted). PostgreSQL reads 42× fewer pages to return the same 20 rows.

When partial indexes shine (and when they don’t)

✅ Use partial indexes when:

1. Skewed data distribution. 80%+ of rows have one value; you query the rare values.
2. Dashboard / reporting queries. “Show me pending orders” is a common query that always filters on the same predicate.
3. Soft deletes. Index WHERE deleted_at IS NULL - only live rows are indexed.
4. Multi-tenant isolation. Index WHERE tenant_id = 42 for a large tenant.
5. Conditional uniqueness. CREATE UNIQUE INDEX … WHERE status = 'active' - only one active record per key.

❌ Don’t use partial indexes when:

1. Uniform data distribution. If all status values are equally common, use a full index.
2. The query predicate doesn’t match. PostgreSQL can’t use it.
3. The predicate changes. Plan for the queries you actually run.

The unique partial index: a killer feature

One of the most powerful uses of partial indexes is conditional uniqueness. Suppose users can have only one active email, but multiple archived emails:

CREATE UNIQUE INDEX idx_user_emails_active
  ON user_emails (user_id)
  WHERE status = 'active';

Without the partial index, you’d need a trigger or application-level logic to enforce “one active email per user.” With the partial unique index, PostgreSQL enforces it at the storage level - no race conditions, no application bugs, no TOCTOU gaps.

Connection to previous days

Partial indexes are the third dimension of index optimization:

• Day 1 (Indexes): Which columns to index.
• Day 6 (Composite Indexes): Which combination of columns, and in what order.
• Day 20 (Covering Indexes): Which extra columns to INCLUDE for index-only scans.
• Day 22 (Partial Indexes): Which rows to index - not all of them, just the ones you query.

Together: composite (how to sort) + covering (what to include) + partial (which rows to include) = the complete toolkit for index design. Every index you create should be evaluated on all three dimensions.

Key takeaways

1. A partial index indexes only rows matching a WHERE clause. Smaller index, faster scans, less cache pressure.
2. The query predicate must match (or be more restrictive than) the index predicate. If PostgreSQL can’t prove the query only touches indexed rows, it won’t use the partial index.
3. Partial unique indexes enforce conditional uniqueness - “only one active record per key” - at the storage level. No triggers, no race conditions.
4. Combine with composite + covering for maximum effect. Partial (which rows) + composite (which columns, what order) + covering (INCLUDE for index-only scan) = the complete index design toolkit.
5. Use when data is skewed. If 80%+ of rows have one value and you query the rest, a partial index is a 31× win.