Database Indexing Interview Quiz

Q: How many questions are in the Database Indexing quiz?

There are 30 questions in total: 10 Beginner, 10 Intermediate, and 10 Advanced, randomized every run.

Q: Which database engines do these concepts apply to?

The concepts apply to standard relational databases such as PostgreSQL, MySQL, and SQL Server, with code examples emphasizing PostgreSQL.

Q: Do I need to create an account?

No, the tool runs entirely client-side without registration.

Question 1

What is a database index?

Accepted Answer

A data structure that accelerates lookups at the cost of write overhead

Answer

A compressed snapshot backup stored on secondary tape media

Answer

A security constraint that hashes column values against injection attacks

Answer

A temporary in-memory cursor created during complex analytical joins

Question 2

Which of the following best describes the read vs. write trade-off of adding an index?

Accepted Answer

Indexes speed up reads but add overhead to INSERT, UPDATE, and DELETE operations

Answer

Indexes speed up both reads and writes equally

Answer

Indexes slow down reads because the planner must check the index first

Answer

Indexes only affect DELETE performance, not SELECT

Question 3

What distinguishes a primary index from a secondary index?

Accepted Answer

Primary indexes determine table layout or PK; secondary indexes index other fields

Answer

Primary indexes allow duplicates; secondary indexes enforce uniqueness

Answer

Primary indexes are stored in RAM; secondary indexes reside on disk storage

Answer

Primary indexes support range filters; secondary indexes only support equality

Question 4

What is a full table scan, and when does the database use one?

Accepted Answer

Reading only the first and last storage blocks to compute row approximations

Answer

Reading all table rows sequentially when no index fits or costs are lower

Answer

Scanning all secondary indexes concurrently before accessing table records

Answer

Validating foreign key constraints sequentially across parent table pages

Question 5

Given the query `SELECT * FROM orders WHERE customer_id = 42`, which condition allows the database to use an index on `customer_id`?

Accepted Answer

An index must exist on the `customer_id` column

Answer

The table must have fewer than 1,000 rows

Answer

`customer_id` must be the first column defined in the table

Answer

The query must use `LIMIT 1`

Question 6

What does a UNIQUE index guarantee?

Accepted Answer

That no two rows share the same value in the indexed column(s)

Answer

That the indexed column is never NULL

Answer

That the index is stored in a separate tablespace from the table data

Answer

That SELECT queries on that column always return results in sorted order

Question 7

In PostgreSQL, what does the `EXPLAIN` command show you?

Accepted Answer

The planned execution tree with operator nodes, costs, and estimated rows

Answer

The rewritten SQL query text after applying view and rule substitutions

Answer

A comprehensive report detailing all existing table constraints and indexes

Answer

The total elapsed wall-clock time and CPU utilization for query completion

Question 8

What is the key difference between a clustered and a non-clustered index?

Accepted Answer

Clustered index stores rows in index order; non-clustered stores row pointers

Answer

Clustered index supports single columns; non-clustered supports multi-columns

Answer

Clustered index requires memory cache; non-clustered lives on disk storage

Answer

Clustered index is for joins; non-clustered index is solely for aggregations

Question 9

Why are indexes on low-cardinality columns (e.g., a boolean `is_active`) often ineffective?

Accepted Answer

Few distinct values mean an index scan touches most pages, beating no seeks

Answer

B-Tree indexes cannot be constructed on columns using boolean data types

Answer

Low-cardinality index structures are automatically purged by engine workers

Answer

Boolean columns require specialized columnar engines absent in PostgreSQL

Question 10

What happens to an index when you update a row's indexed column?

Accepted Answer

The old index key entry is deleted and a new key entry is inserted

Answer

Nothing — index modifications are deferred to the subsequent VACUUM run

Answer

The entire table index is rebuilt asynchronously from scratch by engine

Answer

The corresponding index node is flagged invalid until the next read scan

Question 11

When the B-Tree index is traversed to find a key value, what is the time complexity of the search?

Accepted Answer

O(log n) — logarithmic in the number of rows

Answer

O(n) — linear in the number of rows

Answer

O(1) — constant time regardless of table size

Answer

O(n²) — quadratic in the number of rows

Question 12

Which statement correctly contrasts a hash index with a B-Tree index?

Accepted Answer

Hash indexes offer O(1) equality but no ranges; B-Trees support both

Answer

Hash indexes excel at range filters; B-Trees only handle point lookups

Answer

Hash indexes store entries in physical sorted order for range lookups

Answer

Hash and B-Tree indexes provide identical performance characteristics

Question 13

Given a composite index on `(last_name, first_name)`, which WHERE clause can use the index efficiently?

Accepted Answer

`WHERE last_name = 'Smith'`

Answer

`WHERE first_name = 'Alice'` (skipping `last_name`)

Answer

`WHERE first_name = 'Alice' AND last_name = 'Smith'` — only when both columns appear

Answer

Neither column can use the index unless the query includes all indexed columns

Question 14

What is a covering index?

Accepted Answer

An index that contains all columns needed by a query, allowing the engine to return results without touching the heap

Answer

An index that automatically spans every physical column in the table to eliminate the need for table heap storage

Answer

A dynamic index that detects slow queries and adds new columns automatically to its B-Tree structure during runtime

Answer

A clustered index that reorganizes table rows physically on disk to ensure range queries avoid random storage seeks

Question 15

What is index selectivity, and why does it matter to the query planner?

Accepted Answer

The ratio of distinct values to rows; high selectivity favors index use

Answer

The total disk blocks occupied by index pages; larger sizes scan faster

Answer

Whether the index definition was applied directly to a primary key column

Answer

The historical percentage of SQL queries that reference the index keys

Question 16

What is a partial index in PostgreSQL?

Accepted Answer

An index containing rows that satisfy a specific WHERE filter

Answer

An index indexing only the prefix bytes of lengthy text fields

Answer

An index covering the leading column of a composite key pair

Answer

An index synthesized temporarily for a single query execution

Question 17

What is a function-based (expression) index?

Accepted Answer

An index on column expressions or functions enabling computed lookups

Answer

An index defined as a database stored procedure rebuilt nightly

Answer

An index executing a trigger function whenever row mutations happen

Answer

An index hashing key values cryptographically for tenant protection

Question 18

What is index bloat and how does VACUUM help in PostgreSQL?

Accepted Answer

Bloat is dead tuple space from updates/deletes; VACUUM frees reusable pages

Answer

Bloat means too many index columns; VACUUM drops extra columns automatically

Answer

Bloat means index size exceeds heap; VACUUM compresses both to equal sizes

Answer

Bloat stems from concurrent connections; VACUUM flushes connection pools

Question 19

Why might the PostgreSQL query planner ignore an index even when one exists on the filtered column?

Accepted Answer

The optimizer estimates a sequential scan is cheaper for matching row volumes

Answer

The database engine strictly never bypasses an active defined B-Tree index

Answer

The index is only accessible if the query includes an explicit ORDER BY sort

Answer

B-Tree indexes are evaluated solely during relational JOIN table conditions

Question 20

Given a B-Tree index on a `name` column, which LIKE pattern can use the index?

Accepted Answer

`WHERE name LIKE 'smith%'` (prefix wildcard)

Answer

`WHERE name LIKE '%smith'` (suffix wildcard)

Answer

`WHERE name LIKE '%smith%'` (both sides wildcard)

Answer

None — LIKE never uses a B-Tree index

Question 21

Which PostgreSQL index type is designed for geometric and spatial data types such as points, boxes, and polygons?

Accepted Answer

GiST (Generalized Search Tree)

Answer

GIN (Generalized Inverted Index)

Answer

BRIN (Block Range INdex)

Answer

SP-GiST (Space-Partitioned GiST)

Question 22

A GIN (Generalized Inverted Index) is most appropriate for which type of column?

Accepted Answer

Composite columns like arrays, JSONB, or tsvector

Answer

A single-value auto-increment integer primary key

Answer

A strictly increasing timestamp in append tables

Answer

A low-cardinality two-state boolean status flag

Question 23

What is 'write amplification' in the context of LSM-tree (Log-Structured Merge-tree) indexes?

Accepted Answer

Each logical write is physically written multiple times across different levels of the LSM tree during compaction

Answer

The database writes a redundant copy of every row to the transaction log

Answer

Write amplification refers to the overhead of updating a B-Tree's parent nodes after a leaf split

Answer

It is the extra latency caused by flushing the write-ahead log to disk

Question 24

What is the purpose of `CREATE INDEX CONCURRENTLY` in PostgreSQL?

Accepted Answer

Builds the index without taking an exclusive write lock on the table, allowing concurrent reads and writes at build time

Answer

Builds the index across all available CPU cores simultaneously to minimize the total creation time for B-Tree pages

Answer

Builds the index structure on a read replica before promoting it to master status, eliminating I/O on the primary node

Answer

Defers uniqueness constraint validation until the transaction commit, maximizing ingestion throughput for batch jobs

Question 25

What is an 'invisible index' (supported in MySQL 8+ and Oracle) and what is its primary use case?

Accepted Answer

An index maintained by engine but hidden from planner to test drop impacts

Answer

An index encrypted on disk so contents cannot be inspected by system admins

Answer

An ephemeral index resident in RAM that is never serialized to table storage

Answer

An internal index on system tables hidden from information_schema metadata

Question 26

How does PostgreSQL's MVCC (Multi-Version Concurrency Control) model contribute to index bloat over time?

Accepted Answer

UPDATEs create new tuple versions; both old and new index keys stay until VACUUM

Answer

MVCC compresses historical rows causing index B-Tree pages to split apart

Answer

MVCC appends 64-bit transaction IDs to every leaf index key, doubling disk footprint

Answer

MVCC isolates index pages so dead rows remain strictly confined to table heap data

Question 27

When does the PostgreSQL planner choose a Bitmap Index Scan over a plain Index Scan?

Accepted Answer

When combining multiple indexes or reading moderate row counts in physical heap page order to eliminate random seeks

Answer

When queries return a solitary row identified by a unique index constraint, bypassing heap validation checks

Answer

When tables lack physical heap pages because records are stored entirely inside the leaves of a clustered index

Answer

When the cost optimizer forces bitmap scans because they are unconditionally faster than plain index scans

Question 28

In a partitioned table, what is a key consideration when creating indexes to ensure all partitions benefit?

Accepted Answer

Indexes created on parent tables automatically apply across all partitions

Answer

Indexes must reside strictly on partition keys; other columns cannot index

Answer

Every individual partition mandates manual index declaration with unique tags

Answer

Partitioned tables reject standard B-Trees and exclusively permit BRIN types

Question 29

What is a BRIN index and for which table characteristic is it best suited?

Accepted Answer

A block range index storing min/max values for physically sorted columns

Answer

A balanced range index engineered to replace B-Trees across numeric fields

Answer

A bitmap range index designed to accelerate random heap data lookups

Answer

A binary radix index restricted to UUID and primary key identity lookups

Question 30

Which PostgreSQL system view would you query to find the number of index scans, sequential scans, and tuples fetched for user-created indexes?

Accepted Answer

pg_stat_user_indexes

Answer

pg_indexes

Answer

information_schema.table_constraints

Answer

pg_index

Database Indexing Interview Quiz

What the Database Indexing interview quiz covers

Difficulty levels

Question topics covered

How to use the Database Indexing quiz

Frequently asked questions about the Database Indexing quiz

Database Design

System Architecture

SQS & Kafka

Database Indexing Interview Quiz

What the Database Indexing interview quiz covers

Difficulty levels

Question topics covered

How to use the Database Indexing quiz

Frequently asked questions about the Database Indexing quiz

Related Interview Drills

Database Design

System Architecture

SQS & Kafka