Backend interview prep

Start

PostgreSQL Indexes Interview Questions

Master PostgreSQL Indexes with interview-focused questions covering B-Tree, Hash, GIN, GiST, BRIN, Partial Indexes, Composite Indexes, Expression Indexes, Covering Indexes, Index Scan, Bitmap Scan, Index-Only Scan, and enterprise production best practices.

Introduction

Indexes are one of the most important performance optimization techniques in PostgreSQL.

Without indexes,

the database scans every row.

With indexes,

PostgreSQL can locate rows quickly.

Indexes improve

  • Query Performance
  • Search Speed
  • Sorting
  • Filtering
  • Joins

Understanding PostgreSQL indexes is essential for

  • Java Developers
  • Backend Engineers
  • Database Engineers
  • PostgreSQL DBAs
  • Solution Architects

PostgreSQL Index Architecture

flowchart LR

SqlQuery["SQL Query"] --> PlannerIndexMatchingRows["Planner --> Index --> Matching Rows --> TableData["Table Data"]"]

1. What is an Index?

Answer

An Index is a special database object that allows PostgreSQL to locate rows efficiently without scanning the entire table.

Think of it like the index in a book.

Instead of reading every page,

you jump directly to the required page.

2. Why are indexes important?

Without an index

1 Million Rows

↓

Sequential Scan

↓

Slow

With an index

Index Lookup

↓

Required Rows

↓

Fast

Sequential Scan vs Index Scan

Sequential Scan Index Scan
Reads Entire Table Reads Matching Rows
Slower Faster
Good for Small Tables Good for Large Tables

3. What types of indexes does PostgreSQL support?

PostgreSQL supports

  • B-Tree
  • Hash
  • GIN
  • GiST
  • BRIN
  • SP-GiST
  • Bloom (Extension)

PostgreSQL Index Types

flowchart TD

Indexes --> BTree

Indexes --> Hash

Indexes --> GIN

Indexes --> GiST

Indexes --> BRIN

4. What is a B-Tree Index?

B-Tree is the default index type.

Suitable for

  • Equality
  • Range Queries
  • ORDER BY
  • BETWEEN
  • Comparison Operators

Example

CREATE INDEX idx_employee_name

ON employees(name);

5. When should B-Tree be used?

Ideal for

  • WHERE id = ?
  • WHERE salary > ?
  • ORDER BY name
  • BETWEEN
  • JOIN conditions

Most PostgreSQL indexes are B-Tree.

B-Tree Structure

            Root
          /      \
      Internal  Internal
      /   \      /   \
   Leaf  Leaf  Leaf  Leaf

6. What is a Hash Index?

Hash indexes are optimized for

equality comparisons.

Example

WHERE email = ?

Cannot efficiently support range queries.

B-Tree vs Hash

B-Tree Hash
Equality Equality
Range Queries No
ORDER BY No
Default Specialized

7. What is a GIN Index?

GIN

Generalized Inverted Index

Used for

  • JSONB
  • Arrays
  • Full Text Search

Example

CREATE INDEX idx_profile

ON users

USING GIN(profile);

GIN Architecture

flowchart LR

JSONB --> GinIndexFastsearchfastSearch["GIN Index --> FastSearch["Fast Search"]"]

8. What is a GiST Index?

GiST

Generalized Search Tree

Supports

  • Geospatial Data
  • Range Types
  • Full Text Search
  • Custom Data Types

Often used with PostGIS.

9. What is a BRIN Index?

BRIN

Block Range Index

Stores metadata

instead of every row.

Ideal for

  • Very Large Tables
  • Time-series Data
  • Sequential Data

Consumes very little storage.

BRIN Example

CREATE INDEX idx_orders

ON orders

USING BRIN(created_at);

10. What is a Composite Index?

Composite Index

contains multiple columns.

Example

CREATE INDEX idx_customer

ON orders(customer_id, order_date);

Useful when queries filter on both columns.

Composite Index

(customer_id, order_date)

11. What is the Leftmost Prefix Rule?

For a composite index

(customer_id, order_date)

Efficient queries include

customer_id

customer_id + order_date

Querying only

order_date

generally cannot use the composite index effectively.

12. What is a Partial Index?

Indexes only selected rows.

Example

CREATE INDEX idx_active_users

ON users(status)

WHERE status='ACTIVE';

Benefits

  • Smaller Index
  • Faster Search
  • Lower Storage

Partial Index

flowchart LR

Table --> FilteredRowsPartialindexpartialIndex["Filtered Rows --> PartialIndex["Partial Index"]"]

13. What is an Expression Index?

Indexes

computed values.

Example

CREATE INDEX idx_lower_name

ON employees

(LOWER(name));

Useful for

case-insensitive searches.

14. What is a Covering Index?

A covering index contains

all columns required by a query.

Example

CREATE INDEX idx_order

ON orders(customer_id)

INCLUDE(total_amount);

Allows Index-Only Scans.

(PostgreSQL supports INCLUDE columns from version 11.)

15. What is an Index-Only Scan?

If all required columns

exist in the index,

PostgreSQL may avoid reading the table.

Benefits

  • Faster Queries
  • Less Disk IO

Index-Only Scan

flowchart LR

Query --> Index --> Result

16. What is an Index Scan?

Reads matching index entries,

then fetches rows

from the table.

17. What is a Bitmap Index Scan?

PostgreSQL first builds

a bitmap of matching rows,

then retrieves rows efficiently.

Useful when

many rows match.

Bitmap Scan

flowchart LR

Index --> Bitmap --> TableRows["Table Rows"]

18. What is a Sequential Scan?

Reads

every row

of a table.

Chosen when

  • Table is Small
  • Most Rows Match
  • Index is More Expensive

19. How do you check index usage?

Example

EXPLAIN ANALYZE

SELECT *

FROM employees

WHERE id=100;

Look for

  • Index Scan
  • Bitmap Scan
  • Index Only Scan

20. How do you list indexes?

Example

SELECT *

FROM pg_indexes

WHERE tablename='employees';

21. Can too many indexes hurt performance?

Yes.

Each INSERT

UPDATE

DELETE

must update indexes.

Problems

  • Slower Writes
  • More Storage
  • Longer Maintenance

22. When should indexes NOT be created?

Avoid indexes on

  • Very Small Tables
  • Frequently Updated Columns
  • Low Selectivity Columns
  • Rarely Queried Columns

23. Banking Example

Account Lookup

WHERE account_number=?

B-Tree Index

↓

Milliseconds

24. E-Commerce Example

Product Search

attributes JSONB

↓

GIN Index

↓

Fast Search

25. SaaS Example

Find Active Customers

status='ACTIVE'

↓

Partial Index

↓

Small Index

↓

Fast Queries

26. Logging Example

500 Million Logs

↓

BRIN Index

↓

Fast Date Filtering

27. Common Index Problems

  • Missing indexes
  • Duplicate indexes
  • Unused indexes
  • Wrong index type
  • Too many indexes
  • Ignoring execution plans

PostgreSQL Index Workflow

flowchart LR

SQL --> PlannerChooseIndexRetrieve["Planner --> Choose Index --> Retrieve Rows --> ReturnResult["Return Result"]"]

Enterprise Best Practices

  • Use B-Tree for most OLTP workloads.
  • Use GIN for JSONB and Full Text Search.
  • Use GiST for geospatial data.
  • Use BRIN for huge append-only tables.
  • Create composite indexes based on query patterns.
  • Use partial indexes for filtered queries.
  • Use expression indexes for computed searches.
  • Remove unused indexes periodically.
  • Monitor index usage using pg_stat_user_indexes.
  • Always validate changes with EXPLAIN ANALYZE.

Quick Revision

Topic Key Point
Index Faster Data Lookup
B-Tree Default Index
Hash Equality Search
GIN JSONB & Arrays
GiST Spatial Data
BRIN Large Tables
Composite Index Multiple Columns
Partial Index Filtered Rows
Expression Index Computed Values
Covering Index INCLUDE Columns
Index Scan Uses Index
Bitmap Scan Many Matching Rows
Index-Only Scan Reads Only Index

Interview Tips

Interviewers frequently ask

  • What is an index?
  • B-Tree vs Hash.
  • GIN vs GiST.
  • What is BRIN?
  • Composite Index.
  • Leftmost Prefix Rule.
  • Partial Index.
  • Expression Index.
  • Index Scan vs Sequential Scan.
  • Explain EXPLAIN ANALYZE.

A strong interview explanation is:

"PostgreSQL supports multiple index types for different workloads. B-Tree is the default choice for equality and range queries, GIN is optimized for JSONB and full-text search, GiST supports spatial and specialized data types, and BRIN is designed for very large append-only tables. Choosing the right index type based on query patterns and validating it with EXPLAIN ANALYZE is essential for achieving optimal database performance."

Summary

Indexes are one of the most effective ways to improve PostgreSQL query performance. PostgreSQL provides specialized index types such as B-Tree, Hash, GIN, GiST, and BRIN to support different workloads. Features like Composite Indexes, Partial Indexes, Expression Indexes, Covering Indexes, and Index-Only Scans enable efficient execution of complex queries.

Understanding index selection, execution plans, and indexing strategies is essential for PostgreSQL Developers, Backend Engineers, Database Administrators, and Solution Architects building high-performance enterprise applications.