iiinova — where your digital self keeps evolving

I explained about SQLite's neat little trick here about reserved space management per page (5/5) bsky.app/profile/did:...

000

For example, if we use AEGIS-256 with a nonce size of 32 bytes and a 16-byte tag, you'd need extra space for 48 bytes per page. During decryption, you read the tag and nonce from the reserved space and provide them to the decryption algorithm. (4/5)

100

The size of this tag and nonce varies by algorithm. To make space, SQLite uses reserved space per page. Once the page is encrypted, this portion can carry the metadata. (3/5)

100

Since we encrypt each page separately, we should use a different nonce for different pages. Even the same page, when encrypted again, should use a different nonce for better security™️ So during encryption, we generate a secure random nonce every time. (2/5)

100

AEADs provide a verification tag after encryption. For each page, we need a nonce too. Both the nonce & the tag become metadata for an encrypted page So where do you store them? We could store them separately, but it's much better & neater to store them in the page itself (1/5)

240

Btw, SQLite also has `secure_delete` pragma setting which overwrites deleted content with zeros. (9/9)

000

SQLite uses this neat trick for its "reserved space" feature. Extensions can store any data they want in each page without interfering with B Tree operations. Extensions like encryption and checksums need to store extra metadata per page, and they use this reserved space. (8/9)

Now, remember how I said cells start from the rightmost end? If you intentionally leave some space at that end before starting the cells, the B Tree would work exactly the same. That space can contain anything, and the B Tree would never touch it since there's no pointer to it. (7/9)

This means garbage data from previous uses can actually get written back to disk. I don't know any other data structure that works like this. (6/9)

100

Calling memset (the API that zeroes every bit) adds latency, so as an optimization, databases might skip it entirely. Similarly, a deletion of cell is just removing the pointer, but the data might remain as it is. (5/9)

100

It could have garbage data sitting around, but the page would never access it since no cell pointer references it. Databases also maintain a buffer pool, think of it as a cache of pages loaded from disk. These pages often get reused. (4/9)

100

while the cells themselves grow from right to left, meeting somewhere in the middle. That middle section is logically free space, but here's the interesting part is it can contain any data. Neither the page nor the B Tree cares about what's in there. (3/9)

100

One common way to organize data within a page is the slotted page structure. It starts with a header, followed by a bunch of cell pointers. These pointers reference cells at the end of the page. As you add more data, the pointers grow from left to right (2/9)

100

Query Limits and Timeouts | MariaDB Documentation

The B Tree data structure fascinates me. Databases use B Trees to store data on disk, organizing everything into pages that typically range from 4kb to 8kb. All I/O operations happen in units of these pages. The page looks like this... (1/9)

170

v @avi.im · 23/10/2025

seems it works on Maria too mariadb.com/docs/server/...

mariadb.com

010

v @avi.im · 22/10/2025

BTW, SQL_SAFE_UPDATES also auto-enables two other protections: - Limits SELECT to 1,000 rows (no more accidental SELECT * on giant tables) - Caps joins at 1M row combinations You can override these with --select-limit and --max-join-size if needed.

v @avi.im · 22/10/2025

Pro database tip: enable `SQL_SAFE_UPDATES` in MySQL to avoid accidental UPDATE/DELETE queries without a WHERE clause. It forces you to use a key or a LIMIT, instead of wiping whole database by mistake at 2:19am.

2172

PS: I'm taking some liberties calling these "shards" — they're really just isolated databases. But I'd still call it a single logical db since they share a schema and present a unified interface, even if you can't query across them.

000

You'd have to handle schema management manually or do it in the application layer. You also can't do mass migrations easily. I call this Limitless Sharding.

100

Even on a tiny machine, I can run millions of SQLite databases. The downsides: You'd only need this at "scale". The entire architecture assumes you don't need cross-shard queries. So this only works for applications where each document is truly an individual entity.

Since some of these embedded databases can run as WASM in the browser, each document could sync its own file directly with a backend db with some CRDT. I'd bet this model can handle way more writes per second than a sharded Postgres/MySQL setup.

A Figma document could just be stored as a SQLite file in the backend. You'd also need libSQL or SQLite with lightstream for backup/replication.

If each document is its own database, it can easily handle all requests without breaking a sweat. Running a million Postgres or MySQL instances sounds crazy and is total overkill. But embedded databases like SQLite, lmdb, and RocksDB fit this perfectly. They're just files!

100

What if we ran disconnected databases that all acted as a single logical db? Think of applications like Notion, Figma, or Google Docs — each document is disconnected from the others. The key insight: each document gets very few write requests since users are making changes manually.

100

Usually there's a proxy that routes queries to the right shards and does aggregation for cross-shard queries. Even though the db is split across multiple machines, it's still logically a single database. They're all "connected". But...what if we flipped this around?

100

Andy Pavlo @andypavlo.bsky.social · 18/09/2025

Sharding. Database sharding is one of the common techniques to scale a database horizontally. You split the db into small parts called shards and distribute them across machines. Shards are typically in the few hundreds or even thousands (for extremely large databases).

130

v @avi.im · 17/10/2025

on orange site - news.ycombinator.com...

000

v @avi.im · 17/10/2025

SQLite has a page where they explain why they use C. They specifically elaborate on why not Rust www.sqlite.org/whyc....

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.

3251

v @avi.im · 23/09/2025

Lil trivia to remember when it comes to Snapshot Isolation jepsen.io/consistenc...

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

210

Reposted by v

Next week is the start of @db.cs.cmu.edu's latest seminar series: Future Data Systems @samarchdb.bsky.social and I are hosting speakers from leading systems in the datalake / lakehouse space. Mondays @ 4:30pm ET via Zoom. Open to the public. Videos posted to YouTube: db.cs.cmu.edu/seminars/fal...

Carnegie Mellon University
Future Data Systems
Fall 2025 Seminar Series
Mondays @ 4:30pm ET

14013

v @avi.im · 18/09/2025

read more about SI here: jepsen.io/consistency/...

jepsen.io

Snapshot Isolation

010

v @avi.im · 18/09/2025

The correct answer is either. Transaction B gets a snapshot that may or may not include the changes from A. SI does not provide real time guarantees. If you need that, you need Strict Serializability, which guarantees that transactions are ordered in real time.

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.

131

v @avi.im · 16/09/2025

Database systems question Assume the database is in snapshot isolation mode. If transaction A updates, and writes x, commits, *then* transaction B starts and reads x's value, then B will see (assume single node for simplcity): 1 - Value before A's write 2 - Value written by A 3 - Either 4 - 🍿

221

v @avi.im · 13/09/2025

Yes! Chroma’s post is how I learned about setsum and I have mentioned in the blog post too!

010

v @avi.im · 13/09/2025

Published a new blog post: Setsum - order agnostic, additive, subtractive checksum post - avi.im/blag/2025/setsum code - github.com/avinassh/...

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!

1133

7. DST (Deterministic Simulation Testing) - very few databases do DST, where everything runs in a simulated universe. You can stop/pause time, get the same random data, and reproduce the most complicated bugs easily. The entire DST code is under 10K lines. So anon, are going to locked in?

100

When in doubt, you can run SQLite and compare how it behaves. 6. It has lots of fancy new stuff that isn't available in SQLite: asynchronous I/O (with io_uring), Materialized Views (IVM), and MVCC.

100

However, it's large enough to have most database features. 4. It is an embedded DB, which makes some things simpler, like running and debugging. 5. The code and features closely follow SQLite. If you run into an issue, you can refer to SQLite. Most LLMs know SQLite's internals.

100

2. It's in Rust. Compared to other systems programming languages, I find Rust to be the easiest to get into and start making changes. The compiler nicely guides you along the way. 3. The codebase is still small; small enough to keep most things in your head.

100