BRIN Usage

On 18/02/2016 9:34 am, "Tom Smith" <tomsmith1989sk@gmail.com> wrote:

Hi:

I feel it is a stupid question.

Can BRIN index enforce uniqueness?
My issue is
the column I'd like to apply BRIN index also needs to be unique
(think of timestamp as primary key).

Only btree supports unique.
Is there a special reason not to use btree? I'm also finding it hard to
imagine a case where a timestamp primary key is a good idea.

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

On Thu, Feb 18, 2016 at 2:14 AM, David Rowley <david.rowley@2ndquadrant.com>
wrote:

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