PostgresqlHow-ToBeginner · 4 min read

How to Create BRIN Index in PostgreSQL: Syntax and Examples

To create a BRIN index in PostgreSQL, use the CREATE INDEX statement with USING brin. This type of index is efficient for very large tables with naturally ordered data.

📐

Syntax

The basic syntax to create a BRIN index in PostgreSQL is:

CREATE INDEX: Command to create an index.
index_name: Name you assign to the index.
ON table_name: Specifies the table to index.
USING brin: Specifies the BRIN index type.
(column_name): The column(s) to index.

sql

CREATE INDEX index_name ON table_name USING brin (column_name);

💻

Example

This example creates a BRIN index on the created_at column of a large events table. BRIN indexes work well when the column data is naturally sorted, like timestamps.

sql

CREATE TABLE events (
  id serial PRIMARY KEY,
  created_at timestamp NOT NULL,
  description text
);

-- Insert sample data
INSERT INTO events (created_at, description)
SELECT now() - (interval '1 second' * generate_series(1, 1000000)), 'Event ' || generate_series(1, 1000000);

-- Create BRIN index
CREATE INDEX events_created_at_brin_idx ON events USING brin (created_at);

Output

CREATE TABLE INSERT 0 1000000 CREATE INDEX

⚠️

Common Pitfalls

Common mistakes when creating BRIN indexes include:

Using BRIN on small tables where it offers no benefit.
Indexing columns with random or unsorted data, which reduces BRIN effectiveness.
Not analyzing the table after bulk inserts, which can affect index performance.

Always consider if your data is naturally ordered before choosing BRIN.

sql

/* Wrong: BRIN on unsorted column */
CREATE INDEX idx_random_brin ON events USING brin (description);

/* Right: Use BRIN on sorted timestamp column */
CREATE INDEX idx_created_at_brin ON events USING brin (created_at);

📊

Quick Reference

Clause	Description
CREATE INDEX index_name	Starts index creation with a chosen name
ON table_name	Specifies the table to index
USING brin	Chooses BRIN as the index type
(column_name)	Specifies the column(s) to index
Optional: WITH (pages_per_range = value)	Adjusts BRIN range size for performance tuning

✅

Key Takeaways

Use BRIN indexes for very large tables with naturally ordered data.

Create a BRIN index with: CREATE INDEX index_name ON table_name USING brin (column_name);

Avoid BRIN on small or unsorted data columns for best performance.

Consider tuning BRIN with the pages_per_range option for large datasets.

Always analyze your table after bulk data changes to keep indexes efficient.