iiinova — where your digital self keeps evolving

v @avi.im · 21/04/2026

I often get emails asking for ideas (or materials) to learn about distributed systems. I always recommend getting started with Gossip Glomers. Only six challenges, some easy and some difficult, but an absolute top tier fun and learning experience fly.io/dist-sys

2223

v @avi.im · 17/04/2026

Here is an insane piece of lore inside SQLite's source code I am researching VACUUM and I was studying their code. In VACUUM, SQLite creates a temp file prefixed with `etilqs_` Here is why:

** 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

3444106

v @avi.im · 25/01/2026

redditor explains why job hunting in tech is exactly like auditioning for acting roles

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

180

v @avi.im · 28/10/2025

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

v @avi.im · 26/10/2025

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)

110

v @avi.im · 26/10/2025

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 · 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

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

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 · 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

v @avi.im · 07/09/2025

This is the opening text of Transaction Processing: Concepts and Techniques by Jim Gray "Six thousand years ago, the Sumerians invented writing for transaction processing."

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

031

v @avi.im · 06/09/2025

Published a new post: Oldest recorded transaction. This totally could have been just a tweet (skeet?), but I wanted to publish something today. avi.im/blag/2025/old...

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:

141

v @avi.im · 05/09/2025

My extreme opinion is that anything other than serializable isolation is a scam. Database people haven't figured out how to make it fast, so we have ended up with other half baked isolation levels.

030

v @avi.im · 04/09/2025

This is the oldest transaction database from 3100 BC - recording accounts of malt and barley groats. Considering this thing survived 5000 years (holy shit!) with zero downtime and has stronger durability guarantees than most databases today. I call it rock solid durability.

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

130

v @avi.im · 04/09/2025

btw, the new semester of the database systems course is back! The first video is already out, with an assignment where you implement a Count-Min Sketch 15445.courses.cs.cmu.edu/fall2025/

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

050

v @avi.im · 03/09/2025

your priorities need to be in the right places if you want to pursue a database-centric lifestyle

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.

190

v @avi.im · 31/08/2025

Published a new blog post: Replacing a Cache Service with a Database We already use databases, why can't we use them to replace caches as well? Will we ever replace caches entirely with databases? In this post, I will share some ideas, & discuss how we are moving toward this avi.im/blag/2025/db...

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.

4143

v @avi.im · 30/08/2025

Published a new blog post: SQLite commits are not durable under default settings avi.im/blag/2025/sq...

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.

061

v @avi.im · 23/08/2025

as if the topic of databases wasn't hard enough alone, now we gotta decipher math with digital signal processing

020

v @avi.im · 23/08/2025

Weekend paper time! One more attempt to read this paper before I give up again

171

v @avi.im · 20/08/2025

A new theorem called the L2AW just dropped. It's kind of an alternative to the CAP theorem! law-theorem.com

040

v @avi.im · 20/08/2025

thankfully this is not an issue for databases, as most of them have checksums and handle corruptions gracefully

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

161

v @avi.im · 17/08/2025

For example, here's pagination in notorious LIMIT/OFFSET style, but with streaming in Postgres (got it from my fren claude)

110

v @avi.im · 14/08/2025

HN never disappoints 🫡 news.ycombinator.com...

020

v @avi.im · 09/08/2025

DuckDB on checksums:

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

120

v @avi.im · 27/07/2025

I always thought Go FFI was slow af. This is a common assumption in the Go community too. A couple of years ago, there used to be so many posts about how slow it is. Turns out it may not be true and Go might have improved.

120

v @avi.im · 24/07/2025

Published a new post: "PSA: SQLite WAL checksums fail silently and may lose data" This is a follow up to my previous posts. When SQLite encounters checksum failures in WAL, instead of raising an error, it drops all subsequent frames; even if they are not corrupt. It's not a bug

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.

121

v @avi.im · 24/07/2025

Loved reading this excellent post by Marc Brooker and Ankush Desai: Systems Correctness Practices at Amazon Web Services on how they use formal methods internally at AWS. One of the best articles I've read this year on formal verification and testing.

130

v @avi.im · 21/07/2025

Published a new blog post: Rickrolling Turso DB avi.im/blag/2025/ri...

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.

030

v @avi.im · 20/07/2025

I found out about lsr, an ls rewrite in Zig using io_uring by @rockorager.dev, started as a fun experiment to learn io_uring. It uses 35x fewer sys calls! I loved reading the companion blog post too.

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

190

v @avi.im · 20/07/2025

CedarDB on why they picked C++ over Rust

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

091

v @avi.im · 20/07/2025

matklad on why TigerBeetle picked Zig linked post - matklad.github.io/2023/03/26/z...

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)

0100

v @avi.im · 19/07/2025

I don't have more information, but this comment by Marc Brooker provides more context lobste.rs/s/cbd7rn/jus...

040

v @avi.im · 07/07/2025

kek, Jane Street came under SEBI's scanner because they sued another firm for stealing their "strategy." This is a movie plot right here.

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.

030

v @avi.im · 07/07/2025

What's the type affinity if there are multiple types? SQLite has a precedence table and based on it, it selects one of them. It's also wild that it considers words like CLOB or DOUB

** 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.

060

v @avi.im · 06/07/2025

sorry not sorry but you gotta know this cursed SQLite fact too

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”.

46422

v @avi.im · 05/07/2025

This is from a 40 year old textbook: The database should guarantee durability under the weakest possible system assumptions. That includes hardware corruption, yet no mainstream database today cares about it. Most just assume hardware is sound. (TigerBeetle is an exception)

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.

291

v @avi.im · 04/07/2025

Here is the full SEBI report: sebi.gov.in/enforcement/... (pdf). Pages 12-13 are highly relevant

130

v @avi.im · 28/06/2025

god bless clippy for idiomatic rust suggestions

$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"` |$

150

v @avi.im · 16/06/2025

Performance optimisation trick: by doing fewer things, you can reduce latency by a large extent

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

071

v @avi.im · 08/06/2025

Sunday morning read jepsen.io/analyses/t...

041

v @avi.im · 04/06/2025

You know it's gonna be a great week when DST (Deterministic Simulation Testing) finds a bug in itself

DST found a bug in itself. We have achieved AGI

020

v @avi.im · 19/05/2025

turns out you can keep doing this

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

120

v @avi.im · 18/05/2025

If you wish to explore, here is the paper 'Scalable In-Memory Transaction Processing with HTM.' They combine Optimistic Concurrency Control and fine-grained locks to achieve insanely high throughput

041

v @avi.im · 15/05/2025

Performance optimisation trick: to save DRAM (and also cost), you can just store fewer things in it

grafana viz showing memory reduction to almost half

010

v @avi.im · 14/05/2025

If you aren't rust maxxing like this, ngmi

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

100