Index

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

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.

Index size:

SELECT pg_size_pretty(pg_total_relation_size('index_name'));