1. Index Fundamentals
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...
Read full chapter →The essential PostgreSQL Indexing Strategies cheat sheet: 7 concise chapters you can read in minutes, distilled from the full deck. When you're ready, drill the flashcards or test yourself under exam conditions.
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...
Read full chapter →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...
Read full chapter →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...
Read full chapter →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...
Read full chapter →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...
Read full chapter →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...
Read full chapter →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...
Read full chapter →Done reading?
Test yourself with the PostgreSQL Indexing Strategies practice exam — timed questions, instant score, full review of wrong answers. Free.
🎯 Take the Practice Exam →