Index dramatically speeds up query, but Postgres does not always use your index.
Index all primary keys.
Index all foreign keys.
Syntax: 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 (See the WHERE below). Also uses less storage.
CREATE INDEX index_blocked_users_created_at
ON users(created_at)
WHERE blocked IS TRUE
;
CREATE INDEX CONCURRENTLY — build index without taking any lock.
REINDEX CONCURRENTLY — rebuild index without taking any lock.
Index size:
SELECT pg_size_pretty(pg_total_relation_size('index_name'));