PostgreSQL Partial Indexes Performance Guide

PostgreSQL indexes are powerful, but a full-table index is not always the smartest choice.

Sometimes the real workload does not care about every row. It cares about a specific slice of rows over and over again.

Examples:

• active users
• unpaid invoices
• open tickets
• non-deleted records
• published posts
• recent events with a particular status

In cases like that, a normal index may be doing unnecessary work. It stores entries for rows your application almost never searches for. That increases:

• index size
• write overhead
• vacuum and maintenance work
• and cache pressure

This is exactly where partial indexes can be one of the most useful PostgreSQL performance tools.

A partial index indexes only the rows that match a condition. That sounds simple, but it can change both query speed and operational efficiency significantly when used for the right workload.

This guide explains how PostgreSQL partial indexes work, why they can outperform full indexes, and the common mistakes that stop them from helping.

The Most Important Partial Index Rule

Before getting into examples, remember this:

A partial index helps when your queries repeatedly target a predictable subset of rows, and PostgreSQL can prove that the query condition matches the index predicate.

That second part matters just as much as the first.

A partial index is not just a smaller index. It is a smaller index that is only valid for certain queries.

If the planner cannot tell that your query fits the predicate, it may ignore the index completely.

That is why partial indexes can be brilliant when designed carefully and disappointing when added casually.

1. What a Partial Index Is

A partial index is an index built only on rows that satisfy a WHERE condition.

For example:

CREATE INDEX idx_orders_unpaid
ON orders (created_at)
WHERE paid = false;

This does not index every row in orders. It only indexes rows where paid = false.

That means:

• the index is smaller
• updates to paid rows do not need entries in that index
• and queries for unpaid orders may become faster

The key idea is selective indexing.

Instead of indexing the whole table, you index the part that the application cares about most.

2. Why Partial Indexes Can Be Faster

A smaller index often performs better for several reasons:

• fewer index pages to store
• fewer pages to read
• better cache efficiency
• less write amplification on inserts and updates
• less maintenance overhead over time

This matters because PostgreSQL performance is not only about raw query logic. It is also about how much structure the database must carry around for every read and write.

If 95% of rows are irrelevant to a common query pattern, indexing all of them may be wasteful.

A partial index removes much of that waste.

3. The Core Benefit: Index Only the Rows That Matter

This is the biggest reason partial indexes are so effective.

Imagine a table with:

• 10 million rows
• 9.7 million archived rows
• 300,000 active rows

And the application mostly queries active rows.

A normal index on a status or timestamp column would include all 10 million rows. A partial index on only the active subset might include only the 300,000 rows that matter.

That means:

• less storage
• faster scans through the relevant subset
• and lower write cost for the inactive bulk of the table

That is often a much better fit for real workloads than a general-purpose index.

4. Common Use Cases for PostgreSQL Partial Indexes

Partial indexes are especially useful when your application repeatedly filters on a stable condition.

Common examples include:

Soft-deleted rows

Many applications use a flag or timestamp like:

• deleted_at IS NULL
• is_deleted = false

If most queries only want live records, a partial index can focus on those.

Example:

CREATE INDEX idx_customers_active_email
ON customers (email)
WHERE deleted_at IS NULL;

Open or active workflow states

Examples:

• open tickets
• pending jobs
• unpaid invoices
• active subscriptions

If users constantly search the active subset, indexing only those rows can be far more efficient.

Published content

Draft content may exist in the same table, but the site mostly queries published rows.

Sparse boolean or status conditions

If one condition is rare but queried often, a partial index can target that subset without burdening the whole table.

5. Partial Indexes Are Best When the Predicate Is Selective

A partial index is usually most useful when the predicate filters the table down meaningfully.

If the predicate includes almost every row, the benefit shrinks.

For example, if:

• 98% of rows have is_active = true

then a partial index on WHERE is_active = true may not be very different from a normal index in practical size or cost.

But if:

• only 5% of rows are unpaid
• only 2% of rows are pending review
• only 10% of rows are live and visible

then a partial index may be an excellent choice.

So the more selective and stable the subset, the more likely the index is to pay off.

6. Partial Indexes Reduce Write Overhead Too

This part is easy to overlook.

People often think about indexes mainly as read optimization. But every index also affects writes.

When PostgreSQL inserts, updates, or deletes rows, it has to maintain relevant indexes. If an index exists on the whole table, many rows may contribute to that maintenance even if the application never queries them through that index.

A partial index changes that.

Rows outside the predicate do not get entries in the index. That means:

• fewer index updates
• less index bloat pressure
• less disk churn
• and sometimes better overall system behavior under write-heavy workloads

So partial indexes can improve both:

• targeted read performance
• and general write efficiency

7. Partial Indexes Are Not Just for Boolean Columns

A common misconception is that partial indexes are mainly for true or false flags.

They are often used there, but they can be much broader than that.

Examples:

• WHERE deleted_at IS NULL
• WHERE status IN ('pending', 'processing')
• WHERE published_at IS NOT NULL
• WHERE amount_due > 0
• WHERE tenant_id IS NOT NULL AND archived = false

What matters is not the column type. What matters is whether the predicate captures a meaningful subset that the workload repeatedly targets.

8. Partial Unique Indexes Can Enforce Conditional Uniqueness

One of the most useful advanced patterns is the partial unique index.

This lets you enforce uniqueness only for rows that satisfy a condition.

Example:

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

This allows:

• only one active user per email

while still allowing:

• archived or soft-deleted duplicates

That can be extremely useful in real systems where uniqueness should apply only to live or relevant records.

This is one of the strongest reasons partial indexes are more than just a performance trick. They can also model business rules more precisely.

9. PostgreSQL Must Be Able to Prove the Predicate Matches

This is the part that causes the most confusion.

A partial index is only usable when PostgreSQL can determine that the query’s filter condition logically matches the index predicate.

For example, if the index is:

CREATE INDEX idx_tasks_open
ON tasks (created_at)
WHERE status = 'open';

then a query like:

SELECT *
FROM tasks
WHERE status = 'open'
ORDER BY created_at DESC
LIMIT 20;

is a strong match.

But if the query condition is expressed in a different or more complex way, the planner may not use the index.

This is why partial indexes are more sensitive to predicate shape than full indexes.

10. Why Partial Indexes Often Do Not Get Used

The most common reason is predicate mismatch.

Examples of trouble:

• the query uses different boolean logic
• the condition is wrapped in an expression
• the comparison is equivalent in business meaning but not obvious to the planner
• prepared statements or parameterized queries hide the final predicate shape
• the query is too broad to guarantee it only touches rows inside the partial predicate

If PostgreSQL cannot prove the query only needs rows inside the indexed subset, it may fall back to another plan.

That is not PostgreSQL being stubborn. It is PostgreSQL being correct.

A partial index cannot safely answer queries outside its defined slice.

11. The Query Pattern Must Match the Index Pattern

As with any PostgreSQL index, the indexed columns still need to line up with the actual access path.

For example:

CREATE INDEX idx_invoices_unpaid_due_date
ON invoices (due_date)
WHERE paid = false;

This is useful for queries like:

SELECT id, due_date
FROM invoices
WHERE paid = false
ORDER BY due_date
LIMIT 50;

But it is much less helpful for unrelated queries such as:

• sorting by another column
• searching a different state
• or filtering in a way that does not stay inside the unpaid subset

A partial index is not magical. It still follows the same indexing rules about:

• filter columns
• sort order
• selectivity
• and real query shape

12. Partial Indexes Can Beat Compound Full Indexes

Sometimes teams respond to a query pattern by adding a larger general-purpose index like:

CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at);

That can work, but it still covers all statuses. If the application mostly cares about one or two statuses, a partial index may be more efficient:

CREATE INDEX idx_orders_open_created_at
ON orders (created_at)
WHERE status = 'open';

This index may be:

• smaller
• more cache-friendly
• cheaper to maintain
• and more directly aligned with the workload

That is one reason partial indexes are so powerful. They let you design for real usage instead of theoretical generality.

13. Partial Indexes Are Great for Soft-Delete Tables

This is one of the most common real-world patterns.

Suppose a table keeps historical rows by setting:

• deleted_at instead of deleting physically.

The problem is that over time, the table may contain many dead-to-the-application rows even though most user-facing queries only care about the live ones.

A partial index like this:

CREATE INDEX idx_projects_live_tenant_created
ON projects (tenant_id, created_at DESC)
WHERE deleted_at IS NULL;

can be much more useful than a full-table index, because it keeps the live working set focused.

This is a very common PostgreSQL performance win in SaaS systems.

14. Partial Indexes and Multi-Tenant Systems Work Well Together

Partial indexes are often especially effective in multi-tenant applications.

Why?

Because multi-tenant tables often combine:

• tenant filtering
• status filtering
• soft delete logic
• active versus archived rows

That creates strong opportunities for selective indexes.

Example:

CREATE INDEX idx_jobs_active_by_tenant
ON jobs (tenant_id, created_at DESC)
WHERE archived = false;

If the app mostly shows recent active jobs per tenant, this can be a very strong fit.

The same logic applies to:

• non-deleted tenant records
• active subscriptions by tenant
• pending invoices by tenant
• visible content by tenant

15. Partial Indexes Need Stable Business Logic

A partial index works best when the predicate reflects a stable, meaningful boundary in the application.

Good examples:

• unpaid
• active
• not deleted
• published
• pending
• currently visible

Less ideal examples are predicates that change constantly or are too broad and inconsistent to represent a durable access pattern.

You want the predicate to reflect a real repeated workload, not a temporary experiment.

This is because indexes are schema-level structures. They should support durable query patterns, not one-off guesses.

16. Measure Before and After

Like any optimization, partial indexes should be driven by evidence.

You should measure:

• query latency before and after
• index size
• scan behavior with EXPLAIN
• write overhead changes
• and whether the planner actually uses the index

A partial index can look brilliant in theory and still miss in practice if:

• the predicate is not selective enough
• the queries do not match it closely
• or another index already handles the workload well enough

This is why good PostgreSQL tuning is always empirical.

17. Partial Indexes Can Proliferate Too Easily

One risk is adding too many very specific indexes for every slight variation of a query.

That creates its own problems:

• more write cost
• more storage
• more maintenance
• harder schema reasoning
• and more confusion about which index matters

A partial index should exist because it supports an important repeated access pattern, not because one query was slow once.

This is the same discipline required for all indexing, but partial indexes make it easy to over-specialize.

18. Partial Indexes Are Often Better Than Indexing Low-Value Rows

A full-table index on a status column can be disappointing when most values are not useful to the application’s main queries.

For example, if a ticketing system has:

• closed tickets making up most of the table
• and open tickets driving most user activity

then indexing every ticket may not be the smartest design.

A partial index on open tickets often provides a better balance between:

• performance
• size
• and write cost

This is one of the clearest cases where partial indexes outperform broader indexing.

Common PostgreSQL Partial Index Mistakes

Creating a partial index on a predicate that is not selective

If the subset is most of the table anyway, the gain may be minimal.

Expecting PostgreSQL to use the index for loosely related queries

The planner must be able to prove the predicate matches.

Forgetting the indexed columns still need to support the real query

The predicate alone does not make the index useful.

Adding many highly specialized partial indexes

This can hurt overall system simplicity and write performance.

Ignoring prepared-query behavior

Parameterized query shapes can sometimes prevent the planner from matching a partial predicate the way you expect.

Not measuring index usage

A partial index that is never used is just overhead.

FAQ

When should I use a partial index in PostgreSQL?

Use a partial index when your workload frequently queries the same subset of rows and indexing the entire table would waste space and write overhead. Common examples include active users, open orders, unpaid invoices, and soft-deleted tables.

Why is PostgreSQL not using my partial index?

Usually because the planner cannot prove that your query condition matches the partial index predicate. The SQL filter, boolean logic, parameterization, or expression shape may differ too much from the index condition.

Conclusion

PostgreSQL partial indexes are one of the best ways to align indexing with real application behavior.

Instead of indexing everything, they let you index the rows that matter most. That can improve:

• query speed
• cache efficiency
• write performance
• and storage efficiency

They work especially well when:

• the workload repeatedly targets a narrow subset
• the predicate is stable
• the indexed columns match the access path
• and the query condition matches the predicate clearly enough for the planner to use it

That is why partial indexes are not just smaller indexes.

They are more intentional indexes.

And in PostgreSQL, intentional indexing is often what makes the difference between an index that merely exists and an index that actually helps.