Title: Postgresql Indexing Strategies — LearnCoachAssist

Q: What is an index in PostgreSQL?
A: A secondary data structure that lets the planner find rows matching a predicate without scanning the whole table, at the cost of extra storage and write overhead.

Q: What is a B-tree index in PostgreSQL?
A: The default index type, a balanced tree that keeps keys sorted and supports equality and range predicates, plus IS NULL, IS NOT NULL, and ORDER BY traversal in index order.

Q: Which index access method is the default in PostgreSQL?
A: btree.

Q: What index types does PostgreSQL ship with for general use?
A: B-tree, Hash, GIN, GiST, SP-GiST, BRIN, and the extension-provided Bloom (and others like zombodb, rum, pg_trgm).

Q: What is the main trade-off of any index?
A: It speeds up reads (selections, joins, sorts) at the cost of additional storage, slower writes (INSERT/UPDATE/DELETE), and extra work for VACUUM.

Q: How do you create a B-tree index on a single column?
A: CREATE INDEX idx_users_email ON users (email);

Q: How do you create a unique B-tree index?
A: CREATE UNIQUE INDEX idx_users_email ON users (email); — Postgres automatically creates one when you declare UNIQUE or PRIMARY KEY.

Q: How do you create a composite (multi-column) B-tree index?
A: CREATE INDEX idx_o_cust_date ON orders (customer_id, order_date);

Q: Why does column order matter in a composite B-tree index?
A: Because the keys are sorted lexicographically; the index can only be used for predicates on a leading prefix of the columns.

Q: For which predicates can a composite index on (a, b, c) be used?
A: Equality on a; equality on a + range on b; equality on a, b, c in that order. It cannot serve a query that filters only on b or c.

Q: What is index-only scan?
A: A plan where the planner reads everything it needs from the index itself (no heap fetch) because the visibility map shows the page is all-visible and all required columns are in the index.

Q: When is index-only scan possible?
A: When the query references only indexed columns (including those covered by INCLUDE) and the corresponding heap pages are marked all-visible by VACUUM.

Q: What is the visibility map?
A: A bitmap that marks heap pages whose tuples are visible to all current and future transactions, enabling index-only scans and speeding up vacuum.

Q: How do you make an index cover extra columns without adding them to the key?
A: CREATE INDEX idx ON t (a) INCLUDE (b, c); — INCLUDE columns are stored in leaf pages only and are not part of the key, so uniqueness does not apply to them.

Q: What is a partial index?
A: An index built over a subset of rows defined by a WHERE clause, useful for sparse predicates like WHERE deleted_at IS NULL.

Q: How do you create a partial index?
A: CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL;

Q: Why are partial indexes often smaller and faster?
A: They store fewer rows, fit in fewer pages, and require fewer updates on writes, so the planner is more likely to choose them for the matching query.

Q: What is an expression index (functional index)?
A: An index built on the result of an expression, e.g. CREATE INDEX idx ON t (lower(email));.

Q: When do you need an expression index?
A: When the query uses the same expression in WHERE, JOIN, or ORDER BY — the index is invisible to the planner otherwise.

Q: What is a Hash index?
A: A simple index type that supports only equality (=) comparisons, using a hash of the key.

Q: What is a key advantage of Hash indexes in PostgreSQL 10+?
A: They are write-ahead-logged (crash-safe) and are sometimes picked by the planner because they can be slightly smaller than equivalent B-trees for equality-only workloads.

Q: When is a Hash index preferable to a B-tree?
A: For large tables with = lookups on wide keys (e.g., long strings, UUIDs), where B-tree comparison cost dominates.

Q: What does GIN stand for, and what is it for?
A: Generalized Inverted Index; designed for values that contain many elements of another type, like arrays, JSONB, full-text tsvector, and trigrams.

Q: What operator classes are commonly used with GIN?
A: jsonb_ops, jsonb_path_ops, array_ops, tsvector_ops, and gin_trgm_ops (from pg_trgm).

Q: What is the difference between jsonb_ops and jsonb_path_ops?
A: jsonb_ops indexes every key and value, supports ?, ?|, ?&, and containment @>. jsonb_path_ops indexes only paths to values, supports only @>, but is smaller and faster for containment queries.

Q: How do you create a GIN index on a JSONB column?
A: CREATE INDEX idx_gin ON docs USING GIN (data jsonb_path_ops);

Q: What is GiST?
A: Generalized Search Tree; a balanced tree that supports many different search strategies (geometry, range types, full-text, exclusion constraints).

Q: When would you use GiST over GIN?
A: For nearest-neighbor (<->) searches, range overlap (&&), and data types that support only GiST (e.g., box, circle, tsquery in some forms).

Q: What is SP-GiST?
A: Space-Partitioned GiST; for non-balanced data such as quadtrees, kd-trees, and radix trees; useful for phone numbers, IP addresses (with inet), and geometric point data.

Q: What is BRIN?
A: Block Range INdex; stores a summary of values (min/max, optionally other aggregates) for each range of heap pages, making it tiny in size.

Q: When is a BRIN index appropriate?
A: On very large tables with data that is naturally correlated with physical location — e.g., append-only time-series with monotonically increasing timestamps.

Q: How do you create a BRIN index?
A: CREATE INDEX idx_logs_ts ON logs USING BRIN (created_at) WITH (pages_per_range = 32);

Q: What is the pages_per_range BRIN parameter?
A: The number of heap pages summarized in each BRIN block range. Smaller values = more accurate index, larger index size; larger values = more compact but coarser.

Q: Why is BRIN often dramatically smaller than B-tree?
A: Because it stores one summary tuple per range of pages (default 128 pages), not one per row.

Q: When is BRIN a poor choice?
A: On tables with random inserts/updates — the correlation between physical and logical order breaks, summaries become wide, and selectivity drops.

Q: What is a Bloom filter index (extension bloom)?
A: An extension index that supports many equality predicates across multiple columns in a compact form, useful for star-schema fact tables where any combination of columns may be queried.

Q: How do you enable the Bloom extension?
A: CREATE EXTENSION bloom;

Q: What is pg_trgm?
A: An extension that adds trigram (3-character substring) similarity functions and GiST/GIN operator classes for fast LIKE, ILIKE, and % (%> similarity) searches on text.

Q: How do you create a trigram index?
A: CREATE INDEX idx ON articles USING GIN (title gin_trgm_ops);

Q: What index supports ILIKE 'foo%' efficiently?
A: A B-tree on a text_pattern_ops or bpchar_pattern_ops opclass, or a GIN with gin_trgm_ops for the ILIKE '%foo%' pattern.

Q: What is text_pattern_ops for?
A: An operator class that lets B-tree indexes support LIKE and ILIKE with a non-C-collation locale (e.g., C collation cannot use a regular B-tree for prefix LIKE).

Q: What is a covering index?
A: An index that contains all columns referenced by a query so it can be served by an index-only scan. Created with INCLUDE.

Q: What is index bloat?
A: Unused or sparse space in an index caused by MVCC, page splits, and dead tuples; it wastes space and slows scans.

Q: How do you detect bloat in indexes?
A: Use the contrib module pgstattuple or pgstatindex, or query pg_stats/pg_class for ratios of expected vs. actual size.

Q: How do you rebuild a bloated index?
A: REINDEX INDEX CONCURRENTLY idx_name; (the CONCURRENTLY option avoids an ACCESS EXCLUSIVE lock).

Q: What does CREATE INDEX CONCURRENTLY do?
A: Builds the index without locking the table against writes; takes longer and can leave an invalid index if it fails, which must be cleaned up manually.

Q: How do you drop an index without locking reads?
A: DROP INDEX CONCURRENTLY idx_name;

Q: What is the main cost of indexes on writes?
A: Every INSERT/UPDATE/DELETE that affects indexed columns must update each relevant index, causing additional WAL volume and random I/O.

Q: What is a fillfactor?
A: A storage parameter that leaves free space on each index (or heap) page, leaving room for in-place updates and reducing page splits.

Q: How do you create a B-tree with fillfactor 70?
A: CREATE INDEX idx ON t (col) WITH (fillfactor = 70);

Q: What is the default fillfactor for B-tree indexes?
A: 90.

Q: How does the planner decide which index to use?
A: It estimates the cost of each candidate plan using statistics (pg_stats), and chooses the lowest-cost plan; sometimes it correctly picks a sequential scan instead of an index.

Q: How do you see which index a query uses?
A: EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Q: What does the Recheck Cond line in a Bitmap Heap Scan mean?
A: The bitmap was lossy (one bit per page) and PostgreSQL must re-evaluate the predicate against the heap tuples fetched.

Q: What is a Bitmap Index Scan?
A: A plan node that builds a bitmap of pages matching an index condition, often combined (BitmapAnd/BitmapOr) with results from several indexes, then visited by a Bitmap Heap Scan.

Q: Why might a query with multiple predicates not use a multi-column index?
A: The planner estimates a BitmapOr/And of single-column indexes is cheaper, or it mis-estimates selectivity; ANALYZE and accurate pg_stats often help.

Q: What is ANALYZE?
A: A maintenance command that updates the table's statistics in pg_statistic/pg_stats for the planner.

Q: When does the planner run ANALYZE automatically?
A: During autovacuum after a configurable fraction of rows has changed, and on bulk loads with COPY/INSERT in some cases.

Q: How can stale statistics mislead the planner?
A: Out-of-date row counts or histograms cause wrong cardinality estimates, which can lead to bad index choices, bad join orders, and bad memory sizing.

Q: How do you tune per-column statistics?
A: ALTER TABLE t ALTER COLUMN c SET STATISTICS 1000; then ANALYZE t;.

Q: What is a functional dependency in pg_stats?
A: A correlation between two columns that helps the planner when both appear in WHERE or one is a GROUP BY key.

Q: How do you check whether an index is being used?
A: Query pg_stat_user_indexes; the idx_scan counter shows cumulative index scans since the last reset.

Q: What is a duplicate (redundant) index?
A: An index whose key set is a prefix of (or functionally the same as) another index on the same table; it adds write cost with no extra selectivity.

Q: How can you find unused indexes?
A: SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

Q: What is an index on a foreign key column used for?
A: It speeds up referential integrity checks during INSERT/UPDATE/DELETE on the referenced table, and enables efficient joins on the FK column.

Q: Why does PostgreSQL not automatically create indexes on foreign keys?
A: To avoid the write cost; the DBA must add them if the workload includes heavy ON DELETE CASCADE or joins on the FK.

Q: What is the purpose of an exclusion constraint?
A: To prevent overlapping or conflicting rows using a GiST index, e.g., preventing room booking overlaps with EXCLUDE USING gist (room_id WITH =, during WITH &&).

Q: What is a unique index used for besides uniqueness?
A: It is also the mechanism behind ON CONFLICT (col) DO UPDATE upserts, enabling INSERT ... ON CONFLICT to find conflicts efficiently.

Q: How does INSERT ... ON CONFLICT DO NOTHING work?
A: Postgres looks up the row via the specified unique index (or primary key) and skips inserting if a conflict is found; without a unique index, the command cannot be used.

Q: What is a primary key?
A: A combination of NOT NULL plus a UNIQUE index; Postgres uses the underlying B-tree index for lookups and joins.

Q: What is a clustered index in PostgreSQL?
A: PostgreSQL does not have true clustered indexes (unlike MySQL InnoDB); the heap is unsorted. The CLUSTER command reorders the heap physically to match an index once, but is not maintained on subsequent inserts.

Q: What is a ctid?
A: A system column giving the physical location (block, item) of a row in the heap; it changes on UPDATE, so it is not useful as a long-term key.

Q: What is an INCLUDE clause's main benefit?
A: It allows index-only scans by storing extra columns in leaf pages without enlarging the search key, so they do not affect ordering or uniqueness.

Q: How many columns can a B-tree index have?
A: Up to 32 columns by default; the limit is controlled by the compile-time constant INDEX_MAX_KEYS.

Q: What is the maximum size of an indexed value?
A: B-tree entries must fit in a single page (8 KB by default) after TOAST compression; very long values are not indexable as keys but can appear in INCLUDE.

Q: What is TOAST?
A: The Oversized Attribute Storage Technique; long field values are stored out-of-line in a separate TOAST table, and indexed keys are compressed before insertion into the index.

Q: How do you inspect a query plan with a focus on indexes?
A: EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;

Q: What is a Nested Loop and when does it use indexes?
A: A join that, for each row of the outer side, probes the inner side using an index; preferred when the outer side is small or the inner side has a selective index.

Q: What is a Hash Join?
A: A join that builds a hash on the inner side and probes with the outer; preferred for large unsorted equi-joins; does not use an index directly.

Q: What is a Merge Join?
A: A join that merges two pre-sorted inputs; often uses B-tree indexes to provide the sorted inputs cheaply.

Q: What is a CTE and how does it interact with indexes?
A: A WITH query (CTE) is inlined in older versions; in PostgreSQL 12+ non-MATERIALIZED CTEs are inlined and can use indexes; MATERIALIZED CTEs are computed once into a work table with no indexes by default.

Q: What is a Bitmap Heap Scan?
A: A node that fetches heap tuples in the order requested by an index bitmap, applying the original predicate as a recheck when the bitmap is lossy.

Q: What is a lossy bitmap?
A: When a single column bitmap exceeds work_mem, PostgreSQL switches to a lossy representation with one bit per page; selectivity drops and recheck is required.

Q: How does increasing work_mem help bitmap plans?
A: More memory allows non-lossy bitmaps, which skip the recheck and can be faster.

Q: What is random_page_cost?
A: A planner parameter that estimates the cost of an index scan; lowering it (e.g., to 1.1) makes the planner favor indexes on SSD storage.

Q: What is seq_page_cost?
A: A planner parameter that estimates the cost of reading a heap page sequentially; defaults to 1.0.

Q: What is effective_cache_size?
A: A hint to the planner about the OS and shared buffer cache size; affects whether index scans vs. seq scans look attractive; does not allocate memory.

Q: What is maintenance_work_mem?
A: Memory used for VACUUM, index creation, and ANALYZE; larger values speed up CREATE INDEX by reducing sort spill.

Q: How do you force the planner to use (or not use) an index?
A: You cannot directly, but you can disable plan types with SET enable_indexscan = off; or disable specific joins with enable_* GUCs for testing only.

Q: What is EXPLAIN (FORMAT JSON) useful for?
A: Machine-readable plan output for tools like pg_plan_guarantee, pev2, or auto_explain analysis.

Q: What is auto_explain?
A: A contrib module that logs plans of slow queries automatically using log_min_duration.

Q: How do you limit how deep Postgres logs plan details?
A: auto_explain.log_nested_statements and auto_explain.sample_rate.

Q: What is a partial index on a small set of distinct values called?
A: Often a "low-cardinality partial index" — useful for hot rows like WHERE status = 'pending' when most rows are status = 'done'.

Q: How do you create a UNIQUE index on a partial set?
A: CREATE UNIQUE INDEX idx ON t (email) WHERE deleted_at IS NULL; — duplicates can exist among the excluded rows but not among the indexed ones.

Q: What is a multicolumn index and a single-column index on the same table called together?
A: Overlapping indexes; generally you keep the one that best matches query patterns and drop the others to save write cost.

Q: What is a covering index used for OLTP vs. reporting?
A: For OLTP it usually avoids a heap fetch for small projections (e.g., SELECT id, status FROM orders WHERE customer_id = ?). For reporting, it lets the planner serve star-schema queries entirely from the index.

Q: What is the maximum key size for a B-tree index?
A: About 1/3 of a page (≈ 2.7 KB on 8 KB pages), because the upper tree pages must fit many entries; the planner logs "index row size X exceeds btree version 4 maximum" otherwise.

Q: How do you safely add an index on a large production table?
A: Use CREATE INDEX CONCURRENTLY to avoid blocking writes; monitor for the INVALID state and retry or drop the failed index.

Q: What is the difference between REINDEX and REINDEX CONCURRENTLY?
A: Plain REINDEX takes an ACCESS EXCLUSIVE lock and is faster; REINDEX CONCURRENTLY rebuilds the index in place without blocking writes (Postgres 12+).

Q: What is a tablespace in index context?
A: A location on disk where index (or table) files are stored; you can put indexes on fast storage (e.g., SSD) via CREATE INDEX ... TABLESPACE fast_ssd;.

Q: How do you move an existing index to another tablespace?
A: ALTER INDEX idx SET TABLESPACE fast_ssd;

Q: What is a parallel index scan?
A: Postgres can build a B-tree index using multiple background workers since version 11, controlled by max_parallel_maintenance_workers.

Q: How do you increase parallelism for index creation?
A: SET max_parallel_maintenance_workers = 4; before CREATE INDEX on a large table.

Q: What is CREATE INDEX ... WITH (deduplicate_items = on)?
A: A B-tree option (default on since PG 13) that stores only one copy of equal adjacent key values, saving space when many rows share the same key.

Q: What is the size of a B-tree entry's null bitmap?
A: Null columns are stored in the index key with a bit in the entry's null bitmap; the index is not "skipped" for NULL values.

Q: Can you index NULL values?
A: Yes — B-tree indexes include NULL values, and you can filter IS NULL/IS NOT NULL with a B-tree.

Q: What is a HASH index's main weakness vs. B-tree?
A: Hash indexes do not support range queries or ordering, and historically they were not WAL-logged (fixed in PG 10).

Q: What is the GIN "fastupdate" option?
A: Enables storing pending list updates in an unordered list for faster inserts at the cost of slower lookups until VACUUM flushes; default on.

Q: What is VACUUM's role for GIN indexes?
A: It removes deleted tuples from the pending list, ensuring the index stays compact and queries remain fast.

Q: How do you disable fastupdate for a GIN index?
A: ALTER INDEX idx SET (fastupdate = off);

Q: What is a GIN jsonb_path_ops index's main limitation?
A: It supports only the containment operator @> and not the existence operators ?, ?|, ?&.

Q: How do you create a GIN index with the default jsonb_ops?
A: CREATE INDEX idx ON docs USING GIN (data); — Postgres picks the default opclass for the type.

Q: What is the tsvector data type?
A: A normalized lexeme representation of text used for full-text search; to_tsvector('english', text) produces it.

Q: How do you index tsvector for full-text search?
A: CREATE INDEX idx ON articles USING GIN (to_tsvector('english', body));

Q: What is a tsquery index use case?
A: Queries like body @@ to_tsquery('english', 'postgres & indexing') are accelerated by the GIN index on the tsvector expression.

Q: How do you add a new column to an index?
A: You cannot; you must DROP and re-CREATE, or use REINDEX CONCURRENTLY to a new definition.

Q: What is the CONCURRENTLY option's cost?
A: Indexes built with CONCURRENTLY take roughly 2-3x longer and can fail, leaving an INVALID index that must be dropped or recreated.

Q: How can you tell whether an index is valid?
A: SELECT indisvalid, indisready FROM pg_index WHERE indexrelid = 'idx_name'::regclass;

Q: What is the difference between indisvalid and indisready?
A: indisready means the index is ready for updates; indisvalid means it is ready to be used by queries. A CONCURRENTLY-built index becomes ready first, then valid.

Q: What is an index advisor?
A: Tools like pg_qualstats and pg_hint_plan, or EXPLAIN analysis, can suggest missing indexes from a workload.