Indexing Strategy Language — Database & SQL Exercises

1 / 5

A DBA says: "B-tree indexes support equality and range queries on sortable types." A new column description TEXT is proposed for full-text search. Which query type would NOT benefit from a standard B-tree index on description?

-- Query A: WHERE description = 'exact match'
-- Query B: WHERE description LIKE 'prefix%'
-- Query C: WHERE description LIKE '%substring%'
-- Query D: WHERE description > 'alpha' AND description < 'beta'

B-tree index limitations vocabulary:

Option D is correct. A B-tree index stores values in sorted order, which enables equality lookups, prefix scans (LIKE 'prefix%'), and range queries. However, a leading wildcard pattern (LIKE '%substring%') cannot use the B-tree because there is no sorted starting point — the matching substring could appear anywhere in the value.

Query pattern	B-tree usable?	Why
`= 'value'`	Yes	Equality — direct lookup in sorted tree
`LIKE 'prefix%'`	Yes (with text_pattern_ops)	Prefix — B-tree can seek to the start of the prefix
`LIKE '%substring%'`	No	Leading wildcard — no sorted traversal entry point
`> 'a' AND < 'z'`	Yes	Range scan — B-tree traversal between two sorted bounds

Alternatives for substring/full-text search:

GIN index with to_tsvector — PostgreSQL full-text search; enables @@ match operator
pg_trgm extension + GIN/GiST index — enables fast LIKE '%substring%' and fuzzy matching
Dedicated search engine — Elasticsearch, OpenSearch for complex search requirements

2 / 5

A DBA proposes the following index during a performance review: "We should index only the active rows to avoid indexing 90% of the archived records." What is this index type called, and what is its primary maintenance advantage?

CREATE INDEX idx_orders_active ON orders (created_at)
WHERE status = 'active';

Partial index vocabulary:

Option B is the correct definition. A partial index is defined by a WHERE clause that limits which rows are included in the index. Only rows satisfying the predicate are indexed.

Why partial indexes matter in production:

Aspect	Full-table index	Partial index (WHERE status='active')
Index size	Indexes all N rows	Indexes only active rows (e.g., 10% of N)
Write overhead	Updated on every INSERT/UPDATE/DELETE	Updated only when predicate rows change
Query selectivity	Planner chooses based on statistics	Very selective for active-row queries
Planner usage	Used for any query on the column	Only used when WHERE matches the predicate

Practical comment for schema reviews: "Since 90% of orders are archived and we only query active orders, a partial index on WHERE status = 'active' will be roughly 10x smaller than a full index, update faster, and fit more easily in the buffer pool."

3 / 5

A senior engineer explains an index in a code review: "The composite index (last_name, first_name) will be used by queries filtering by last_name alone, but not by queries filtering only by first_name." What is this property called, and why does it apply to B-tree composite indexes?

Leftmost prefix rule vocabulary:

Option C is correct. A composite B-tree index sorts entries first by the first column, then by the second column within ties, etc. — like a phone book sorted by last name, then first name. A query filtering only by first_name has no sorted entry point in this structure.

Query predicate	Uses index (last_name, first_name)?	Why
WHERE last_name = 'Smith'	Yes	Leftmost column — can seek to sorted position
WHERE last_name = 'Smith' AND first_name = 'Alice'	Yes	Both columns — full composite lookup
WHERE first_name = 'Alice'	No	Skips leftmost column — no sorted starting point

Practical implications for index design discussions:

Column order in a composite index should match the most common query patterns.
If queries filter by first_name alone frequently, add a separate single-column index on first_name.
A three-column index (A, B, C) supports queries on A; A+B; and A+B+C — but not B alone, C alone, or B+C.
PostgreSQL can sometimes use a composite index with a range skip scan, but this is not guaranteed — always verify with EXPLAIN.

4 / 5

An engineer says during a schema review: "We added salary to the users index so the planner doesn't need to visit the heap for our reporting query." What is a covering index and what read path does it eliminate?

CREATE INDEX idx_users_dept_salary ON users (department_id)
INCLUDE (salary, full_name);

Covering index vocabulary:

Option A is the precise definition. A covering index stores all columns the query needs — both filter columns and SELECT column projections — within the index structure itself, so the query engine never needs to read from the heap (the main table storage).

INCLUDE clause vocabulary (PostgreSQL 11+):

Part of the index definition	Role
Key columns (before INCLUDE)	Stored in all B-tree levels; used for sorting and lookup
INCLUDE columns (after INCLUDE)	Stored only in leaf pages; not used for sorting; satisfy projections

Example:

-- Without covering index: Index Scan + heap fetch for salary
SELECT department_id, salary, full_name FROM users WHERE department_id = 5;

-- With INCLUDE (salary, full_name): Index Only Scan — no heap fetch
-- CREATE INDEX idx_users_dept_salary ON users (department_id) INCLUDE (salary, full_name);

Trade-off to mention in reviews: Covering indexes are larger because payload columns are duplicated from the table into the index. They increase write overhead for updates to the included columns. Use them selectively for hot, frequently-executed queries where the heap fetch is the dominant cost.

5 / 5

A junior engineer argues: "We should add a B-tree index on every foreign key column — it'll speed up all the JOIN operations." Which response best explains the trade-off of excessive indexing?

Indexing trade-off vocabulary:

Option C gives the accurate and complete trade-off explanation. Every index on a table is a secondary data structure that must be kept in sync with the table's data on every write operation.

Operation	Effect of each additional index
INSERT	New entry added to every index — N index writes per 1 table write
UPDATE (indexed column)	Old entry removed, new entry inserted in affected indexes
DELETE	Entry removed from every index
Autovacuum / VACUUM	Must clean dead entries in every index — vacuum time grows with index count
Storage	Each index occupies additional disk space

How to push back professionally in a review:
"Indexing every foreign key is a reasonable starting heuristic, but it isn't free. Let's add indexes only where we have a measured query that does a JOIN or lookup on that FK column. On our orders table (high insert/update volume), unnecessary indexes will slow down every write. I'd suggest starting with the FKs hit by our top-10 slow queries and measuring before indexing the rest."