A PostgreSQL index does not have to represent every row in its table. A partial index adds a predicate to the index definition, so only rows satisfying that predicate receive index entries. This changes both the physical scope of the index and the set of queries for which the planner can use it.

The mechanism fits workloads where a stable subset of rows receives disproportionate query attention. An application might repeatedly inspect pending jobs while completed jobs remain mostly historical, or query active accounts while disabled accounts stay in the same table.

The predicate defines index membership

A partial index uses a WHERE clause in CREATE INDEX. Consider a job table where most rows eventually reach a terminal state:

CREATE TABLE jobs (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    queue text NOT NULL,
    status text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX jobs_pending_queue_created_idx
    ON jobs (queue, created_at)
    WHERE status = 'pending';

The index contains (queue, created_at) entries only for rows whose status is pending. Rows with any other status remain in the heap but are absent from this index.

The predicate column does not need to appear among the indexed columns. Here, status controls membership while queue and created_at provide the index keys used to locate and order matching rows.

This distinction matters during writes. Inserting a pending job adds an index entry. Changing that row from pending to running removes it from the logical contents of the partial index as the new row version no longer satisfies the predicate. Rows that never satisfy the predicate do not need entries in this particular index.

Query conditions must establish the predicate

Index membership alone is not enough for planner use. PostgreSQL must be able to establish at planning time that a query condition implies the index predicate.

A query that explicitly restricts status to the indexed subset is a direct fit:

SELECT id, created_at
FROM jobs
WHERE status = 'pending'
  AND queue = 'email'
ORDER BY created_at
LIMIT 50;

The condition status = 'pending' establishes that every row relevant to the query is eligible to exist in the partial index. The planner can then consider the index alongside its other available plans.

Remove the status condition and that guarantee disappears:

SELECT id, created_at
FROM jobs
WHERE queue = 'email'
ORDER BY created_at
LIMIT 50;

The partial index cannot represent running or completed email jobs, so it cannot serve as a general index for this query.

PostgreSQL recognizes some simple logical implications, including certain inequalities, but it does not attempt arbitrary symbolic proof between a query condition and an index predicate. Predicate spelling and query structure therefore affect whether an otherwise plausible partial index is considered.

Parameterization can hide a usable subset

Planning-time implication also creates a boundary around parameterized conditions. An index such as:

CREATE INDEX jobs_recent_priority_idx
    ON jobs (created_at)
    WHERE priority < 10;

can be considered for a query whose fixed condition is known to imply priority < 10. A generic parameter condition such as priority < $1 cannot establish that implication for every possible parameter value. The planner cannot assume $1 will always make the query a subset of the index predicate.

This is a property of predicate proof, not a limitation of B-tree lookup itself. A partial index can have perfectly suitable key columns and still be unavailable because the query does not establish membership in its indexed subset during planning.

Smaller scope changes maintenance costs

A conventional index on (queue, created_at) represents rows across every status. The partial version represents only pending rows. When pending rows form a limited portion of the table, the partial index can occupy less space and avoid index maintenance for row versions outside its predicate.

Those properties do not make a partial index automatically preferable. Its usefulness depends on the relationship between the predicate, the data distribution, and actual query conditions. If nearly every row satisfies the predicate, little scope has been removed. If application queries express the target subset in forms the planner cannot connect to the predicate, the index may see little use.

The subset can also change over time. A predicate chosen around a temporary data distribution can become a poor fit as table contents or access patterns shift. Partial indexes are most coherent when the predicate reflects a durable distinction in the data model or workload rather than a short-lived threshold.

Partial uniqueness applies the same membership rule

Adding UNIQUE makes the index enforce uniqueness only among rows satisfying its predicate. For example, a system can permit many historical records for an account while allowing only one active record with a given external identifier:

CREATE UNIQUE INDEX account_active_external_id_idx
    ON account_records (external_id)
    WHERE active;

Rows where active is true participate in the uniqueness check. Other rows do not belong to the index and are outside that constraint. This is the same partial-index membership mechanism used for query access, with uniqueness enforcement added to the indexed subset.

A partial index is therefore more than a smaller copy of a full index. Its predicate becomes part of the index’s semantics: it determines which row versions belong, which queries can rely on the structure, and, for a unique partial index, which rows participate in the constraint. Keeping that predicate aligned with stable query conditions is what makes the narrower structure useful.

PostgreSQL Database Indexes Query Planning