Add the Postgres Index That Matches the Query You Run

An index is a sorted side structure Postgres can walk instead of reading the whole table. It helps only when it matches the query. A random index on a column nobody filters is extra writing on every insert and zero help on the page that is slow. Start from the SELECT the page actually runs, then add the smallest index that matches that shape.

A blog home page usually wants the newest published posts, limited to a page of results. The slow version reads every row, throws away drafts, sorts what remains, and keeps twelve. On a small table you will not feel it. On a table that also stores revisions, images, and old drafts, that sort becomes the wait on every visit.

Write the query the page runs#

select id, slug, title, published_at
from posts
where status = 'published'
order by published_at desc
limit 12;

Three facts in that statement decide the index. The filter is an equality on status. The order is published_at descending. The page only needs twelve rows. Postgres can use an index to jump to the published rows already stored in that order and stop after twelve. It cannot invent that order from an index on title.

Ask for the plan before you add anything#

Run the statement with explain analyze in a session that has realistic data. Look for a sequential scan paired with a sort. That pair is the sign the table is being read and then ordered in memory. After the index exists, run the same explain again. You want an index scan, or an index-only scan if the selected columns are in the index, and a much smaller actual time. Keep both plans in the pull request so the next person can see what changed.

explain analyze
select id, slug, title, published_at
from posts
where status = 'published'
order by published_at desc
limit 12;

Match the filter and the sort#

A partial index stores only the published rows, which is the set the home page cares about. Putting published_at first, in the same direction as the ORDER BY, lets Postgres walk the newest rows and stop. Include the columns the page selects if you want a chance at an index-only scan. id is enough to join back when the page needs the full row later.

create index posts_published_recent_idx
on posts (published_at desc, id)
where status = 'published';

A few rules that prevent a junk drawer of indexes#

• One query, one index, until a second query shows a different plan problem.
• Equality columns come before the range or sort column. An index on (published_at, status) does not serve a filter on status.
• A partial index must use the same condition the query uses. status = 'published' in SQL and a different string in the app will miss the index.
• Create it with create index concurrently on a live table so writes are not blocked for the whole build. A migration runner has to allow that statement outside a transaction.

Supabase and any other hosted Postgres accept the same SQL. Put it in a migration with a name that mentions the page, such as home_published_posts_idx. When the query changes, drop the index that no longer matches. An unused index still costs time on every insert and update of posts.