For the curious:
* UUIDv4 are 128 bits long, 122 bits of which are random, with 6 bits used for the version. Traditionally displayed as 32 hex characters with 4 dashes, so 36 alphanumeric characters, and compatible with anything that expects a UUID.
* UUIDv7 are 128 bits long, 48 bits encode a unix timestamp with millisecond precision, 6 bits are for the version, and 74 bits are random. You're expected to display them the same as other UUIDs, and should be compatible with basically anything that expects a UUID. (Would be a very odd system that parses a UUID and throws an error because it doesn't recognise v7, but I guess it could happen, in theory?)
* ULIDs (https://github.com/ulid/spec) are 128 bits long, 48 bits encode a unix timestamp with millisecond precision, 80 bits are random. You're expected to display them in Crockford's base32, so 26 alphanumeric characters. Compatible with almost everything that expects a UUID (since they're the right length). Spec has some dumb quirks if followed literally but thankfully they mostly don't hurt things.
* KSUIDs (https://github.com/segmentio/ksuid) are 160 bits long, 32 bits encode a timestamp with second precision and a custom epoch of May 13th, 2014, and 128 bits are random. You're expected to display them in base62, so 27 alphanumeric characters. Since they're a different length, they're not compatible with UUIDs.
I quite like KSUIDs; I think base62 is a smart choice. And while the timestamp portion is a trickier question, KSUIDs use 32 bits which, with second precision (more than good enough), means they won't overflow for well over a century. Whereas UUIDv7s use 48 bits, so even with millisecond precision (not needed) they won't overflow for something like 8000 years. We can argue whether 100 years is future proof enough (I'd argue it is), but 8000 years is just silly. Nobody will ever generate a compliant UUIDv7 with any of the first several bits aren't 0. The only downside to KSUIDs is the length isn't UUID compatible (and arguably, that they don't devote 6 bits to a compliant UUID version).
Still feels like there's room for improvement, but for now I think I'd always pick UUIDv7 over UUIDv4 unless there's an very specific reason not to. Which would be, mostly, if there's a concern over potentially leaking the time the UUID was generated. Although if you weren't worrying about leaking an integer sequence ID, you likely won't care here either.
They (and others) are great for this kind of case - and many others.
Example Ulid vs UUID:
000360TJXZDDMSKJSQGBQHA5YA
fb87f306-b613-4948-be24-00609cf9ccc8
We could fork it and remove the interface or switch to ULID[1] instead.
[0] https://github.com/stephenc/eaio-uuid/blob/master/src/main/j...
ulid() // 01ARZ3NDEKTSV4RRFFQ69G5FAV
- 128-bit compatibility with UUID 1.21e+24 unique ULIDs per millisecond Lexicographically sortable!
- Canonically encoded as a 26 character string, as opposed to the 36 character UUID
- Uses Crockford's base32 for better efficiency and readability (5 bits per character)
- Case insensitive No special characters (URL safe)
- Monotonic sort order (correctly detects and handles the same millisecond)
There are pros and cons to using random IDs as a PK. For RDBMS clustering on the PK (InnoDB), it's a terrible idea. If you're going to sort by the PK, it's usually a terrible idea (UUIDv1 isn't as bad since it includes the timestamp, but that assumes your access pattern is based on insertion time). There is ULID [0] if you'd like something that's sortable. You could also just have a secondary index. An advantage can be that it _can_ be a good way (again, this depends heavily on your access patterns) to do sharding.
My other concern for nano id is twofold, both around their PRNG. They mention using Node's crypto.randomBytes(), but their source code instead references crypto.randomFill() [1]. Node's docs mention that having "surprising and negative performance implications for some applications" [2], related to libuv's thread pool. See my later comment about libuv and containers. Also, Node's crypto.randomBytes() mentions that it "will not complete until there is sufficient entropy available." That sounds suspiciously like they're using `/dev/random` instead of `/dev/urandom`, which at least for this application of it, would be an odd decision. I did note that elsewhere in nano id, they're creating their own entropy pool, so it may not matter either way.
With that out of the way:
If the plan is only for self-hosting, then yeah, you don't really need to consider schema design that carefully. Databases are really good at their job. Also, honestly nearly none of this matters until you have significant scale.
If you plan on starting a SaaS, there's a lot to consider. An incomplete list, in no particular order:
* Foreign keys. They're very handy, but they can introduce performance problems with some access patterns. Consider indexing child table FKs (but not always - benchmark first).
* DDL like ALTER TABLE. I suggest getting intimately familiar with Postgres' locks [3]. The good news is that instant ADD COLUMN with {DEFAULT, NOT NULL} is safer now. The bad news is that it does so by lazy-loading, so if your queries are doing silly things like SELECT *, you're still going to end up a ton of contention.
* Connection pooling. You don't want to eat up RAM dealing with connections. PgBouncer [4] and Pgpool-II [5] are two that come to mind, but there are others as well. The latter also handles replication and load balancing which is nice. If you aren't using that, you'll need to handle replication and load balancing on your own.
* Load balancing. HAProxy [6] is good for load balancing, but has its own huge set of footguns. Read their docs [7]. A few things that come to mind are:
* Any kind of abstraction away from the CPU, like containers, may cause contention. Same with VMs (i.e. EC2), for that matter, since a noisy neighbor can drop the single-core turbo of Xeons A LOT. Look into CPU pinning if possible.
* HAProxy really likes fast clocks over anything else, for x86. Xeons will beat Epyc. ARM can beat x86 if tuned correctly.
* If you're using Kubernetes, look into Intel's CPU Management [8], which is also now native in K8s v1.26 [9].
* If you have access to the disk (e.g. you're running bare metal or VMs with this capability), think carefully about the filesystem you use and its block size (and record size, if you use ZFS).
Good luck!
[0]: https://github.com/ulid/spec
[1]: https://github.com/ai/nanoid/blob/main/async/index.js#L5
[2]: https://github.com/nodejs/node/blob/main/doc/api/crypto.md#c...
[3]: https://www.postgresql.org/docs/current/explicit-locking.htm...
[4]: https://www.pgbouncer.org/
[5]: https://www.pgpool.net/mediawiki/index.php/Main_Page
[7]: https://cbonte.github.io/haproxy-dconv/2.4/configuration.htm...
[8]: https://networkbuilders.intel.com/solutionslibrary/cpu-pin-a...
[9]: https://kubernetes.io/docs/tasks/administer-cluster/cpu-mana...
[10]: https://github.com/nodejs/node/blob/main/src/node_os.cc#L100
[11]: https://github.com/libuv/libuv/blob/v1.x/src/unix/linux.c#L8...*
Also curious because I don't actually know: If a format spec is GPL, does that encumber implementations of said spec?
Functionally, UUIDv7 might be the _same_ but the hope would be for a more rigid specification for interoperability.
[1]: https://github.com/ahawker/ulid
Going to the spec [1]... Yeah, that's weird. The spec calls those 80 bytes "randomness", and apparently you are meant to generate a random number for the first use within a particular ms... but on second and subsequent uses you need to increment that random number instead of generating another random number?
Very odd. I don't entirely understand the design constraints that led to a non-random "randomness" section still called "randomn" in the spec even though they are not.
UUIDv7 (currently a draft spec[0]) are IDs that can be sorted in the chronological order they were created
In the meantime, ulid[1] and ksuid[2] are popular time-sortable ID schemes, both previously discussed on HN[3]
[0] https://datatracker.ietf.org/doc/html/draft-peabody-dispatch...
[1] https://github.com/ulid/spec
[2] https://github.com/segmentio/ksuid
[3] ulid discussion: https://news.ycombinator.com/item?id=18768909
UUIDv7 discusison: https://news.ycombinator.com/item?id=28088213
A ULID is 48 bits of timestamp plus 80 bits of entropy. These new formats just don’t have enough random-component entropy. 64 bits isn’t enough. Who cares in your millisecond timestamp wraps every 8900 years as they do in a ULID?
It appears to have a similar constraint that two ID's generated within the same timestamp (ms, ns) have no strong guarantee of ordering. That might not be a deal breaker depending on your use case but something to consider.
"Universally Unique Lexicographically Sortable Identifier"
https://github.com/aarondcohen/id128 and https://github.com/ulid/javascript
I’ve also worked on a UUID-ULID bridge for Go
https://github.com/sudhirj/uulid.go
And seeing as this is just 128 bits it’s quite easy to move seamlessly between formats and representations.
I’ve found this concept especially useful in nosql stores like DynamoDB, where using a ULID primary key makes objects time sortable automatically. It’s also quite easy to query for items by zeroing out the random component and setting only the time stamp bytes.
* they are sortable so pagination is fast and easy.
* they can be represented as UUID in database (at least in Postgres).
* they are kind of serial, so insertion into indexes is rather good, as opposed to completely random UUIDs.
* 48 bits timestamp gives enough space for the next 9000 years.
Other than that, they have 80 bits of randomness, enough to be unique even if millions are generated per second.
I've found ULID to be great for my purposes. Wonder if anyone else has tried using them too?
ULIDs are sortable (time component), short (26 chars) and nearly human readable, and good enough entropy/randomness for everything I'd ever be working on.
Does anyone have any criticisms of ULIDs? I can't see how they don't take over general purpose use of unique ids in the future except where a more guarantee of uniqueness is needed. (ie, bajillion records a second unique...)
And, like this spec, the values are aliases so 0/o are the same, 1/I/l are the same, etc
Generally I'm using a `folder/structure/ULID` approach to keys and its really easy with start_key and end_key on allDocs to grab an entire "folder" at a time. I've had some pretty large "folders" and not seen too much trouble. At this point the biggest application I worked on pulls a lot of folders into Redux on startup and so far (knock on wood) performance seems strong. (ULIDs [1] are similar to GUIDs but are timestamp ordered lexicographically so synchronizations leave a stable sort within the folder when just pulling by _id order.)
At least as far as my queries have been and what my applications needs have been, PouchDB is as fast or faster than the equivalent server-side queries (accounting for HTTPS time of flight), especially now that all modern browsers have good IndexedDB support. (There were some performance concerns I had previously when things fell back to WebSQL or worse, such as various iOS IndexedDB polyfills built on top of bad WebSQL polyfill implementations, and also a brief attempt that did not go well to use Couchbase Mobile on iOS only.)
Photos have been the bane of my applications' existence, but not for client-side reasons. I had PouchDB on top of IndexedDB handling hundreds of photos without breaking a sweat and those size limits all have nice opt-ins permission dialogs for IndexedDB if you exceed them. Where I found all of the pain in working with photos was server side. CouchDB supports binary attachments, but the Replication Protocol is really dumb at handling them. Trying to replicate/synchronize photos was always filled with HTTP timeouts due to hideously bloated JSON requests (because things often get serialized as Base64), to the point where I was restricting PouchDB to only synchronize a single document at a time (and that was painfully slow). Binary attachments would balloon CouchDB's own B-Tree files badly and its homegrown database engine is not great with that (sharding in 3.0 would help, presumably). Other replication protocol servers had their own interesting limits on binary attachments; Couchbase in my tests didn't handle them well either and Cloudant turned out to have attachment size limits that weren't obvious and would result in errors, though at least their documentation also kindly pointed out that Cloudant was not intended to be a good Blob store and recommended against using binary attachments (despite CouchDB "supporting" them). (It sounds like the proposed move to FoundationDB in CouchDB 4.0 would also hugely shake up the binary attachment game. The 8 MB document limit already eliminates some of the photos I was seeing from iOS/Android cameras.)
I'd imagine you'd have all the same replication problems with large data URIs (as it was the Base64 encoding during transfers that seemed the biggest trouble), without the benefits of how well PouchDB handles binary attachments (because of how well the browsers today have optimized IndexedDB handle binary Blobs).
The approach I've been slowly moving towards is using `_local` documents (which don't replicate) with attached photos in PouchDB, metadata documents that do replicate with name, date, captions, ULID, resource paths/bucket IDs (and comments or whatever else makes sense) and a Blurhash [2] so there's at least a placeholder to show when photos haven't replicated, and side-banding photo replication to some other Blob storage option (S3 or Azure Storage). It's somewhat disappointing to need two entirely different replication paths (and have to secure both) and multiple storage systems in play, but I haven't found a better approach.
And these things are often stored in databases...
And usually the database puts them into a btree internally, because that's how tables are stored.
The moment you have any kind of load on such a table, your performance goes to hell. This is because the inserts are going to happen all over the place, and the way tables are stored definitely prefers appends.
So a common word of wisdom is to have an auto-increment primary key, and the uuid indexed! Ugh what a work around.
A better way is to discover ULIDs, which are like UUIDs but with the high-bits including a timestamp. This turns inserts into, approximately, appends. Much nicer!
https://github.com/ulid/spec is fairly recent, but the 'trick' has been used for years. I've build gazillion-row databases where all the data sources have, independently, used this same high-bits-are-timestamp trick. So even though I didn't see it discussed and called ULID until fairly recently, its a very old and well-known trick in some circles.
Once you have ULIDs and you know you have UILDs, a lot of database type tasks become easier because the ID encodes a creation date. I've found myself adding AND id BETWEEN to queries, and using them as a partition key, and using them to DELETE old data etc, and various other admin stuff that the original creators of the table never thought about.
Luckily it looks like the author has already written up his thoughts on the differences[2].
[1] https://github.com/ulid/spec [2] https://github.com/segmentio/ksuid/issues/8