Generating primary keys: By Pulling from a stock of Ids, kept at the application layer of web application

I am building a web application using a distrbuted NoSQL DB. However, for generating Ids for the new e开发者_开发问答ntities, I am using MySQL as Flickr's ticket server

I just stuck across an idea of keeping the stock of Ids at the application layer, so that newer entities may be easily allotted ids in a fast manner, without the need for fetching from DB, one by one, for each of them. Instead I can just pull a stock of Ids from the DB say around 1000, and use them uptil stack reaches a certain limit of remaining Ids, when I'll pull more of Ids from DB to fill the stack again.

Your ideas and thoughts upon this appreciated.

---

That's a good idea unless you need your ids to be sequential, and that it is ok that some ids are never used.

e.g. the SAP ERP does this (configurably) for what it calls "number ranges" when they have no ordering requirement and can have "holes". Each application server will request a set of keys rather than just one, and serve them out of this cache. It refills its cache whenever necessary. This saves quite some round trips and locking on the DB.

If an app server goes down (or the system is stopped), whatever ids that were present in the cache but not yet handed out are lost (i.e. will never be used). This is sometimes fine if all you need is a unique id. Not fine at all if you can't have holes in the sequences (which happens for business document numbers (like invoice numbers for example) in some countries).

---

While this will work, you're probably adding unnecessary complexity.

Universally Unique IDs are designed for exactly this case. A previous answer here on SO contains a pure-PHP UUIDv4 generator, if you don't feel like using the UUID PECL extension.

The major downside of UUIDs is that they're ugly, though they can be represented in other forms that are merely freakishly long.