Re: [GENERAL] 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 signature.asc Description: Digital signature
Re: [GENERAL] BRIN Usage
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 wrote: > > On 18/02/2016 9:34 am, "Tom Smith" 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. >
Re: [GENERAL] BRIN Usage
On 18/02/2016 9:34 am, "Tom Smith" 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.
[GENERAL] BRIN Usage
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). Thanks
