Hacker News Reader

Comment by endymi0n | Hacker News Reader

Pretty weak, partly terrible advice.

- Storing images and blobs: granted, usually not a good idea

- Limit/offset will take you a veeeery long way until you have to think about stuff like deep paging. And however you try to tackle that, if the stuff you paginate needs ordering, it's simply a hard problem and not a mistake.

- UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale (and even they often use 64bit integer keys for a lot of entities, see Graph API or Adwords). Will wreak havoc on insert and join performance and index size. 64 bit is more than you'll ever need even for the most serious application.

- Default values on NULL columns: Okay advice, but a pretty random issue

- Going away from normalization is optimization that's mostly premature and regarding the drawbacks should only be done with utmost care and to resolve specific performance problems, not as a general approach.

Overall, pretty mediocre advice.

View on HN

> UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale

UUID primary keys aren't needed for big keyspaces, they are needed when you need multiple processes (especially a flexible number of multiple processes) to generate unique IDs independently of each other and the central database, which will eventually get stored in the central database. This can be important at much smaller than Google/Facebook scale, depending on the use case.

Oh yes! We ran into precisely this problem when we had a number of machines that mostly run independently, that needed to communicate with a central server. Integer ID's don't work well for that, and especially in light of the fact that Mysql reuses them in certain circumstances. I couldn't do anything about Mysql being on the machines, but used Postgres on the server, of course.

The second generation of machines, where I did have a say in things, used UUID's and Postgres.

Wait, what? MySQL reuses them? Can you elaborate on that?

Here's what happens. Perhaps it has been fixed now:

* New record is created, with new ID.

* Record is deleted.

* Database is shut down (cleanly).

* Database is started again.

* New record is created. It gets the old ID, because it's just looking at the current max(ID) or something like that.

How old was the version of mysql? Even years ago I remember mysql stored the auto increment value separately.

You can also do the same in certain versions SQL Server. Change the ID field from auto-increment enabled to disabled. Add and delete a few fields. Then re-enable auto increment. Now, normally the server should start new IDs at the last position. Normally being the operative word.

That'a bug reported in 2003 and not yet fixed.

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

Oh wow, that's definitely good to know. I do quite a number of migrations and so far this hasn't bitten me, but it could.

Migrating from Mysql to Postgres might be a good migration :-)

Sadly that's not always an option for me...

Am I the only one who thinks that primary keys should be derived from the actual data? That way it's impossible for two processes to accidentally create the same conceptual piece of data (which is still possible with uuids). It also makes it much easier to recover from situations when you have to quickly promote a slave to a master role without first verifying that the slave is up to date. The main bonus though is that the database is much more comprehensible, e.g. foreign keys are legible without having to join back to the primary table. The relational model for data is pretty cool and breaks down when e.g. UNION doesn't work if 2 rows differ only in an arbitrary integer primary key.

The problem is that a lot of people end up screwing that up and picking something that doesn't work well as a primary key. Also, just using an integer is often a great way to get started without thinking about things so much. That works for a lot of web apps.

The problem is there's very few examples of real-world data that actually matches a primary key - i.e. is guaranteed to be unique and never changes. I've been burned by this so many times. In reality, everything needs to at least have some capability to change.

Unique and never changes can be two things concatenated, not one thing.

If that concatenation is too long you can ram anything thru a hash to get a constant length smoothly distributed key. Its also fun to use "weak" hashes for this because it trolls wanna be security types who don't understand the application.

Given the above, some important things concatenated and hashed works. Can always add an application ID or importer ID or process ID or a timestamp of high enough resolution.

Obviously needs are different if you're trying to create a bank user database vs deduplicating sampled engineering data.

So something that's unique is NOW() (assuming low enough sample rate LOL) and something that never changes is a laser serial number, concatenate those and ram thru a hash to make it small and fit.

This is a lot of thinking and work to replace an auto-incremented integer, which is simple and works fine in most cases, though.

"Hash of the data defining the entity identified" is how version 3 and 5 UUIDs work (plus a namespace). If you really want a weak hash use version 3 - it uses MD5.

Concatenating helps you with uniqueness, but not with whether things change (if anything that's part of a composite key changes, it can no longer reliably be part of a key). And it's the changing part that's not realistic with natural keys.

Once you get into application ids / timestamps / whatever you're no longer really using a natural key in my mind, you're just making your own algorithm for a surrogate key.

> So something that's unique is NOW() (assuming low enough sample rate LOL) and something that never changes is a laser serial number, concatenate those and ram thru a hash to make it small and fit.

That sounds very similar to UUID version 1 except that you replaced MAC address with some other identifier and added a hashing step.

> So something that's unique is NOW() (assuming low enough sample rate LOL)

We can all invent algorithms for nice unique values when we include the caveat "apart from the edge cases". It's those edge cases that bugger everything up.

> Unique and never changes can be two things concatenated,

No, for a good primary key, the "unique" parts can be due to concatenation, but the whole key (and thus all the elements) needs to be unchanging. [0]

[0] Modern databases can actually deal with changeable PKs, though with a potentially serious performance hit, but in many use cases where data travels outside the database for some kind of interaction where the results need to get reentered into the database, this is still a problem, since you can't (in any general way) cascade updates to things (which may not even be online systems, e.g., paper records feeding human processes) outside of the DB.

Then you'll end up with a lot of inconvenient compound keys.

The problem is usually with the assumptions about natural fields being unique, or not change over time. One day you realize they can actually change, or they are not unique. Changing the data model at that time to support a different primary key would be very difficult.

A natural primary key is best, yes, if you can find it.

Okay so you run a school and want to make a students database. What do you use a a PK?

1) Social security #? Fails when you have international student.

2) Last Name, First Name, Middle Name? Fails when you have a repeat name.

3) Last Name, First Name, Middle Name, Home Town, Start Year? I guess this works for most of the time. Now you need to join to this table from the classes table. So now you need all 5 keys duplicated in the classes table to do the join.

Simple Integer primary keys "suck" in that you are adding bogus data to your database that has no value.. but man, they sure solve a LOT of problems. You need to think fairly hard before you get rid of them. In some cases you totally can. But making your default datamodel include an Integer PK solves a LOT of problems.

Also, names can change!

I have actually done this for a classroom management app I made mostly for fun when I taught high school. At first I wanted to use the district issued ID number as the primary key for each student. Except some students came and started classes and needed attendance records before the district got around to issuing them their official numbers. So I fell back to auto-increment int as the primary key, with their "district_id" nullable, default null, but unique constrained as just another field equivalent to their first_name.

That's what unique keys are for usually.

That's called a natural key and I only use it for logins in user tables. I haven't found anything else that doesn't break down.

> Am I the only one who thinks that primary keys should be derived from the actual data?

Where a valid natural key exists, it absolutely should be used. Surrogate keys should only be used where there isn't a natural attribute (or composite of such attributes) that corresponds to the unique identity of a tracked entity.

However, that's very common in the real world.

Isn't the timestamp pretty much the most likely thing to be unique in the data, generally?

Including timestamp may be appropriate for log data, where otherwise identical records differing by time should be considered different entities. Otherwise it loses the property asked for upthread that our primary key detects/eliminates duplication.

Version 3 and 5 UUIDs are derived from the data. They can be a good fit some places, but you have to be sure the only (pre-hash) collisions you'll have are the collisions you want.

Postgres's concurrency control (https://www.postgresql.org/docs/9.5/static/mvcc.html) does fine with many clients adding rows with numeric IDs derived from a sequence. In Postgres a sequence is a database object that always returns the number 1 more than the number it last gave someone (This is customizable too, https://www.postgresql.org/docs/9.5/static/sql-createsequenc...).

With 64-bit integers (in Postgres, "bigint") then you can have over 9 quintillion rows before you run out of numbers.

UUIDs are for when you have more than one database server that people can write to, and those databases must not share IDs. In many cases this is not a design requirement but a design after too little research.

These were not 'remote clients' accessing PG directly, but independent computers that were not even on or connected to the network some of the time.

> UUIDs are for when you have more than one database server that people can write to, and those databases must not share IDs.

Exactly.

> Postgres's concurrency control (https://www.postgresql.org/docs/9.5/static/mvcc.html) does fine with many clients adding rows with numeric IDs derived from a sequence.

Which is fine for circumstances where a database round trip is acceptable at that point; there's situations where assigning IDs to items is something you want to have happen without database interaction at all.

Depending on your configuration and needs, using a method to generate primary keys in a way unique to each system might still yield better performance. For example, in MySQL you can use auto_increment_increment and auto_increment_offset to force one server use insert using even integers and one odd, or any combination you need. It does eat bits from the key size though. Changing it later can be a pain, but it possible if you think it through. I don't doubt something like this is possible in Postgres as well.

A special case where UUID keys are extremely helpful is when you have to convert your data from one system to another - customer "105" can be different in different databases, but customer "7C823BE3-CB5B-4E34-BE8D-4B5A71945D3F" is truly unique. This takes dealing with foreign keys from "nightmare" to "non-issue".

Indeed. I recently built a todo app inspired by a particular paper-based methodology (FVP) to scratch a personal itch, and it was a web app that uses localStorage. It was much easier to use UUID keys for the todos than to store/retrieve the max ID every time the app was opened.

That's basically the 'hello world' of web apps example where UUID's make sense already.

If you have a lot of data to synchronize, you probably still want some kind of 'version vector' thing, which in the simple case with a centralized server, is an integer 'change counter' (I forget the exact term for it) that will let you fetch only the updated/new items. You'd still use a UUID for your ID though.

wouldn't filtering by date field be good enough, generally?

Either way, once I realized that I use my own little todo-app constantly, a server-based implementation became necessary. I'm working on a Horizon/RethinkDB version right now which uses UUID's by default. But if that were not the case I would've probably gone for simple incrementing ID's.

Dates and distributed systems... is something you need to be careful with. Anyone have a good link summing this up?

https://aphyr.com/posts/299-the-trouble-with-timestamps isn't bad.

Clarifying my original reply here:

UUIDs absolutely have their place - both the problems of distributed ID generation (mostly in remote clients / apps) and avoiding information leakage (-> German Tank Problem) are very real world examples where using them is a sensible thing.

However, my point was that using them as a default choice has a lot of drawbacks. All over our data model, we've got like 60-70 entities with serial IDs and about 2 with UUID columns.

Also, don't underestimate how fast PostgreSQL can deal you out from a sequence via nextval(), should you really go down that route. But yes, if the database is more than a millisecond away, UUIDs definitely have their time and place.

> UUID primary keys aren't needed for big keyspaces

Agreed. UUID's are great for distributed/eventually consistent dbs. However, the article gives the primary reason the reason given to prefer UUID was specifically for key exhaustion.

Which, BIGINT is a much simpler solution.

The main problem I have seen with UUIDs from a performance standpoint has to do with indexing and ordering. To start with ordering, it is inherently a NP-complete problem. UUID can add a good bit of over head here since it's less computationally expensive to order an integer than it is to order something like a unique identifier. This is also why UUIDs tend to increase fragmentation with indexes, since indexing can be so closely tied to ordering. SQL Server has made some improvements here with the newsequentialid() function, so maybe it's less of a problem moving forward. To contradict myself for a second, perhaps the move to more distributed systems and service models over the long term increases the need for the move from INTs to UUIDs. Better to architect for the future now and save yourself some pain down the road. It will be interesting to see how that plays out.

UUID internally is a 128 bit integer. Cheap as hell to sort.

To be fair, most of my experience with UUID/GUID is with SQL Server, and in that case it's stored as a 16-byte binary. Character string format: '6F9619FF-8B86-D011-B42D-00C04FC964FF' Binary format: 0xff19966f868b11d0b42d00c04fc964ff

The reason given in the article was scaling. At that scale you probably don't want beginners designing your database for you.

Agreed. And the reason here for using UUIDs is not accurate. UUIDs are for having multiple systems (or components) create IDs and having a reasonable expectation for uniqueness, hence the Universal Unique part.

It grinds my gears when people sneer at integers when they perform optimally or nearly optimally on the majority of use cases.

Integer IDs are definitely easier, but I've seen it cause so many security issues -- "hmm, I wonder what happens if I manually type in /user/239?"

IME It's easier to teach junior devs not to use integers than it is to get them to think holistically about security.

This relates to the "sometimes security by obscurity is okay" post from yesterday.

Vehemently disagree. What you are proposing is everything wrong with security-by-obscurity.

In this case the security hole of /user/123 just needs to be properly locked down. That is all.

I mostly use integers (but see my comment elsewhere). There is most definitely a german tank problem involved in them, though:

https://en.wikipedia.org/wiki/German_tank_problem

For most things I do, it's not a concern, but it is something to keep in mind.

In which cases would this be a German tank problem for you? I can think of apps where you want investors or something, but I can't help but wonder if they'd be savvy enough to check for that.

I do know that I try to make my invoices to clients a bit more impressive, because I don't send many of them and they most definitely do notice if they get invoice #3 in May. Main problem is that in my country the rules for invoices are both murky and stringent, so I'm pretty much limited to a <year>0000<invoice no.> format.

Well if you have an 'order #', your competition can make an order a week and see how many other orders you've gotten in the meantime.

> in my country the rules for invoices are both murky and stringent,

I sympathize, as the rules were like that in Italy, where I lived for a long time.

You can use hashids for this problem [0] This just "encrypts" the primary key with a given salt.

[0] http://hashids.org/

Yes. Any time you let a non-associated record be displayed by a user session that is not linked to that record and should not have access to it, that's not a problem of easily guessable keys, that's a problem of not securing data by checking access rights.

password reset email #123 gets url /user/123

password reset email #124 gets url /user/124

password reset email #125 gets url /user/125 but that doesn't work because someone predicted it and got there before the requestor. no idea what account they'll get, but they'll get an account of some type.

This also comes up in shipping records. OK where do we go to steal an XYZ delivered today and sitting on a front porch? Well lets check

/shippinglabel/345

/shippinglabel/346

/shippinglabel/347 oh look delivered today, sitting on back porch step, and the address is right there

Another fun one is online financial documents with sequential accounts.

Shouldn't this risk be mitigated with authorization rules? Or do we assume we are delivering pages without any type of auth first?

You should allow to reset password to the users without authentication (and therefore without authorization).

That's the nature of password reset link.

Oh of course. Good point.

Yes, of course that's what needs to happen. And that's what I do when I'm the one doing the implementation.

But a junior dev just out of code school doesn't necessarily think of this. So when I ask one to build the basic scaffold and db schema I say "make sure you use UUID," then later I show them how security holes like this can manifest.

I've seen this security hole so many times in other sites that I feel like it's a good first principle to limit "guess-ability" in the schema wherever possible.

I have faced this issue in my web application. My solution is to use a UUID wherever the ID will be exposed to the user (only one place in my application) and use an integer ID everywhere else. Although this does mean 2 IDs need to be generated and stored. The other solution is to never allow access to users without a log in.

You can avoid the need for two IDs if you HMAC the URL. There's a single private key on the server used to generate and verify the MAC on subsequent requests.

Can you link to this post? This is a topic that interests me and I'm on mobile :(

https://news.ycombinator.com/item?id=11854576

> IME It's easier to teach junior devs not to use integers than it is to get them to think holistically about security.

IMO, explicitly prohibiting unauthorized access to an API endpoint is a basic security tenant, not a "holistic" one. if iterating through an API's integer key sequence results in unauthorized access to data, replacing the integers with UUIDs only masks the problem and I'd say is a classic example of how relying on obscurity for security can be a pernicious mistake, especially for a novice developer.

Yes it is a basic security tenant.

But sometimes you're working with a legacy API and/or a bad auth mechanism.

Not every project is greenfield or is maintained by senior devs.

> Integer IDs are definitely easier, but I've seen it cause so many security issues -- "hmm, I wonder what happens if I manually type in /user/239?"

You're misidentifying the actual security problem. Using URLs in this manner requires cryptographically secure random numbers, or my preferred method is to HMAC the URL to sign it's protected parameters. I actually wrote a small library for .NET called Clavis to demonstrate this idea [1]. The MAC acts as the cryptographically secure identifier needed to make the URL unguessable.

[1] https://higherlogics-trac.sourcerepo.com/higherlogics_clavis...

You can also use ZooKeeper to increment the id for distributed systems. If we're talking about a lot of inserts per second, you can increment by a thousand.

For example system-A gets id range for 1-1000, system-B gets id range for 1001-2000 and so on.

If you can avoid exposing those ID's then 64 bits should do fine with no problems.

But it turns out JavaScript can only handle ~54 bit integers, so if you try to randomize your IDs to prevent people scanning your data, you'll be in for a rough patch making sure you always treat IDs as strings. At least with UUID you pretty much have to treat it as a string.

You also avoid being the next developer in a long line who thinks they know what 'unique' 'random' mean but can't actually be trusted with that much responsibility (it's okay, most of us can't. I once stopped someone in the 11th hour from shipping a mutually authenticated SSL application that could only generate 256 unique AES session keys, due to a seeding bug. That was scary)

The common pattern I've seen is to send the 64 bit integer back as a string. So it would be a "numeric string"

100% agree with this method. Math calculations are never really used with IDs in my experience.

Right, but generally this has not been the default behavior. If you're lucky and your chosen libraries were written by wise people, it's a couple lines of code and a few tests.

But it's usually set wrong by default, and if your IDs are monotonically increasing you'll probably never notice.

It also depends on your environment. On a platform like .Net it's not impossible to get your JS serializer to serialize 64-bit ints and floats as strings while keeping 32-bit and smaller numeric. So no matter how you represent it server-side it will survive a round trip. For a language that doesn't make that kind of distinction in the type system (I'm thinking Python, but I could be wrong), you really have to toString it right when you get it from the database. The only other option is to base the serialization decision on the value, which means sometimes your ids are returned as ints and sometimes as strings. At best it's ugly, at worst it leads to bugs.

If you get to the point of sniffing, then a slightly better option might be to suffix all your foreign keys with something like 'key' or 'ID'. Or use slugs for everything.

Or you could initialize all your tables with the first primary key being 2^32 and only lose one quarter billionth of your legal keyspace.

I think the advice on shying away from db normalization is a terrible idea for beginners. If anything, they should try to stick to creating db tables in 3rd normal form until they learn when and where to bend those rules.

They're literally suggesting an "array" in a VARCHAR field (by which I assume they mean comma separated or similar). That's a dangerous habit to get into for a beginner, and how you wind up with so many over stuffed columns.

In the article's specific scenario I'd recommend they just create the darn tables rather than doing his hacky solution.

Original author here, what I'm proposing is an ARRAY[VARCHAR]. Postgres has a native array type, different from manually mangling an array.

Fair enough. Still an ugly hack, and you're still over stuffing a column.

It does avoid some issues with string passing which is nice, but that's just about all it solves.

On top, arrays in PG don't support foreign key constraints and in this way make your model weaker. We do use them too, but in well defined, pretty rare circumstances.

I'm sorry, but I still must disagree with this advice as it pertains to beginners. I would rather a beginner "overnormalize" their database tables rather than have them default to an array column type. It's much easier to transition from "overnormalized" db tables to one that uses array columns when appropriate than it is to go the opposite direction.

I found the images advice unnecessarily black and white. Of course there are cases where storing images or documents is the right move. few reasons i can think of: 1. you can leverage the same auth scheme as the rest of your data 2. audit trail 3. transactional support for image CRUD operations 4. leveraging replication features already offered by your DB 5. blobs can be searchable if the database supports full text search, super useful to search document contents. Edit: shortened some points

Storing images / blobs in a database is fine! https://sqlite.org/intern-v-extern-blob.html

I just recently built a e-commerce site that stores the images in the database. But be careful, some ORM's will automatically fetch that blob together with the rest of your object.

The thing is, for most cases relevant to web apps, you don't even actually want binary data. Your application isn't going to touch the file itself anyway - a client somewhere is going to be the one processing and downloading the file.

There's never a circumstance where dealing with hundreds of kilobytes or more of data is going to be faster than a 50-character file name, regardless of where it's stored.

But if you replicate your DB, now you also have to replicate your filesystem. Easier to just segregate BLOBs in their own tables and let DB replication do all the work.

It depends on why you're replicating but I would say in most cases you don't need to.

If you're forking your DB (i.e. there will now be two copies that will diverge from each other) than sure, you would need to replicate your filesystem. But that's not complicated (copying directories is a solved problem).

If you're talking about replication for performance reasons, then DB replication and file replication aren't necessarily going to go hand-in-hand, and the solutions are going to look fairly different (for web apps, "file replication" probably looks like a CDN).

Actually in some cases you want your application to touch that blob, especially if want to automatically generate thumbnails and have aggressive/best effort compression on that image. It is the best way to do something about users that uploads a 10MB image that is uncompressed :-)

For sure - but you should ask yourself if you need to routinely deal with that blob, or just process it on the way in? If your upload functionality generates thumbnails and resizes images properly, you can isolate file handling to that section of the code and the rest of your app can just deal with nice, simple URLs.

So if you do this on a large site, what % of your DB load is going to be serving images?

If you put your images behind a CDN, you solve 98% of that.. but you still have a thundering herd problem if your cache gets cold and a bunch of people want to see a non-CDNed iamge.

IMHO it is way way way easier to just throw the image up on google cloud storage (or s3), and store the URL in your database. Or better yet, derive the path from the ID so nothing to store. You can handle millions of hits per second with no DB load.

I use Varnish, so it is never a performance issue.

Does Varnish preload your images from cache?

What if 100 people hit the page at the same time for an uncached image?

I am not saying it can't be done, just having trouble figuring out the benefits.. its easier to store an image in cloud storage over in a database... and cheaper.. and less bug prone...

Varnish will actually hold those 100 connections open while it dispatches 1 single request to the backend. When that request returns (assuming the cache control headers allow it), it will serve that one response to all 100 requestors. It's a very powerfull tool for anything that's even slightly cachable.

If your site has 1 varnish, if your site has 20 varnishes it would do 20 connections.

Years ago I built an image server that served up millions of tiny images (1k or so) and found mysql did a pretty good job for that, avoiding the file system overhead which would overwise be awful.

I think it very much depends on specifics. Years and years ago I stored emails as BLOBs in an Informix database for a large webmail system. That turned out to be a bad idea and we ended up migrating (very painfully) millions of emails to a file server.

However I don't think it's universally true this would always be bad. It depends on the speed at which you can insert and fetch blobs from your database, the speed of your NFS server, whether your database or file server can be replicated, and the problems with data integrity you're definitely going to have when not everything is known by the database.

No substitute for careful analysis of individual cases.

How did MySQL help here? It uses the filesystem too...

I can imagine that you want to hold these images in RAM to bypass the filesystem. Maybe MySQl did that for you, but that sounds like a heavy middleman.

If you have a great amount of tiny images then you are relying in how fast is the filesystem to localize the image. Chances are that depending on the index you are using MySQL could do a better job (this depends on the storage engine but usually is one file for the table and another ones for indexes)

"bypassing the filesystem" in this case means not having a million of tiny files, which often hurts performance.

If you'd have non-DB system for that (e.g. any game with a lot of tiny images), then you would generally want to use spritesheets, archives or some other approach to store many of these images in a single file instead of having each of them be a separate entry in the filesystem.

Typically, filesystems are designed and tuned around the assumption that most of your data is stored in relatively large files. For instance, a typical ext4 filesystem uses 256 bytes per inode and rounds each data block up to a multiple of 4096 bytes.

So if you want to store lots and lots of tiny objects, it's very inefficient to store them each in a separate file. At that point, you could design your own compound data format, making sure it efficiently supports all the different operations you might want to do... or you could just use a database which has already solved the problem for you.

>UUID primary keys?

Also somewhat useful to have when your organization decides that a multimaster cluster is a requirement.

Or your customers start merging and acquiring each other.

Or you end up hosting your app for customers and want to go multitennant.

LIMIT / OFFSET should be avoided from day one for a realtime app. It's not too difficult to build a future proof solution, and in my experience you run into problems rather quickly if you're building a realtime system (user generated content especially).

LIMIT / OFFSET problems:

- There are portability issues with some other kinds of datastores and can be a mess to keep caches consistent (removing an item from one page requires invalidating every page set after it)

- It makes caching at the request / cdn level more complicated.

- Any update to the data while the user is paginating results in duplicate or missing items (especially noticeable with any kind of infinite scroll).

You can instead use something like ?object_id=123&page_size=50 to get the next 50 results after item with id=123 (assuming some default order, you could also pass in an order param). To keep your client code clean, you can return a pagination object with the response so you don't need the client to figure out the id of the last object and build the url.

Its sad that UUIDs are not handled well yet, even in 2016. Its a final id solution that clearly avoids all id collision issues.

UUIDs don't avoid all id collision issues, and it's far from final. They'll prevent you from using BRIN indexes effectively, for example. Timestamp plus unique server id is an arguably much better approach; on top of everything else, timestamps are actually useful, and they fit in 8 bytes.

Denial isn't the same as argument. The definition of a UUID pretty much means they are. Enough bits and the chances of collision are less than that of a superintelligence spontaneously evolving in your morning coffee and hacking your computer. And its past time amateurs stopped thinking they've come up with the perfect UUID (timestamp + server id! Perfect! Until I set my clock back, or repurpose the server) and just use the vetted solutions available.

Timestamp + server id can be uniquely guaranteed per server without coordination, so yes, it works fine :P Also, modern computers have monotonic clocks available and can use things like PTP.

Well you need to ensure that two servers never re-use the same ID so they can't come up with their own ID and there must be some form of co-ordination there. You also must ensure that you never enter two things into the database from the same server within one tick of the clock. Monotonic doesn't mean you'll never get the same number twice, just that it always changes in the same direction (in this case, the timestamp never gets lower, but nothing stops you getting the same number twice).

I'm aware. Step 1 is generally still considered under the "eventual consistency" umbrella (fork-join specifically). Step 2 doesn't really matter because all you actually need is monotonicity from the clock; locally it's really easy to guarantee uniqueness (and remember, it's fine to delay transactions as long as they commit eventually, which is one reason CAP is kind of imprecisely stated; you're only unavailable if you never respond to a request, when in practice it's really trying to convey latency requirement).

Could you elaborate on that? I use UUID's at the moment, trusting that they'll be unique, but in part because I don't properly understand them, I feel a little anxiety about the chance of a collision. I sort of assumed they already used timestamps as a basis already, but apparently they do not. Is it just a matter of it being highly improbably of generating the same UUID twice? I'm assuming yes on gut feeling, but I highly distrust that body part these days.

(Edit: I realize I could go look this up myself, but 1) I like explaining things so perhaps you do too, and 2) I'm hoping perhaps at the very least you can point me to a good resource to properly understand this topic)

If you write a sufficiently long series of random digits, it becomes increasingly likely that your number is the only use of that number anywhere in the universe. That's the crux of it.

I'm on a train now, but I think there's also a timestamp element to it somewhere, making it less likely that a restart of the random number generator will cause you to have a collision.

In my experience this is only true if the UUID is generated by the database system in a controlled manner. It is not uncommon to have a unique identifier be used as a key in a database that comes from an external source. Consider an environment where you have a database that stores data for a monitoring system. So you decide to use the machine ID (a UUID) as a key. In your design you fail to take into account sys admins that do not clone a VM the correct way, so you wind up with two different machines with the same supposedly unique identifier. Sure, if you have a unique constraint on the key, you will find out about it pretty quickly, but it is just one example.

Version 1 UUIDs are server ID (MAC address) plus timestamp. Your overall proposal is already included in "UUIDs." They don't fit into 8 bytes, though, so if you want to squeeze them down you'd have to come up with your own scheme.

If you do use this scheme, be careful that your timestamp is sufficiently granular that it's not possible to generate two identical IDs in quick succession....

Granted re: UUIDs. There are definitely tradeoffs here (time precision / inserts per second [in practice you'll just end up throttling or massaging the timestamp if you do have insufficient granularity] vs. number of supported partitions vs. ability to efficiently route a path to the key vs. fast reservation ability vs. key size, etc., etc.). I was referring to the notion of a global 128-bit pseudorandom number as the final solution to everything... things are rarely that straightforward.

Of the five UUID versions, only version 4 is pseudorandom. The other four versions are completely non-random.

Reply on news.ycombinator.com