PostgreSQL Indexing - Complete Notes

A practical and interview-focused guide to B-tree, Hash, GIN, GiST, SP-GiST and BRIN indexes, composite column order, partial and covering indexes, expression indexes, planner decisions and production-safe index design.

00. The indexing mental model

An index is an additional data structure that trades write work and storage for a faster way to find, order or validate rows. It is useful only when its structure and operators match a real query.

PostgreSQL tables store rows in a heap. The heap does not promise key order. Most indexes store indexable values plus a reference to a heap tuple, called a tuple identifier or TID. A normal index scan first navigates the index and then visits matching heap locations. This second step matters because PostgreSQL must read columns not stored in the index and check row visibility under MVCC.

01. Index anatomy, syntax and cost

PostgreSQL indexes are secondary structures stored separately from the heap. They speed reads by reducing the locations examined, but the database must maintain them during writes.

CREATE INDEX essentials

-- B-tree is the default access method.
CREATE INDEX orders_customer_ordered_idx
    ON orders (customer_id, ordered_at DESC);

-- Name the access method when it is not B-tree.
CREATE INDEX documents_search_idx
    ON documents USING GIN (search_vector);

-- Conditional, expression and payload features can be combined.
CREATE INDEX orders_open_customer_idx
    ON orders (customer_id, ordered_at DESC)
    INCLUDE (total)
    WHERE status = 'open';

An index belongs to one table and one schema. PostgreSQL generates a name if one is omitted, but an intentional name makes migrations, monitoring and incident work easier. A useful convention is table_purpose_idx. Constraint-owned indexes should normally be changed through the constraint rather than dropped independently.

Ordinary, bitmap and index-only access

A bitmap scan loses index ordering because it reorganizes work by heap page. It can combine several indexes with bitmap AND or OR. It may become lossy when its bitmap needs to summarize whole pages, so the heap scan can show Rows Removed by Index Recheck. Lossy here means "return candidates and verify them", not incorrect results.

The cost side of every index

• Every insert adds index entries for the new row.
• An update may add new entries to every affected index. Even an index whose key value is unchanged can prevent a heap-only tuple update when the update changes any column stored by that index.
• Deletes leave entries that vacuum must later clean after tuple versions become removable.
• Indexes consume disk, buffer cache, backup, restore, replication and WAL capacity.
• More candidate indexes increase planning work and operational complexity.
• Wide keys reduce fan-out, grow tree height sooner and move more bytes through memory.

02. Choosing an index type

The access method describes the physical search strategy. The operator class connects a data type and a set of operators to that strategy.

Do not ask which type is fastest in general. Ask which type supports the query's operator and data distribution at the lowest total workload cost. B-tree is the correct default for ordinary scalar equality and range lookups. Specialized data and predicates justify specialized indexes.

03. B-tree indexes

B-tree is PostgreSQL's default and most broadly useful index type. It keeps sortable keys in logical order and supports equality, ranges, null tests and ordered output.

Predicates B-tree can support

A compatible B-tree operator class normally supports <, <=, =, >= and >. PostgreSQL can translate constructs such as BETWEEN and many IN conditions into compatible searches. It can also use B-tree for IS NULL and IS NOT NULL.

CREATE INDEX orders_customer_time_idx
    ON orders (customer_id, ordered_at DESC);

SELECT order_id, ordered_at
FROM orders
WHERE customer_id = 42
  AND ordered_at >= TIMESTAMPTZ '2026-07-01 00:00:00+00'
ORDER BY ordered_at DESC
LIMIT 20;

Here the equality on customer_id selects one contiguous key region. Within that region, rows are already ordered by descending ordered_at. PostgreSQL can stop after finding 20 qualifying entries instead of sorting every matching order.

Ordering, direction and null placement

A default B-tree key is ascending with nulls last. A backward scan reverses both direction and null placement. For one key, an ordinary index can therefore serve ascending and descending requests. Mixed ordering in a multicolumn query may require explicit key directions.

CREATE INDEX leaderboard_region_score_idx
    ON leaderboard (region ASC, score DESC, player_id ASC);

SELECT player_id, score
FROM leaderboard
WHERE region = 'APAC'
ORDER BY score DESC, player_id ASC
LIMIT 100;

B-tree is the only built-in index method that can directly produce general sorted output. For a large portion of a table, a sequential scan plus sort may still cost less than many heap visits through the index. Ordering is especially valuable with a small LIMIT.

Text prefixes and collations

B-tree can support an anchored constant pattern such as name LIKE 'Sam%', but not a leading wildcard such as name LIKE '%Sam%'. With non-C collations, deterministic prefix matching can require text_pattern_ops, varchar_pattern_ops or bpchar_pattern_ops. A pattern-operator index may need to coexist with the default operator-class index when ordinary locale-aware comparisons are also important.

CREATE INDEX customers_name_prefix_idx
    ON customers (full_name text_pattern_ops);

SELECT customer_id, full_name
FROM customers
WHERE full_name LIKE 'Som%';

04. Hash indexes

Hash indexes store a hash code and support equality comparisons only. They do not support range predicates or ordered output.

CREATE INDEX sessions_token_hash_idx
    ON sessions USING HASH (token_hash);

SELECT user_id, expires_at
FROM sessions
WHERE token_hash = $1;

PostgreSQL hash indexes are WAL-logged and crash-safe in supported modern releases. That historical concern is no longer a valid reason to reject them. The practical comparison is still usually against B-tree: B-tree also supports equality, can be unique, can order results, can support ranges and often performs very well.

Consider Hash only after measuring a narrow equality-only workload where its size or behavior is beneficial. Do not select it merely because the application calls a value a hash or token. The query operator, not the column's business name, determines index compatibility.

05. GIN inverted indexes

GIN maps component keys to the rows containing them. It is designed for values that hold many searchable elements, such as array members, JSONB keys and values, or full-text lexemes.

Arrays and full-text documents

CREATE INDEX articles_tags_idx
    ON articles USING GIN (tags);

SELECT article_id, title
FROM articles
WHERE tags @> ARRAY['postgresql', 'performance'];

CREATE INDEX articles_search_idx
    ON articles USING GIN (search_vector);

SELECT article_id, title
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'index tuning');

One indexed row can produce many GIN entries. This makes membership and containment efficient but makes inserts and updates more expensive than a simple scalar index. GIN entries generally do not reconstruct the complete original value, so GIN cannot provide index-only scans.

JSONB operator classes

-- Default jsonb_ops supports key existence, containment and JSON path operators.
CREATE INDEX events_payload_ops_idx
    ON events USING GIN (payload);

SELECT event_id
FROM events
WHERE payload ? 'tenant_id';

-- jsonb_path_ops supports @>, @? and @@, and is often smaller for that workload.
CREATE INDEX events_payload_path_idx
    ON events USING GIN (payload jsonb_path_ops);

SELECT event_id
FROM events
WHERE payload @> '{"kind":"purchase","currency":"INR"}';

A whole-document GIN index is flexible, but it can be much larger than a targeted expression index. If queries repeatedly compare one scalar JSON path, extract that value with the same expression and index it using an appropriate type.

CREATE INDEX events_tenant_created_idx
    ON events (((payload ->> 'tenant_id')::bigint), created_at DESC);

SELECT event_id
FROM events
WHERE (payload ->> 'tenant_id')::bigint = 42
ORDER BY created_at DESC
LIMIT 50;

GIN write behavior and pending list

With the default fastupdate behavior, PostgreSQL can place new GIN entries into a pending list and merge them into the main structure later. This improves many writes, but searches must also inspect the pending list. A large pending list can slow reads, and the write that crosses its limit may perform cleanup work.

• Keep autovacuum healthy so maintenance can move pending entries.
• Measure write latency as well as read latency before adding broad GIN indexes.
• Avoid indexing enormous unbounded documents without proven predicates.
• Consider fastupdate = off only when consistent read behavior matters more than foreground update cost and measurements justify it.

06. GiST and SP-GiST

GiST and SP-GiST are extensible indexing frameworks. Their capabilities come from the selected operator class, so the data type and operators matter more than the access-method name alone.

GiST: generalized search trees

GiST is a balanced tree framework commonly used for geometric relationships, range overlap, nearest-neighbor distance, text similarity through extensions and exclusion constraints. Internal keys can summarize or bound many child values. A search may return candidates whose exact predicates are rechecked against the heap, depending on the operator class.

CREATE INDEX bookings_during_idx
    ON bookings USING GIST (during);

SELECT booking_id
FROM bookings
WHERE during && tstzrange($1, $2, '[)');

CREATE INDEX places_location_idx
    ON places USING GIST (location);

SELECT place_id, name
FROM places
ORDER BY location <-> point '(77.59, 12.97)'
LIMIT 10;

Nearest-neighbor ordering is called a KNN-GiST search and depends on an operator class that declares a distance-ordering operator. An ordinary B-tree cannot answer multidimensional "closest" questions from independent latitude and longitude ordering.

Exclusion constraints

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings
ADD CONSTRAINT bookings_no_overlap
EXCLUDE USING GIST (
    room_id WITH =,
    during WITH &&
);

The constraint states that no two rows may have all listed comparisons true at once. Here, two bookings cannot share a room and overlap in time. The btree_gist extension supplies B-tree-like GiST behavior for the scalar room id so it can share one GiST structure with the range-overlap operator.

SP-GiST: space-partitioned search

SP-GiST supports non-balanced partitioning structures such as quadtrees, k-d trees and radix trees. Built-in operator classes cover selected point, range, network and text-prefix use cases. Like GiST, it can support nearest-neighbor search where the operator class provides distance ordering.

CREATE INDEX firewall_network_idx
    ON firewall_rules USING SPGIST (network inet_ops);

SELECT rule_id
FROM firewall_rules
WHERE network >>= inet '10.42.3.8';

07. BRIN block-range indexes

BRIN stores a compact summary for each range of adjacent heap pages. It is powerful when column values correlate with physical row location, especially on very large append-oriented tables.

How BRIN skips work

A minmax BRIN summary can record the smallest and largest timestamp in each block range. For a time-window query, PostgreSQL discards ranges whose summaries cannot overlap the requested window. It reads every candidate heap page and rechecks its rows, so BRIN scans are deliberately lossy.

CREATE INDEX events_created_brin_idx
    ON events USING BRIN (created_at)
    WITH (pages_per_range = 64, autosummarize = on);

SELECT COUNT(*)
FROM events
WHERE created_at >= TIMESTAMPTZ '2026-07-01 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2026-08-01 00:00:00+00';

Correlation is the critical variable. An append-only created_at often follows heap order. A randomly assigned customer id usually does not. Updates that scatter old and new values across the heap make each summary wider, causing more candidate pages and less skipping.

Summarization and inspection

Vacuum summarizes unsummarized BRIN ranges. The optional autosummarize setting asks autovacuum to summarize newly filled ranges. Operators can also call brin_summarize_new_values(). An unsummarized range must be treated as possibly matching, so it cannot be skipped.

SELECT attname, correlation
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'events'
  AND attname = 'created_at';

SELECT brin_summarize_new_values('events_created_brin_idx');

08. Composite indexes and column order

A composite index is one ordered structure over several keys. In a B-tree, key order determines which query prefixes define a narrow scan and which conditions are merely checked during it.

The leftmost-prefix rule, accurately stated

For a multicolumn B-tree, equality constraints on leading keys, plus an inequality on the first following key without equality, reliably limit the scanned portion. Conditions farther right can still be checked in the index and save heap visits, but they may not shrink the contiguous range read.

CREATE INDEX orders_tenant_status_time_idx
    ON orders (tenant_id, status, ordered_at DESC);

-- Excellent prefix: tenant equality, status equality, then time range.
WHERE tenant_id = 7
  AND status = 'paid'
  AND ordered_at >= $1

-- Useful prefix: tenant equality constrains a contiguous region.
WHERE tenant_id = 7

-- Usually cannot use the ordinary leading prefix efficiently.
WHERE ordered_at >= $1

PostgreSQL 18 can use B-tree skip scan in selected cases. For an index on (region, email) and a condition on email alone, the planner may repeatedly search one email value for each region. This is attractive only when the omitted leading column has few distinct values and most leaf pages can be skipped. Do not treat skip scan as permission to ignore deliberate key order.

A practical key-order method

1. Put columns required to isolate the access domain first, such as tenant_id in a shared multi-tenant table.
2. Within that domain, place frequently used equality keys before the main range key.
3. Choose directions that satisfy important mixed ORDER BY patterns.
4. Add a stable tiebreaker when keyset pagination needs deterministic ordering.
5. Use INCLUDE for returned payload that should not affect search ordering.
6. Verify the design against all important query shapes, not one example.

CREATE INDEX orders_tenant_feed_idx
    ON orders (tenant_id, ordered_at DESC, order_id DESC)
    INCLUDE (status, total);

SELECT order_id, ordered_at, status, total
FROM orders
WHERE tenant_id = $1
  AND (ordered_at, order_id) < ($2, $3)
ORDER BY ordered_at DESC, order_id DESC
LIMIT 50;

The "most selective column first" myth

Selectivity alone is not a universal ordering rule. If every important query constrains both leading keys with equality, either ordering can identify the same key combination. The meaningful differences are which single-key prefixes other queries need, where range predicates start, which ordering is required and whether tenant or security boundaries must always lead.

09. Combining indexes and satisfying order

PostgreSQL can combine indexes through bitmap operations, but one well-designed composite B-tree often wins when filtering and ordered output must happen together.

CREATE INDEX orders_customer_idx ON orders (customer_id);
CREATE INDEX orders_status_idx ON orders (status);

SELECT *
FROM orders
WHERE customer_id = 42
  AND status = 'paid';

The planner might use one index and filter, or bitmap-AND both. Bitmap combination is flexible, but it loses each index's ordering. If the workload repeatedly needs a customer's paid orders in newest-first order, (customer_id, status, ordered_at DESC) can filter and order in one scan.

Redundancy decisions require care. An index on (a, b) can often serve queries on a, so a separate index on a may be redundant. But different uniqueness, predicates, operator classes, sort orders, payload columns, access methods or workload behavior can make apparently overlapping indexes meaningfully different.

10. Partial indexes

A partial index contains only rows satisfying its predicate. It can be smaller, cheaper to update and more selective when queries repeatedly target a stable subset.

CREATE INDEX jobs_ready_idx
    ON jobs (priority DESC, run_at, job_id)
    WHERE status = 'ready';

SELECT job_id
FROM jobs
WHERE status = 'ready'
  AND run_at <= clock_timestamp()
ORDER BY priority DESC, run_at, job_id
LIMIT 100;

The planner can use a partial index only when it can prove at planning time that the query's conditions imply the index predicate. Exact textual matching is not always required, and simple mathematical implications can be recognized, but PostgreSQL is not a general theorem prover.

Parameterized-query trap

CREATE INDEX orders_unbilled_idx
    ON orders (ordered_at)
    WHERE billed IS NOT TRUE;

-- The fixed clause clearly implies the predicate.
SELECT * FROM orders
WHERE billed IS NOT TRUE
  AND ordered_at < $1;

-- A generic plan cannot assume what $1 will be.
SELECT * FROM orders
WHERE billed = $1
  AND ordered_at < $2;

The second query might execute with $1 = false, but a plan intended for arbitrary values cannot assume that. Keep the invariant subset literal in the query shape when a partial index depends on it, and confirm actual prepared-statement plans.

Conditional uniqueness

CREATE UNIQUE INDEX subscriptions_one_active_idx
    ON subscriptions (customer_id)
    WHERE ended_at IS NULL;

This permits historical ended rows while enforcing one current row. It is an index-backed uniqueness rule, not a normal SQL unique constraint. Foreign keys generally cannot reference a partial unique index because it does not prove uniqueness across every table row.

11. Covering indexes and INCLUDE

A covering index stores every column needed by a query. PostgreSQL may then use an index-only scan and avoid heap reads when MVCC visibility can be established from the visibility map.

CREATE INDEX orders_customer_cover_idx
    ON orders (customer_id, ordered_at DESC)
    INCLUDE (order_id, status, total);

SELECT order_id, ordered_at, status, total
FROM orders
WHERE customer_id = 42
ORDER BY ordered_at DESC
LIMIT 25;

customer_id and ordered_at are key columns: they guide search and order. The included columns are payload only. They do not change B-tree ordering and do not participate in uniqueness.

CREATE UNIQUE INDEX users_email_cover_idx
    ON users (lower(email))
    INCLUDE (user_id, display_name);

-- lower(email) must be unique.
-- user_id and display_name do not expand the unique key.

Why "covering" does not guarantee index-only

Index entries do not hold tuple visibility information. PostgreSQL checks the visibility-map bit for each relevant heap page. If the page is all-visible, the index entry can be returned without visiting the heap. If not, the executor fetches the heap tuple to check visibility.

• Frequently updated tables may have many pages that are not all-visible.
• Vacuum helps set all-visible bits after old tuple versions no longer matter.
• EXPLAIN (ANALYZE, BUFFERS) reports heap fetches for an index-only scan.
• A plan named Index Only Scan can still perform heap fetches.
• Wide included values make every matching index entry larger and can exceed tuple limits.

12. Expression indexes, operator classes and collations

An expression index stores a computed key. It works when the query uses a compatible expression and the functions involved satisfy PostgreSQL's immutability requirements.

Case-insensitive lookup and uniqueness

CREATE UNIQUE INDEX users_email_lower_uidx
    ON users (lower(email));

SELECT user_id
FROM users
WHERE lower(email) = lower($1);

The stored result is computed during inserts and non-HOT updates, not during each index search. That moves CPU work from reads to writes and consumes index space. The query must present an expression the planner can match. An index on lower(email) does not accelerate WHERE email = $1.

Immutability and stable meaning

Index expressions and partial-index predicates may use only immutable functions. PostgreSQL must trust that the same input always maps to the same indexed result. Session settings, time zones, current time and mutable lookup tables can violate that contract.

CREATE INDEX measurements_rounded_idx
    ON measurements ((round(value, 2)));

SELECT measurement_id
FROM measurements
WHERE round(value, 2) = 19.95;

Casts and implicit conversions also matter. If an application compares a text expression to a differently typed parameter, PostgreSQL may introduce a cast on the indexed side or choose an operator that is outside the index's operator class. Bind parameters with the intended database type and inspect the actual plan.

Operator classes and families

An operator class tells an access method how a data type's operators map to index behavior. One type can have several classes for different semantics, such as default JSONB GIN behavior versus jsonb_path_ops, or locale ordering versus text prefix matching.

SELECT am.amname, opc.opcname, opc.opcintype::regtype
FROM pg_opclass AS opc
JOIN pg_am AS am ON am.oid = opc.opcmethod
WHERE opc.opcname IN (
    'text_ops',
    'text_pattern_ops',
    'jsonb_ops',
    'jsonb_path_ops'
)
ORDER BY am.amname, opc.opcname;

Collation must match the query

A collatable index key uses a declared collation. A query with a different explicit collation cannot generally use that index for the incompatible ordering or comparison semantics. If an application needs multiple collation-specific orderings, it may need multiple indexes, each with a measured benefit.

CREATE INDEX customers_name_c_idx
    ON customers (full_name COLLATE "C");

SELECT customer_id, full_name
FROM customers
ORDER BY full_name COLLATE "C"
LIMIT 50;

13. Why PostgreSQL ignores an index

PostgreSQL does not prefer indexes by rule. The planner estimates the total cost of legal plans and chooses the cheapest. A sequential scan can be the correct plan even when an index exists.

1. Too many rows qualify

An index scan may perform many scattered heap reads. When a predicate returns a large fraction of the table, reading heap pages sequentially and filtering can cost less. Low-cardinality does not make a column unindexable, but a common value often has weak selectivity.

CREATE INDEX users_active_idx ON users (active);

-- If 99 percent of users are active, the index may add work.
SELECT *
FROM users
WHERE active = true;

2. The table is small

If the entire table fits in a few pages, a sequential scan is simple and cheap. This is why tiny development fixtures often produce different plans from production-shaped data. Test with representative volume and distribution.

3. The predicate does not match the indexed expression

CREATE INDEX orders_ordered_at_idx ON orders (ordered_at);

-- The plain timestamp index does not directly search this derived date.
WHERE ordered_at::date = DATE '2026-07-23'

-- A half-open range uses the plain timestamp index and handles all instants that day.
WHERE ordered_at >= TIMESTAMPTZ '2026-07-23 00:00:00+00'
  AND ordered_at <  TIMESTAMPTZ '2026-07-24 00:00:00+00'

Alternatively, create an expression index if the precise derived expression is stable and common. Prefer half-open ranges for timestamps because they avoid fragile "end of day" values and align with ordinary ordered keys.

4. The operator or operator class is unsupported

• A B-tree cannot accelerate LIKE '%middle%' with its ordinary scalar ordering.
• A Hash index cannot support >, BETWEEN or ordering.
• A JSONB jsonb_path_ops index does not support the key-existence ? operator.
• A collation or data-type mismatch can select incompatible comparison semantics.
• A custom operator needs membership in a suitable operator family to use that index path.

5. Composite key order does not fit

A condition on a trailing B-tree key without useful leading constraints may require reading most of the index. Skip scan can help only for favorable distinct counts. Add a correctly ordered index when the trailing-only query is important enough to justify it.

6. A partial predicate cannot be proven

If the query omits the index predicate, phrases it incompatibly, or hides the required value behind a generic parameter, the planner cannot assume that every needed row is in the partial index. The index is legally unusable, not merely expensive.

7. Statistics are stale or insufficient

The planner needs row-count, null-fraction, distinct-value, most-common-value and histogram estimates. If data changed substantially or important correlations are invisible, it can misestimate selectivity and compare the wrong costs.

ANALYZE orders;

ALTER TABLE orders
ALTER COLUMN status SET STATISTICS 500;

CREATE STATISTICS orders_tenant_status_stats (dependencies, mcv)
ON tenant_id, status
FROM orders;

ANALYZE orders;

A higher per-column target stores more detailed statistics at added analysis and catalog cost. Extended statistics help the planner estimate related columns, but they do not create an access path. An index and statistics solve different problems.

8. Cost settings or cache assumptions differ from reality

Planner costs model sequential I/O, random I/O, CPU and expected caching. Lowering random_page_cost relative to seq_page_cost makes random index access look cheaper. effective_cache_size is an estimate used by the planner, not allocated memory.

9. Prepared plans and parameter values differ

A custom plan can use a supplied value's selectivity. A generic prepared plan must be safe and reasonable across possible values. Skewed predicates can therefore use different plans in a literal query and a long-lived prepared statement. Inspect the plan through the same driver and parameter types used by the application.

10. Another plan is simply better

PostgreSQL may prefer a different index, bitmap combination, join-driven lookup, partition pruning or sequential scan. An index being unused in one plan does not make it broken. Compare total execution behavior, not whether a preferred index name appears.

A disciplined diagnostic workflow

1. Capture the exact SQL, bound parameter types and representative values.
2. Confirm the index is valid, ready and defined on the expected table or partition.
3. Write the index keys, predicate, expressions, operator classes and collation explicitly.
4. Check whether the query is legally compatible with those definitions.
5. Run ANALYZE if statistics do not reflect current data.
6. Use EXPLAIN (ANALYZE, BUFFERS) safely on a representative environment.
7. Compare estimated versus actual rows at the earliest divergence.
8. Measure the current plan and a plausible alternative, including buffers and repeated runs.
9. Change query, index or statistics for a concrete reason, then measure again.
10. Remove experimental indexes that do not improve the target workload.

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'orders'
ORDER BY indexname;

SELECT
    i.relname AS index_name,
    x.indisvalid,
    x.indisready,
    pg_size_pretty(pg_relation_size(i.oid)) AS size
FROM pg_index AS x
JOIN pg_class AS i ON i.oid = x.indexrelid
WHERE x.indrelid = 'public.orders'::regclass
ORDER BY i.relname;

14. Production-safe index lifecycle

Building, monitoring and removing an index are operational changes. Treat lock duration, write amplification, failure cleanup and replica impact as part of the design.

Regular versus concurrent builds

CREATE INDEX CONCURRENTLY orders_customer_time_idx
    ON orders (customer_id, ordered_at DESC);

A concurrent build performs multiple phases and waits for transactions that could affect correctness. If it fails, it can leave an INVALID index that still consumes space and may add update overhead. Inspect the failure, then drop or rebuild the exact invalid index safely. Only one concurrent index build can run on a table at a time.

CREATE INDEX CONCURRENTLY has extra restrictions for partitioned tables. A common operational pattern is to build indexes concurrently on individual partitions and then create or attach the matching partitioned index metadata. Verify the procedure for the exact supported PostgreSQL version.

Rebuild and removal

REINDEX INDEX CONCURRENTLY orders_customer_time_idx;

DROP INDEX CONCURRENTLY orders_old_idx;

Concurrent variants reduce blocking but take longer and have command restrictions. A standard REINDEX or drop can be appropriate in maintenance windows. Never remove an index only because its scan counter is zero in a short observation window.

Usage and size monitoring

SELECT
    s.schemaname,
    s.relname AS table_name,
    s.indexrelname AS index_name,
    s.idx_scan,
    s.idx_tup_read,
    s.idx_tup_fetch,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes AS s
ORDER BY pg_relation_size(s.indexrelid) DESC;

Statistics counters are cumulative since their reset and can be zero after restart, reset or failover. They also do not show every benefit: uniqueness enforcement, replica queries and rare period-end jobs can justify an index. Combine counters with query statistics, schema semantics and a full business workload cycle.

Vacuum, bloat and HOT

PostgreSQL updates create new tuple versions. Indexes accumulate references that vacuum can remove only when no relevant snapshot needs the old versions. Long transactions can delay cleanup. Repeated changes can produce table and index bloat when reusable space does not match future inserts.

A heap-only tuple, or HOT, update can avoid creating new index entries when no indexed column changes and the new tuple version fits on the same heap page. Adding an indexed or included column to a frequently updated attribute can remove this benefit. This is an important hidden cost of over-covering indexes.

• Keep autovacuum healthy instead of scheduling reindexing as a substitute for vacuum.
• Investigate long transactions and write patterns before treating bloat symptoms.
• Use measured bloat tools and relation sizes, not a universal rebuild schedule.
• Remember that VACUUM FULL rewrites and locks a table; it is not routine vacuum.

15. Design patterns, mistakes and performance review

Useful index patterns

Common mistakes

Index review checklist

1. List the exact high-value queries, parameter types and workload frequency.
2. Mark equality, range, join, ordering, grouping and returned columns.
3. Inventory constraint-owned and manually created indexes.
4. Check access method, operator class, collation, predicate and expression compatibility.
5. Choose composite order from reusable prefixes and order requirements.
6. Estimate index size and impact on the table's write path.
7. Test representative data with warm and cold-cache awareness.
8. Inspect estimated versus actual rows, buffers, heap fetches, rechecks and sort nodes.
9. Deploy with a lock and failure plan appropriate to table size and traffic.
10. Monitor the complete workload and remove only proven redundancy.

16. Practice problems

17. Interview Q&A

18. One-screen cheat sheet

• Start with: exact predicate, order, output and workload frequency.
• B-tree: equality, range, null tests, ordering and uniqueness.
• Hash: equality only; measure against B-tree.
• GIN: component-to-row lookup for arrays, JSONB and full-text search.
• GiST: extensible ranges, geometry, overlap and nearest neighbor.
• SP-GiST: partitioned spaces such as points, prefixes and networks.
• BRIN: tiny lossy summaries for huge physically correlated tables.
• Composite B-tree: leading equalities, then main range and order keys.
• Skip scan: possible with few distinct omitted-prefix values, not guaranteed.
• Partial: query must imply the fixed index predicate at planning time.
• INCLUDE: payload only; no search, ordering or uniqueness semantics.
• Index-only: stored columns plus favorable visibility map; inspect heap fetches.
• Expression: query must match an immutable stored expression.
• Operator class: connects operators and data type to access method.
• Ignored index: may be incompatible, too costly or based on bad estimates.
• Low selectivity: many heap visits often favor a sequential scan.
• Statistics: estimate rows; indexes provide access paths.
• Bitmap scan: combines locations and groups heap access, but loses order.
• CONCURRENTLY: reduces write blocking but adds time, work and restrictions.
• Every index: costs writes, WAL, storage, cache and maintenance.

19. Official reference map

• PostgreSQL index concepts
• B-tree, Hash, GiST, SP-GiST, GIN and BRIN overview
• Multicolumn indexes and B-tree skip scan
• B-tree indexes and ORDER BY
• Combining multiple indexes
• Indexes on expressions
• Partial indexes
• Index-only scans and covering indexes
• Operator classes and operator families
• Indexes and collations
• Examining index usage
• CREATE INDEX and concurrent builds
• GIN implementation and behavior
• GiST indexes
• SP-GiST indexes
• BRIN indexes
• Planner method and cost settings
• Planner statistics and extended statistics
• Index usage statistics views