Partial and Expression Indexes in PostgreSQL

A regular index covers every row and indexes the raw column value. That's often more than a query needs, and occasionally the wrong thing entirely. Partial indexes let you index only the rows that matter, keeping the index small and fast. Expression indexes let you index a computed value, so queries that transform a column can still use an index. Both are underused, and both can turn a slow query fast without adding much overhead.

This is part 12 of the PostgreSQL Masterclass, following composite and covering indexes.

Partial indexes: index only what you query

A partial index has a WHERE clause. It indexes only the rows matching that condition, and ignores the rest. This matters when your queries only ever care about a slice of the table.

The classic case is a status column with a skewed distribution. Imagine an orders table with millions of completed orders and a few thousand that are still pending. Your dashboard queries only pending orders. A full index on status wastes space and effort indexing millions of completed rows you never search by:

-- only the rows you actually query are in the index
CREATE INDEX orders_pending_idx ON orders (created_at)
  WHERE status = 'pending';

This index is tiny, because it holds only the pending orders. It's faster to scan, faster to update (a completed order isn't in it at all), and it perfectly serves WHERE status = 'pending' ORDER BY created_at. For any query that always includes the same filter, folding that filter into a partial index is close to a free win.

Another common use is enforcing conditional uniqueness. Say each user can have only one default payment method, but many non-default ones. A plain unique constraint can't express "unique among the defaults." A partial unique index can:

CREATE UNIQUE INDEX one_default_per_user
  ON payment_methods (user_id)
  WHERE is_default;

Now the database guarantees at most one default row per user, while allowing any number of non-default rows. That rule is impossible with a normal unique constraint and trivial with a partial one.

Soft deletes love partial indexes

If you use a deleted_at column for soft deletes, almost every query filters WHERE deleted_at IS NULL. Make your indexes partial on that condition so they only hold live rows:

CREATE INDEX users_email_active_idx ON users (email)
  WHERE deleted_at IS NULL;

The index skips deleted rows entirely, which keeps it small even after years of accumulated soft-deleted data, and matches how the application actually queries.

Expression indexes: index the computed value

The other tool solves a different problem. Postgres can only use an index on a column when the query uses that column directly. The moment you wrap it in a function, a plain index stops applying:

-- this canNOT use a plain index on email
SELECT * FROM users WHERE lower(email) = 'user@example.com';

Even with an index on email, that query does a sequential scan, because the index stores the raw email and the query asks about lower(email). The fix is to index the expression itself:

CREATE INDEX users_email_lower_idx ON users (lower(email));

Now the query that filters on lower(email) uses the index. The rule to remember: index the exact expression your query uses. Case-insensitive lookups, date truncation (WHERE date_trunc('day', created_at) = ...), and computed keys are all common reasons to reach for an expression index.

For case-insensitive email specifically, you have two clean options: an expression index on lower(email) with all queries using lower(), or the citext extension which makes the column itself case-insensitive. The expression index is more explicit; citext is more convenient. Either beats scanning.

Combining the two

Partial and expression indexes compose. You can index a computed value over just the rows you care about:

CREATE UNIQUE INDEX active_users_email_ci
  ON users (lower(email))
  WHERE deleted_at IS NULL;

This enforces case-insensitive unique emails among active users only, letting a deleted user's address be reused by a new signup. That single line encodes a real product rule that would otherwise take application code and a race condition to get wrong.

Confirm, don't assume

There's one habit that makes both of these reliable: check that the query actually uses the index. It's easy to build an expression index whose expression doesn't quite match the query's, or a partial index whose condition the planner can't prove the query satisfies. In both cases the index silently goes unused and you're back to a scan. The way to know is EXPLAIN, which is the very next article.

Partial and expression indexes are how you stop indexing everything and start indexing exactly what your queries touch. Smaller indexes, cheaper writes, and the ability to index computed values and enforce conditional rules: all from adding a WHERE or an expression to a CREATE INDEX.

Next, we make all of this measurable. EXPLAIN and EXPLAIN ANALYZE show which index a query really uses, so you can stop guessing and start confirming.