PostgreSQL Indexing Strategies

This deck walks you through the core ideas behind PostgreSQL indexing, starting from what an index actually is and building up to more nuanced topics like composite key ordering, index-only scans, and partial indexes. You'll get comfortable with the default B-tree access method, learn the practical SQL syntax for creating single-column, unique, and multi-column indexes, and explore how PostgreSQL decides whether an index can be used for a given query. By the end, you should have a clear mental model of when indexes help, when they don't, and what they cost.

It's a good fit for developers and database practitioners who want to move beyond writing queries that just work and start tuning them for performance. If you're preparing for a backend or database-focused interview, maintaining a production PostgreSQL database, or simply curious about how the query planner uses indexes under the hood, these cards will give you a solid working vocabulary. The questions mix conceptual understanding ("why does column order matter?") with concrete commands, so you can both explain the reasoning and produce the right SQL on the spot.

To get the most out of the deck, try to connect each card to a real table you work with, even if only in your head, and ask yourself whether the rule in question would change your index choices there. The material is cumulative, so spacing your review sessions over several days will help the trade-offs between read speed, write overhead, and storage size stick better than cramming. When you hit a card about syntax, actually type the command into a scratch database alongside the deck to reinforce the muscle memory.

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-off is universal: indexes accelerate reads (selections, joins, and orderings) at the cost of additional storage, slower writes (because every INSERT, UPDATE, or DELETE that touches an indexed column must update each relevant index), and extra work during VACUUM. PostgreSQL ships with a rich family of access methods: B-tree, Hash, GIN, GiST, SP-GiST, and BRIN, supplemented by extension-provided types such as Bloom, zombodb, rum, and pg_trgm's trigram operator classes.

B-tree is the default access method and the workhorse for most workloads. It maintains keys in sorted order on disk, which means it supports equality and range predicates, IS NULL and IS NOT NULL checks, and ORDER BY traversal in index order. The planner can also chain B-tree indexes through bitmap scans and use them as inputs to merge joins, making them remarkably versatile.

Writing performance is the constant reminder behind every indexing decision. Each indexed column adds write amplification: more WAL volume, more random I/O, and more dead tuples to clean up later. The art of indexing in PostgreSQL is choosing access methods and definitions that match real query patterns while keeping the write cost proportional to the benefit gained on reads.

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, and PostgreSQL automatically creates one whenever you declare a column UNIQUE or as a PRIMARY KEY. Composite B-tree indexes list multiple columns in CREATE INDEX orders (customer_id, order_date), and column order matters profoundly because keys are sorted lexicographically: the index is only usable for predicates on a leading prefix. So an index on (a, b, c) serves a query filtering on a alone, on a plus a range on b, or on equality across all three, but it cannot serve a query that filters only on b or only on c.

Two refinements extend what a B-tree can cover. The INCLUDE clause adds extra columns to the leaf pages without making them part of the search key, so they do not affect ordering or uniqueness rules; this is how you build a covering index that allows the planner to satisfy a query entirely from index pages. Expression indexes go further by indexing the result of a function, such as CREATE INDEX idx ON t (lower(email)), and they are invisible to the planner unless the query uses the exact same expression. Partial indexes add a WHERE clause to restrict the rows indexed, yielding smaller, faster, and less frequently updated indexes for sparse predicates like status = 'pending' or deleted_at IS NULL.

Two more B-tree details are worth noting. Index-only scans read everything directly from the index when the visibility map shows the required heap pages are all-visible and all referenced columns are indexed or INCLUDE-d, eliminating the heap fetch altogether. For LIKE and ILIKE with non-C locales, B-tree indexes built with the text_pattern_ops or bpchar_pattern_ops opclass are required; otherwise the locale-aware comparison prevents the optimizer from using the index for prefix patterns. A GIN index built with the gin_trgm_ops opclass serves the related case of LIKE '%foo%' substring matching, while a plain B-tree on a prefix column handles ILIKE 'foo%'.

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 10, Hash indexes are WAL-logged and therefore crash-safe, and they are sometimes chosen over B-trees for very large tables where equality lookups occur on wide keys (long strings or UUIDs) because they can be slightly smaller. They remain useless for range queries or ordering.

GIN stands for Generalized Inverted Index and is designed for values that contain many sub-elements: arrays, JSONB documents, full-text tsvectors, and trigrams. The operator class dictates what is indexed; for JSONB, jsonb_ops indexes every key and value and supports the existence operators (?, ?|, ?&) as well as containment (@>), while jsonb_path_ops indexes only the paths to values, supports only @>, and is smaller and faster for containment-only queries. GIN maintains a pending list of recent updates for speed; VACUUM flushes this list, and the fastupdate option (on by default) controls the trade-off.

GiST, the Generalized Search Tree, is a balanced structure that hosts many search strategies including range overlap (&&), nearest-neighbor searches (<->), geometry and full-text types, and exclusion constraints. SP-GiST, the Space-Partitioned variant, handles non-balanced data such as quadtrees, kd-trees, and radix trees, useful for phone numbers, IP addresses via the inet type, and geometric point data. BRIN, Block Range INdex, takes a fundamentally different approach: it stores a summary (typically min/max, optionally other aggregates) for each range of heap pages, making it tiny in size. BRIN shines on very large append-only tables where data is naturally correlated with physical order, such as monotonically increasing timestamps in time-series logs, but performs poorly on tables with random inserts and updates because the correlations it relies on break down. The pages_per_range parameter tunes the granularity: smaller values produce more accurate and larger indexes, larger values produce more compact but coarser ones.

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 predicates across multiple columns in a compact form; it is a strong fit for star-schema fact tables where any combination of filter columns may appear in queries. The pg_trgm extension provides trigram (three-character substring) similarity functions and GIN or GiST operator classes, most importantly gin_trgm_ops, which accelerate LIKE, ILIKE, and similarity (%) searches on text far faster than sequential scans when the pattern is not anchored to the prefix.

Full-text search is another extension-flavored workload that lives primarily on GIN. The tsvector data type stores the normalized lexeme form of a document, produced by to_tsvector('english', text), and tsquery represents a parsed search expression. A typical setup creates GIN indexes like CREATE INDEX idx ON articles USING GIN (to_tsvector('english', body)), so queries of the form body @@ to_tsquery('english', 'postgres & indexing') are accelerated by the index. Exclusion constraints are also worth knowing here: they pair GiST indexes with operators such as && to prevent overlapping rows, so a booking system can forbid two reservations for the same room during overlapping periods.

These specialized indexes share the usual cost calculus. Bloom, trigram, and full-text GIN all accelerate reads dramatically but add cost to writes, and many of them grow quickly if the data is highly varied. The rule of thumb is to enable them only when a clear workload demands the access pattern, and to verify with EXPLAIN that the planner is actually choosing them.

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 PostgreSQL ships contrib tools for diagnostics: pgstattuple and pgstatindex report density and free space ratios, and queries against pg_stats and pg_class can estimate expected versus actual sizes. When bloat becomes excessive, REINDEX INDEX CONCURRENTLY idx_name rebuilds the index without taking an ACCESS EXCLUSIVE lock, which is critical on production systems. Plain REINDEX is faster but blocks all access to the table during the rebuild.

Several storage parameters shape how an index ages. fillfactor leaves a percentage of each page free for future in-place updates, reducing page splits at the cost of a slightly larger initial index; B-trees default to 90, and you can specify WITH (fillfactor = 70) at creation time. Since PostgreSQL 13, B-trees default to deduplicate_items = on, which stores only one copy of adjacent equal key values to save space on indexes with many duplicates such as those on status columns. Large builds can also use parallelism: setting max_parallel_maintenance_workers before CREATE INDEX launches parallel workers (PostgreSQL 11+) to speed up B-tree construction on big tables.

Creating and dropping indexes on busy tables deserves care. CREATE INDEX CONCURRENTLY builds the index without locking writes, at roughly 2-3x the time of a regular build; if it fails, it leaves an INVALID index that must be cleaned up manually. You can check validity through pg_index: indisready means the index is ready for updates while indisvalid means it is visible to query planning, and a failed CONCURRENTLY build typically has indisready = true but indisvalid = false. DROP INDEX CONCURRENTLY performs a similar lock-free removal. Tablespaces add another axis of operational flexibility: CREATE INDEX ... TABLESPACE fast_ssd places an index on fast storage, and ALTER INDEX idx SET TABLESPACE fast_ssd moves an existing one.

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 picks the lowest cost, which sometimes means choosing a sequential scan over an available index. ANALYZE refreshes these statistics, both manually and during autovacuum after a configurable fraction of rows has changed. Stale statistics mislead the planner into bad cardinality estimates, bad join orders, and bad memory sizing. Per-column sampling depth can be raised with ALTER TABLE t ALTER COLUMN c SET STATISTICS 1000 followed by ANALYZE, and functional dependencies recorded in pg_stats help the planner when correlated columns appear in the same query.

EXPLAIN is the window into planner decisions. EXPLAIN (ANALYZE, BUFFERS) SELECT ... shows the actual plan with row counts, timings, and buffer usage, while EXPLAIN (FORMAT JSON) provides a machine-readable form for tools like pev2 and pg_plan_guarantee. The auto_explain contrib module can log slow-query plans automatically using log_min_duration, with sample_rate and log_nested_statements parameters to control verbosity. Among the plan nodes, Bitmap Index Scan builds a page-level bitmap that may combine (BitmapAnd, BitmapOr) results from several indexes; Bitmap Heap Scan then visits those pages in physical order, applying a Recheck Cond when work_mem forces the bitmap to degrade to a lossy one-bit-per-page representation. Increasing work_mem keeps more bitmaps exact and reduces rechecks.

Three cost parameters strongly influence index selection. random_page_cost estimates the cost of an index scan (lowered to around 1.1 on SSD storage to favor indexes); seq_page_cost estimates sequential heap page reads (defaulting to 1.0); and effective_cache_size hints how much of the database fits in OS and shared buffer caches, nudging the planner toward index scans when the hint is large. Joining strategies also rely on indexes in different ways: Nested Loop probes an inner index for each outer row, Merge Join benefits when B-tree indexes already provide sorted inputs, and Hash Join builds its own hash table and does not depend on indexes. Disabling plan types with enable_indexscan = off (or similar enable_* GUCs) is acceptable for diagnosis but should never reach production configurations.

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, and to enable efficient joins on the FK column; PostgreSQL does not create such indexes automatically because of the write overhead, so the DBA must add them when the workload includes heavy cascading deletes or frequent joins. Unique indexes, beyond enforcing uniqueness, are the mechanism behind INSERT ... ON CONFLICT (col) DO NOTHING and DO UPDATE, allowing PostgreSQL to detect conflicts quickly; the upsert forms cannot work without a unique index or primary key on the arbiter column.

Primary keys combine NOT NULL with a unique B-tree (created automatically), which the system uses for lookups, joins, and as the default join key in ORMs. Note that PostgreSQL does not have true clustered indexes; the heap is unsorted, and the CLUSTER command physically reorders the heap to match an index once, but is not maintained on subsequent inserts. The system column ctid exposes the current physical position of a row as (block, item), but it changes on UPDATE, so it is not a usable long-term key.

Detecting unused or redundant indexes completes the picture. pg_stat_user_indexes exposes cumulative usage counters, and the simple query SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0 lists candidates that have never been used (though freshly reset statistics can mislead). Overlapping indexes whose key set is a prefix of another are a common source of wasted write effort; keeping the one that best matches the dominant query pattern and dropping the rest pays back in reduced write amplification. Workload-driven advisors such as pg_qualstats, pg_hint_plan, and EXPLAIN-driven analysis can suggest missing indexes when the planner is doing too much sequential work.