Indexes: the performance feature with a write bill

1. Start with a query people actually run

A customer opens their order history and expects the latest twenty purchases. The database must find that customer’s rows, put them in order, and return a small result. An index can organize references to those rows so PostgreSQL has less work to do each time. That organization must also be maintained as orders change.

Write down the filter, ordering, result size, and frequency before choosing columns. Our target is one customer’s newest orders, ordered by created_at descending and then id descending. The unique id breaks timestamp ties, making the result deterministic. This index supports that access pattern; it is not a general solution for every report against orders.

2. Build a small local experiment

Run these statements in a fresh local PostgreSQL scratch database where you can create objects. Keep using that database for every step. The index_lab schema should not already exist. This setup adds 30,000 synthetic orders across 300 customers, with repeated timestamps to exercise the tie-breaker. It creates no customer data or external connections.

The primary key already creates an index on id. Our baseline therefore means without the additional customer-history index. ANALYZE gathers statistics for the planner after the load. The deliberately regular sample teaches the mechanism; production data may have much larger customers and different distributions.

CREATE SCHEMA index_lab;

CREATE TABLE index_lab.orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL,
  created_at timestamptz NOT NULL,
  total_cents integer NOT NULL
);

INSERT INTO index_lab.orders
  (customer_id, created_at, total_cents)
SELECT
  1 + ((g - 1) % 300),
  TIMESTAMPTZ '2026-01-01 00:00:00+00'
    + (g / 600) * INTERVAL '1 minute',
  500 + (g % 10000)
FROM generate_series(1, 30000) AS sample(g);

ANALYZE index_lab.orders;

3. Record what PostgreSQL does today

EXPLAIN alone shows an estimated plan. EXPLAIN with ANALYZE executes the query and adds observed rows and execution timing. The statement below is a simple read, but this distinction matters: applying ANALYZE to an INSERT, UPDATE, or DELETE actually performs that write. Run this experiment locally, and assess workload impact before measuring expensive production statements.

Save the output. Look for a sequential scan, a customer filter, and a sorting step. Your version and settings may produce another plan. Run the same statement several times and retain the first and later results separately: cached data can make subsequent runs cheaper. Planner cost values are estimates in relative units, not milliseconds.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM index_lab.orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;

4. Match the filter and the requested order

Create this composite B-tree index, then repeat the exact query. B-tree is PostgreSQL’s default index method. Putting customer_id first groups a customer’s entries together. With that column fixed by equality, the remaining keys follow the requested newest-first order, including the id tie-breaker. PostgreSQL can consider reading the first twenty qualifying entries without sorting all of that customer’s orders.

Column order describes a useful route through the data. An index beginning with created_at serves a different arrangement and may require more work to locate one customer. Leading equality conditions are especially useful for a multicolumn B-tree. Avoid turning that observation into a claim that an index can never help without its first column: planner capabilities and data distribution also matter.

The query still requests total_cents from the table. An ordinary index scan with table visits is expected; this example does not promise an index-only scan.

CREATE INDEX orders_customer_recent_idx
ON index_lab.orders (customer_id, created_at DESC, id DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM index_lab.orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;

5. Compare the work, then change the question

Compare the access method, any remaining Sort node, rows filtered out, buffer activity, and execution time. A matching index may remove the explicit sort and let LIMIT stop the scan early. Check estimated and actual row counts, remembering that LIMIT can stop a child node before it produces all the rows estimated for a complete scan. Large unexplained differences deserve investigation into statistics and data distribution.

Selectivity describes how much of the table a condition matches. Choosing one customer is selective in this sample; requesting almost all customers is not. Try the broader query below. PostgreSQL may reasonably prefer scanning the table because scattered table visits through an index can cost more. A sequential scan on a small table or a broad query is not automatically a defect.

Do not disable sequential scans just to make the new index appear successful. Test representative customer sizes and actual application queries. Report measurements with the database version, data size, and cache conditions; this guide supplies no universal speedup.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM index_lab.orders
WHERE customer_id <= 290;

6. Account for storage and ongoing writes

Each inserted order now needs another index entry. Updates can require new entries too, while obsolete row versions and their index entries eventually need cleanup. The extra structure consumes disk space and contributes to memory and I/O demand. Measure its size with the query below; total index bytes also include the primary-key index.

PostgreSQL’s heap-only tuple optimization can avoid new index entries for eligible updates when indexed columns are unchanged and the original page has room. Adding an index on a frequently changed column can remove that opportunity. More indexes therefore affect updates beyond the time spent searching for rows.

Routine vacuuming reclaims dead versions in tables and indexes for reuse; ordinary VACUUM generally does not shrink files back to their minimum size. Keep autovacuum healthy. This read experiment does not measure write overhead: compare representative inserts and updates on equivalent scratch datasets with and without the extra index before estimating the production bill.

SELECT
  pg_size_pretty(pg_relation_size(
    'index_lab.orders_customer_recent_idx'
  )) AS added_index_size,
  pg_size_pretty(pg_indexes_size(
    'index_lab.orders'
  )) AS all_indexes_size;

7. Treat production creation as an operation

The plain CREATE INDEX used here blocks writes on its table while building. For a live table, evaluate CREATE INDEX CONCURRENTLY. It permits ongoing writes, but performs extra work, can wait on transactions, and adds CPU and I/O load. It cannot run inside a transaction block, so confirm how your migration system wraps statements. Only one concurrent index build can run on a table at a time.

A failed concurrent build can leave an invalid index that is unavailable to queries yet still incurs update overhead. Check build completion and index validity, and plan recovery rather than assuming a failed command left nothing behind. Partitioned tables have additional restrictions; check the documentation for your server version.

After rollout, observe the target query and write workload over a representative period. Review rarely used indexes with their constraint responsibilities and occasional reporting workloads in mind before removing them. Keep an index because measured value justifies its continuing cost.