Thread: Indexes on UUID - Fragmentation Issue

On Mon, Oct 29, 2018 at 9:18 AM Uday Bhaskar V
<uday.bhaskar579@gmail.com> wrote:
>
> Hi,
>
> I have searched in many postgres blogs for Sequential UUID generation, which can avoid Fragmentation issue.
>
> I did a POC(in postgres) with sequential UUID against Non sequential which has shown lot of different in space
utilizationand index size. Sql server has "newsequentialid" which generates sequential UUID. I have created C function
whichcan generate a sequential UUID, but I am not sure how best I can use that in postgres. 
>
> I would really like to contribute to Postgres, If I can. Please let me know your thoughts or plans regarding UUID
generation.

I think the right approach here is to build a custom extension.  There
are lots of examples of extensions within contrib and on pgxn.
https://pgxn.org/   I guess there might be some utility for this type
as UUID fragmetnation is a major problem (it's one of the reasons I
discourage the use off UUID type indexes).

merlin

On 10/29/2018 02:29 PM, Uday Bhaskar V wrote:> I have
> created C function which can generate a sequential UUID, but I am not 
> sure how best I can use that in postgres.
> 
> I would really like to contribute to Postgres, If I can. Please let me 
> know your thoughts or plans regarding UUID generation.

How is it implemented? I can personally see two ways of generating 
sequential UUID:s. Either you use something like PostgreSQL's sequences 
or you can implement something based on the system time plus some few 
random bits which means they will be mostly sequential.

It could be worth checking on the hackers mailing list if there is any 
interest in this feature, but if it works like a sequence it should also 
probably be a sequence if it is ever going to be accepted into the core.

For your own use I recommend doing like Merlin suggested and write an 
extension. As long as you know a bit of C they are easy to write.

Andreas

Andreas Karlsson

Monday, October 29, 2018 10:52 AM

On 10/29/2018 02:29 PM, Uday Bhaskar V wrote:> I have

How is it implemented? I can personally see two ways of generating sequential UUID:s. Either you use something like PostgreSQL's sequences or you can implement something based on the system time plus some few random bits which means they will be mostly sequential.

It could be worth checking on the hackers mailing list if there is any interest in this feature, but if it works like a sequence it should also probably be a sequence if it is ever going to be accepted into the core.

For your own use I recommend doing like Merlin suggested and write an extension. As long as you know a bit of C they are easy to write.

Andreas

Uday Bhaskar V

Monday, October 29, 2018 9:29 AM

Hi,

I have searched in many postgres blogs for Sequential UUID generation, which can avoid Fragmentation issue.

I did a POC(in postgres) with sequential UUID against Non sequential which has shown lot of different in space utilization and index size. Sql server has "newsequentialid" which generates sequential UUID. I have created C function which can generate a sequential UUID, but I am not sure how best I can use that in postgres.

I would really like to contribute to Postgres, If I can. Please let me know your thoughts or plans regarding UUID generation.

Regards,

Uday