91–100 of 117 posts
Earlier quoted context omitted.
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.
Rule of thumb: if you’re not doing math with a value, it’s not a number.
Thanks for the benching, Anders! So grateful for the stuff you've shared over the years. Invariably, every single post has been useful and/or educational to me. I read this post more as an illustration of the *value* of UUIDv7 as primary key, over integer primary keys , in lieu of minimal loss of read/write performance, and marginally more data on disk bloat. SQLite's automatic integer rowID primary key is a no-brain…
I've updated the article with the correct rowid alias (integer not int) so the rowid version is now 715ms. I've also added an example of rowid and a secondary index UUID4, and that also seems to be bad for performance (as although it's not a clustered index it's still random inserts into a b-tree).
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)How much time is `(random-uuid7-bytes)` taking?
How much time is `(random-uuid7-bytes)` taking?
I can't believe I had to scroll down to this far to see someone making this point. Also INSERT speed instead of SELECT? Typically most time is spend in SELECT or UPDATE.
Earlier quoted context omitted.
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)
Which can be avoided by using UUIDs
Earlier quoted context omitted.
Statistically impossible to inadvertently generate a collision using UUID keys. UUID is designed to be unique when generated across any computer system. Practically speaking if you have an exactly matching pair of UUIDs from disparate system you have found the exact record match. The name gives a hint "Universally unique identifier". -Not a cryptographer.
It definitely is possible, just very improbable
Earlier quoted context omitted.
!! 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.
Browser!! The browser reads it as Number. If your rest api returns {"id": 1324535222364012585} for example, javascript will try and parse that as number from the response!!! You can of course, change the api such that it does {"id": "1324535222364012585"} instead and voila, it will no longer try parsing it as number. Or the many other workarounds people have recommended above (like appending a prefix, or using a diff…
You seem to be talking about JSON. (Which technically has no limit on number size or precision, but in practice is float64.)
UUIDs are way over used. There is almost always a better key to use, usually a bigint for databases. If you're making some kind of leaderless distributed data store, then maybe, but even then there are other ID sharding strategies I'd go for first depending on the constraints. For a single database, bigints are smaller and faster, with less footguns. UUIDs can be nice for an opaque public ID, however I'd still prefer…
UUIDs also have a nice benefit of it being impossible to query the wrong table with one if you mixup what an FK goes to
Earlier quoted context omitted.
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.
Rule of thumb: if you’re not doing math with a value, it’s not a number.
Earlier quoted context omitted.
How do I know the time zone of an integer? Sure there are plenty of cases where one doesn't care, but there are also many cases where the original time zone is important.
The integer is a UTC time so it can be sorted. If you need the time zone you store than in a smaller field.
Eg I save a date 18 months in the future in US Western time at 0600. 3 months from now the US gets rid of Daylight Savings. You saved it as 1400 UTC (which was correct), which is now an hour off because the local time zone is different.
Encoding it in local time fixes that, because it delays the TZ conversion until you need it and your times stay correct as long as you update tzdb like once a month.