TopolearnThese options make indexes smaller, more targeted and safe to create on a live production table.
A plain CREATE INDEX blocks writes to the table until it finishes — minutes or hours on a big table. In production use `CREATE INDEX CONCURRENTLY`: it builds without blocking inserts, updates and deletes, at the cost of taking longer and scanning the table twice. It can't run inside a transaction block, and if it fails it leaves an INVALID index you must drop and retry. REINDEX CONCURRENTLY and DROP INDEX CONCURRENTLY exist too.
Expression indexes index the result of an expression, so queries using that same expression can use them: CREATE INDEX ON users (lower(email)) serves WHERE lower(email) = $1. The query must match the expression exactly.
Partial indexes include only rows matching a WHERE clause: CREATE INDEX ON orders (created_at) WHERE status = 'pending' stays tiny when most orders are completed. A partial unique index enforces uniqueness on a subset, for example one active subscription per user.
Combine with INCLUDE (cols) for covering index-only scans. To find dead weight, check pg_stat_user_indexes: an index with idx_scan = 0 over weeks (on the primary and every replica) is a candidate to drop. Every index slows writes and takes cache space, so keep the set lean.
Example
CREATE TABLE users (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL, deleted_at timestamptz);
INSERT INTO users (email)
SELECT 'User' || g || '@Example.com' FROM generate_series(1, 50000) g;
CREATE UNIQUE INDEX CONCURRENTLY users_email_lower_uk
ON users (lower(email)) WHERE deleted_at IS NULL;
ANALYZE users;
EXPLAIN SELECT id FROM users
WHERE lower(email) = 'user42@example.com' AND deleted_at IS NULL;
-- Index Scan using users_email_lower_uk on users
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes WHERE relname = 'users';Remember
- CREATE INDEX CONCURRENTLY avoids blocking writes; check for INVALID leftovers
- Expression indexes need the query to use the same expression
- Partial indexes are small and can enforce conditional uniqueness
Watch out for
- Running CREATE INDEX (without CONCURRENTLY) on a large hot table during business hours.