The perils of UUID primary keys in SQLite

Good trick is to prefix all such keys with magic, i.e. a couple of letters that identify type type of key.

Then it will always be a string and you will be free to change the format/type of the key in the future to UUID or whatever you like.

> Javascript WILL interpret your bigints as Number()

A similar horror story from PHP, which I discovered by diagnosing a test failure. (Or maybe it was in production? Long ago, can't remember.)

I think the code in question was for some kind of web auth, comparing random 32-character hexadecimal strings. PHP has a "feature" where its == operator falls back to trying certain strings as numbers... and that includes a version with scientific notation. (12000 == "12000" == "12e3")

Such a collision through bad comparison may seem unlikely, but there are two islands of higher odds: 0*10^X is zero for any X, and X*10^0 is one for any X. Finally, leading zeros can be included. ("0e1234" == "00000e1" and "1234e0" == "9e0000")

The fix was simply going to stricter ===, but it definitely reinforced my dislike of "loose" languages.

!!

Node.js drivers will correctly read int64 as string or bigint, not number.

E.g. pg for PostgreSQL

Maybe there’s a buggy driver but I don’t know it.

Using a Feistel cipher and base 32 encoding at the boundaries of the system can help catching vibe coded edge code that attempt to decode identifiers in javascript. It also somewhat obfuscate the cardinalities and fill rate of the tables.

This can be avoided by supplying a reviver:

    const json = '{ "a": 9007199254740993 }'
    JSON.parse(json, (_key, value, context) => /^\d+$/.test(context.source) ? BigInt(context.source) : value)

Fortunately we're seeing more JS DB libraries offering to read large numbers as the BigInt type.

    const obj = { a: 9007199254740993n }
    JSON.stringify(obj, (_key, value) => typeof value === 'bigint' ? JSON.rawJSON(value.toString()) : value)

    {"a":9007199254740993}

    {"a":"9007199254740993"}

    const json = '{ "a": 9007199254740993 }'
    JSON.parse(json, (_key, value, context) => /^\d+$/.test(context.source) ? BigInt(context.source) : value)

You can achieve this with numeric sequences too, by having a consistent step and unique offset in all your sequences. For example, if you will never exceed 16 types, reserve four bits as the type discriminant. (You don’t have to use powers of two, but it may be convenient.)

All sequences use step 16.

Type A has discriminant/offset 0, yielding IDs {0, 16, 32, 48, 64, …}.

Type B has discriminant/offset 1, mapping to IDs {1, 17, 33, 49, 65, …}.

All the way up to Type P with discriminant/offset 15 and IDs {15, 31, 47, 63, 79, …}.

This is also trivially invertible so that you can determine the type from the ID.

A more common approach is to make IDs opaque strings and put a type prefix—A0, B12, P34, that kind of thing. But this way you can keep it as a number, if you wish.

Alternatively just use a shared sequence for all tables.

Or just write tests, instead of relying on statistical improbability to prevent disaster.

Yeah this is nice - also helps with grepping dump files.

How is this done?

Every DB, even MySQL can return the autoincrementing integer for you as part of the insert. Postgres, SQLite, and MariaDB (likely others, I’m just not familiar) can even return the rest of the data, should you need that.

IME, most of the arguments for why UUIDs make things better are due to developer ignorance of RDBMS features (or B+tree performance).

They are generally distributed in such a way that values created at nearly the same time dont cluster together which is less efficient for DBs

Well, I expect to never need WITHOUT ROWID. And even if such an arcane situation hits my system, WITHOUT ROWID has so many ifs and buts that I'll probably elect to eat the $$$ cost of running an un-optimised normie SQLite as far as possible.

cf. https://sqlite.org/withoutrowid.html

> The WITHOUT ROWID syntax is an optimization. It provides no new capabilities. Anything that can be done using a WITHOUT ROWID table can also be done in exactly the same way, and exactly the same syntax, using an ordinary rowid table. The only advantage of a WITHOUT ROWID table is that it can sometimes use less disk space and/or perform a little faster than an ordinary rowid table.

As of now, I am doing the following in my (Bitemporal data system) experiment (When will it see the light of day? Nobody knows.).

All data are globally uniquely identified by a UUIDv7. However all tables have `rowid` integer primary key asc (which is just an alias for SQLite's autoincrement int id). The `rowid` is the basis for joins, and is the foreign key reference. This lets me offload some useful disambiguation work to the DB as well as have it enforce global (across data systems) record uniqueness guarantees, while retaining local (within process) query efficiency by retaining the ability to use integer rowids.

While the idealised insert performance in your bench is indeed mind-boggling, the DB Schema isn't doing anything CPU-intensive during inserts (checks, constraints, triggers etc.). My schema / query pattern yields comparatively meagre throughput, but I am happy with the ballpark it has landed in, given all the work I'm making SQLite do for me on each `assert!` and `redact!`.

cf. my dirty-but-useful-enough bench, with production-like record content:

A poor man's napkin-mathy, append-only SQLite write/read benchmark

https://gist.github.com/adityaathalye/3c8195dc70626b33c23867...

Summary:

  ;; Okay, I think I can live with this...

  ;; - "facts" table: 12M+ records
  ;;     - single process writes to it
  ;;     - ~ 400 transactions/second
  ;;     - append-only table, enforced via SQLite "before" triggers
  ;; - "now" table: 
  ;;     - updates on every assert/redact on "facts" table, via triggers
  ;;     - currently at "limit case": for each read it is empty, or very small, because writes do back-to-back assert/redact of the same fact
  ;;     - gets reads from two reader threads (evenly split)
  ;;     - ~41,000 reads/second
  ;; - all reads are concurrent with writes (poor man's futures)

If you had read the article, you'd have seen that UUIDv4 with Rowid was slightly slower than UUIDv7

Oh yes, I meant don’t store as an ID in its string format!

Update the article there's now a section for UUID4 with rowid. It's less bad than UUID4 without rowid but it's still about 4-6x slower than UUID7 without rowid.

The reason to use it is that it skips the double lookup. A normal rowid table with a UUID primary key keeps two B-trees: the table itself keyed by the hidden rowid, and a separate index from your UUID to that rowid. A lookup by UUID walks the index to find the rowid, then walks the table to find the row. WITHOUT ROWID makes the UUID the table's key directly, so the row sits in that leaf and you walk one tree instead of two, and you don't store the UUID a second time.

The tradeoff is what the benchmark is hitting. Once the table is physically ordered by the key, a random v4 scatters every insert across the tree and you pay for the page splits. A plain rowid table keeps that churn in the secondary index, which is just the key plus a rowid, while the table itself stays append-ordered. So it only really pays off when the key is something you look up directly and is roughly sequential, which is why v7 comes back near baseline.

It's more for performance that you shouldn't use them as PK's - If you insert a lot you'll get massive fragmentation over time. A sequential Id avoids that and still gives you a unique row.

The Guid is purely for an external system to grab onto something that I can tie back to an actual row in the database but the external system does not need to know anything about the backend other than <guid>.

It's running on an M1 mac with synchronous full. Wouldn't surprise me if it's possible to get higher numbers.

Except this source code is not using :memory: The linked source code has

    (defonce db
      (d/init-db! "db/db.db"
        {:pool-size 4 :pragma {:synchronous "FULL"}}))

That's writing to disk.

Although not as prominent as insert SELECT and UPDATE both benefit from page cache locality, assuming rows that are stored near each other are often selected/updated together.