Re: UUID or auto-increment - Mailing list pgsql-general

From Israel Brewster
Subject Re: UUID or auto-increment
Date
Msg-id 6A5ADFD3-AF87-4DFF-AA71-5AC31ED94A07@alaska.edu
Whole thread Raw
In response to Re: UUID or auto-increment  (Stephen Frost <sfrost@snowman.net>)
Responses Re: UUID or auto-increment  (Stephen Frost <sfrost@snowman.net>)
Re: UUID or auto-increment  (Adam Brusselback <adambrusselback@gmail.com>)
Re: UUID or auto-increment  ("Peter J. Holzer" <hjp-pgsql@hjp.at>)
List pgsql-general

---
Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

On Aug 10, 2020, at 8:53 AM, Stephen Frost <sfrost@snowman.net> wrote:

Greeitngs,

* Ron (ronljohnsonjr@gmail.com) wrote:
On 8/10/20 11:38 AM, Ravi Krishna wrote:
Finally UUID results in write amplication in wal logs.  Keep that in mind
if your app does lot of writes.

Because UUID is 32 bytes, while SERIAL is 4 bytes?

and because it's random and so will touch a lot more pages when you're
using it...

I would point out, however, that using a V1 UUID rather than a V4 can help with this as it is sequential, not random (based on MAC address and timestamp + random). There is a trade off, of course, as with V1 if two writes occur on the same computer at the exact same millisecond, there is a very very small chance of generating conflicting UUID’s (see https://www.sohamkamani.com/blog/2016/10/05/uuid1-vs-uuid4/). As there is still a random component, however, this seems quite unlikely.



Avoid UUIDs if you can- map them to something more sensible internally
if you have to deal with them.

Thanks,

Stephen

pgsql-general by date:

Previous
From: Michael Nolan
Date:
Subject: Re: SQL Question about like
Next
From: Stephen Frost
Date:
Subject: Re: UUID or auto-increment