Hey Nikhil, thanks for reading. That's a good callout. However, for most scenarios, you' want to generate an ID yourself instead of relying on the database sequence, in which case it would be deterministically available in the app memory before you write the record, say a UUID or better ksuid.

To explain this, you must realise that relying on database sequences is inherently broken for two major reasons.

I) Sequences might not survive restarts and can be lost. For example, here's a bug in MySQL (< v8.0) that messes up the sequence. You don't want to depend on vendor implementation to get guarantees on this. If the autoincrement sequence pointer is lost, new inserts would be blocked.

https://bugs.mysql.com/bug.php?id=199

For more details, you can check out the amazing article by my favourite database blogger https://www.burnison.ca/notes/fun-mysql-fact-of-the-day-auto_increment-is-_mostly_-monotonic

II) Using auto sequences makes sharding almost impossible. If the table grows so big that you want to shard it based on some identifier, you most likely can't because there's no guarantee of two sequences being unique across the new shards i.e two sharded tables might carry the same sequence because sequence is locally persisted and generated.

===========

tl;dr No non trivial production use case should be using database sequences / auto increments

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Aman Garg

Aman Garg

More from Medium

CS373 Spring 2022: Daniel Fernandez

Dopamine and Its Role In Addiction

CS371p Spring 2022: Christopher Carrasco — Week 6

Bullet Club for Life: Listing Every Member in the Faction’s History (3/4)