v

@avi.im

1459 Followers 584 Following

breaking databases @tur.so W1 '21 @recursecenter.bsky.social excited about databases, storage engines and message queues

gossip glomers

** 2006-10-31: The default prefix used to be "sqlite_". But then
** Mcafee started using SQLite in their anti-virus product and it
** started putting files with the "sqlite" name in the c:/temp folder.
** This annoyed many windows users. Those users would then do a
** Google search for "sqlite", find the telephone numbers of the
** developers and call to wake them up at night and complain.
** For this reason, the default name prefix is changed to be "sqlite"
** spelled backwards. So the temp files are still identified, but
** anybody smart enough to figure out the code is also likely smart
** enough to know that calling the developer will not help get rid
** of the file.
*/
#ifndef SQLITE_TEMP_FILE_PREFIX
# define SQLITE_TEMP_FILE_PREFIX "etilqs_"
#endif

I have the benefit of having an actress in my life, so I get to watch the process of auditioning.

Auditioning is a crap-shoot. You never really know what the director is looking for. They may have someone in mind already and the whole audition is a courtesy / box-check on some grant program they're operating the theater under. You do your best, you take rejection, you suit up and do it again. She does some additional work: she researches the theaters, keeps her ear to the ground, talks to other actors in the area about their experience (that's easier with actors, where the gigs are one-offs; software engineers aren't moving as fast through industry so you have to build a larger contact web to get a richer picture of who's hiring in your area).

But the biggest thing that controls whether you get a role is if you keep showing up. The role finds you; your control over getting the role (unless you're a big enough name that people recognize it) is minimal.

I know it's not the most comfor

SQLite reserved space

page with reserved space

The page strcture

example usage of mysql safe updates

All that said, it is possible that SQLite might one day be recoded in Rust. Recoding SQLite in Go is unlikely since Go hates assert(). But Rust is a possibility. Some preconditions that must occur before SQLite is recoded in Rust include:

Rust needs to mature a little more, stop changing so fast, and move further toward being old and boring.
Rust needs to demonstrate that it can be used to create general-purpose libraries that are callable from all other programming languages.
Rust needs to demonstrate that it can produce object code that works on obscure embedded devices, including devices that lack an operating system.
Rust needs to pick up the necessary tooling that enables one to do 100% branch coverage testing of the compiled binaries.
Rust needs a mechanism to recover gracefully from OOM errors.
Rust needs to demonstrate that it can do the kinds of work that C does in SQLite without a significant speed penalty.

https://jepsen.io/consistency/models/snapshot-isolation

Snapshot isolation implies read committed. However, it does not impose any real-time constraints. If process A completes write w, then process B begins a read r, r is not necessarily guaranteed to observe w. Some databases provide real-time variants of snapshot isolation. Compare with strict serializability, which provides a total order and real-time guarantees.

Setsum - order agnostic, additive, subtractive checksum

Say you’re building a database replication system. The primary sends logical operations to replicas, which apply them in order:

{"op": "add", "id": "apple"}
{"op": "add", "id": "apple"}
{"op": "remove", "id": "orange"}

After the replica processes these changes (add two apples, remove an orange), how do you verify both nodes ended up in the same state? One naive (rather horrible) approach is to dump both states and compare them directly. It’s expensive, impractical, and doesn’t scale!

Six thousand years ago, the Sumerians invented writing for transaction processing

The other day I posted a tweet with this image which I thought was funny:

This got me thinking, can I insert this date in today’s database? What is the oldest timestamp a database can support?

So I checked the top three databases: MySQL, Postgres, and SQLite:

Skeletor running away meme

A cuneiform tablet about an administrative account, with entries concerning malt and barley groats, 3100–2900 BC.

CMU course home page - https://15445.courses.cs.cmu.edu/fall2025/

Q10: Is it a good idea to start a family while taking this course?

No, we strongly advise that every student who is serious about pursuing a database-centric lifestyle refrain from acquiring, obtaining, or receiving a baby during the semester. Children are a crushing obligation that requires at least 30 hours of your time per week, which could have been spent learning about and working on database systems instead.

those who trade safety for performance deserve neither safety nor performance

Why do we even use caches?

Caches solve one important problem: providing pre-computed data at insanely low latencies, compared to databases. I am talking about typical use cases where we use a cache along with the db (cache aside pattern), where the application always talks with cache and database, tries to keep the cache up to date with the db. There are other patterns where cache itself talks with DBs, but I think this is the more common pattern where application talks to both cache and database.

Previously, I claimed that transactions in SQLite with WAL are not durable under default settings. Turns out, I was only half wrong but technically correct; the issue is actually with SQLite in rollback journal mode (the default). This post is now amended with the changes.

Here’s what I mean by durability: when the database acknowledges that a transaction is committed, it’s ‘durably’ saved to disk. That is, neither an application crash nor an OS crash should make that transaction disappear. Imagine you make a new commit, the db acknowledges success, and suddenly your OS reboots. Do you expect your transaction changes to be persisted? For example, in Postgres you can expect your changes to be there. This is how most OLTP databases behave.

DBSP front matter

https://law-theorem.com/

Report: Microsoft's latest Windows 11 24H2 update breaks SSDs/HDDs, may corrupt your data [Update]

A more advanced future direction is self-checking. We
have learned to distrust the hardware the database is running
on. This is particularly relevant in the edge computing use
case, where hardware failures are to be commonplace. One
approach is to keep checksums on all persistent and intermediate data and piggy-back checksum verification on scan
operators. This might be possible without a significant performance impact. A vectorized engine is particularly suited
for this since a chunk of data typically fits in the CPU cache
and additional passes are not requiring RAM access

PSA: SQLite WAL checksums fail silently and may lose data

This is a follow-up post to my PSA: SQLite does not do checksums and PSA: Most databases do not do checksums by default. In the previous posts I mentioned that SQLite does not do checksums by default, but it has checksums in WAL mode. However, on checksum errors, instead of raising error, it drops all the subsequent frames. Even if they are not corrupt. This is not a bug; it’s intentional.

Rickrolling Turso DB (SQLite rewrite in Rust)

This is a beginner’s guide to hacking into Turso DB (formerly known as Limbo), the SQLite rewrite in Rust. In this short post, I will explore how to get familiar with Turso’s codebase, tooling, and tests (hereafter mentioned as just Turso).

Disclosure: I work in the same organization where Turso is being developed. However, I am not a contributor (yet) and most of my work is on the Turso Server.

I don’t contribute to the Turso Database, but I am somewhat familiar with SQLite. I wanted to take Turso for a spin and explore the codebase. I timeboxed my experiment for 6 hours, including writing this blog post. Do note that Turso is under heavy development, so much so that core developers spend more time resolving merge conflicts than writing code. So expect most of the content in this blog post to get obsoleted by next month.

Syscalls
Data gathered with strace -c on a directory of n plain files. (Lower is better)

Rust is a great language. I really enjoy using it. However, when it comes to database systems, I would still choose C++ over Rust.

I know this may be unpopular, but during the development of CedarDB, we discussed this and decided to continue using C++. The main reasons were safety and undefined behavior (UB).

Shouldn't safety be a reason to choose Rust over C++? In theory, yes. That's what I personally enjoy about Rust: you get much more safety by default than in C++. However, when you absolutely need to use potentially unsafe code, Rust makes things much harder. In a code-generating database system like CedarDB, which uses many low-level CPU features, intrusive data structures, and optimistic, racy memory access with validation, you would definitely need the unsafe keyword.

Once you write unsafe code in Rust, all bets are off. It's very easy to run into UB with unsafe code, much easier than with C++. The rules of what constitutes UB are already complex in C++ (I would know, I have

The super high-level answer is that in the context of TigerBeetle the tradeoffs weighed in favor of Zig. I wrote a bit about the relevant context here:

https://matklad.github.io/2023/03/26/zig-and-rust.html

The main benefit Rust provides is memory safety through composable abstractions. Due to TigerBeetle’s architecture (no dynamic resource allocation, single threaded control loop, tight direct integration between network/store/state transition rules, simulation-based testing), the magnitude of this benefit is relatively smaller than it usually is. OTOH, the benefits of Zig loom larger:

simple language
ability to both use standard hash map and at the same time statically guarantee that no allocation happens after startup
more expressiveness/less noise for some lower-level things (pointers that carry alignment in the type, mem.asBytes, some more advanced meta programming)

It all started in a New York courtroom in April 2024. Jane Street sued Millennium Management, accusing them of stealing a proprietary, multi-billion dollar trading strategy involving Indian index options. They settled quickly, but the case caught SEBI's attention.

** This routine does a case-independent search of zType for the
** substrings in the following table. If one of the substrings is
** found, the corresponding affinity is returned. If zType contains
** more than one of the substrings, entries toward the top of
** the table take priority. For example, if zType is 'BLOBINT',
** SQLITE_AFF_INTEGER is returned.

** Substring | Affinity
** --------------------------------
** 'INT' | SQLITE_AFF_INTEGER
** 'CHAR' | SQLITE_AFF_TEXT
** 'CLOB' | SQLITE_AFF_TEXT
** 'TEXT' | SQLITE_AFF_TEXT
** 'BLOB' | SQLITE_AFF_BLOB
** 'REAL' | SQLITE_AFF_REAL
** 'FLOA' | SQLITE_AFF_REAL
** 'DOUB' | SQLITE_AFF_REAL
**
** If none of the substrings in the above table are found,
** SQLITE_AFF_NUMERIC is returned.

Not only that, it does not throw any error if you give some random type.

CREATE TABLE t(value TIMMYSTAMP);

There is no TIMMYSTAMP type, but SQLite accepts this happily.

SQLite has five types: NULL, INTEGER, REAL, TEXT, BLOB. Want to know something cursed? The type affinity works by substring match!

CREATE TABLE t(value SPONGEBLOB) --- This is BLOB type!
So yeah, this happens too:

Note that a declared type of “FLOATING POINT” would give INTEGER affinity, not REAL affinity, due to the “INT” at the end of “POINT”.

The DBS should guarantee the permanence of Commit under the weakest
possible assumptions about the correct operation of hardware, systems soft-
ware, and application software. That is, it should be able to handle as wide a
variety of errors as possible. At least, it should ensure that data written by
committed transactions is not lost as a consequence of a computer or operating
system failure that corrupts main memory but leaves disk storage unaffected.

$error: manually constructing a nul-terminated string --> server/src/sqlite/connection.rs:583:17 | 583 | b"main\0".as_ptr() as *const _, | ^^^^^^^^^ help: use a `c""` literal: `c"main"` |$

Line graph comparing execution time for two methods ("Original" in red and "Optimized" in green) across increasing database sizes (16MB to 1GB)

DST found a bug in itself. We have achieved AGI

same application, the usage now halved from 700MB to 300MB

grafana viz showing memory reduction to almost half

https://github.com/oxidecomputer/omicron/blob/5fd1c35/nexus/db-queries/src/db/pagination.rs#L78-L181

type aliases in Odin - https://odin-lang.org/docs/overview/#type-alias

rust code: https://play.rust-lang.org/?version=stable&mode=debug&edition=2024&gist=1485a2a67aa3c4ceed4ee0d221d792ad

Matt Godbolt sold me on Rust (by showing me C++)

Matt Godbolt, of Compiler Explorer fame, is awesome and you should scour the web for every single bit of content he puts out. That is exactly what I was doing when I watched Correct by Construction: APIs That Are Easy to Use and Hard to Misuse. After 20+ years of working with C/C++, this theme resonates a lot with me.

While watching the talk I kept thinking "Yes! And that's why Rust does it that way." I came out at the end thinking that this talk was actually a great way of getting the intuition for how Rust helps you beyond the whole memory safety thing, and that is what this article intends to show.

titled: popularity distributions are diffuse. request popularities are roughly assumed to follow the 80/20 rule

slide from the talk cachelib: https://www.youtube.com/watch?v=JIM08lWPvNs

the birth of komora

- I decided to try to rewrite sled in a day
- The next day, I would throw it away and start over
- I would keep the tests and high level interfaces
- Often, I would decide to just focus on a small recurring component
- komora was born

pipe syntax paper, linked in the next tweet

"What Goes Around Comes Around... And Around..." paper first page, linked next tweet

What If We Could Rebuild Kafka From Scratch?

The last few days I spent some time digging into the recently announced KIP-1150 ("Diskless Kafka"), as well AutoMQ’s Kafka fork, tightly integrating Apache Kafka and object storage, such as S3. Following the example set by WarpStream, these projects aim to substantially improve the experience of using Kafka in cloud environments, providing better elasticity, drastically reducing cost, and paving the way towards native lakehouse integration.

image is a screenshot of current /r/rust weekly posts, tagged with projects:

Is Rust faster than Fortran and C++? A case study with scientific applications.

gm-quic: A native asynchronous Rust implementation of the QUIC protocol

rzozowski: a Brzozowski derivative-based regex crate for the real world

Built db2vec in Rust (2nd project, 58 days in) because Python was too slow for embedding millions of records from DB dumps.

Nebulla, my lightweight, high-performance text embedding model implemented in Rust.🛠️ project

Mountpoint for Amazon S3 is a simple, high-throughput file client for mounting an Amazon S3 bucket as a local file system. With Mountpoint for Amazon S3, your applications can access objects stored in Amazon S3 through file operations like open and read. Mountpoint for Amazon S3 automatically translates these operations into S3 object API calls, giving your applications access to the elastic storage and throughput of Amazon S3 through a file interface.