PostgreSQL Cheatsheet

Indexes

Use this PostgreSQL reference while you build software engineering projects, review code, or refresh the syntax you reach for most.

Index types

Six access methods ship in core. Btree is the default and covers almost everything. The rest exist for query shapes btree cannot answer.

Type	Use it when	Supports	Notes
`btree`	Equality, ranges, sorting, prefix `LIKE`	`=` `<` `>` `BETWEEN` `IN` `IS NULL`, `ORDER BY`	The default, only one that can serve a sort
`hash`	Equality only, on a wide value	`=`	Crash-safe and WAL-logged since 10, rarely beats btree
`gin`	Many keys inside one value	jsonb `@>` `?`, arrays `@>` `&&`, full text `@@`, `pg_trgm`	Slower writes, `fastupdate` batches them
`gist`	Geometry, ranges, nearest-neighbour	`&&` `@>` `<->`, `EXCLUDE` constraints	Lossy, needs a recheck
`brin`	Huge tables where the column correlates with physical order	Ranges on append-only timestamps and ids	Tiny, block-range summaries only
`spgist`	Non-balanced trees, quadtrees, text prefix search	Points, inet, ranges, `text` prefixes	Niche

CREATE INDEX orders_placed_at_idx     ON orders (placed_at);              -- btree
CREATE INDEX orders_meta_gin          ON orders USING gin (metadata);
CREATE INDEX orders_tags_gin          ON orders USING gin (tags);
CREATE INDEX bookings_during_gist     ON bookings USING gist (during);
CREATE INDEX events_occurred_brin     ON events USING brin (occurred_at);
CREATE INDEX ip_spgist                ON logs USING spgist (client_ip inet_ops);

A brin index on a 500 GB append-only events table can be a few megabytes and still prune 99% of blocks for a date range. It is useless on a randomly-ordered column.

Multicolumn and the leftmost prefix

A multicolumn btree is usable only for a leftmost prefix of its column list. An index on (a, b, c) serves a, (a, b), and (a, b, c), but not b alone or (b, c).

CREATE INDEX o_cust_status_placed ON orders (customer_id, status, placed_at DESC);

Query predicate	Uses that index?
`WHERE customer_id = 7`	Yes
`WHERE customer_id = 7 AND status = 'paid'`	Yes, best case
`WHERE customer_id = 7 ORDER BY placed_at DESC`	Yes, index provides the order
`WHERE status = 'paid'`	No leading column, a full index scan at best
`WHERE customer_id = 7 AND placed_at > now() - '7d'::interval`	Partly, `status` gap forces a filter

Column order rules: equality columns first, then the range or sort column last. A DESC or NULLS FIRST in the index only matters when it lets a query skip a sort, and btree can scan backwards, so (a DESC) and (a) both serve ORDER BY a DESC.

Covering, partial, and expression indexes

INCLUDE (11+) stores extra columns in the leaf pages without making them part of the key, which enables an index-only scan without widening the search structure.

-- Index-only scan for: SELECT total FROM orders WHERE customer_id = $1
CREATE INDEX orders_cust_incl ON orders (customer_id) INCLUDE (total);

A partial index only holds rows matching its WHERE. It is smaller, cheaper to maintain, and it is the right tool for a skewed boolean or status column.

CREATE INDEX orders_pending ON orders (placed_at) WHERE status = 'pending';
CREATE INDEX users_active   ON users (email)      WHERE deleted_at IS NULL;

The planner uses a partial index only when it can prove the query predicate implies the index predicate, so query with the literal status = 'pending', not a parameter that is sometimes something else.

An expression index materializes a computed value. The query must use the exact same expression, character for character in effect, for the planner to match it.

CREATE INDEX users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);      -- matches

CREATE INDEX orders_meta_region
  ON orders ((metadata ->> 'region'));
CREATE INDEX orders_month
  ON orders (date_trunc('month', placed_at AT TIME ZONE 'UTC'));

The function must be IMMUTABLE. now() and random() are not, and neither is anything whose answer depends on the session time zone, which catches the two forms people reach for first:

CREATE INDEX ON orders ((placed_at::date));                 -- ERROR, not IMMUTABLE
CREATE INDEX ON orders (date_trunc('day', placed_at));      -- ERROR, not IMMUTABLE
CREATE INDEX ON orders (date_trunc('day', placed_at AT TIME ZONE 'UTC'));  -- ok
CREATE INDEX ON orders (date_trunc('day', placed_at, 'UTC'));  -- ok, 16+

Pinning the zone explicitly makes the expression immutable. The three-argument date_trunc is 16+.

Text pattern matching

A default btree on text uses the database collation and cannot serve LIKE 'foo%' unless the collation is C. Use an _pattern_ops operator class for prefix search, or pg_trgm for anything with a leading wildcard.

Pattern	What indexes it
`LIKE 'foo%'`	`text_pattern_ops` btree, or a `C`-collation column
`LIKE '%foo%'`	`gin_trgm_ops` GIN (`pg_trgm`)
`ILIKE 'foo%'`	Expression index on `lower(col)` with `text_pattern_ops`
`col = 'foo'` case-insensitive	Expression index on `lower(col)`, or `citext`
`~ '^foo'`	`text_pattern_ops` btree
Full-text `@@`	GIN on `to_tsvector(...)`

CREATE INDEX users_email_prefix ON users (email text_pattern_ops);
SELECT * FROM users WHERE email LIKE 'ada%';

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm ON products USING gin (name gin_trgm_ops);
SELECT * FROM products WHERE name ILIKE '%widget%';

CREATE INDEX docs_fts ON docs
  USING gin (to_tsvector('english', title || ' ' || body));
SELECT * FROM docs
WHERE to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('english', $1);

A text_pattern_ops index serves LIKE but not = ordering or ORDER BY, so a column doing both needs two indexes.

Building without downtime

A plain CREATE INDEX takes an ACCESS EXCLUSIVE-adjacent SHARE lock that blocks every write for the whole build. CONCURRENTLY does not.

CREATE INDEX CONCURRENTLY orders_placed_at_idx ON orders (placed_at);
DROP INDEX CONCURRENTLY orders_placed_at_idx;
REINDEX INDEX CONCURRENTLY orders_placed_at_idx;   -- 12+
REINDEX TABLE CONCURRENTLY orders;                -- 12+

The rules that bite:

- CONCURRENTLY cannot run inside a transaction block, on CREATE INDEX, DROP INDEX, or REINDEX alike. Most migration tools wrap everything in one, so this needs an explicit escape hatch. - It scans the table twice and waits for old transactions, so it is slower. - On failure it leaves an invalid index behind that costs writes and answers no query. Find and drop it, then retry.

SELECT indexrelid::regclass AS index, indrelid::regclass AS table
FROM pg_index WHERE NOT indisvalid;

DROP INDEX CONCURRENTLY orders_placed_at_idx;

To replace an index in place, build the new one concurrently under a temporary name, then ALTER INDEX ... RENAME TO inside one transaction after dropping the old.

Auditing what you have

Unused indexes cost every write and every vacuum. pg_stat_user_indexes counts scans since the last stats reset, so check pg_stat_reset time before trusting a zero.

-- Never-scanned indexes, largest first, skipping constraint-backed ones
SELECT s.relname AS table,
       s.indexrelname AS index,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

-- Sizes for one table
SELECT pg_size_pretty(pg_relation_size('orders'))       AS heap,
       pg_size_pretty(pg_indexes_size('orders'))        AS indexes,
       pg_size_pretty(pg_total_relation_size('orders')) AS total;

-- Index vs table scan balance
SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup
FROM pg_stat_user_tables ORDER BY seq_tup_read DESC LIMIT 20;

-- Duplicate index definitions
SELECT indrelid::regclass AS table, count(*), array_agg(indexrelid::regclass)
FROM pg_index GROUP BY indrelid, indkey HAVING count(*) > 1;

Function	Returns
`pg_relation_size(rel)`	Main fork bytes, heap or one index
`pg_indexes_size(rel)`	All indexes on a table
`pg_total_relation_size(rel)`	Heap plus indexes plus TOAST
`pg_size_pretty(bigint)`	`12 MB`-style text
`pg_table_size(rel)`	Heap plus TOAST, no indexes

Bloat is the gap between live tuples and allocated pages. REINDEX CONCURRENTLY fixes index bloat without an exclusive lock, and VACUUM FULL fixes heap bloat but rewrites the table under ACCESS EXCLUSIVE. See EXPLAIN and Performance.