BRIN index for large sequential data in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of BRIN index usage on sequential data scan

Given a table logs with a timestamp column storing sequential log entries, and a BRIN index created on timestamp, what will be the output of the following EXPLAIN query?

EXPLAIN SELECT * FROM logs WHERE timestamp BETWEEN '2024-01-01' AND '2024-01-02';

Assuming the table is very large and data is inserted in timestamp order.

PostgreSQL

CREATE TABLE logs (id SERIAL PRIMARY KEY, timestamp TIMESTAMP NOT NULL, message TEXT);
CREATE INDEX brin_timestamp_idx ON logs USING BRIN (timestamp);

EXPLAIN SELECT * FROM logs WHERE timestamp BETWEEN '2024-01-01' AND '2024-01-02';

A Index Scan using brin_timestamp_idx on logs (cost=0.00..50.00 rows=100 width=50) Index Cond: (timestamp >= '2024-01-01'::timestamp without time zone AND timestamp <= '2024-01-02'::timestamp without time zone)

B Seq Scan on logs (cost=0.00..1000.00 rows=100 width=50) Filter: (timestamp >= '2024-01-01'::timestamp without time zone AND timestamp <= '2024-01-02'::timestamp without time zone)

C Seq Scan on logs (cost=0.00..5000.00 rows=100000 width=50)

D

Bitmap Index Scan on brin_timestamp_idx  (cost=0.00..10.00 rows=100 width=50)
Bitmap Heap Scan on logs  (cost=10.00..100.00 rows=100 width=50)  Recheck Cond: (timestamp >= '2024-01-01'::timestamp without time zone AND timestamp <= '2024-01-02'::timestamp without time zone)

💡 Hint

BRIN indexes are used with Bitmap Index Scan and Bitmap Heap Scan for large sequential data.

BRIN indexes in PostgreSQL are used to quickly filter large ranges by summarizing block ranges. The planner uses Bitmap Index Scan on the BRIN index followed by Bitmap Heap Scan on the table to fetch matching rows efficiently. Direct Index Scan is not used because BRIN indexes are block summaries, not exact pointers to rows.

🧠 Conceptual

intermediate

Why use BRIN indexes for large sequential data?

Which of the following best explains why BRIN indexes are suitable for very large tables with sequentially inserted data?

A BRIN indexes summarize ranges of pages, reducing index size and improving performance on sequential data.

B BRIN indexes store exact row locations for every value, making lookups very fast.

C BRIN indexes require no maintenance and automatically sort data physically on disk.

D BRIN indexes duplicate the entire table data in compressed form for faster access.

💡 Hint

Think about how BRIN indexes summarize data instead of storing exact row pointers.

BRIN indexes store summaries (min/max values) for ranges of physical pages, which makes them very small and efficient for large tables where data is naturally ordered, like sequential timestamps. They do not store exact row locations.

📝 Syntax

advanced

Correct syntax to create a BRIN index with custom pages per range

Which option correctly creates a BRIN index on the event_time column of the events table with a pages_per_range setting of 16?

A CREATE INDEX brin_event_time_idx ON events USING BRIN (event_time) WITH (pages_per_range := 16);

B CREATE INDEX brin_event_time_idx ON events USING BRIN (event_time) WITH PAGES_PER_RANGE = 16;

C CREATE INDEX brin_event_time_idx ON events USING BRIN (event_time) WITH (pages_per_range = 16);

D CREATE INDEX brin_event_time_idx ON events USING BRIN (event_time) WITH (pages_per_range = '16');

💡 Hint

The syntax for BRIN options uses WITH and parentheses with key = value pairs.

The correct syntax to specify BRIN index options is WITH (option_name = value). The value can be numeric without quotes. Option C uses the correct syntax.

🔧 Debug

advanced

Identify the cause of slow queries despite BRIN index

A developer created a BRIN index on a large sales table's sale_date column. However, queries filtering by sale_date are still slow and do not use the index. Which is the most likely cause?

A The <code>sale_date</code> column has many NULL values, and BRIN indexes do not index NULLs.

B The table data is not physically ordered by <code>sale_date</code>, so BRIN summaries are ineffective.

C The BRIN index was created without specifying the <code>pages_per_range</code> option, causing it to be ignored.

D BRIN indexes cannot be used on date or timestamp columns.

💡 Hint

Think about how BRIN indexes rely on physical ordering of data.

BRIN indexes work best when the data is physically ordered or clustered by the indexed column. If the data is randomly ordered, the BRIN index summaries cover wide ranges and are less selective, causing the planner to ignore the index.

❓ optimization

expert

Optimizing BRIN index performance for a large log table

You have a large logs table with a log_time timestamp column. Data is inserted sequentially by log_time. You want to optimize BRIN index performance for queries filtering recent logs. Which combination of actions is best?

A Create a BRIN index on <code>log_time</code> with a small <code>pages_per_range</code> value and run <code>CLUSTER</code> on the table by <code>log_time</code> regularly.

B Create a BRIN index on <code>log_time</code> with a small <code>pages_per_range</code> value and periodically run <code>VACUUM FULL</code> to reorder data physically.

C Create a BRIN index on <code>log_time</code> with default <code>pages_per_range</code> and rely on autovacuum to maintain physical order.

D Create a BRIN index on <code>log_time</code> with a large <code>pages_per_range</code> value and run <code>CLUSTER</code> on the table by <code>log_time</code> regularly.

💡 Hint

Consider how physical ordering and pages per range affect BRIN index efficiency.

A smaller pages_per_range makes BRIN index summaries more precise but larger. Regularly clustering the table by log_time physically orders data, improving BRIN index effectiveness for recent data queries.