The Postgres indexes you actually need

Before you reach for a cache, a read replica, or a rewrite, check the query plan. Most latency we are asked to fix comes down to a sequential scan where an index should be — and adding it is a one-line migration.

Read the plan first

EXPLAIN ANALYZE
SELECT * FROM articles
WHERE published = true
ORDER BY published_at DESC
LIMIT 12;

If you see Seq Scan on a large table with a filter and a sort, you have found the fix.

The partial + composite that covers listings

CREATE INDEX idx_articles_published_recent
  ON articles (published_at DESC)
  WHERE published = true;