PostgreSQL indexing basics every web developer should know

An index trades a little write cost and disk space for much faster reads. Used well, it is the cheapest performance win in a database-backed app.

What an index actually does

Without an index, a filter forces a table scan — every row is read. With one, PostgreSQL can jump straight to the matching rows. The default B-tree index handles equality and range queries well.

create index invoices_org_created_idx
  on invoices (org_id, created_at desc);

Read the query plan first

Before adding an index, look at what the database is actually doing. If the plan already uses an index, a second one probably will not help.

explain analyze
select * from invoices
where org_id = '...'
order by created_at desc
limit 20;

Indexes that pay off

• Foreign keys you filter or join on often.
• Columns used in ORDER BY alongside a filter.
• A partial index when you only ever query a subset of rows.

Remove indexes you no longer use. Every one of them has to be maintained on every write.