> Reduced throughout for inserts since the primary index pages won’t all be in cache if the keys are randomized. Lose ability to use id as a fast temporal ordering.
You can generate the first few bytes (we use the first 3) of the UUID from a timestamp - that gives you index locality. It's even better than an auto-inc because you can control exactly how much of your index will be used for hot inserts based on how many timestamp bits you use, so you can avoid lock contention around the single latest index page.
> Encourages uuids generated client-side.
It's really an API design choice about whether you allow this, but it can be useful in some circumstances (if you trust the client).
> so you can avoid lock contention around the single latest index page.
That is usually the total opposite of what you want. There are some optimizations for inserting to the last page but primarily it is because you want to to sequential inserts. So if you want to avoid contention on the last page you should insert ordered by (connection ID, sequential ID or high resolution timestamp). That way every connection will do sequential inserts on its own page.
Nice this makes sense. Seems like storing connection_id in the table would make it difficult to look up data later? It seems like you could use (customer_id, seq_id) for similar effect.
- UUID (fully random) is completely secure and private. You cannot infer anything from it. As soon as you add anything to it ( like timestamp or counter), outsiders can infer things like how fast you are generating new objects
- UUID (fully random) cannot be abused by developers, which might skip creating timestamp columns and read it from the id instead
- UUIDs are slower, but as you see y's not for no benefits
Not that much slower than bigint as long as you replace the top one or two bytes in the UUID with a rotating per-minute counter.
You keep 106 bits of entropy (from v4's 122 bits) while largely eliminating page faults. Walkability is eliminated while not leaking too much temporal info.
You can generate the first few bytes (we use the first 3) of the UUID from a timestamp - that gives you index locality. It's even better than an auto-inc because you can control exactly how much of your index will be used for hot inserts based on how many timestamp bits you use, so you can avoid lock contention around the single latest index page.
> Encourages uuids generated client-side.
It's really an API design choice about whether you allow this, but it can be useful in some circumstances (if you trust the client).