Partial, Expression, INCLUDE/Covering, Unique, and Concurrent Indexes

Learning outcomes

ServiceHub now has several B-tree candidates, but production index design needs more than key order. Some queries touch only active rows; others search a normalized expression such as lower(email); dashboards return extra columns that could potentially be carried as non-key payload; business rules require uniqueness; and live tables cannot always tolerate a write-blocking index build. PostgreSQL provides partial, expression, INCLUDE, unique, and concurrent index features for these different problems. Combining them blindly is dangerous because each has planner, write-cost, visibility, and operational semantics.

1. Build a mixed workload

DROP TABLE IF EXISTS app.ch10_design_lab;CREATE TABLE app.ch10_design_lab (    ticket_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    tenant_id integer NOT NULL,    email text NOT NULL,    status text NOT NULL CHECK (status IN ('open','closed','cancelled')),    priority smallint NOT NULL,    created_at timestamptz NOT NULL,    summary text NOT NULL);INSERT INTO app.ch10_design_lab(tenant_id,email,status,priority,created_at,summary)SELECT (g % 20) + 1,       'User' || (g % 30000) || '@Example.COM',       CASE WHEN g % 10 < 2 THEN 'open' WHEN g % 10 < 9 THEN 'closed' ELSE 'cancelled' END,       (g % 5) + 1,       timestamptz '2026-01-01' + g * interval '15 seconds',       'ServiceHub ticket ' || gFROM generate_series(1,180000) AS g;ANALYZE app.ch10_design_lab;

2. Expression indexes: index the expression the workload actually uses

A normal B-tree on email does not automatically provide ordering/search semantics for lower(email). If case-insensitive lookup is intentionally modeled as lower(email) = lower($1), an expression index can materialize that derived key.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT ticket_idFROM app.ch10_design_labWHERE lower(email) = 'user1234@example.com';

CREATE INDEX ch10_email_lower_idxON app.ch10_design_lab (lower(email));ANALYZE app.ch10_design_lab;EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT ticket_idFROM app.ch10_design_labWHERE lower(email) = 'user1234@example.com';

Expect the second plan to have an index condition on lower(email) if the lookup is selective enough. The stored expression adds update/insert work because PostgreSQL must compute and maintain the index key when relevant rows change.

3. Partial indexes: the query must imply the predicate

Only about 20% of this lab is open. If the high-value workload repeatedly scans open tickets, a partial index can omit the closed/cancelled majority.

CREATE INDEX ch10_open_tenant_created_idxON app.ch10_design_lab (tenant_id, created_at DESC)WHERE status = 'open';SELECT pg_get_indexdef('app.ch10_open_tenant_created_idx'::regclass),       pg_size_pretty(pg_relation_size('app.ch10_open_tenant_created_idx'));EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT ticket_id, created_atFROM app.ch10_design_labWHERE tenant_id = 7  AND status = 'open'ORDER BY created_at DESCLIMIT 25;

The planner can use a partial index only when it can prove at planning time that the query predicate implies the index predicate. PostgreSQL deliberately does not run a general theorem prover.

SET plan_cache_mode = force_generic_plan;PREPARE tickets_by_status(text, integer) ASSELECT ticket_id, created_atFROM app.ch10_design_labWHERE status = $1 AND tenant_id = $2ORDER BY created_at DESCLIMIT 25;EXPLAIN (ANALYZE, BUFFERS, SETTINGS)EXECUTE tickets_by_status('open', 7);DEALLOCATE tickets_by_status;RESET plan_cache_mode;

With plan_cache_mode = force_generic_plan, the planner sees placeholders rather than a value it can treat as a planning-time constant. It therefore cannot prove that status = $1 always implies status = 'open', so the partial index is not generally eligible. Reset the setting after the lab. The design lesson is not “never parameterize”—parameters are essential for safety. It is that partial-index design must be tested with the application's actual prepared-statement and generic/custom-plan behavior.

4. INCLUDE/covering indexes: payload is not search key

INCLUDE stores additional columns in the index without making them ordering/search keys. This can let an index contain every column needed by a query, creating an index-only scan opportunity. It does not guarantee heap avoidance: PostgreSQL still needs MVCC visibility, normally via the visibility map.

CREATE INDEX ch10_open_cover_idxON app.ch10_design_lab (tenant_id, created_at DESC)INCLUDE (ticket_id, priority, summary)WHERE status = 'open';VACUUM (ANALYZE) app.ch10_design_lab;EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT ticket_id, priority, created_at, summaryFROM app.ch10_design_labWHERE tenant_id = 7 AND status = 'open'ORDER BY created_at DESCLIMIT 25;

If PostgreSQL chooses Index Only Scan, inspect Heap Fetches. A covering index can still perform heap fetches on pages that are not all-visible. Also remember that every included payload byte increases index size, cache pressure, and write traffic. INCLUDE is not a “put the rest of the table here” feature.

5. Unique indexes are correctness structures

A unique index both supports lookup and enforces a business invariant. Do not classify it as “unused” based solely on idx_scan. Its most important work may occur during writes.

-- Separate teaching table for uniqueness and NULL semanticsDROP TABLE IF EXISTS app.ch10_contact_keys;CREATE TABLE app.ch10_contact_keys(    tenant_id integer NOT NULL,    external_key text);CREATE UNIQUE INDEX ch10_contact_key_uqON app.ch10_contact_keys(tenant_id, external_key) NULLS NOT DISTINCT;INSERT INTO app.ch10_contact_keys VALUES (1, NULL);-- This second row fails because NULLS NOT DISTINCT treats the NULL keys as duplicates:INSERT INTO app.ch10_contact_keys VALUES (1, NULL);

The deliberately failing statement should report a unique-violation error (SQLSTATE 23505). Use a transaction or reset the table before rerunning the lab.

6. CREATE INDEX CONCURRENTLY: lower write blocking, more phases

Normal CREATE INDEX permits reads but blocks writes while building. CREATE INDEX CONCURRENTLY avoids that write-blocking mode by using multiple transactions/scans and waiting for relevant transactions. It costs more total work and cannot run inside a transaction block.

SELECT pid, datname, relid::regclass AS table_name,       index_relid::regclass AS index_name,       command, phase,       lockers_total, lockers_done,       blocks_total, blocks_done,       tuples_total, tuples_doneFROM pg_stat_progress_create_index;

CREATE INDEX CONCURRENTLY ch10_status_created_conc_idxON app.ch10_design_lab(status, created_at DESC);

To observe progress on a small local table, run the build in one psql session and query pg_stat_progress_create_index rapidly from another. Small builds may finish before you catch them.

7. Deliberate concurrent-build failure and invalid-index cleanup

When a concurrent build fails during a scan—uniqueness conflicts are a classic case—the catalog can retain an invalid index. PostgreSQL ignores an incomplete invalid index for ordinary query planning, but it can still consume write maintenance. That state must be detected explicitly.

DROP TABLE IF EXISTS app.ch10_bad_unique;CREATE TABLE app.ch10_bad_unique(k integer NOT NULL);INSERT INTO app.ch10_bad_unique VALUES (1),(1),(2),(3);-- Run as a top-level statement. It is expected to fail with duplicate key data:CREATE UNIQUE INDEX CONCURRENTLY ch10_bad_unique_idxON app.ch10_bad_unique(k);

SELECT c.relname AS index_name,       i.indisvalid,       i.indisready,       i.indislive,       pg_get_indexdef(i.indexrelid)FROM pg_index AS iJOIN pg_class AS c ON c.oid = i.indexrelidWHERE i.indrelid = 'app.ch10_bad_unique'::regclass;

The safe repair is to remove or rebuild the invalid artifact after fixing the underlying data and confirming operational intent. REINDEX INDEX CONCURRENTLY can rebuild an invalid index; dropping and recreating is often simpler in a disposable lab.

DROP INDEX CONCURRENTLY IF EXISTS app.ch10_bad_unique_idx;DELETE FROM app.ch10_bad_unique aUSING app.ch10_bad_unique bWHERE a.ctid > b.ctid AND a.k = b.k;CREATE UNIQUE INDEX CONCURRENTLY ch10_bad_unique_idxON app.ch10_bad_unique(k);

ctid is used only as a disposable-lab tie-breaker here. It is not a durable business identifier.

8. Production judgment and cleanup

Use expression indexes when the expression is the stable application predicate, partial indexes when a provable subset serves a real workload, covering indexes when heap avoidance is likely enough to justify payload storage, unique indexes for correctness, and concurrent builds when reduced write blocking is worth extra time/I/O and operational phases. Measure before and after on the same data and workload.

DROP TABLE IF EXISTS app.ch10_bad_unique;DROP TABLE IF EXISTS app.ch10_contact_keys;DROP TABLE IF EXISTS app.ch10_design_lab;

Authoritative references

Index behavior depends on PostgreSQL major version, operator class, collation, statistics, data distribution, visibility, and workload. Use the documentation for the exact major you operate.

• PostgreSQL 18 — Indexes on Expressions
• PostgreSQL 18 — Partial Indexes
• PostgreSQL 18 — Index-Only Scans and Covering Indexes
• PostgreSQL 18 — Unique Indexes
• PostgreSQL 18 — CREATE INDEX