Index dramatically speeds up query, but Postgres does not always use your index.
Index all primary keys.
Index all foreign keys.
CREATE INDEX index_books_on_name ON (name)
CREATE INDEX — Locks write, can still read until finished
REINDEX INDEX — Rebuild index
- Index built on single column => Single Index
- Index built on multiple columns => Composite Index
- Index built on WHERE conditions => Partial Index
Composite Index is better because it could also cover single column query.
Partial Index is great to keep index target certain portion of table. Also uses less storage.
CREATE INDEX CONCURRENTLY — build index without taking any lock.
REINDEX CONCURRENTLY — rebuild index without taking any lock.