Обсуждение: 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.
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.
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