Optimizing PostgreSQL chevron_right BRIN and Hash Indexes in PostgreSQL: When and Why to Use Them

BRIN and Hash Indexes in PostgreSQL: When and Why to Use Them

calendar_today schedule 8 min read

Learning Objectives: By the end of this lesson, you will be able to:

  • Explain what BRIN and Hash indexes are and how they work in PostgreSQL
  • Identify scenarios where BRIN or Hash indexes provide performance benefits (and their limitations)
  • Design, create, and analyze BRIN and Hash indexes for real-world queries

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever wondered how to index a table with hundreds of millions of rows efficiently? Or why hash indexes are rarely used in PostgreSQL, even though they sound fast?

Lesson Overview

  • Duration: 90 minutes
  • Prerequisites: Understanding of B-Tree, GIN, and GiST indexes; SQL and PostgreSQL basics
  • Materials Needed: PostgreSQL instance, SQL client (psql, DBeaver, pgAdmin, etc.)

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

  1. What are BRIN and Hash indexes and how do they work?
  2. Use cases, benefits, and limitations of BRIN and Hash indexes
  3. Designing and testing BRIN and Hash indexes for real queries
  4. Hands-on practice and exercises
  5. Common pitfalls and how to avoid them

Success Criteria:

By the end of this session, you should be able to design, create, and analyze BRIN and Hash indexes for a variety of query patterns, and understand their impact on performance and storage.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

What is a BRIN Index?

A BRIN (Block Range Index) is designed for very large tables where the physical order of rows correlates with the indexed column (e.g., timestamps, IDs). Instead of indexing every row, BRIN stores summary information for ranges of blocks, making it extremely space-efficient.

Real-World Analogy: Imagine a library where books are shelved in order by year. Instead of indexing every book, you keep a note of the first and last year on each shelf. To find books from 1995, you only check shelves whose range includes 1995.

Why This Matters: BRIN indexes are tiny compared to B-Tree, making them ideal for huge tables where traditional indexes would be too large or slow to maintain.

What is a Hash Index?

A Hash index uses a hash function to map values to locations in the index. It is designed for fast equality lookups (WHERE col = value), but not for range queries or ordering.

Real-World Analogy: Think of a phone book organized by a hash of the name, not alphabetically. You can find a specific name quickly, but you can’t easily find all names in a range.

Why This Matters: Hash indexes can be fast for equality, but have significant limitations in PostgreSQL and are rarely the best choice.

Key Terminology

  • BRIN Index: Index summarizing value ranges for blocks of rows
  • Block Range: A group of consecutive table blocks summarized by a BRIN index entry
  • Hash Index: Index using a hash function for fast equality lookups
  • Correlation: The degree to which physical row order matches logical order

4. Detailed Explanation and Examples (25-30 minutes)

Understanding BRIN and Hash Indexes in PostgreSQL

BRIN indexes are best for very large, append-only tables where data is naturally ordered (e.g., logs, time series). Hash indexes are only useful for equality queries and have important limitations (e.g., not WAL-logged before PostgreSQL 10, not used for ordering or range queries).

Practical Applications

  • BRIN: Indexing a timestamp column in a huge log table
  • Hash: Fast equality search on a column with high cardinality (rarely better than B-Tree)

Common Misconceptions

  1. Misconception: BRIN indexes are as fast as B-Tree for all queries. Reality: BRIN is only efficient when data is well-correlated; otherwise, it may return many false positives.

  2. Misconception: Hash indexes are always faster for equality. Reality: B-Tree is usually just as fast and much more flexible.

5. Live Coding Demonstration (20-25 minutes)

Example 1: Creating and Using a BRIN Index

-- Create a large table with a timestamp column
CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    event_time TIMESTAMP NOT NULL,
    message TEXT
);

-- Insert sample data (simulate millions of rows for real use)
INSERT INTO logs (event_time, message)
SELECT NOW() - (g || ' seconds')::interval, 'Event ' || g
FROM generate_series(1, 100000) AS g;

-- Create a BRIN index on event_time
CREATE INDEX idx_logs_event_time_brin ON logs USING BRIN (event_time);

-- Query using the BRIN index
EXPLAIN ANALYZE SELECT * FROM logs WHERE event_time BETWEEN NOW() - INTERVAL '1 hour' AND NOW();

Explanation:

  • The BRIN index is tiny and efficient for time-correlated data.

Example 2: BRIN vs. B-Tree Size Comparison

-- Create a B-Tree index for comparison
CREATE INDEX idx_logs_event_time_btree ON logs (event_time);

-- Compare index sizes
\di+ idx_logs_event_time*

Key Points:

  • BRIN indexes are much smaller than B-Tree for large tables.

Example 3: Creating and Using a Hash Index

-- Create a table for hash index demo
CREATE TABLE hash_demo (
    id SERIAL PRIMARY KEY,
    code TEXT NOT NULL
);

INSERT INTO hash_demo (code)
SELECT md5(g::text) FROM generate_series(1, 10000) AS g;

-- Create a hash index
CREATE INDEX idx_hash_demo_code_hash ON hash_demo USING HASH (code);

-- Query using the hash index
EXPLAIN ANALYZE SELECT * FROM hash_demo WHERE code = md5('5000');

Debugging Demonstration

Show a common mistake: using BRIN on uncorrelated data or Hash for range queries.

-- BRIN on random data (ineffective)
CREATE TABLE random_data (id SERIAL, value INT);
INSERT INTO random_data (value) SELECT (random()*100000)::int FROM generate_series(1, 100000);
CREATE INDEX idx_random_data_value_brin ON random_data USING BRIN (value);
EXPLAIN ANALYZE SELECT * FROM random_data WHERE value BETWEEN 100 AND 200;
-- The index is not efficient.

-- Hash index for range query (not used)
EXPLAIN ANALYZE SELECT * FROM hash_demo WHERE code > md5('5000');

6. Hands-On Practice (30-40 minutes)

Exercise 1: Basic Practice (10 minutes)

Objective: Create and use a BRIN index for a large, time-ordered table.

Instructions:

  1. Create a table called events with columns id, created_at, and description.
  2. Insert 50,000 sample events with sequential timestamps.
  3. Create a BRIN index on created_at.
  4. Query for recent events and compare performance with and without the index.

Starter Code:

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL,
    description TEXT
);

INSERT INTO events (created_at, description)
SELECT NOW() - (g || ' seconds')::interval, 'Event ' || g
FROM generate_series(1, 50000) AS g;

Solution:

CREATE INDEX idx_events_created_at_brin ON events USING BRIN (created_at);
EXPLAIN ANALYZE SELECT * FROM events WHERE created_at > NOW() - INTERVAL '10 minutes';

Exercise 2: Intermediate Challenge (15 minutes)

Objective: Compare BRIN and B-Tree indexes for size and performance.

Requirements:

  • Create a B-Tree index on created_at.
  • Compare index sizes and query performance for recent and random queries.
  • Observe when BRIN is more efficient.

Hints:

  • Use \di+ to compare index sizes.
  • Use EXPLAIN ANALYZE for query plans.

Exercise 3: Creative Application (15 minutes)

Objective: Design an index strategy for a huge, append-only table.

Challenge: Suppose you have a table with 100 million sensor readings, each with a timestamp and value. What index would you use for time-based queries? Test your strategy and measure performance.

Extension Ideas:

  • Try different BRIN page sizes (using the pages_per_range option).
  • Experiment with BRIN on other correlated columns.

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

  1. Problem: Using BRIN on uncorrelated or small tables. Cause: BRIN returns too many false positives or is unnecessary. Solution: Use BRIN only for large, well-ordered tables.

  2. Problem: Using Hash indexes for range or ordering queries. Cause: Hash cannot support these queries. Solution: Use B-Tree for range and ordering; Hash only for equality.

Debugging Tips

  • Use EXPLAIN and EXPLAIN ANALYZE to check index usage.
  • Compare index sizes with \di+ in psql.
  • Use pg_stat_user_indexes to monitor effectiveness.

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

  • Use BRIN for massive, append-only, time-ordered tables.
  • Use B-Tree for most equality and range queries.
  • Use Hash only for special cases where B-Tree is not suitable.
  • Regularly review and drop unused indexes.

Next Steps in Your Learning Journey

  • Learn about partitioning strategies for huge tables
  • Explore index maintenance and vacuuming
  • Practice analyzing and tuning real-world queries

9. Summary and Review (5 minutes)

Key Takeaways

  1. BRIN indexes are space-efficient for huge, well-ordered tables.
  2. Hash indexes are rarely needed; B-Tree is usually better.
  3. Always analyze and test your indexes with real queries and data.

Quick Review Questions

  1. When should you use a BRIN index? When should you use a Hash index?
  2. What are the main limitations of each index type?
  3. How can you verify if a BRIN or Hash index is being used by a query?

For Instructors

Teaching Tips

  • Use live demos to show the impact of BRIN and Hash indexes on index size and query speed.
  • Encourage students to experiment with different data distributions and index types.
  • Discuss trade-offs between index size, build time, and query performance.

Assessment Ideas

  • Quiz: Identify which queries would benefit from BRIN or Hash indexes.
  • Project: Optimize a sample database for a set of time-series or equality queries.
  • Peer review: Analyze and critique each other’s index strategies.

Adaptation Notes

  • For beginners, focus on BRIN for time-series and append-only tables.
  • For advanced students, introduce Hash and discuss its limitations.
  • In online settings, use shared SQL sandboxes or cloud PostgreSQL instances for exercises.