An index is not magic. It is a data structure that helps PostgreSQL find rows faster, but only when the query pattern makes sense for that index.
This post is part of the DBApreneur starter series. The goal is to explain the topic in plain language, then give you practical checks or examples you can use in real work.
B-tree is the default workhorse
Most normal equality and range lookups use B-tree indexes. If you are just starting, learn B-tree behavior before jumping into specialized index types.
Selectivity matters
An index is more useful when it filters a small part of the table. A column with only two values may not be selective enough unless combined with other columns or used in a partial index.
Composite index order
For multi-column indexes, column order matters. Put columns in an order that matches common filters and joins. Do not create random composite indexes just because queries mention multiple columns.
Example
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 1001
ORDER BY created_at DESC
LIMIT 20;
Practical checklist
- Start with the problem you are trying to solve.
- Confirm the environment and version before applying any command.
- Test in a lab or lower environment first.
- Keep notes of what changed and why.
- Review performance, security, and rollback impact before production.
Final thought
Good engineering is rarely about memorizing commands. It is about understanding the shape of the system, asking better questions, and making changes that are boring in production. That is the kind of DBA work this series is trying to encourage.