4 ms·
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
by Merad 2y ago
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 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.
- westurner 2y agoWhat 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.html#DATATYPE-NUMERIC-TABLE 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 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-battle/ 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 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/generated/networkx.relabel.relabel_nodes.html https://networkx.org/documentation/stable/reference/generate...
- Merad 2y agoMany 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.
- westurner 2y agoWhich 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