4 ms·

Well, obviously UUDs should not be stored as a string but using a proper "uuid" data type (which also makes storing them much more efficient). Then the database

Well, obviously UUDs should not be stored as a string but using a proper "uuid" data type (which also makes storing them much more efficient). Then the database will automatically reject the value with an error.

lysecret 5y ago
ah interesting, didnt even think about storing them this way makes them more efficient. Do you have any ressource on that?:)
- silvestrov 5y ago
  https://www.postgresql.org/docs/14/datatype-uuid.html https://www.postgresql.org/docs/14/datatype-uuid.html
- KronisLV 5y ago
  In case anyone needs the equivalent for MariaDB: https://mariadb.com/kb/en/uuid-data-type/ https://mariadb.com/kb/en/uuid-data-type/ Couldn't actually find something similar for MySQL apart from using BINARY as the datatype, historical sources also refer to that though are (hopefully?) out of date: https://dev.mysql.com/blog-archive/storing-uuid-values-in-mysql-tables/ https://dev.mysql.com/blog-archive/storing-uuid-values-in-my... An observation: seems like PostgreSQL is HN's darling (and maybe the industry's as a whole), i wonder why MariaDB doesn't get similar amounts of attention, apart from maybe not being as actively developed? Or maybe its relative simplicity or history works against it here?
  - hans_castorp 5y ago
    > i wonder why MariaDB doesn't get similar amounts of attention, apart from maybe not being as actively developed? I can obviously only speak for myself. But (in my opinion) Postgres simply offers the better overall package. I don't think there is a single "killer feature" that makes it better, but the sum of numerous things (JSON support, indexing, parallel query) that make it more efficient (and easier) to work with. Note that the UUID type in MariaDB is not yet part of an officially released version. 10.7 is still in preview/alpha
    - evanelias 5y ago
      MariaDB 10.7 is now GA. They've recently moved to a once-per-quarter release series model, and 10.8 is the current preview/alpha series.
  - evanelias 5y ago
    MySQL doesn't have a native uuid column type, but it does provide conversion functions UUID_TO_BIN() [1] and BIN_TO_UUID() [2]. These also have a convenient optional "time swapping" flag, to improve insertion order / btree efficiency. [1] https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid-to-bin https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functi... [2] https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_bin-to-uuid https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functi...
- BongoMcCat 5y ago
  uuid = 128-bit int for a database. the "abc-1234-abc...." is just a representation of that int. So, if we think about it as using a int-128 or a string[~25], one of them takes more space than the other. So, except for taking up more space, the question then becomes, are larger keys/indexes less efficient, I would say yes. Other people can probably explain this better than me, but at least for me, it seems intuitive that going through and comparing entries in a list of small things is easier than doing it on a list of large things.
- ciupicri 5y ago
  Why wouldn't a dedicated data type be more efficient than an improvisation with CHAR, a pair of two BIGINT/INT8, INT16* or BLOB? * Is there a SQL database with support for 128 bit integers?
  - pritambaral 5y ago
    PostgreSQL natively supports a UUID datatype. SQLite provides an official extension to add UUID support. Since you asked for "SQL database" and not really "OLTP database": ClickHouse natively supports UUID and 128-bit numeric datatypes. I think by now it also supports 256-bit.
    - ciupicri 5y ago
      You're right it, Clickhouse has UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256 [1]. [1]: https://clickhouse.com/docs/en/sql-reference/data-types/int-uint/ https://clickhouse.com/docs/en/sql-reference/data-types/int-...
      - hashimotonomora 5y ago
        What are some use cases for Int256?
        ciupicri 5y ago
        Maybe SHA-256 or BLAKE3?
    - hans_castorp 5y ago
      SQL Server also has a dedicated data type for UUIDs

lysecret 5y ago

ah interesting, didnt even think about storing them this way makes them more efficient. Do you have any ressource on that?:)

silvestrov 5y ago
https://www.postgresql.org/docs/14/datatype-uuid.html https://www.postgresql.org/docs/14/datatype-uuid.html
KronisLV 5y ago
In case anyone needs the equivalent for MariaDB: https://mariadb.com/kb/en/uuid-data-type/ https://mariadb.com/kb/en/uuid-data-type/ Couldn't actually find something similar for MySQL apart from using BINARY as the datatype, historical sources also refer to that though are (hopefully?) out of date: https://dev.mysql.com/blog-archive/storing-uuid-values-in-mysql-tables/ https://dev.mysql.com/blog-archive/storing-uuid-values-in-my... An observation: seems like PostgreSQL is HN's darling (and maybe the industry's as a whole), i wonder why MariaDB doesn't get similar amounts of attention, apart from maybe not being as actively developed? Or maybe its relative simplicity or history works against it here?
- hans_castorp 5y ago
  > i wonder why MariaDB doesn't get similar amounts of attention, apart from maybe not being as actively developed? I can obviously only speak for myself. But (in my opinion) Postgres simply offers the better overall package. I don't think there is a single "killer feature" that makes it better, but the sum of numerous things (JSON support, indexing, parallel query) that make it more efficient (and easier) to work with. Note that the UUID type in MariaDB is not yet part of an officially released version. 10.7 is still in preview/alpha
  - evanelias 5y ago
    MariaDB 10.7 is now GA. They've recently moved to a once-per-quarter release series model, and 10.8 is the current preview/alpha series.
- evanelias 5y ago
  MySQL doesn't have a native uuid column type, but it does provide conversion functions UUID_TO_BIN() [1] and BIN_TO_UUID() [2]. These also have a convenient optional "time swapping" flag, to improve insertion order / btree efficiency. [1] https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid-to-bin https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functi... [2] https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_bin-to-uuid https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functi...
BongoMcCat 5y ago
uuid = 128-bit int for a database. the "abc-1234-abc...." is just a representation of that int. So, if we think about it as using a int-128 or a string[~25], one of them takes more space than the other. So, except for taking up more space, the question then becomes, are larger keys/indexes less efficient, I would say yes. Other people can probably explain this better than me, but at least for me, it seems intuitive that going through and comparing entries in a list of small things is easier than doing it on a list of large things.
ciupicri 5y ago
Why wouldn't a dedicated data type be more efficient than an improvisation with CHAR, a pair of two BIGINT/INT8, INT16* or BLOB? * Is there a SQL database with support for 128 bit integers?
- pritambaral 5y ago
  PostgreSQL natively supports a UUID datatype. SQLite provides an official extension to add UUID support. Since you asked for "SQL database" and not really "OLTP database": ClickHouse natively supports UUID and 128-bit numeric datatypes. I think by now it also supports 256-bit.
  - ciupicri 5y ago
    You're right it, Clickhouse has UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256 [1]. [1]: https://clickhouse.com/docs/en/sql-reference/data-types/int-uint/ https://clickhouse.com/docs/en/sql-reference/data-types/int-...
    - hashimotonomora 5y ago
      What are some use cases for Int256?
      - ciupicri 5y ago
        Maybe SHA-256 or BLAKE3?
  - hans_castorp 5y ago
    SQL Server also has a dedicated data type for UUIDs