B-tree, Hash, and Partial Indexes

PostgreSQL supports several index types for different use cases. B-tree (the default) is suitable for most queries involving equality, range, ordering, and NULL checks. Hash indexes are optimised for equality-only lookups and are smaller, but cannot handle ranges or ordering. GIN (Generalised Inverted Index) excels at full-text search, JSONB containment, and array membership queries. GiST (Generalised Search Tree) supports geometric types, range types, and full-text search. BRIN (Block Range Index) is very compact and works well for naturally ordered data like timestamps in append-only tables. Partial indexes only index rows satisfying a WHERE condition, making them smaller and faster for queries that always filter on that condition.

Example

-- B-tree (default) — general purpose
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Compound B-tree — supports prefix queries
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Hash — equality-only, slightly smaller than B-tree
CREATE INDEX idx_sessions_token ON sessions USING HASH (token);

-- Partial index — only index active products
CREATE INDEX idx_active_products_price
  ON products (price)
  WHERE is_active = TRUE;

-- Partial unique — only one active record per user
CREATE UNIQUE INDEX idx_one_active_session_per_user
  ON sessions (user_id)
  WHERE is_active = TRUE;

-- Covering index (INCLUDE) — include extra columns to avoid table fetch
CREATE INDEX idx_orders_covering
  ON orders (user_id, status)
  INCLUDE (total, created_at);

-- GIN index for full-text search
CREATE INDEX idx_products_fts
  ON products USING GIN (to_tsvector('english', name || ' ' || description));

-- List all indexes with size
SELECT indexname, indexdef, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'orders';

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned