Lightnews — Scholar-powered news

v

@avi.im

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)

October 28, 2025 at 3:14 PM

v

@avi.im

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)

October 26, 2025 at 2:38 PM

v

@avi.im

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)

October 26, 2025 at 2:37 PM

v

@avi.im

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.

October 22, 2025 at 3:19 PM

v

@avi.im

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.

October 17, 2025 at 4:33 AM

v

@avi.im

Lil trivia to remember when it comes to Snapshot Isolation

jepsen.io/consistenc...

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

September 23, 2025 at 4:41 AM

v

@avi.im

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.

September 18, 2025 at 2:19 PM

v

@avi.im

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!

September 13, 2025 at 2:50 PM

v

@avi.im

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

September 7, 2025 at 1:38 PM

v

@avi.im

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:

September 6, 2025 at 2:33 PM

v

@avi.im

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.

September 5, 2025 at 1:38 PM

v

@avi.im

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.

September 4, 2025 at 1:38 PM

v

@avi.im

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/

September 4, 2025 at 4:49 AM

v

@avi.im

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.

September 3, 2025 at 1:38 PM

v

@avi.im

those who trade safety for performance deserve neither safety nor performance

September 1, 2025 at 5:32 PM

v

@avi.im

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.

August 31, 2025 at 2:55 PM

v

@avi.im

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.

August 30, 2025 at 2:47 PM

v

@avi.im

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

August 23, 2025 at 6:07 AM

v

@avi.im

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

August 23, 2025 at 6:07 AM

v

@avi.im

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

https://law-theorem.com

August 20, 2025 at 4:57 PM

v

@avi.im

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]

August 20, 2025 at 4:40 AM

v

@avi.im

For example, here's pagination in notorious LIMIT/OFFSET style, but with streaming in Postgres

(got it from my fren claude)

August 17, 2025 at 1:05 PM

v

@avi.im

HN never disappoints 🫡

news.ycombinator.com...

August 14, 2025 at 7:04 AM

v

@avi.im

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

August 9, 2025 at 12:46 PM

v

@avi.im

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.

July 27, 2025 at 12:46 PM

Add to Home Screen

Light up
your news

Add to Home Screen

Light upyour news

Sign in to Lightnews

Sign up to start reading

Connect Bluesky

Connect with Bluesky

Light up
your news