Indexes

What is it? #

An index is a separate, sorted structure that lets PostgreSQL find rows without reading the whole table.

Without one, finding WHERE email = '[email protected]' in a million-row table means examining all million rows. With one, it means a handful of lookups. That is the difference between 400 milliseconds and 0.2 milliseconds, and it grows as the table grows.

The cost is real and worth stating plainly: every index must be updated on every insert, update and delete of the rows it covers. Indexes make reads faster and writes slower, and they take disk space.

So the skill is not "add indexes". It is choosing the few that earn their place.

Think of it like this #

A textbook's index at the back.

To find every mention of "transactions" without it, you read all 600 pages. With it, you look up one word and get the page numbers. Enormously faster.

But the index has to be kept accurate. Add a paragraph on page 240 and every page number after it may shift — the index must be updated too. One index is clearly worth it. Fifteen separate indexes, each needing maintenance on every edit, is why a book has one index and not fifteen.

And an index sorted by word cannot help you find "the chapter with the most diagrams". An index only answers questions matching the order it is sorted in.

Simple example #

An orders table with five million rows. The application constantly runs "all orders for this customer, newest first", and a nightly job runs "all orders still pending".

Those two queries want two different indexes — and the second one only needs to cover a tiny fraction of the table.

Code #

-- ---------- Creating and inspecting indexes ----------

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

-- On a live table, avoid locking out writes for the whole build:
CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);
-- Slower, cannot run inside a transaction block, but does not block writes.
-- ALWAYS use CONCURRENTLY on a production table that is in use.

\di                                   -- list indexes (psql)
\d orders                             -- shows the table's indexes too

DROP INDEX CONCURRENTLY orders_customer_id_idx;   -- also non-blocking

-- ---------- Proving an index works ----------

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- Before the index:
--   Seq Scan on orders  (cost=0.00..89234.00 rows=12 width=48)
--                       (actual time=0.3..412.8 rows=12 loops=1)
--   ^^^^^^^^  read every row

-- After the index:
--   Index Scan using orders_customer_id_idx on orders
--                       (actual time=0.03..0.09 rows=12 loops=1)
--   ^^^^^^^^^^  jumped straight to the matching rows

-- ---------- B-tree: the default, and the right answer 90% of the time ----------

CREATE INDEX ON customers (email);     -- B-tree is the default type

-- B-tree supports:  =  <  <=  >  >=  BETWEEN  IN  IS NULL
--                   ORDER BY  (it is already sorted)
--                   LIKE 'prefix%'   (anchored at the start ONLY)

-- B-tree CANNOT help with:
--   LIKE '%middle%'     -- no leading anchor, so sorting is useless
--   ILIKE 'a%'          -- case differs from the stored order
--   lower(email) = ...  -- the function changes the value being compared

-- ---------- Expression index: index the computed value ----------

-- This query cannot use an index on email, because lower() changes the value:
SELECT * FROM customers WHERE lower(email) = '[email protected]';

-- So index the expression itself:
CREATE INDEX customers_email_lower_idx ON customers (lower(email));
-- Now the query above uses it. The expression must match EXACTLY.

-- ---------- Composite index: COLUMN ORDER IS EVERYTHING ----------

CREATE INDEX orders_customer_placed_idx
    ON orders (customer_id, placed_at DESC);

-- This ONE index serves:
--   WHERE customer_id = 42                              -- uses the first column
--   WHERE customer_id = 42 ORDER BY placed_at DESC      -- uses both. Ideal.
--   WHERE customer_id = 42 AND placed_at > '2026-01-01'

-- It does NOT help:
--   WHERE placed_at > '2026-01-01'        -- skips the first column

-- THE LEFTMOST PREFIX RULE:
-- an index on (a, b, c) can serve queries filtering on
--     a        |  a, b     |  a, b, c
-- but NOT on
--     b        |  c        |  b, c
--
-- Think of a phone book sorted by (surname, first name).
-- Finding "Verma, Asha" is instant. Finding everyone called "Asha" is not.

-- ---------- Partial index: index only the rows you query ----------

-- 5,000,000 orders, but only ~200 are ever 'pending' at once.
CREATE INDEX orders_pending_idx
    ON orders (placed_at)
    WHERE status = 'pending';

-- The index contains 200 entries instead of 5,000,000:
--   tiny on disk, fast to scan, and cheap to maintain
--   (rows that are not pending never touch this index at all)

-- PostgreSQL uses it only when the query's WHERE clause implies the index's:
SELECT * FROM orders WHERE status = 'pending' ORDER BY placed_at;   -- uses it
SELECT * FROM orders ORDER BY placed_at;                            -- does not

-- ---------- Unique index: a constraint AND an index ----------

CREATE UNIQUE INDEX customers_email_uidx ON customers (email);
-- Enforces uniqueness and speeds up lookups at the same time.

-- Partial unique index: "only one ACTIVE subscription per customer"
CREATE UNIQUE INDEX one_active_subscription
    ON subscriptions (customer_id)
    WHERE status = 'active';
-- Old cancelled rows are ignored by the constraint. Very useful, very common.

-- ---------- The other index types, and when each is right ----------

-- GIN: for values CONTAINING many searchable items — jsonb, arrays, full text
CREATE INDEX orders_details_gin ON orders USING gin (details);          -- jsonb
SELECT * FROM orders WHERE details @> '{"gift_wrap": true}';            -- uses it

CREATE INDEX articles_tags_gin ON articles USING gin (tags);            -- text[]
SELECT * FROM articles WHERE tags @> ARRAY['postgres'];                 -- uses it

-- Full-text search:
CREATE INDEX articles_search_gin
    ON articles USING gin (to_tsvector('english', title || ' ' || body));

-- Trigram GIN: makes LIKE '%middle%' and ILIKE fast
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX customers_name_trgm ON customers USING gin (name gin_trgm_ops);
SELECT * FROM customers WHERE name ILIKE '%ash%';                       -- uses it

-- GiST: overlapping ranges, geometry, nearest-neighbour searches
CREATE INDEX bookings_period_gist ON bookings USING gist (period);      -- tstzrange

-- BRIN: enormous tables where rows are naturally ordered (e.g. append-only logs).
-- Tiny index, works by storing min/max per block range.
CREATE INDEX events_created_brin ON events USING brin (created_at);

-- Hash: equality only. B-tree does that too and does more, so rarely worth it.

-- ---------- Finding indexes that are not earning their place ----------

-- Never-used indexes (still costing you on every write):
SELECT schemaname, relname AS table, indexrelname AS index,
       idx_scan AS times_used,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (SELECT conindid FROM pg_constraint WHERE conindid <> 0)
ORDER BY pg_relation_size(indexrelid) DESC;

-- Table vs index size — if indexes dwarf the table, look hard at why:
SELECT relname,
       pg_size_pretty(pg_table_size(relid))   AS table_size,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_indexes_size(relid) DESC
LIMIT 10;

How it works #

A B-tree index is a balanced tree of sorted keys. Finding a value means descending a handful of levels rather than scanning every row — and because the depth grows logarithmically, a table ten times larger needs only about one extra level. That is why indexed lookups stay fast as data grows while sequential scans do not.

Since the index is sorted, it also satisfies ORDER BY for free, and it supports range conditions and prefix matches like LIKE 'Ash%'. It cannot help with LIKE '%ash%', because sorting by the start of a string tells you nothing about what appears in the middle.

Composite index column order is the part most worth getting right. An index on (a, b, c) is sorted by a first, then b within equal a, then c. It can therefore serve any leftmost prefix — a, or a, b, or all three — but not b alone. The phone book analogy is exact: sorted by surname then first name, finding a specific full name is instant, finding everyone with a given first name is not. Put the column used with = first, and the column used for ranges or ordering after it.

A partial index carries a WHERE clause and indexes only the matching rows. When you repeatedly query a small subset of a large table — pending orders, unprocessed jobs, active sessions — this is dramatically effective. An index over 200 rows instead of five million is smaller, faster and, crucially, only maintained when a row actually matches the condition.

An expression index stores the result of a function. It exists because WHERE lower(email) = '...' cannot use an index on email — the function transforms the value, so the stored order no longer applies. Indexing lower(email) fixes that, provided the query's expression matches the index's exactly.

GIN indexes are for values that contain many searchable elements: the keys inside a jsonb document, the elements of an array, the words in a document. Rather than one entry per row, GIN stores an entry per element pointing back at the rows containing it. With the pg_trgm extension, GIN also makes ILIKE '%middle%' fast by indexing three-character fragments — the standard fix for substring search.

GiST handles overlap and proximity: does this booking period overlap that one, what are the five nearest points. BRIN is for very large, naturally ordered tables; it stores only a minimum and maximum per block range, making it minuscule but only useful when physical row order tracks the column's values, as with append-only timestamps.

The write cost is the whole reason for restraint. Every index on a table must be updated when a row is inserted or deleted, and when an update touches an indexed column. Six indexes mean six structures maintained per write, plus disk space, plus more work for VACUUM. Unused indexes are pure cost — which is why pg_stat_user_indexes is worth checking periodically.

Real-world use #

Start by indexing foreign keys. PostgreSQL creates an index for primary keys but not for foreign keys, and foreign keys are what joins and "all children of this parent" queries use constantly. Their absence also makes deleting a parent row slow, because the child table must be scanned to check the constraint.

Then index from evidence rather than intuition. Run the slow query, read EXPLAIN ANALYZE, add the index the plan implies, and run it again. pg_stat_statements (covered in the query-performance lesson) shows which queries actually consume your database's time, which is often not the ones people assume.

On a production table, always use CREATE INDEX CONCURRENTLY. A plain CREATE INDEX takes a lock that blocks writes for the entire build, which on a large table can be many minutes of failed writes. CONCURRENTLY takes longer and cannot run inside a transaction block, and it is worth both limitations. If it fails partway it leaves an invalid index behind, so check \d afterwards and drop any that are marked invalid.

Beware redundancy. If you have an index on (customer_id, placed_at), a separate index on (customer_id) is usually redundant — the composite already serves that prefix. Dropping the redundant one removes write overhead for no loss.

Partial unique indexes deserve a specific mention because they solve a problem that comes up constantly: "only one active record per user". A plain unique constraint would also block a second cancelled row; a partial unique index applies only to the active ones.

Finally, indexes do not help small tables. Below a few thousand rows PostgreSQL will often choose a sequential scan anyway, and it is right to — reading a small table outright is cheaper than the indirection. Do not be alarmed when your test table ignores your new index; test on realistic data volumes.

Common mistakes #

• Creating an index on a production table without CONCURRENTLY, blocking writes for the whole build.
• Getting composite column order wrong — an index on (a, b) cannot serve a query filtering only on b.
• Indexing every column "just in case", which slows every write and wastes disk for no benefit.
• Expecting a B-tree index to speed up LIKE '%middle%' or ILIKE, which need a pg_trgm GIN index.
• Never checking pg_stat_user_indexes, so unused indexes keep costing write performance forever.

Practice #

Create an orders table with at least 100,000 rows of generated data. Run a query filtering by customer_id with EXPLAIN ANALYZE and note the Seq Scan and the time. Add the index, run it again, and compare. Then build a composite index on (customer_id, placed_at DESC) and confirm a query that filters by customer and orders by date uses it — and that one filtering only by date does not. Finally, create a partial index for pending orders and compare its size against the full index with pg_size_pretty.