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