Hacker News new | past | comments | ask | show | jobs | submit login

The question is the same; why would you use bigint instead of the native UUID type?

Why does OT compare text and UUID instead of char(32) and UUID?

What advantage would there be for database abstraction libraries like SQLalchemy and Django to implement the UUID type with bigint or bigserial instead of the native pg UUID type?




Best practice in Postgres is to use always use the text data type and combine it with check constraints when you need an exact length or max length.

See: https://wiki.postgresql.org/wiki/Don't_Do_This#Text_storage

Also, I think you're misunderstanding the article. They aren't talking about storing a uuid in a bigint. They're talking about have two different id's. An incrementing bigint is used internally within the db for PK and FK's. A separate uuid is used as an external identifier that's exposed by your API.


What needs to be stored as text if there is a native uuid type?

Chapter 8. Data Types > Table 8.2. Numeric Types: https://www.postgresql.org/docs/current/datatype-numeric.htm... :

> bigint: -9223372036854775808 to +9223372036854775807

> bigserial: 1 to 9223372036854775807

2*63 == 9223372036854775807

Todo UUID /? postgres bigint UUID: https://www.google.com/search?q=postgres+bigint+uuid :

- UUIDs are 128 bits, and they're unsigned, so: 2*127

- "UUID vs Bigint Battle!!! | Scaling Postgres 302" https://www.scalingpostgres.com/episodes/302-uuid-vs-bigint-...

"Reddit's photo albums broke due to Integer overflow of Signed Int32" https://news.ycombinator.com/item?id=33976355#33977924 re: IPv6 addresses having 64+64=128 bits

FWIW networkx has an in-memory Graph.relabel_nodes() method that assigns ints to unique node names in order to reduce RAM utilization for graph algorithms: https://networkx.org/documentation/stable/reference/generate...


Many people store UUID's as text in the database. Needles to say, this is bad. TFA starts by proposing that it's bad, then does some tests to show why.

I'm not quite sure what all the links have to do with the topic at hand.


Which link are you concerned about the topicality of, in specific?

Shouldn't we then link to the docs on how many bits wide db datatypes are, whether a datatype is prefix or suffix searchable, whether there's data leakage in UUID namespacing with primary NIC MAC address and UUIDv7, and whether there will be overflow with a datatype less wasteful than the text datatype for uuids when there is already a UUID datatype for uuids that one could argue to improve if there is a potential performance benefit




Join us for AI Startup School this June 16-17 in San Francisco!

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: