A free, self-paced textbook in 7 chapters. Read a chapter, then drill it with the 120 companion flashcards using spaced repetition.
An index in PostgreSQL is a secondary data structure maintained alongside a table that lets the query planner locate rows matching a predicate without scanning every row. The trade...
The most common task is creating a single-column B-tree, for example CREATE INDEX idx_users_email ON users (email). Adding the UNIQUE keyword makes the index enforce uniqueness, an...
Hash, GIN, GiST, SP-GiST, and BRIN each address workloads that B-tree handles poorly. A Hash index supports only equality (=) comparisons using a hash of the key. Since PostgreSQL...
Two extensions are particularly valuable for real applications. The bloom extension, enabled with CREATE EXTENSION bloom, supplies a Bloom filter index that supports many equality...
Indexes accumulate bloat over time as MVCC creates dead tuples, page splits fragment the structure, and updates produce sparse pages. Bloated indexes waste space and slow scans, so...
The query planner does not directly know which index to use; it estimates the cost of every candidate plan based on statistics stored in pg_statistic (exposed via pg_stats) and pic...
Indexes serve more than SELECT queries. Foreign key columns should be indexed to speed up referential integrity checks on the referenced table during INSERT, UPDATE, and DELETE, an...