PostgreSQL 9.5: BRIN Index Maintenance using brin_summarize_new_values

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

The newly introduced BRIN (Block Range Index) Index is one my favourite index type of RDBMS system like PostgreSQL.

The BRIN index is stored the only summary of data pages instead of storing actual key data. It is 10 times faster than the normal BTREE Index.

I have already published articles on What is BRIN and How we can implement BRIN in PostgreSQL 9.5.?

Database Theory: What is BRIN (Block Range Index), How is faster than BTREE Index

PostgreSQL 9.5: Introduced BRIN – Block Range Index with Performance Report

In this post, I am sharing maintenance part of BRIN Index.

Database Administrators are doing maintenance of Indexes on a regular basis like Rebuild Index, Reorganize Index, Update Statistics of Indexes.

But these types of maintenance will not work for BRIN Indexes because BRIN Indexes are not storing any actual data and it stores only a summary of the data page.

PostgreSQL 9.5 also introduced brin_summarize_new_values() BRIN Index maintenance function.
This function is used to find a new range of pages which are not yet summarized by BRIN Index.

It performs the table scanning and creates a new summary index tuple for newly added data pages and adds into BRIN Index. It also returns a count of total newly added pages.

The syntax is: