Use flakeids
Unexpected downsides of UUID keys in PostgreSQL
101–110 of 215 posts
Re: Unexpected downsides of UUID keys in PostgreSQL
#102UUID is also used to avoid leaking information about the underlying system. This includes temporal information that could be used to infer the size of the dataset for all users. If this isn’t a concern, then using a timestamp based approach as recommended in this article is a good approach. That is the default in MongoDB. If it is a concern, one approach is to use random UUIDs to give to end users but then internally…
I actually have been using a combination of a numeric integer ID as the PK and a UUID as a lookup field to do routing lookups etc. for this purpose in Postgres backed app I'm working on. I found this approach to be more trouble than it's worth and plan on switching to a UUID PK key and doing away with the integer sequence. Here are the complications I ran into: The libraries I'm using for the ORM and API are designed…
I personally find the numeric id extremely valuable for internal data analysis and sharing. I can refer to rows by the numeric id, including a range of rows, and seeing the ids gives exactly that intuitive information about it's relation in the set that we are hiding from end users. Numeric ids can also be used for the same reason in an admin-only UI.
On the efficiency side, joining and querying by id is generally more efficient on CPU usage for querying, but you do have to pay the cost of having the additional column and index.
Re: Unexpected downsides of UUID keys in PostgreSQL
#103Earlier quoted context omitted.
At scale you’re probably sharded across multiple DBs and you’re already operating through replicas. Point being you’re less likely to hit a warm cache as you scale up anyway as your application layer gets load balanced to different DB endpoints.
Yes exactly, with proper sharding, raw performance is not as important; to some extent, you trade it away for improved concurrency. In fact, I struggle to see how one would implement sharding with auto-incrementing integers (you would get ID collisions for different resources across different shards/database instances); there needs to be a way to uniquely refer to resources across potentially multiple databases and U…
We did auto-increment integers with multiple servers like 20 years ago: The caveat is that you have to know how many servers are in the set in advance. Each server increments by the population size, and their starting number is their position within the pool.
Not hyper scale, but good enough for failover or a 3-5 node setup.
Re: Unexpected downsides of UUID keys in PostgreSQL
#104Earlier quoted context omitted.
> I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs... I find auto-incrementing IDs far more elegant in many ways. They are much easier to make sense of for users, they give you some loose metadata (ordering, sometimes a rough time range) which can be handy in debugging stuff. At my previous workplace most things were auto-incrementing IDs, and while we got bitten by them a few…
I'm a big fan of SERIAL and BIGSERIAL, for aesthetic/cognitive reasons more than performance, but it can be really nice to have a data-generating process generate the primary ID of a piece of data, which you can then log before you put it in the database and look up the same ID everywhere when debugging. Not to mention the distributed database situation. In fact I like that enough that I sometimes use ULIDs for secon…
Absolutely, these are also good things to take into account. There are really situations for all of these things, and this is why I don't like the approach of trying to "solve" this problem. Lots of people say "UUIDs solve IDs", or "UUIDs cause too many issues to use", or "ULIDs solve UUID issues", and in reality this is just another place where there are trade-offs, and what works for one company/project/dbms/table may not work for another, or may even cease to work over time as requirements or scale change.
Re: Unexpected downsides of UUID keys in PostgreSQL
#105Earlier quoted context omitted.
It's still not as elegant because it doesn't work in a multi-process or multi-server back end; if the request fails, you need to make sure that the retry request will hit the same server process (assuming you're keeping the UUIDs in memory on the server). You can use some in-memory data store like Redis to share the request UUIDs across multiple processes/hosts but that can add a lot more complexity, latency and risk…
You can use the DB for this as well, just make a table e.g. "requests" with two columns, the user and the request token. Old entries can be purged on a cron job. If you add a column to store params as well then you can also do better validation: > Responding when a customer changes request parameters on a subsequent call where the client request ID stays the same > We design our APIs to allow our customers to explici…
You can but you add an additional database lookup step which will cost you more in performance (and latency) than just using UUIDs as the ID directly with a single table.
Re: Unexpected downsides of UUID keys in PostgreSQL
#106CouchDB uses sequential UUID by default: https://docs.couchdb.org/en/stable/config/misc.html#uuids-co...
> Impact of UUID choices: the choice of UUID has a significant impact on the layout of the B-tree, prior to compaction.
> For example, using a sequential UUID algorithm while uploading a large batch of documents will avoid the need to rewrite many intermediate B-tree nodes. A random UUID algorithm may require rewriting intermediate nodes on a regular basis, resulting in significantly decreased throughput and wasted disk space space due to the append-only B-tree design.
> It is generally recommended to set your own UUIDs, or use the sequential algorithm unless you have a specific need and take into account the likely need for compaction to re-balance the B-tree and reclaim wasted space.
Re: Unexpected downsides of UUID keys in PostgreSQL
#107Earlier quoted context omitted.
Yes exactly, with proper sharding, raw performance is not as important; to some extent, you trade it away for improved concurrency. In fact, I struggle to see how one would implement sharding with auto-incrementing integers (you would get ID collisions for different resources across different shards/database instances); there needs to be a way to uniquely refer to resources across potentially multiple databases and U…
We did auto-increment integers with multiple servers like 20 years ago: The caveat is that you have to know how many servers are in the set in advance. Each server increments by the population size, and their starting number is their position within the pool. Not hyper scale, but good enough for failover or a 3-5 node setup.
Or increment by more than you need. If you think you'll only ever have 5 nodes, increment by 20. Lots of space for expansion and in practice you'll hit the datatype limits just a bit earlier - you'd need to work around them anyways.
Re: Unexpected downsides of UUID keys in PostgreSQL
#108Earlier quoted context omitted.
It's a good option purely for cosmetics but don't rely on it for any kind of serious obscurity since it's trivially reversed. I've used it to great effect in the past to encode multiple integer values like start/end ID, sort order (0/1), etc. for cursor-based pagination. But that's only because there's nothing secret in those numbers. Just purely for convenience.
I once heard of some fancy data science that inferred that the company was the supplier's major customer from the gaps in the sequential ids they saw when using that supplier's API. That was useful in negotiations. And so the company that did that data science realised they too were susceptible to exactly the same 'attack'. So they created a system to obscure the ids they were themselves exposing to their customers,…
Not that fancy (or at least not that novel). In WWII the allies used sequential serial numbers on destroyed and captured tanks to have a frighteningly accurate picture of German industrial output.
Re: Unexpected downsides of UUID keys in PostgreSQL
#109Earlier quoted context omitted.
I actually have been using a combination of a numeric integer ID as the PK and a UUID as a lookup field to do routing lookups etc. for this purpose in Postgres backed app I'm working on. I found this approach to be more trouble than it's worth and plan on switching to a UUID PK key and doing away with the integer sequence. Here are the complications I ran into: The libraries I'm using for the ORM and API are designed…
Well, I'll give another take. If you do not have two separate forms of identifier AND you have a "public" API (including basically any client apps, frontend JS, or anything found in query params) then you are making compliance with European regulators a massive headache when it comes to erasure of PII, since shared identifiers must be destroyed one way or another. Trying to merely delete the records is complicated by…
I think you raise some good questions around IDs and PII and we definitely will be tackling GDPR sooner or later.
I don't quite follow on the European regulation issues raised by using as a UUID in a route and that being the PK of the record.
I know you should not expose PII or any information that can be used to identify a person, however, in our case any route is behind an authed login on an SSL connection which encrypts the path (we don't use query params).
The only place that contains data that ties a UUID to a person is in the database. This would be the case whether we used a PK as an integer or not.
Could you elaborate or share any resources around dual IDs DB design for PII compliance? That would be super helpful.
Regarding framework hacking or workarounds, I have a principle to not go against the grain of a framework. The reason for this is that modifying/hacking adds complexity when building on top of it or onboarding other software engineers. If necessary I'll do it as a last resort.
Re: Unexpected downsides of UUID keys in PostgreSQL
#110Another (potentially!) significant problem with uuids is that they're inefficient to store. Compared to an 8 byte value obviously they double the storage size. But it's actually worse - if you have loosely ordered integers, even with gaps, you can compress those down to even less, like practically 1 byte on average. That makes uuids ~16x worse for disk storage, ~2x worse for memory storage (cache). That + Losing loca…
I'm not aware of any situation where Postgres (without extensions) would compress ints that way? That is more of a column-oriented or time-series DB thing to me.
Are there any cases in Postgres where this actually plays a role and sequential ints get compressed?