pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndexes on UUID - Fragmentation Issue
5+ messages / 4 participants
[nested] [flat]
* Indexes on UUID - Fragmentation Issue
@ 2018-10-29 13:29 Uday Bhaskar V <uday.bhaskar579@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Uday Bhaskar V @ 2018-10-29 13:29 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Indexes on UUID - Fragmentation Issue
@ 2018-10-29 14:28 Merlin Moncure <mmoncure@gmail.com>
parent: Uday Bhaskar V <uday.bhaskar579@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Merlin Moncure @ 2018-10-29 14:28 UTC (permalink / raw)
To: uday.bhaskar579@gmail.com; +Cc: pgsql-performance
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 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.
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Indexes on UUID - Fragmentation Issue
@ 2018-10-29 14:52 Andreas Karlsson <andreas@proxel.se>
parent: Uday Bhaskar V <uday.bhaskar579@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Andreas Karlsson @ 2018-10-29 14:52 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Indexes on UUID - Fragmentation Issue
@ 2018-10-29 14:56 Uday Bhaskar V <uday.bhaskar579@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Uday Bhaskar V @ 2018-10-29 14:56 UTC (permalink / raw)
To: mmoncure@gmail.com; +Cc: pgsql-performance
We have migrated our Database from Oracle to Postgresql there because of
replication we went for UUIDs. I have C function ready, will try.
Thanks,
Uday
On Mon, Oct 29, 2018 at 7:58 PM Merlin Moncure <mmoncure@gmail.com> wrote:
> 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 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.
>
> 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Indexes on UUID - Fragmentation Issue
@ 2018-10-29 19:12 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Andreas Karlsson <andreas@proxel.se>
0 siblings, 0 replies; 5+ messages in thread
From: MichaelDBA @ 2018-10-29 19:12 UTC (permalink / raw)
To: Andreas Karlsson <andreas@proxel.se>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
or prepend the UUID with a timestamp?
Regards,
Michael Vitale
> Andreas Karlsson <mailto:andreas@proxel.se>
> 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 <mailto:uday.bhaskar579@gmail.com>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-10-29 19:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-10-29 13:29 Indexes on UUID - Fragmentation Issue Uday Bhaskar V <uday.bhaskar579@gmail.com>
2018-10-29 14:28 ` Merlin Moncure <mmoncure@gmail.com>
2018-10-29 14:56 ` Uday Bhaskar V <uday.bhaskar579@gmail.com>
2018-10-29 14:52 ` Andreas Karlsson <andreas@proxel.se>
2018-10-29 19:12 ` MichaelDBA <MichaelDBA@sqlexec.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox