2 ms·
Another (potentially!) significant problem with uuids is that they're inefficient to store. Compared to an 8 byte value obviously they double the storage size.
by insanitybit 3y ago
Another (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 locality can be, for some workloads, a significant loss. Where UUIDs reign supreme for performance is in terms of generation - if you have a super high write load and really high latency requirements it may not be viable to have a single integer counter.
Although, in my own testing, I've found Postgres is more than capable of hundreds of thousands of increments per second and if you're willing to allow for gaps/ interleaving in your counter (slight hit to locality) you're really unlikely to hit a bottleneck.
Sequentual uuids help and are desirable but they still can't compete on storage :)
- fabian2k 3y agoI'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?
- insanitybit 3y agoSorry, let me clarify. I used Postgres to generate the counters as external IDs into other data stores (so instead of `give me a new uuid` I said `hey postgres give me a new counter`. Postgres was only used to maintain the counters themselves in a tiny database where compression isn't relevant anyways. The other data stores where I used this were a combination of Parquet on S3 (where you get compression) and ScyllaDB (where you also get compression).
- insanitybit 3y agoOh yeah, this also makes pagination trivial. You can basically just give your clients a literal number indicating where they are. So good, you basically get paging APIs for free.
- Fire-Dragon-DoL 3y agoWell, records can be deleted
- insanitybit 3y agoThat doesn't matter unless you reuse ids, which if you have a 64bit counter there is no need.