What is an index in a database? Should you index every column?

An index is an extra data structure (usually a B-tree) that finds rows by a column's value without scanning the whole table, like the index at the back of a book.

You shouldn't index every column, because:

• Every INSERT, UPDATE and DELETE also has to update the indexes, so writes get slower.
• Indexes take up storage.
• Columns with few distinct values (such as a status with only 2 values) usually don't gain much.

create index posts_published_at_idx on posts (published_at desc)
  where status = 'published';

A good answer mentions measuring first with EXPLAIN ANALYZE, then adding an index for the specific slow query.