search

jonra1993 / fastapi-alembic-sqlmodel-async

This is a project template which uses FastAPI, Pydantic 2.0, Alembic and async SQLModel as ORM. It shows a complete async CRUD using authentication and role base access control.
MIT License
964 stars 150 forks source link

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

Closed dongfengweixiao closed 1 year ago

dongfengweixiao commented 2 years ago

RT

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

jonra1993 commented 2 years ago

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

dongfengweixiao commented 1 year ago

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 commented 1 year ago

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

bazylhorsey commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

@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 commented 1 year ago

@dongfengweixiao may have a point, use your best judgment

jonra1993 commented 1 year ago

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 commented 1 year ago

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!