Beginner LevelPostgreSQL
What index types exist in PostgreSQL (B-Tree, GIN, GiST, BRIN, SP-GiST, Hash)?
❓ Interview Prompt: Explain PostgreSQL index engines: B-Tree (default), GIN (arrays/JSONB), GiST (spatial/geometry), BRIN (large append-only data), and Hash.
PostgreSQL provides six distinct index access methods:
•
B-Tree (Default): Ideal for range queries ('<', '<=', '=', '>=', '>') and sorting.
•
GIN (Generalized Inverted Index): Ideal for composite items (JSONB, arrays, full-text search).
•
GiST (Generalized Search Tree): Ideal for spatial/geometry data (PostGIS) and ranges.
•
BRIN (Block Range Index): Ideal for multi-gigabyte append-only time-series tables.
Code Implementation Example
sql
-- Create B-Tree index
CREATE INDEX idx_users_email ON users(email);
-- Create GIN index on array column
CREATE INDEX idx_users_tags_gin ON users USING gin(tags);💡Creating B-Tree and GIN indexes in PostgreSQL.
🎯 Interview Key Takeaway
Use B-Tree for standard lookups, GIN for arrays/JSONB, and BRIN for massive time-series tables.
#Indexes#B-Tree#GIN#GiST#BRIN#Beginner
Back to all PostgreSQL Questions→