Various index examples in PostgreSQL
B-tree, GIN/ GiST, BRIN - These different types of PostgreSQL indexes are designed to optimize specific kinds of queries. You choose an index type based on the data type in the column and the type of query you plan to run.
Here is an explanation of each index type and the SQL commands to create them.
1. B-Tree Index (Default and General Use) 🌳
The B-Tree (Balanced Tree) is the default and most versatile index type in PostgreSQL. It organizes data in a tree structure, making it fast for single-value lookups and sequential ordering.
| Query Type Optimized | Example |
|---|---|
| Equality Queries | WHERE user_id = 123 |
| Range Queries | WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31' |
| Ordering | ORDER BY last_name |
Command Example
You would use a B-Tree index on a simple column like a user ID or a date/timestamp. Since it's the default, you often don't need to specify the type.
-- Syntax to create a B-Tree index (used for general columns)
CREATE INDEX idx_products_price ON products (price);
-- The same command, explicitly specifying the B-Tree type (optional)
CREATE INDEX idx_products_price_btree ON products USING BTREE (price);
2. GIN (Generalized Inverted Index) & GiST (Generalized Search Tree) 🌲
GIN and GiST are specialized index types used when a single row contains many searchable values (like a document, an array, or a JSON blob). They work by building an index of all those individual values.
| Index Type | Query Type Optimized | Data Type | Command Example |
|---|---|---|---|
| GIN | Full-Text Search, JSON/JSONB queries, Arrays. Best when the data inside a row is very complex. | jsonb, text, tsvector, array | CREATE INDEX idx_docs_fts ON documents USING GIN (to_tsvector('english', content)); |
| GiST | Geographic/Spatial data, Range types, and Full-Text Search. Best for indexing complex data with an ordered structure. | geometry, box, range | CREATE INDEX idx_locations_gist ON locations USING GIST (geom); |
Command Example (GIN for JSONB)
This is common for querying data inside a JSON field.
-- Assume you have a 'data' column of type JSONB
CREATE INDEX idx_products_data_gin ON products USING GIN (data);
-- This index optimizes queries like:
-- SELECT * FROM products WHERE data @> '{"color": "red"}';
Command Example (GiST for Spatial Data)
This is used by the PostGIS extension to quickly search maps and coordinates.
-- Assume you have a PostGIS geometry column named 'location'
CREATE INDEX idx_shops_location_gist ON shops USING GIST (location);
-- This index optimizes spatial queries like:
-- SELECT * FROM shops WHERE ST_DWithin(location, target_point, 1000);
3. BRIN (Block Range Index) 🧱
The BRIN index is designed for very large tables where the data is naturally ordered on the disk (sequentially). It's extremely small and fast to create.
| Query Type Optimized | Example |
|---|---|
| Range Queries | WHERE created_at > '2025-01-01' |
| Data Type | Data that is inserted sequentially (e.g., auto-incrementing IDs, timestamps). |
How it Works
Instead of indexing every row, the BRIN index only records the minimum and maximum values for large blocks of data on the disk.
- Example: The BRIN index might record that disk block 1 holds
created_atdates from Jan 1st to Jan 31st. - Benefit: When you query for a March date, the database skips disk block 1 entirely, saving massive time, and the index file itself is tiny compared to a B-Tree.
Command Example
You would use a BRIN index on a sequential column like a creation timestamp in a massive log table.
-- Assuming 'log_time' is a column that grows sequentially
CREATE INDEX idx_logs_time_brin ON logs USING BRIN (log_time);
Note: If your data is scattered randomly across the table (not inserted sequentially), a BRIN index will be useless, and you should use a B-Tree instead.