6 PostgreSQL Index Types Explained: A Complete Guide

Without an index, PostgreSQL answers a filtered query with a sequential scan, reading every row and checking each one against the filter condition, which takes seconds on a large table. Adding an index that matches the query pattern lets PostgreSQL locate matching rows directly, cutting query time from seconds to milliseconds for a selective filter.

PostgreSQL supports 6 index types, including B-tree, Hash, GIN, GiST, SP-GiST, and BRIN. Each one is built for a different data pattern and query type. If you pick the wrong one, an index fails to speed up a query, or slows down writes without giving anything back on reads.

This guide explains how each PostgreSQL index type works, when to use it, and how to avoid the mistakes that make indexes worse than no index at all. It includes a decision table, and working CREATE INDEX examples for every type.

>> Read more:

• Mastering Different Types of Indexes in SQL with Examples
• Clustered vs Non-Clustered Index in SQL: Key Differences

Quick Index Selection Decision Table

Use this table to quickly identify your best option before reading the full breakdown of each type below.

Decision Shortcuts:

• Default choice: B-tree (covers 80% of cases)
• Complex types (JSONB, arrays): GIN
• PostGIS spatial: GiST
• Massive time-series tables: BRIN
• Equality-only, space-constrained: Hash

6 PostgreSQL Index Types Compared

Once you've narrowed down a candidate from the decision table above, use this table to see how it stacks up against the others on size, write cost, and build time before committing to it.

B-tree Index

B-tree, PostgreSQL's default index type for good reason, handles the vast majority of query patterns efficiently. When you run CREATE INDEX without specifying a type, you get a B-tree.

B-tree indexes excel at:

• Equality comparisons: WHERE user_id = 123
• Range queries: WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'
• Inequality operators: <, <=, >, >=
• Sorting operations: ORDER BY created_at DESC
• Pattern matching: WHERE email LIKE 'user@%' (prefix only)
• NULL handling: WHERE deleted_at IS NULL

The sorted tree structure makes B-tree indexes ideal for range scans and ordered result sets. PostgreSQL can traverse the index sequentially to retrieve rows in sort order without additional sorting overhead.

B-tree Syntax and Examples:

-- Basic B-tree (type implicit)
CREATE INDEX idx_orders_created ON orders(created_at);

-- Explicit B-tree specification
CREATE INDEX idx_users_email ON users USING BTREE (email);

-- Multi-column index (left-to-right usage)
CREATE INDEX idx_orders_status_date ON orders(status, created_at);
-- Efficient: WHERE status = 'shipped' AND created_at > '2026-01-01'
-- Efficient: WHERE status = 'shipped'
-- Inefficient: WHERE created_at > '2026-01-01' (skips first column)

-- B-tree with custom sort order
CREATE INDEX idx_orders_date_desc ON orders(created_at DESC NULLS LAST);

-- Case-insensitive text search
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

Multi-Column Index Order Matters:

The column order in multi-column B-tree indexes is critical. PostgreSQL can efficiently use the index only if your query filters on the leftmost columns first.

-- Index: (status, created_at)
CREATE INDEX idx_orders_composite ON orders(status, created_at);

-- USES INDEX: Queries leftmost column first
SELECT * FROM orders WHERE status = 'pending';
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';

-- CANNOT USE INDEX: Skips leftmost column
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days';

Rule of thumb: Place the most selective (highest cardinality) columns first, unless your query patterns always filter on specific columns.

Unique Indexes: A unique index enforces that no two rows share the same value in the indexed column or column combination. PostgreSQL uses a B-tree unique index automatically to back every PRIMARY KEY and UNIQUE constraint, but you can also create one directly:

-- Unique index on a single column
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Unique index across multiple columns (composite uniqueness)
CREATE UNIQUE INDEX idx_orders_customer_sku ON order_items(order_id, sku);

A unique index does two things at once. It prevents duplicate data, and it speeds up equality lookups on that column, since PostgreSQL can stop searching as soon as it finds the single matching row.

PostgreSQL allows up to 32 columns in a single multi-column index, a limit set by INDEX_MAX_KEYS that can only be changed by recompiling PostgreSQL from source. In practice, indexes with more than four or five columns are rare and usually signal that the query pattern, not the index, needs rethinking.

B-tree Performance Characteristics:

• Lookup time: O(log n) for both equality and range queries
• Insert/Update time: O(log n) per index
• Size: Typically 25-30% of table size (varies by cardinality)
• Best for: General-purpose queries on sortable data types

PostgreSQL has supported parallel B-tree index builds since version 11, which uses multiple worker processes to reduce build time on large tables (controlled by the max_parallel_maintenance_workers setting). PostgreSQL 13 added B-tree deduplication, which reduces index size for columns with many repeated values by storing each distinct value once per leaf page instead of once per row.

Hash Index

Hash indexes are PostgreSQL's space-efficient option for exact equality lookups. Prior to PostgreSQL 10, Hash indexes weren't crash-safe and rarely used. Since PostgreSQL 10, they're fully WAL-logged and production-ready.

Use Hash indexes when:

1. Only equality queries: You never need <, >, BETWEEN, or ORDER BY
2. High cardinality data: UUIDs, long strings, hashes
3. Space constraints: Hash indexes are typically smaller than equivalent B-tree indexes, often by 20 to 30 percent, since they store a fixed-size hash value rather than the indexed value itself

Typical use cases:

• UUID primary key lookups: WHERE uuid = 'a1b2c3d4...'
• Hash-based session tokens
• Exact string matching on high-cardinality columns

Hash Index Limitations:

Hash indexes support only the = operator. If you need any of these, use B-tree:

• Range queries: WHERE id > 100
• Sorting: ORDER BY email
• Pattern matching: LIKE 'prefix%'
• NULL checks: IS NULL (Hash doesn't index NULL values)

Hash Index Code Examples:

-- Hash index for UUID column
CREATE INDEX idx_sessions_uuid ON sessions USING HASH (session_uuid);

-- Query that uses the Hash index
SELECT * FROM sessions WHERE session_uuid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

-- Compare index sizes on your own table
CREATE INDEX idx_users_btree ON users USING BTREE (user_uuid);
CREATE INDEX idx_users_hash ON users USING HASH (user_uuid);

SELECT
    pg_size_pretty(pg_relation_size('idx_users_btree')) AS btree_size,
    pg_size_pretty(pg_relation_size('idx_users_hash')) AS hash_size;
-- Expect Hash to come in smaller, typically 20-30% below the B-tree figure

Hash vs B-tree: Quick Comparison:

Recommendation: Use Hash only when absolutely certain you'll never need range queries or sorting. In most cases, B-tree's flexibility outweighs the modest space savings.

GIN Index

GIN (Generalized Inverted Index) is PostgreSQL's powerhouse for indexing complex data types. If you're working with JSONB, arrays, or full-text search, GIN is likely your best choice.

How GIN Works: Unlike B-tree indexes that store row pointers directly, GIN creates an inverted index: it maps each unique element (JSON key, array item, search term) to a list of rows containing it. This structure makes GIN exceptionally fast for "contains" and "exists" queries.

Trade-offs:

• Read performance: typically 10-100x faster than sequential scans for containment queries
• Write performance: typically 3-5x slower than B-tree due to index maintenance
• Size: typically 1.5-3x the base table size (highly dependent on data)

GIN for JSONB Indexing

JSONB is PostgreSQL's binary JSON storage format, and GIN is the standard indexing method.

-- Table with JSONB column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB
);

-- GIN index on JSONB column
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- Query 1: Check if key exists
SELECT * FROM products WHERE attributes ? 'color';
-- Uses: idx_products_attrs

-- Query 2: Containment (@> operator)
SELECT * FROM products WHERE attributes @> '{"brand": "Nike", "in_stock": true}';
-- Uses: idx_products_attrs

-- Query 3: Path-specific lookup
SELECT * FROM products WHERE attributes @> '{"specs": {"weight": "1.2kg"}}';
-- Uses: idx_products_attrs

-- Query 4: Key exists with OR
SELECT * FROM products WHERE attributes ?| ARRAY['color', 'size'];
-- Uses: idx_products_attrs

GIN JSONB Operators:

• @>: Contains JSON value
• ?: Key exists
• ?&: All keys exist
• ?|: Any key exists

GIN for Array Columns

GIN indexes array elements individually, making overlap and containment queries extremely fast.

-- Table with array column
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]
);

-- GIN index on array column
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- Query 1: Array contains all specified values
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'performance'];
-- Uses: idx_posts_tags

-- Query 2: Array overlap (shares at least one element)
SELECT * FROM posts WHERE tags && ARRAY['database', 'optimization'];
-- Uses: idx_posts_tags

-- Query 3: Specific element membership
SELECT * FROM posts WHERE 'postgresql' = ANY(tags);
-- Uses: idx_posts_tags

GIN for Full-Text Search

PostgreSQL's full-text search uses tsvector and tsquery types, both indexable with GIN.

-- Table with text content
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT
);

-- GIN index on tsvector expression
CREATE INDEX idx_articles_search ON articles
USING GIN (to_tsvector('english', title || ' ' || body));

-- Full-text search query
SELECT id, title
FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'postgresql & performance');
-- Uses: idx_articles_search

-- Alternative: Store tsvector in a generated column
ALTER TABLE articles ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

CREATE INDEX idx_articles_search_vector ON articles USING GIN (search_vector);

GIN Tuning Parameters

-- Increase pending list size for better insert performance
ALTER INDEX idx_products_attrs SET (gin_pending_list_limit = 4096); -- Default: 4MB

-- Fast GIN updates (PostgreSQL 14+)
ALTER INDEX idx_products_attrs SET (fastupdate = on); -- Default: on

GiST Index

GiST (Generalized Search Tree) is PostgreSQL's extensible indexing framework. While less specialized than GIN for text search, GiST is the only option for certain data types, particularly geometric and range types.

GiST excels at:

• PostGIS spatial queries: Bounding box searches, proximity queries
• Range types: Overlapping ranges, containment
• Full-text search (write-heavy): Alternative to GIN
• Network addresses: IP range queries

GiST for PostGIS Spatial Queries

PostGIS extends PostgreSQL with geographic object support. GiST indexes are essential for spatial query performance.

-- Table with geographic data (requires PostGIS extension)
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom GEOMETRY(POINT, 4326) -- WGS 84 coordinate system
);

-- GiST index on geometry column
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

-- Spatial query 1: Find points within distance
SELECT name
FROM locations
WHERE ST_DWithin(
    geom,
    ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326), -- San Francisco
    1000 -- 1000 meters
);
-- Uses: idx_locations_geom

-- Spatial query 2: Bounding box intersection
SELECT name
FROM locations
WHERE ST_Intersects(
    geom,
    ST_MakeEnvelope(-122.5, 37.7, -122.3, 37.8, 4326)
);
-- Uses: idx_locations_geom

GiST for Range Types

PostgreSQL's range types (int4range, int8range, daterange, tsrange, tstzrange) are perfect for booking systems, time-series, and versioning.

-- Booking system with date ranges
CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    room_id INT,
    guest_name TEXT,
    period DATERANGE
);

-- GiST index on range column
CREATE INDEX idx_bookings_period ON bookings USING GIST (period);

-- Query 1: Find overlapping bookings
SELECT * FROM bookings
WHERE period && daterange('2026-02-01', '2026-02-15');
-- Uses: idx_bookings_period

-- Query 2: Check if date is contained in any booking
SELECT * FROM bookings
WHERE period @> '2026-02-10'::date;
-- Uses: idx_bookings_period

-- Query 3: Find adjacent (abutting) periods
SELECT * FROM bookings
WHERE period -|- daterange('2026-02-15', '2026-02-20');
-- Uses: idx_bookings_period

Range Operators:

• &&: Overlap
• @>: Contains element or range
• <@: Is contained by
• -|-: Adjacent (no gap)

Nearest-Neighbor Search With GiST

GiST supports nearest-neighbor (KNN) queries using the <-> distance operator, which lets PostgreSQL return rows ordered by distance from a reference point without scanning the whole table.

-- Find the 10 closest locations to a point, nearest first
SELECT name
FROM locations
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)
LIMIT 10;
-- Uses: idx_locations_geom (GiST)

This pattern only runs efficiently when a GiST (or SP-GiST) index exists on the column and the query both orders by <-> and includes a LIMIT. Without the index, PostgreSQL has to compute the distance for every row and sort the entire result set.

Exclusion Constraints With GiST

GiST also backs exclusion constraints, which reject a new row if it conflicts with an existing row under a specified operator, not just on exact duplicates the way a unique constraint does. This is the standard way to prevent overlapping bookings or scheduling conflicts at the database level.

-- Prevent overlapping bookings for the same room
ALTER TABLE bookings ADD CONSTRAINT no_overlapping_bookings
EXCLUDE USING GIST (room_id WITH =, period WITH &&);

This constraint uses the same GiST range operator as the queries above (&& for overlap), but enforces it at write time. Any INSERT or UPDATE that would create an overlapping period for the same room_id is rejected before it reaches the table.

GiST for Full-Text (Alternative to GIN)

GiST can index tsvector with better write performance than GIN.

-- GiST full-text index (write-optimized)
CREATE INDEX idx_articles_gist_search ON articles
USING GIST (to_tsvector('english', title || ' ' || body));

-- Same query as GIN example
SELECT id, title
FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'postgresql & performance');

Note: GiST full-text indexes are "lossy": PostgreSQL may need to recheck rows after the index lookup, which slightly reduces performance compared to GIN.

PostgreSQL GIN vs GiST: Key Differences

Both GIN and GiST can index full-text search, but they have different trade-offs:

Use GIN when: Read performance is critical, writes are infrequent, and you have storage space.

Use GiST when: Writes are frequent, storage is limited, or you need faster index builds.

SP-GiST Index

SP-GiST (Space-Partitioned Generalized Search Tree) is PostgreSQL's most specialized index type. It's designed for data with non-balanced tree structures: quadtrees, k-d trees, and radix trees.

Use SP-GiST for:

• IP address ranges: CIDR, INET types
• Phone numbers: Prefix-based lookups
• Hierarchical data: Nested categories, org charts
• Text prefix search: Faster than B-tree for long prefixes

SP-GiST Examples:

-- Use case 1: IP address ranges
CREATE TABLE network_logs (
    id SERIAL PRIMARY KEY,
    client_ip INET,
    request_time TIMESTAMPTZ,
    response_code INT
);

-- SP-GiST index for IP range queries
CREATE INDEX idx_network_ip ON network_logs USING SPGIST (client_ip inet_ops);

-- Query: Find all requests from subnet
SELECT * FROM network_logs
WHERE client_ip << '192.168.1.0/24'::inet;
-- Uses: idx_network_ip

-- Query: Find all requests matching IP prefix
SELECT * FROM network_logs
WHERE client_ip <<= '10.0.0.0/8'::inet;
-- Uses: idx_network_ip

-- Use case 2: Text prefix search (phone numbers, product codes)
CREATE TABLE dictionary (
    id SERIAL PRIMARY KEY,
    word TEXT
);

-- SP-GiST index for prefix matching
CREATE INDEX idx_words_prefix ON dictionary USING SPGIST (word text_ops);

-- Query: Find words starting with prefix
SELECT word FROM dictionary
WHERE word ^@ 'post'; -- ^@ is "starts with" operator (PostgreSQL 14+)
-- Uses: idx_words_prefix

-- Alternative for earlier versions
SELECT word FROM dictionary
WHERE word LIKE 'post%';
-- Uses: idx_words_prefix (if optimizer chooses it)

SP-GiST Performance Characteristics:

• Best for: Non-uniformly distributed data
• Size: Typically smaller than GiST for hierarchical data
• Insertion time: Faster than GiST for partitioned structures
• Limitation: Fewer operator classes than B-tree or GiST

When to skip SP-GiST:

For most use cases, B-tree or GiST provide better general performance. Use SP-GiST only when your data has clear hierarchical or partitioned structure.

BRIN Index

BRIN (Block Range Index) is PostgreSQL's space-efficient option for massive tables with natural ordering. While other indexes store pointers to individual rows, BRIN stores summary statistics for ranges of physical blocks.

How BRIN Achieves Tiny Size: BRIN does not record every value. It records the min and max values for each range of pages (default: 128 pages). During queries, PostgreSQL:

1. Checks BRIN summary: Does this block range contain values in my filter range?
2. Skips blocks that definitely don't match
3. Scans blocks that might match

This lossy approach trades perfect selectivity for extreme space efficiency.

BRIN for Large Time-Series Tables

BRIN is ideal for:

• Time-series data: Logs, metrics, events
• Append-only tables: Data written sequentially
• Sequential IDs: Auto-incrementing primary keys
• Massive tables: 100M+ rows where B-tree becomes impractical

-- Time-series events table
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    data JSONB
);

-- BRIN index on timestamp (assumes sequential inserts)
CREATE INDEX idx_events_time ON events USING BRIN (created_at);

-- Query: Recent events (range query)
EXPLAIN ANALYZE
SELECT * FROM events
WHERE created_at > NOW() - INTERVAL '24 hours';
-- Uses: idx_events_time (Bitmap Heap Scan + BRIN scan)

BRIN vs B-tree Storage Comparison

Here’s a real-world comparison on a 100M row table:

-- Create test table
CREATE TABLE metrics (
    id BIGSERIAL PRIMARY KEY,
    metric_name TEXT,
    value NUMERIC,
    recorded_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert 100M rows (sequential timestamps)
INSERT INTO metrics (metric_name, value, recorded_at)
SELECT
    'cpu_usage',
    random() * 100,
    NOW() - (random() * INTERVAL '365 days')
FROM generate_series(1, 100000000);

-- B-tree index
CREATE INDEX idx_metrics_btree ON metrics (recorded_at);
-- Build time: ~180 seconds

-- BRIN index
CREATE INDEX idx_metrics_brin ON metrics USING BRIN (recorded_at);
-- Build time: ~8 seconds

-- Size comparison
SELECT
    pg_size_pretty(pg_relation_size('metrics')) AS table_size,
    pg_size_pretty(pg_relation_size('idx_metrics_btree')) AS btree_size,
    pg_size_pretty(pg_relation_size('idx_metrics_brin')) AS brin_size;

-- Typical results:
-- table_size: 8.2 GB
-- btree_size: 2.1 GB (25% of table)
-- brin_size: 128 KB (0.0015% of table!)

The trade-off: BRIN queries scan more blocks than B-tree, resulting in slower queries. For the 100M row example above:

• B-tree query time: 0.8ms (perfect selectivity)
• BRIN query time: 45ms (scans extra blocks)

This is still 70x faster than a full sequential scan (3,200ms).

BRIN Index Best Practices

-- Tune pages_per_range based on data distribution
-- Default: 128 pages. Lower = larger index, better selectivity
CREATE INDEX idx_logs_time_fine ON logs USING BRIN (timestamp)
WITH (pages_per_range = 64);

-- Higher = smaller index, worse selectivity
CREATE INDEX idx_logs_time_coarse ON logs USING BRIN (timestamp)
WITH (pages_per_range = 256);

-- Ensure physical order matches logical order
-- BRIN requires clustering for effectiveness
CREATE INDEX idx_events_brin ON events USING BRIN (created_at);

-- Periodically reindex if data becomes unsorted
REINDEX INDEX CONCURRENTLY idx_events_brin;

-- Or cluster table by timestamp (requires exclusive lock)
CLUSTER events USING idx_events_time;

When NOT to Use BRIN

BRIN fails when:

• Data isn’t physically ordered: Random inserts break range assumptions
• High selectivity needed: Point queries on non-sequential data
• Small tables: Overhead exceeds benefit below ~1M rows
• Frequent updates: Out-of-order values degrade effectiveness

Check if your data is BRIN-friendly:

-- Measure correlation between physical and logical order
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'events' AND attname = 'created_at';

-- correlation close to 1.0 or -1.0 = BRIN-friendly
-- correlation near 0.0 = use B-tree instead

Common PostgreSQL Index Mistakes

Even experienced developers make indexing errors that hurt performance. Avoid these pitfalls:

Over-Indexing

Problem: Every index slows INSERT, UPDATE, and DELETE operations, since PostgreSQL has to update each one on every write. A table carrying many indexes can see a significant drop in write throughput compared to the same table with only the indexes it actually needs.

Solution: Benchmark write performance. Drop unused indexes.

-- Find indexes that are never used
SELECT
    schemaname || '.' || tablename AS table,
    indexname AS index,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan AS scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
    AND indexrelname NOT LIKE '%_pkey'
    AND indexrelname NOT LIKE '%_unique%'
ORDER BY pg_relation_size(indexrelid) DESC;

Wrong Composite Index Column Order

Problem: PostgreSQL can only use multi-column indexes left-to-right.

Wrong:

CREATE INDEX idx_orders_date_status ON orders(created_at, status);
-- Query filters on status first - index mostly useless
SELECT * FROM orders WHERE status = 'shipped';

Right:

CREATE INDEX idx_orders_status_date ON orders(status, created_at);
-- Query uses index efficiently
SELECT * FROM orders WHERE status = 'shipped';

Using B-tree for JSONB Containment

Problem: B-tree can't accelerate JSONB @> or ? operators.

Wrong:

CREATE INDEX idx_products_attrs_btree ON products(attributes);
-- Sequential scan (index ignored)
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

Right:

CREATE INDEX idx_products_attrs_gin ON products USING GIN (attributes);
-- Index scan (1000x faster)
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

Forgetting to ANALYZE After Bulk Loads

Problem: PostgreSQL's query planner relies on statistics. Bulk inserts without ANALYZE produce poor query plans.

Wrong:

INSERT INTO events SELECT * FROM staging_events; -- 10M rows
-- Queries now use sequential scans despite indexes

Right:

INSERT INTO events SELECT * FROM staging_events;
ANALYZE events; -- Update statistics
-- Queries now use indexes correctly

Not Using EXPLAIN ANALYZE to Verify

Problem: Assumptions about index usage are often wrong.

Solution: Always verify with EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending';

-- Look for:
-- "Index Scan" or "Bitmap Index Scan" = good
-- "Seq Scan" = index not used
-- "Heap Fetches: 0" in Index Only Scan = optimal

Creating Indexes on Low-Cardinality Columns

Problem: Boolean columns, enum-like fields with 2-5 values rarely benefit from indexes.

Wrong:

-- gender has 3 values: 'M', 'F', 'other'
CREATE INDEX idx_users_gender ON users(gender);
-- PostgreSQL will often ignore this index

Right:

-- Use partial index for rare values
CREATE INDEX idx_users_gender_other ON users(gender) WHERE gender = 'other';

Index Best Practices for Production Systems

Modern PostgreSQL versions introduce features that make indexes more powerful and efficient. Here are essential techniques for production systems.

• Partial Indexes to Reduce Bloat

Partial indexes include only rows matching a WHERE clause. This dramatically reduces index size and maintenance overhead for queries targeting specific subsets.

-- Index only active orders (skip archived)
CREATE INDEX idx_orders_active ON orders(customer_id, created_at)
WHERE status IN ('pending', 'processing', 'shipped');

-- Index only non-null values
CREATE INDEX idx_users_deleted ON users(deleted_at)
WHERE deleted_at IS NOT NULL;

-- Index only recent data
CREATE INDEX idx_events_recent ON events(user_id)
WHERE created_at > NOW() - INTERVAL '90 days';

Benefit: If 5% of orders are active, partial index is 95% smaller than full index.

• Expression Indexes for Computed Values

Index the result of a function or expression to avoid runtime computation.

-- Case-insensitive email lookups
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Uses: idx_users_email_lower

-- Extract JSON field
CREATE INDEX idx_products_brand ON products((attributes->>'brand'));
SELECT * FROM products WHERE attributes->>'brand' = 'Nike';
-- Uses: idx_products_brand

-- Date truncation for grouping
CREATE INDEX idx_orders_day ON orders(DATE_TRUNC('day', created_at));
SELECT DATE_TRUNC('day', created_at), COUNT(*)
FROM orders
GROUP BY DATE_TRUNC('day', created_at);
-- Uses: idx_orders_day

• Covering Indexes (INCLUDE Clause)

Covering indexes store additional columns that aren't part of the index key. This enables index-only scans without touching the heap.

-- Include non-key columns for index-only scans
CREATE INDEX idx_orders_customer_cover ON orders(customer_id)
INCLUDE (total, status, created_at);

-- This query scans ONLY the index (no heap access)
SELECT customer_id, total, status, created_at
FROM orders
WHERE customer_id = 12345;

-- Verify with EXPLAIN
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, total, status, created_at
FROM orders
WHERE customer_id = 12345;
-- Look for: "Index Only Scan using idx_orders_customer_cover"
-- Heap Fetches: 0 (ideal)

Benefit: Reduces I/O by 50-90% for queries retrieving indexed columns.

• Concurrent Index Creation

Standard CREATE INDEX locks the table against writes. For production systems, use CONCURRENTLY to avoid downtime.

-- Non-blocking index creation (PostgreSQL 8.2+)
CREATE INDEX CONCURRENTLY idx_large_table_col ON large_table(column_name);

-- Concurrent reindex (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_large_table_col;

-- If concurrent creation fails partway through
-- Find and drop the invalid index
SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;

DROP INDEX CONCURRENTLY idx_large_table_col;

Trade-off: Concurrent creation takes roughly twice as long but doesn't block writes.

• Index Maintenance

-- Monitor index bloat
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Find unused indexes (consider dropping)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
    AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild bloated indexes
REINDEX INDEX CONCURRENTLY idx_name;

-- Update statistics after bulk operations
ANALYZE table_name;

>> Read more: How to Work with PostgreSQL in Golang using pgx Package?

Conclusion

Index selection comes down to matching your data patterns with the right structure:

• Default Choice: B-tree (Handles 80% of cases, including equality, ranges, and sorting. Start here unless you have a specific reason to use another type).
• Complex Data Types: GIN (JSONB, arrays, full-text search. Accept the write penalty for 10-100x read speedups on containment queries).
• Spatial & Ranges: GiST (Required for PostGIS. Better write performance than GIN for overlapping ranges and geometric searches).
• Massive Time-Series: BRIN (When B-tree becomes too large (100 GB+) and data is naturally ordered. Trade perfect selectivity for 1,000x space savings).
• Equality-Only: Hash (Niche use case, only when certain you’ll never need ranges/sorting and space is limited).
• Hierarchical Data: SP-GiST (IP ranges, prefix search, partitioned structures. Rarely needed, but optimal for specific patterns).

>>> Follow and Contact Relia Software for more information!