Shulou(Shulou.com)06/01 Report--

This article will explain in detail what the BRIN index refers to in PostgreSQL. The content of the article is of high quality. Therefore, Xiaobian shares it with you as a reference. I hope that after reading this article, you will have a certain understanding of relevant knowledge.

As we all know, PostgreSQL various plug-ins data volume and its bottomless feature richness, by the user admiration. PostgreSQL has an index, BRIN is not used by many people, and you may have heard the name of this index for the first time. BRIN may have a slightly different reputation than index types like GIST and GIN. So today we're going to see what BRIN can do and why.

Let's start by creating a table

Insert some data (for the Brin index, of course)

Let's look at the query below to see how it looks when querying time-type data without an index.

From the figure below, we can compare the cost gap between adding indexes and full table scanning.

As you can see from the figure above, the query speed is improved after adding the index, but our key point is not here, we want to compare the difference between BTREE index and BRIN index.

We create indexes on the same fields of the same table, but different types. From the graph we can see that the time to create the two indexes is not the same, brin index is about 12 times faster than BTREE index.

Let's compare the size of the two indexes. After reading the picture below, I guess your mouth should not be closed, and maybe you will make some sounds. It's true that the BRIN index's size is super-small, and for a reason.

Let's continue, then such a small index, compared to BTREE index, in the end query efficiency can meet the requirements?

We can see that by default, the BTREE index is used by default, in cases where both indexes exist.

Compared with BRIN index, although BRIN index is a little slower than BTREE index, but compared with the space occupied by index, BTREE takes up 129MB and BRIN takes up 56KB, you can realize that your second big mouth, BRIN index is really powerful.

Having said that, let's talk about how BRIN indexing is done, greatly reducing the storage space of the index, and still ensuring the query rate in the ultra-high index query.

BRIN index is a lossy index, this index is referred to as Block Range Index, and the main reason for BRIN index is also for some "super large table index", imagine, you have a table of 600 million records, it is likely that your index is a few G, and such an index in the query, in fact, with the increase in the amount of data, performance is linear decline.

BRIN therefore stores data is not ordinary index BTREE data, but storage data, as well as related data page information, through these information, greatly reducing the storage space of data, and in the case of determining whether the data meets the conditions, than BTREE index to pay more filtering and comparison process, but relative to his super high cost performance, for large tables, ordered data index establishment, BRIN index is worth considering and using.

Of course, if one asks, does BRIN index involve anything like compression ratios?

pages_per_range, if you adjust the range of each page too much, the loss will be greater, so we can also balance "compression" and accuracy.

Finally, let's review the main scenarios and purposes for using BRIN indexing

1 Index performance issues for large tables

2 The data indexed in the table should be sequential, such as dates, or some scenarios that can be sequenced (in fact, sequential databases conform to this type)

3 Scenarios sensitive to index footprint.

About PostgreSQL BRIN index refers to what is shared here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.