Comments on: BRIN Index for PostgreSQL: Don’t Forget the Benefits https://www.percona.com/blog/brin-index-for-postgresql-dont-forget-the-benefits/ Fri, 29 Dec 2023 00:59:43 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: Jobin Augustine https://www.percona.com/blog/brin-index-for-postgresql-dont-forget-the-benefits/#comment-10971355 Thu, 31 Oct 2019 05:22:39 +0000 https://www.percona.com/blog/?p=58868#comment-10971355 That is an excellent piece of Information. Thank you for that.

]]> By: Jobin Augustine https://www.percona.com/blog/brin-index-for-postgresql-dont-forget-the-benefits/#comment-10971354 Thu, 31 Oct 2019 05:19:53 +0000 https://www.percona.com/blog/?p=58868#comment-10971354 No. BRIN does not support unique indexes. It is lossy in nature.

]]> By: Christopher Hamel https://www.percona.com/blog/brin-index-for-postgresql-dont-forget-the-benefits/#comment-10971312 Thu, 24 Oct 2019 19:14:33 +0000 https://www.percona.com/blog/?p=58868#comment-10971312 Can a primary key use a BRIN index in lieu of a Btree?

]]> By: Spindel https://www.percona.com/blog/brin-index-for-postgresql-dont-forget-the-benefits/#comment-10971005 Thu, 18 Jul 2019 16:13:13 +0000 https://www.percona.com/blog/?p=58868#comment-10971005 In our usecases we found that adding a Btree index on the columns, then performing a CLUSTER using the Btree index, and finally replacing it with a BRIN index was the way to get long-term storage and performance efficiency using Brin indexes.

Brin can have horrible perfomance if the on-disk layout is adversial, so it’s use-case is quite limited as that, and since it cannot be used as a index for CLUSTER.

A typical example is time-series data:
id, timestamp, [value, value…]

Where queries often are of the form id=X timestamp between X, y but where the natural insert-order of things will cause data to be fragmented for such queries, making a BRIN index in “natural order” horribly inefficient. doing a one-time “index-cluster-index” operation can then lay it out so the BRIN index is instead incredibly efficient.

It needs to be taken in account when picking indexes.

]]> By: Jobin Augustine https://www.percona.com/blog/brin-index-for-postgresql-dont-forget-the-benefits/#comment-10971004 Thu, 18 Jul 2019 14:01:15 +0000 https://www.percona.com/blog/?p=58868#comment-10971004 I agree with you Robert, that was a bit stretched statement. if there is good correlation among values sitting in adjacent pages, Nothing should be stopping us from using BRIN. I am removing that statement.
Thank you for your valuable comment.

]]> By: Robert https://www.percona.com/blog/brin-index-for-postgresql-dont-forget-the-benefits/#comment-10971003 Wed, 17 Jul 2019 23:33:49 +0000 https://www.percona.com/blog/?p=58868#comment-10971003 > it is meaningful to have only one BRIN index on a table

I think that’s going a bit far. There are plenty of cases where columns have strong correlation with each other, in which case BRIN indexes on either should work pretty well together, no?

And I suspect there are many cases where, even with less-correlated data, the performance can still be an improvement over a sequential scan.

]]> By: Rahmat Ihsan https://www.percona.com/blog/brin-index-for-postgresql-dont-forget-the-benefits/#comment-10971002 Wed, 17 Jul 2019 23:09:46 +0000 https://www.percona.com/blog/?p=58868#comment-10971002 Is there a feature in mysql similar to this?

]]> By: Jobin Augustine https://www.percona.com/blog/brin-index-for-postgresql-dont-forget-the-benefits/#comment-10970991 Wed, 17 Jul 2019 09:05:02 +0000 https://www.percona.com/blog/?p=58868#comment-10970991 Thank you for your valuable comment.
I shall publish benchmark results on the same soon.

]]> By: Radu https://www.percona.com/blog/brin-index-for-postgresql-dont-forget-the-benefits/#comment-10970990 Wed, 17 Jul 2019 08:19:31 +0000 https://www.percona.com/blog/?p=58868#comment-10970990 Can you include some tests that consider updates? Last time I’ve checked BRIN indexes were showing great performance for append only tables but any update activity killed performance.

]]>