Partial indexes are one of the cleanest ways to make PostgreSQL faster without turning your schema into a science project. If you are searching for partial indexes, you probably already have a table where one slice of the data gets queried constantly while the rest sits there and makes every index bigger than it needs to be.

That is the real problem. Not “PostgreSQL is slow.” Not “we need a read replica.” Just one hot path, one ugly predicate, and an index that is carrying too much dead weight.

At Champlin Enterprises, that is the kind of issue we like because it is measurable. It is also the kind of issue senior engineers miss when they reach for broad fixes before they inspect the access pattern.

What partial indexes solve

A partial index is an index with a WHERE clause. PostgreSQL only stores entries for rows that match the predicate. That sounds simple, because it is simple. The value comes from restraint. You stop indexing rows that your query will never touch.

Example: imagine a orders table with 80 million rows. Ninety percent are archived. Your dashboard only asks for open or pending orders. A full index on status or (customer_id, status) still has to carry every archived row. A partial index can ignore them.

CREATE INDEX CONCURRENTLY idx_orders_open_customer_id
ON orders (customer_id, created_at DESC)
WHERE status IN ('open', 'pending');

That index is smaller, cheaper to maintain, and more likely to stay in memory. It also reduces write amplification. Every insert and update does less work because PostgreSQL updates a smaller index structure. On a busy OLTP system, that matters more than most teams expect.

The best use cases are predictable. Soft-deleted rows. Active subscriptions. Unprocessed jobs. Unpaid invoices. Records with a boolean flag that filters out a large majority of the table. If you are using PostgreSQL and a query has a stable predicate, partial indexes are often the first thing I check before I touch a query plan.

There is a reason this pattern shows up in serious systems. It is boring in the best way. It trims waste. It does not invent new machinery. It makes the database do less.

If you want the broader context around query behavior, our post on Optimizing PostgreSQL Query Performance pairs well with this one. They solve different problems, but the same discipline applies: measure first, then change the shape of the access path.

Partial indexes and PostgreSQL queries

The planner will only use a partial index when it can prove the query predicate implies the index predicate. That sentence is the whole game. If the planner cannot prove the match, the index may sit there untouched while you wonder why the “obvious” optimization did nothing.

Suppose you build this index:

CREATE INDEX idx_tickets_open_priority
ON support_tickets (priority, updated_at DESC)
WHERE closed_at IS NULL;

A query like this can use it:

SELECT id, priority, updated_at
FROM support_tickets
WHERE closed_at IS NULL
  AND priority = 'high'
ORDER BY updated_at DESC
LIMIT 50;

But if your application expresses the same idea differently, the planner may miss it. For example, WHERE status != 'closed' is not always equivalent to closed_at IS NULL from the planner’s point of view. This is where query shape matters. A good index can be hidden by a sloppy predicate.

That is why I like partial indexes in systems where the application code is disciplined. Rails scopes, well-formed repository methods, explicit SQL. If your codebase is a grab bag of dynamic filters and string concatenation, you can still use them, but you need more discipline than the average team has. The database is not a mind reader.

There is also a planner trade-off with parameterized queries. If your app sends $1 placeholders and the predicate is too generic, PostgreSQL may choose a plan that works for many values instead of the specific partial index you wanted. In those cases, you may need to inspect the actual execution plan with EXPLAIN (ANALYZE, BUFFERS) and see whether the planner is choosing a generic plan that hides the win.

One practical test: compare the query with and without the exact predicate from the index. If the partial index only works when the query is written in one narrow way, the index is not the problem. The contract between code and schema is.

If you are building around high-throughput write paths, our article on Database Connection Pooling for High Traffic Apps is a useful companion. Index tuning and pool sizing are different levers, but both show up when a system starts to feel “randomly slow.”

When partial indexes fail

Partial indexes are not a cure-all. They fail in a few predictable ways, and the failures are worth knowing before you ship one into production. The first is predicate churn. If the filtered subset changes often, the index may lose its advantage. You wanted a small, stable structure. You got a moving target.

Consider a work queue where rows move from pending to processing to done every few seconds. A partial index on status = 'pending' may still work, but if nearly every row is pending for a moment and then rapidly changes, the maintenance cost can erode the benefit. In that case, a different access pattern, maybe a SKIP LOCKED queue or a separate table, might be cleaner.

The second failure mode is selectivity drift. A partial index is useful when the predicate filters a large portion of the table. If the active slice grows from 5% to 40%, the index may still work, but the advantage shrinks. This is not a theoretical edge case. Product teams add new states. Operations teams stop archiving as aggressively. “Temporary” exceptions become permanent.

The third failure mode is too many partial indexes. I have seen teams create one index per report, one per dashboard, one per status value, and one per country. The table starts carrying a forest of specialized structures, and every write pays for it. That is not optimization. That is interest on technical debt.

A useful rule: if the predicate cannot be explained in one sentence to another senior engineer, it is probably too clever. Good partial indexes are obvious in hindsight. Bad ones are a puzzle box.

For teams dealing with broader data movement, our post on Bulk Data Migrations Without the Downtime covers a related risk: changes that look harmless in isolation but become expensive at scale. Index changes are smaller than migrations, but the operational thinking is the same.

Partial indexes implementation details

The implementation details matter because PostgreSQL gives you enough rope. Start with CREATE INDEX CONCURRENTLY on live tables unless you can afford a write lock. If the table is active, blocking writes just to add an index is a self-inflicted outage. Concurrent builds take longer, but they preserve availability.

Use a focused column list. Do not index more columns than the query needs. If the query only filters by customer_id and sorts by created_at DESC, that is the shape of the index. Adding amount because it “might help later” usually just increases size and maintenance. PostgreSQL is good, but it is not psychic.

Example pattern:

CREATE INDEX CONCURRENTLY idx_invoices_unpaid_account_created
ON invoices (account_id, created_at DESC)
WHERE paid_at IS NULL;

This helps a query like:

SELECT id, total_cents, created_at
FROM invoices
WHERE account_id = $1
  AND paid_at IS NULL
ORDER BY created_at DESC
LIMIT 25;

There is a subtle benefit here. If the query is covered by the index, PostgreSQL may avoid heap visits for some access patterns. That is not guaranteed, but when it happens, latency drops in a way that is easy to feel. Fewer random reads. Less cache churn. Better tail latency.

Watch the statistics. Run ANALYZE after major data shifts. Look at pg_stat_user_indexes to see whether the index is being used. If the scan count is near zero after a week, you built a decoration. If the table is large, inspect bloat and total index size before and after. The smallest useful index is usually the right one.

For a related look at storage behavior and query shape, see Connection Pool Exhaustion: Why Your Database Locks Up. It is a different failure mode, but the lesson overlaps: the bottleneck is rarely where the first alert points.

Operationalizing partial indexes safely

The real value of partial indexes is not the DDL. It is the operating model around them. You want a repeatable process: identify a hot query, confirm the predicate, test the plan, build concurrently, verify usage, and remove the old index if it is no longer needed. If you skip the cleanup step, old indexes linger and slow every write for no reason.

Here is the decision matrix I use:

  • High selectivity, stable predicate — partial index is usually the right move.
  • Predicate changes often — consider schema changes, queue separation, or a different access path.
  • Query shape is inconsistent — fix the application query first.
  • Many similar reports — a materialized view or summary table may be cleaner than a pile of partial indexes.

The last point matters. Sometimes the right answer is not more indexing. If a dashboard is reading the same filtered subset all day, a materialized view refreshed on a schedule may be the simpler object. Partial indexes help the live table. Materialized views help repeated analytics. Different tools, different job.

In practice, I like to treat partial indexes as a surgical tool. They are excellent for one hot path. They are not a substitute for data modeling. If you keep adding them to paper over a table that is doing too much, you are postponing the real fix.

If your team is deciding whether to patch, refactor, or rebuild the path around a database hot spot, that is the kind of work we handle in our Sprint, Build, or Fractional engagements. We also publish our own internal experiments in work we ship for ourselves, because the cleanest advice comes from systems we actually run. If you are staring at a query plan that should be faster than it is, you can apply for an engagement; the application takes ten minutes.