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

Index built on single column => Single Index
Index built on multiple columns => Composite Index
Index built on WHERE conditions => Partial Index
Index built on multiple columns and WHERE conditions => Composite Partial Index
Index built on expressions => Expression Index

Composite Index also called compound index. It could be more versatile because it could also cover single column query. Composite index (a, b, c) columns can be used to filter conditions like
a or ab or abc. But not for searching b or bc or c. The order is important.

When adding a composite index, try to put high selectivity columns in front to narraow down the search space earlier.

Selectivity
High = Select many results from query. Slower to search.
Low = Select less results from query. Faster to search.

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
;

Expression index is building the index with a matching expression:

CREATE INDEX index_users_email
ON users (lower(email))
;

Index expression is relatively expensive to maintain, because the expression must be computed for each row insertion and non-HOT update. But does not need to recompute when retrieving data with index. Exchange retrieval speed with insertion and update speed.

CREATE INDEX CONCURRENTLY — build index concurrently
REINDEX CONCURRENTLY — rebuild index concurrently
They both take SHARE UPDATE EXCLUSIVE lock.

Index size:

SELECT pg_size_pretty(pg_total_relation_size('index_name'))
;

B-Tree most common.

https://www.postgresql.org/docs/current/indexes-types.html

Index Types