GUIDs Are Not the Only Answer

Re:GUIDs Are Not the Only Answer by dehrmann on Wednesday January 06, 2021 @03:06AM (#25655602)

Since 10 digits puts us in billions, you're really asking "why would someone do 'CREATE TABLE ... id INT AUTO_INCREMENT' when they could use a BIGINT?" These days, there's rarely a reason not to use a BIGINT, but I also have a little trouble faulting someone for thinking 2B would be enough when they're currently at 10k.

[ Parent | on HN ]

Re:GUIDs Are Not the Only Answer by mohaine on Wednesday January 06, 2021 @06:43PM (#25664428)

Please don't use BigInt. It is the same size (usually 16 bytes) as GUID/UUID in binary but has lots of drawbacks and no real upside other than they are easier to type in when they are still small.

1) The ids are clustered around the starting point so an invalid join will return data when it shouldn't. MIN_ID exists for almost every table/object type. With a well designed ID this shouldn't return any data.

2) BigInts are different sizes on different platforms. I've worked at a place 1 order of magnitude (base2) from overflowing JS Number and breaking most client code. This was due to bug that consuming a Seq ids quickly but there was no going back smaller.

3) Will often be coded client side as a Int and no one will notice until you grow large enough to overflow INT

Really, just avoid any sequential, numeric IDs and you will be good, IMO.

[ Parent | on HN ]

Re:GUIDs Are Not the Only Answer by nextaccountic on Thursday January 07, 2021 @12:11AM (#25667503)

> Really, just avoid any sequential, numeric IDs and you will be good, IMO.

Sequential ids make better as database indexes. Do you propose using both a uuid for external purposes and a sequential id as the primary key?

Or something like uuid v6? http://gh.peabody.io/uuidv6/ (which isn't widely supported)

[ Parent | on HN ]

Re:GUIDs Are Not the Only Answer by recursive on Wednesday January 06, 2021 @11:23PM (#25667257)

Postgres and SQL Server both have 8 byte BigInts. Is there a database that has bigger ones? I haven't heard of it.

[ Parent | on HN ]

Re:GUIDs Are Not the Only Answer by koolba on Wednesday January 06, 2021 @11:32PM (#25667299)

Bigint is a 64-but (8 byte) signed integer. It’s half the size of a UUID and using a sequence rather than random ids leads to significantly less WAL ok Postgres when full page writes are enabled.

[ Parent | on HN ]