Rob Conery writes about PostgreSQL, Node.js, and .NET development, and is the author of The Imposter's Handbook.
HN commenters list him among developer blogs, link his Postgres id generator, and note his correction to a RethinkDB performance claim.
> In what context would a primary key change, even when sharding?
The only time I've seen that happen is when a DBA evangelized for just using 'natural' keys, which in this case were slugs based on the item name. The problem came when users decided they wanted to rename things and then wondered why the IDs they were seeing in URLs didn't match the new names.
I'm not a believer in natural keys anymore.
> If you need sorting in your table, provide some kind of indexed timestamp.
A v4 UUID contains a timestamp. So it seems a bit wasteful to add the separate timestamp column. Why not just use the timestamp built into the ID? This is what MongoDB's internally-generated IDs do. (Though they're not quite UUIDs.)
There's a catch with v4 UUIDs though: the timestamp portion of the ID is not the most significant digits, so a simple ORDER BY doesn't do what you want.
I like the solution developed by Instagram, which Rob Conery discusses at the link below. It uses a pretty simple plpgsql function to generate a 64 bit integer based on the current time, a shard ID, and a per-shard sequence. This lets you generate 1024 IDs per second per shard. It also puts the timestamp portion of the ID first, so if all the shards' data ends up in Hadoop or something you can order consistently across the whole set.
http://rob.conery.io/2014/05/29/a-better-id-generator-for-postgresql/
Hmm, going to say a hard no on naively preferring UUIDs. It's a little bit of extra work to go with the instagram style approach [0] of using many logical postgres shards with a custom id function that implements twitter snowflake style IDs, but it's an elegant, scalable and operationally simple solution. I am very fond of it.
[0] http://rob.conery.io/2014/05/29/a-better-id-generator-for-postgresql/
Well, the easy answer is to stop volunteering and attending family/social events[1]. Check out this[2] blog post for other possible ideas.
[1] Number 3 here: http://www.aaronsw.com/weblog/morebooks
[2] http://rob.conery.io/2015/10/06/how-to-learn-a-new-programming-language-while-maintaining-your-day-job-and-still-being-there-for-your-family/
For something a little more practical, I recently came across this postgres id generator[1], which Ive been playing around with as a solution for ordered, scalable bigint ids (that take up less space than standard uuids).
[1] https://rob.conery.io/2014/05/28/a-better-id-generator-for-postgresql/
24:08 Minutes in
https://github.com/robconery/pg_docs_api
references:
http://rob.conery.io/2015/08/20/designing-a-postgresql-document-api/
related:
http://rob.conery.io/2015/03/13/bringing-the-power-of-postgres-to-nodejs/
https://github.com/robconery/massive-js
Why not use Twitter's snowflake id? Instagram blogged about a Postgres function that generates them
http://rob.conery.io/2014/05/28/a-better-id-generator-for-postgresql/