A normal database index contains an entry for every table row that qualifies for the indexed columns. That is often appropriate, but some applications repeatedly query only a small, stable subset of a table.
Consider a task table where most tasks eventually become completed or archived, while the application dashboard mainly reads current open tasks. A full index on project_id keeps index entries for historical rows even though those rows are rarely part of the hot query path.
A partial index solves a narrower problem: it indexes only rows that satisfy a predicate.
The mental model is:
table rows
|
+-- predicate is true ---> included in partial index
|
+-- predicate is false ---> not represented in that indexThis can reduce index size and index-maintenance work when the indexed subset is much smaller than the table. It can also express useful constraints, such as uniqueness only for active records.
Partial indexes are not automatically better than full indexes. They work best when the indexed subset and the queries that target it are both predictable.
This article uses syntax supported by PostgreSQL and SQLite. The exact rules a query planner uses to prove that a query can use a partial index differ between database engines, so planner behavior should always be checked on the database you actually run.
Start with a query that targets a small subset
Suppose an application stores tasks like this:
CREATE TABLE tasks (
task_id INTEGER PRIMARY KEY,
project_id INTEGER NOT NULL,
status TEXT NOT NULL,
assignee_id INTEGER,
archived_at TEXT
);The dashboard frequently runs:
SELECT
task_id,
assignee_id
FROM tasks
WHERE project_id = 42
AND status = 'open'
AND archived_at IS NULL
ORDER BY task_id;A conventional index might be:
CREATE INDEX tasks_by_project
ON tasks (project_id, task_id);That index can help locate rows for a project, but it contains entries for open, completed, cancelled, and archived tasks.
If open, non-archived tasks are only a small portion of the table, you can instead create:
CREATE INDEX tasks_open_by_project
ON tasks (project_id, task_id)
WHERE status = 'open'
AND archived_at IS NULL;The columns inside ON tasks (...) form the index key. The WHERE clause is the index predicate: only rows for which that expression is true get entries in this index.
The dashboard query includes the same conditions, so the index is relevant to exactly the subset it needs.
Understand what the database maintains
The main benefit is not that the database somehow skips evaluating completed tasks at query time. The important change happens when the index is maintained.
Consider these inserts:
INSERT INTO tasks (
task_id,
project_id,
status,
assignee_id,
archived_at
) VALUES
(1, 42, 'open', 7, NULL),
(2, 42, 'done', 7, NULL);The first row satisfies:
status = 'open' AND archived_at IS NULLso it receives an entry in tasks_open_by_project.
The second row does not satisfy the predicate, so it receives no entry in that index.
Now consider an update:
UPDATE tasks
SET status = 'done'
WHERE task_id = 1;The row stops satisfying the predicate. The database must remove its old partial-index entry as part of maintaining the index consistently.
This leads to an important performance rule: partial indexes can reduce write overhead for rows that remain outside the indexed subset, but writes that enter, leave, or modify keys inside the subset still require index maintenance.
Make the query imply the index predicate
A partial index can only help when the database can determine that the query cannot need rows excluded from the index.
Given:
CREATE INDEX tasks_open_by_project
ON tasks (project_id, task_id)
WHERE status = 'open'
AND archived_at IS NULL;this query is a natural match:
SELECT task_id
FROM tasks
WHERE project_id = 42
AND status = 'open'
AND archived_at IS NULL;But this query is not:
SELECT task_id
FROM tasks
WHERE project_id = 42;The second query also needs completed and archived rows. Those rows are deliberately absent from the partial index, so that index alone cannot represent the required search space.
This is the central correctness constraint:
query result may include excluded rows
-> partial index cannot satisfy that search
query conditions guarantee indexed predicate
-> partial index may be eligible“May be eligible” is intentional. The query planner is still free to choose another access path if it estimates that path to be cheaper.
Keep predicates simple and aligned with real queries
Database engines must prove at planning time that a query’s conditions are compatible with a partial-index predicate.
Simple predicates are easier to reason about:
WHERE archived_at IS NULLor:
WHERE status = 'open'or a direct conjunction:
WHERE status = 'open'
AND archived_at IS NULLAvoid creating a clever predicate and assuming the planner will recognize every logically equivalent expression.
For example, the following two expressions might be equivalent under your application’s data rules:
status = 'open'and:
status <> 'done' AND status <> 'cancelled'but they are not syntactically the same condition, and they may not even be logically equivalent if a new status is introduced later.
The safer design is to make the index predicate mirror a stable business condition that appears directly in important queries.
PostgreSQL documentation specifically notes that partial-index matching is performed at planning time and that parameterized clauses cannot always imply a fixed predicate. SQLite uses its own theorem rules for deciding when a query predicate can use a partial index. Do not generalize one engine’s matching behavior to another.
Verify use with the query plan
Creating an index does not prove that a query uses it.
On SQLite, you can inspect the plan with:
EXPLAIN QUERY PLAN
SELECT task_id
FROM tasks
WHERE project_id = 42
AND status = 'open'
AND archived_at IS NULL
ORDER BY task_id;On PostgreSQL, use:
EXPLAIN
SELECT task_id
FROM tasks
WHERE project_id = 42
AND status = 'open'
AND archived_at IS NULL
ORDER BY task_id;Plan output differs between engines and versions. Focus on whether the intended index is selected for representative data, not on memorizing one textual plan format.
Also test with realistic table sizes and distributions. A planner may reasonably choose a table scan for a tiny table even when a useful index exists because reading the table directly can be cheaper.
Use partial unique indexes for conditional rules
Partial indexes are not only a performance tool. A unique partial index can enforce uniqueness for a subset of rows.
Suppose an application allows many historical API tokens for a user but at most one currently active token of a particular label:
CREATE TABLE api_tokens (
token_id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
label TEXT NOT NULL,
revoked_at TEXT
);The desired rule is:
for rows where revoked_at IS NULL,
(user_id, label) must be uniqueYou can express that as:
CREATE UNIQUE INDEX api_tokens_one_active_label
ON api_tokens (user_id, label)
WHERE revoked_at IS NULL;These two rows conflict because both are active:
INSERT INTO api_tokens (
token_id,
user_id,
label,
revoked_at
) VALUES
(1, 10, 'deploy', NULL),
(2, 10, 'deploy', NULL);But after the first token is revoked, a new active token with the same (user_id, label) can be valid:
UPDATE api_tokens
SET revoked_at = '2026-09-04T01:00:00Z'
WHERE token_id = 1;
INSERT INTO api_tokens (
token_id,
user_id,
label,
revoked_at
) VALUES
(2, 10, 'deploy', NULL);This is stronger than checking for duplicates in application code. A database unique index participates in concurrency control, so two concurrent writers cannot both successfully create rows that violate the indexed uniqueness rule.
The application should still handle the resulting constraint error because races can happen between an earlier read and the final write.
Use partial indexes for soft-deleted rows carefully
Soft deletion is a common fit:
CREATE INDEX customers_active_by_email
ON customers (email)
WHERE deleted_at IS NULL;An active-customer lookup can then be written as:
SELECT customer_id
FROM customers
WHERE email = 'user@example.com'
AND deleted_at IS NULL;This can be valuable when the table retains a large amount of deleted history but production reads overwhelmingly target active rows.
However, the pattern has a maintenance cost. Every query that intends to use the active-row index must preserve the active-row predicate. If one code path forgets:
AND deleted_at IS NULLthe problem is not merely slower execution. The query may also return logically deleted data.
That makes soft deletion a domain-design concern first and an indexing opportunity second. An index cannot compensate for inconsistent filtering rules.
Do not use one partial index per possible value
A tempting design is to create many narrow indexes:
one index for status = 'open'
one index for status = 'done'
one index for status = 'cancelled'
one index for status = 'blocked'
...That usually works against the purpose of partial indexing.
Each index consumes storage, increases schema complexity, adds planner choices, and creates maintenance work for rows that belong to its subset. A set of indexes that collectively covers almost every row can cost as much as, or more than, a simpler full index.
Partial indexes are strongest when there is a meaningful asymmetry: a small subset is important to index, while the rest of the table is intentionally unindexed for that access pattern.
Think about changing data distributions
A useful partial index today may become less selective later.
Suppose 3% of tasks are open when you create:
WHERE status = 'open'If product behavior changes and 70% of tasks remain open for months, the partial index is no longer particularly small.
The index can still be correct, but its performance advantage may shrink. The planner may also make different choices as statistics and row counts change.
Treat selectivity as an operational assumption, not a permanent guarantee. Revisit partial indexes when workload or retention patterns change.
Understand the write trade-off
Indexes accelerate some reads by maintaining extra structures during writes.
A partial index narrows that cost; it does not eliminate it.
For a row outside the predicate:
insert/update row
-> database evaluates predicate
-> no index entry if predicate is falseFor a row inside the predicate:
insert/update row
-> database maintains index entryFor a row crossing the predicate boundary:
false -> true : add index entry
true -> false : remove index entryThis is why a partial index can be attractive for large historical tables where only a small active subset receives indexed lookups. It is less compelling when nearly every row satisfies the predicate.
Common mistakes
Assuming a partial index changes query semantics
It does not. The query’s WHERE clause still defines the result. The index is an access structure the planner may use.
Omitting the predicate from queries
If a query can require rows outside the indexed subset, the partial index is not a complete search path for that requirement.
Choosing a volatile business subset
If the indexed condition changes constantly or unpredictably, the index may deliver little size or maintenance advantage.
Depending on clever logical equivalence
Planner implication rules are engine-specific. Prefer straightforward predicates that align with real query conditions, then inspect plans.
Creating the index without measuring
A smaller index is not automatically a faster application. Check representative query plans, latency, write cost, storage, and data distribution.
When a full index is the better choice
Prefer a conventional full index when queries commonly need rows across the whole table, when the proposed predicate matches most rows, or when many query variants do not share one stable subset condition.
A full index is also easier to reason about when the workload is broad. Simpler schema design can be more valuable than saving index entries that do not materially affect performance.
Partial indexes are specialized by design. Use specialization only when the workload is specialized too.
Index the stable hot subset, not every possibility
A partial index says: this subset of rows deserves an index for this access pattern; the rest do not.
That can make an index smaller and reduce unnecessary maintenance, but only when the predicate is selective, stable, and aligned with the queries that matter. Partial unique indexes can also encode conditional integrity rules that are difficult to enforce safely in application code alone.
Start with the query and the data distribution. Define the smallest clear predicate that represents the important subset, create the index, and verify the planner behavior on your actual database.
The goal is not to create fewer index entries at any cost. It is to maintain exactly the index structure that a real workload can justify.