Designing PostgreSQL Partial Indexes for Common Filters

A partial index helps when PostgreSQL queries repeatedly filter on the same condition. It stores entries only for rows that satisfy the index’s WHERE condition. This condition is called the index predicate. For example, an index for open tasks can omit closed tasks, so the index stores less data. The smaller index improves performance only if it fits your queries and data; PostgreSQL’s partial-index documentation describes the benefits as conditional.

The predicate decides which rows belong in the index. The index keys are the columns PostgreSQL uses to search or sort those rows. To design a useful index, choose a predicate that your queries clearly meet, then choose keys that support the queries’ other filters and sorting.

Choose a predicate from recurring queries

Start with an important query that repeatedly selects the same kind of rows, such as open tasks, active records, or unbilled orders. A predicate based on that condition can keep unrelated rows out of the index:

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

Here, status = 'open' determines which tasks get index entries. created_at orders those entries and supports searches by creation time. The predicate and key columns can differ.

A partial index makes sense when queries often need the selected rows and those rows are few enough to justify leaving the rest of the table out. A condition that matches few rows today can match more as the data changes. As the table grows, check how many rows match the condition and whether queries still use it. The workload determines whether a smaller index outperforms a regular index; size alone does not establish a performance benefit.

PostgreSQL requires the predicate to use columns from the indexed table. It cannot contain subqueries or aggregates, and functions or operators in the index definition must be immutable, meaning their results depend only on their inputs. For example, a predicate cannot rely on the current time, because that value changes independently of the row. The CREATE INDEX documentation describes these restrictions.

Make the query imply the predicate

PostgreSQL can use a partial index only when it can determine that every row returned by the query meets the index predicate. A query that states status = 'open' makes that relationship clear:

SELECT id, created_at
FROM tasks
WHERE status = 'open'
  AND created_at < $1
ORDER BY created_at DESC
LIMIT 50;

The query uses $1 for the date cutoff and states the fixed condition used by the index. PostgreSQL can use the index to find qualifying tasks and read them in the requested order.

When PostgreSQL chooses how to run a query, its planner recognizes some straightforward logical implications. It does not prove every possible equivalence between expressions written in different ways. Keep the query’s condition consistent with the index predicate when practical. For example, do not assume PostgreSQL will match a rewritten expression to status = 'open' just because the two expressions seem equivalent to a person.

A query written as status = $1 does not state that the value is always 'open', so PostgreSQL may be unable to prove that the query satisfies the partial-index predicate at planning time. The documentation warns that parameterized clauses do not work with partial indexes. Test the statement form your client actually sends, especially when it uses prepared queries.

An index containing only open tasks cannot answer a query that might return tasks of any status, because the index omits the other rows. Even when a query implies the predicate, PostgreSQL can choose another plan if its cost estimates favor it.

Choose keys for searches within the subset

After choosing the predicate, choose keys for the searches and ordering your queries perform among the included rows. The example’s created_at DESC key supports a time cutoff and descending order. If queries instead find one user’s open tasks and then sort them by creation time, (user_id, created_at) may fit better.

For a multicolumn B-tree index, which stores entries ordered by several columns, the order of the keys matters. PostgreSQL most effectively limits its scan when query conditions apply to the first columns in the index. Put the columns that narrow common searches first, then check that choice against your query patterns using the multicolumn-index guidance.

Avoid adding keys just because a query mentions those columns. Each key makes the index larger and adds work when PostgreSQL updates it. If a query needs extra values only to return them, PostgreSQL’s INCLUDE option can store those values without using them as search keys, but included values still enlarge the index.

A partial index can also enforce a rule for a subset. For example, a unique index on (subject, target) WHERE success enforces uniqueness among successful rows while allowing duplicate pairs among unsuccessful rows. The predicate defines exactly which rows the uniqueness rule covers.

Account for index size and write work

Rows that do not meet the predicate have no entries in the partial index, so PostgreSQL stores fewer entries than in a comparable index covering the whole table. Updates to those excluded rows do not require changes to the partial index. PostgreSQL still has to maintain entries for rows that meet the predicate. If an update changes a row’s status so it no longer meets the predicate, PostgreSQL removes its entry; if the row starts meeting the predicate, PostgreSQL adds one.

Those savings do not guarantee faster writes or reads. The index can lose value if the predicate begins including many more rows, if queries stop using the condition, or if the index keys do not fit the query’s search pattern. PostgreSQL also warns against creating many similar partial indexes to divide a table into categories: the planner does not understand the relationship among those indexes and may need to consider each separately. A regular multicolumn index or table partitioning may fit that design better.

Check the plan PostgreSQL selects

Test the exact query shapes the service runs, including prepared statements where applicable. Use EXPLAIN to inspect the plan PostgreSQL chooses. If the plan does not use the partial index, first check whether the query clearly implies the predicate and whether the selected plan is cheaper according to PostgreSQL’s estimates. The PostgreSQL EXPLAIN documentation explains how to read plans and compare estimated costs.

Use EXPLAIN ANALYZE when you need execution measurements, keeping in mind that it runs the statement and adds measurement overhead. Compare results under representative data and query conditions. Revisit the index when the data distribution or workload changes, since either can change how many rows satisfy the predicate or how often queries benefit from it.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com