A normal PostgreSQL index contains entries for every table row that has indexable values. That is often appropriate, but some workloads repeatedly query a small, well-defined subset of a much larger table.

A partial index stores entries only for rows that satisfy an index predicate. When the predicate matches a stable access pattern, the index can be smaller and cheaper to maintain than an equivalent full-table index.

The trade-off is specificity: PostgreSQL can use the partial index only when it can determine at planning time that the query condition implies the index predicate.

Start with a selective workload

Consider a job table where most rows are completed and workers repeatedly search for ready jobs:

CREATE TABLE jobs (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status text NOT NULL,
    scheduled_at timestamptz NOT NULL,
    payload jsonb NOT NULL
);

A full index for the worker query could be:

CREATE INDEX jobs_status_scheduled_idx
ON jobs (status, scheduled_at);

If completed jobs dominate the table and workers care only about status = 'ready', a partial index can omit the rest:

CREATE INDEX jobs_ready_idx
ON jobs (scheduled_at)
WHERE status = 'ready';

The predicate determines which rows are present in the index. scheduled_at remains the search and ordering key.

A matching query is straightforward:

SELECT id, scheduled_at, payload
FROM jobs
WHERE status = 'ready'
  AND scheduled_at <= CURRENT_TIMESTAMP
ORDER BY scheduled_at
LIMIT 100;

The index is useful because the query explicitly restricts rows to the same subset represented by the index.

Separate index keys from the predicate

The predicate and indexed columns serve different purposes.

In this index:

CREATE INDEX jobs_ready_idx
ON jobs (scheduled_at)
WHERE status = 'ready';

status decides whether a row belongs in the index. It does not need to be an index key.

This is useful when a predicate value is constant for every indexed row. Storing status as a leading key would add little search information inside that subset.

The index key should instead reflect how matching rows are located or ordered.

Predicate implication controls index eligibility

PostgreSQL does not use a partial index merely because a query looks conceptually related to it. The planner must be able to establish that the query’s WHERE condition implies the index predicate.

For example, this index:

CREATE INDEX invoices_open_due_idx
ON invoices (due_at)
WHERE paid_at IS NULL;

can support a query that includes the same condition:

SELECT id, due_at
FROM invoices
WHERE paid_at IS NULL
  AND due_at < CURRENT_TIMESTAMP;

A query without paid_at IS NULL cannot use the index to represent all possible matching invoices, because paid invoices are absent from it.

PostgreSQL recognizes some simple inequality implications, but it is not a general theorem prover. Equivalent business meaning expressed through substantially different SQL may not be recognized as equivalent predicates.

Keep application query predicates aligned with the index definition, and confirm planner behavior with EXPLAIN.

Be careful with parameterized predicates

Predicate matching happens while PostgreSQL plans the query. A parameter whose value is not known in a way that proves the predicate for every relevant plan cannot generally establish the required implication.

Suppose an index contains only rows below a fixed threshold:

CREATE INDEX events_small_score_idx
ON events (created_at)
WHERE score < 100;

A generic parameterized condition such as:

WHERE score < $1

does not imply score < 100 for every possible value of $1.

This issue is different from parameters used for ordinary index search keys. Parameterized queries can use indexes normally; the important restriction here is whether the planner can prove the partial-index predicate.

When partial-index eligibility matters, inspect the actual plans produced by the application’s prepared statements rather than assuming a literal test query behaves identically.

Use partial unique indexes for conditional rules

Partial indexes are not only performance tools. A unique partial index can enforce uniqueness within a subset of rows.

Suppose users may have many historical email addresses but only one active record for a particular address:

CREATE TABLE user_emails (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL,
    email text NOT NULL,
    active boolean NOT NULL
);

You can enforce uniqueness only for active addresses:

CREATE UNIQUE INDEX user_emails_active_email_uq
ON user_emails (email)
WHERE active;

Multiple inactive rows can contain the same email, while two active rows with the same email violate the unique index.

This is often clearer than trying to enforce a conditional uniqueness rule exclusively in application code, where concurrent transactions can make check-then-insert logic unsafe.

Choose predicates that remain meaningful

A partial index works best when its predicate represents a durable property of the workload.

Good candidates often include:

  • active versus archived rows;
  • unprocessed versus completed work;
  • rows with a nullable lifecycle marker such as deleted_at IS NULL;
  • a small exceptional class that is queried frequently.

Be cautious when the predicate reflects a temporary data distribution rather than a stable business condition. If the indexed subset grows from 2 percent of the table to 80 percent, the original space and maintenance advantages may largely disappear.

Revisit partial indexes as data distributions and access patterns change.

Account for update behavior

A row can enter or leave a partial index when an update changes the predicate result.

For example:

UPDATE jobs
SET status = 'completed'
WHERE id = 42;

If row 42 previously had status = 'ready', PostgreSQL must remove its entry from jobs_ready_idx.

Partial indexes reduce index maintenance for rows that remain outside the predicate, but updates that cross the predicate boundary still require index work.

For queue-like tables, this can still be a good trade-off because the active subset stays small even while historical rows accumulate.

Verify plans instead of assuming

Use EXPLAIN with representative query shapes:

EXPLAIN
SELECT id, scheduled_at
FROM jobs
WHERE status = 'ready'
  AND scheduled_at <= CURRENT_TIMESTAMP
ORDER BY scheduled_at
LIMIT 100;

For performance investigations, EXPLAIN (ANALYZE, BUFFERS) can show actual execution and buffer activity, but remember that ANALYZE executes the statement. Use it carefully with statements that can modify data.

Planner choices depend on table statistics, row counts, selectivity, available indexes, and cost estimates. Creating a partial index makes an access path available; it does not force PostgreSQL to choose it.

Do not create many partial indexes as pseudo-partitions

It can be tempting to create one partial index for every category value:

WHERE region = 'a'
WHERE region = 'b'
WHERE region = 'c'
...

That is usually not a substitute for a suitable multicolumn index or table partitioning.

A large collection of overlapping partial indexes increases schema complexity and planning work while making operational behavior harder to understand. Use a partial index when a particular subset has a distinct access pattern, not merely to divide an ordinary index into arbitrary pieces.

Common pitfalls

Omitting the predicate from application queries

If the query does not prove that all matching rows belong to the indexed subset, PostgreSQL cannot use the partial index as though it covered the whole table.

Assuming logically similar SQL always matches

The planner recognizes limited forms of predicate implication. Keep important predicates structurally simple and verify them with real query plans.

Indexing a subset that is no longer selective

A partial index can lose much of its advantage as the indexed population grows. Monitor the workload and index size over time.

Forgetting write transitions

Rows moving into or out of the predicate still cause index maintenance. Model the lifecycle of frequently updated rows before declaring the index cheap.

Duplicating a full index unnecessarily

If a full index already serves the same critical queries and the partial index provides little size or write benefit, maintaining both may waste storage and write capacity.

Treat partial indexes as workload-specific structures

Partial indexes are most effective when the database has a clearly important subset of rows and application queries consistently identify that subset.

Define the predicate from a real access pattern, choose index keys for the searches performed inside that subset, and verify that production query shapes imply the predicate. Then measure whether the smaller index improves the workload enough to justify another specialized schema object.

Used deliberately, partial indexes can keep growing historical data out of hot access paths while preserving normal SQL semantics and database-enforced correctness.

Database PostgreSQL Indexes Performance