May be a good idea to use snowflake as the primary key?

[dongfengweixiao]

RT

https://github.com/agnirudrasil/avault/blob/c6ca7037a5a99d97fcf43eb6b9ef935439171c02/backend/app/api/utils/snowflake.py

[jonra1993]

Hello, @dongfengweixiao what advantage do you expect of using snakeflake vs uuid?

[dongfengweixiao]

Hello @jonra1993 , the pros and cons can be read here: https://medium.com/geekculture/the-wild-world-of-unique-identifiers-uuid-ulid-etc-17cfb2a38fce.

Compared with the uuid v1 and the uuid v4, the v4 version has a very low probability of duplication.

[jonra1993]

Hello @dongfengweixiao thanks for the link I am going to check it

[bazylhorsey]

If this is done there is no need for the created_at column in any tables it is used, as it would be known by decoding the snowflake

[dongfengweixiao]

If this is done there is no need for the created_at column in any tables it is used, as it would be known by decoding the snowflake

Bad idea. All field serves the business logic. The id field should only be used to mark uniqueness. Do not use the id field for additional purposes.

[jonra1993]

@dongfengweixiao @bazylhorsey I agree with both of you that using an Id like a snowflake or another timestamp base Id could be beneficial for better ordering, improving indexing, reducing the database size, and potentially reducing id generation time if it is less than 128 bits and maybe the created_at column can be removed. Currently, I am doing some research and testing so in a next commit this will be implemented.

References:
https://medium.com/geekculture/the-wild-world-of-unique-identifiers-uuid-ulid-etc-17cfb2a38fce
https://medium.com/double-pointer/system-design-interview-scalable-unique-id-generator-twitter-snowflake-or-a-similar-service-18af22d74343
https://itnext.io/choosing-the-right-data-type-means-of-generating-unique-primary-keys-d7aac92968c6
https://blog.devgenius.io/analyzing-new-unique-identifier-formats-uuidv6-uuidv7-and-uuidv8-d6cc5cd7391a
https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-04.html

Implementations
https://github.com/agnirudrasil/avault/blob/c6ca7037a5a99d97fcf43eb6b9ef935439171c02/backend/app/api/utils/snowflake.py
https://github.com/SawdustSoftware/simpleflake/blob/master/simpleflake/simpleflake.py
https://github.com/sony/sonyflake
https://github.com/ergeon/python-flax-id
https://github.com/AmreeshTyagi/goldflake
https://github.com/oittaa/uuid6-python
https://github.com/uuid6/uuid6-ietf-draft

[bazylhorsey]

@dongfengweixiao may have a point, use your best judgment

[jonra1993]

Hello guys, after testing and looking to make the project simple to use and maintain, I have used UUID7 as the database's primary key and added some information about it on the README file including references to alternatives of IDs.

[dongfengweixiao]

Hello guys, after testing and looking to make the project simple to use and maintain, I have used UUID7 as the database's primary key and added some information about it on the README file including references to alternatives of IDs.

GREAT!