UUID type for a database Primary Key

valenterry 3 years ago |

Postgres natively supports UUID, so I'm using that and guess that it will internally optimize things. Otherwise I would certainly use a different format.

ylg 3 years ago |

Unfortunately, the post mostly covers the lack of monotonicity in UUID <= 5 and only acknowledges at its end and only briefly that both UUID and GUID actually have versions that solve for exactly that. Leaving us with the post’s other, unexplored, complaint that UUIDs and GUIDs are "huge" and "take up a lot of space". Any good writing out there on considerations vis-a-vis PK column size in current RDBMs or monotonic UUID versions 6-8 as PKs?

_benedict 3 years ago | |

Even v1 can be sorted if the database is remotely smart about it - Cassandra clusters v1 UUID that are part of a clustering key (and partition keys if the ordered partitioner is used) by sorting on the time components first (and in an upcoming version by reordering the internal representation)

Since it’s less useful for Cassandra (as those conditions won’t be met in most cases), I hope other databases with more to gain have also thought to implement this when they offered UUID types.

ei8ths 3 years ago |

I'll stick with unsigned integers on the server side and I use uuid's for when it's facing a client, example a link, /page/view/<uuid> just makes sense.

wstuartcl 3 years ago | |

Seems like you are choosing the worst of both cases then. If you are effectively using uuid as a slug there are better choices out there that take less space to encode store and index.

ei8ths 3 years ago | | |

I need to keep it unique, don't uuid's stay unique? I'm all ears to changing it for a smaller size.

wstuartcl 3 years ago | | |

https://github.com/Jakeii/nanoid-postgres is an example of one way just pick a length that has enough space for your projected maxcount and ensure unique on the schema/index.