Re: BRIN Usage

On 2016-02-18 13:37:37 -0500, Tom Smith wrote:
> it is for reducing index size as the table become huge. 
> sorry for confusion, by timestamp, I meant a time series number, not the sql
> timestamp type.
> I need the unique on the column to ensure no duplicate,   but the btree index
> is getting
> huge so BRIN seems to solve problem but can not ensure unique

If it is getting huge, then this is because there are a large number of
timestamps. If you want an index to ensure uniqueness, it will have to
store every value. I don't think there's a way around that.

With a BRIN index, you would only get a list of page ranges which could
possibly contain the new value. All these pages would then have to be
scanned sequentially to make sure it isn't already there. That could be
implemented, but it would make inserts very slow - I don't think you
would want that on a huge table even if postgres implemented it.

hp