What Does a Database for SSDs Look Like? • Hacker News Board

Re: What Does a Database for SSDs Look Like?

#101

Post by loeg » Sat, Dec 20, 2025, 6:32 PM UTC

crazygringo wrote: post #12

mrkeen wrote: post #2

> Design decisions like write-ahead logs, large page sizes, and buffering table writes in bulk were built around disks where I/O was SLOW, and where sequential I/O was order(s)-of-magnitude faster than random. Overall speed is irrelevant, what mattered was the relative speed difference between sequential and random access. And since there's still a massive difference between sequential and random access with SSDs, I…

Can you clarify? I thought a major benefit of SSDs is that there isn't any difference between sequential and random access. There's no physical head that needs to move. Edit: thank you for all the answers -- very educational, TIL!

Some discussion in the FragPicker paper (2021) FWIW: https://dl.acm.org/doi/10.1145/3477132.3483593

> Our extensive experiments discover that, unlike HDDs, the performance degradation of modern storage devices incurred by fragmentation mainly stems from request splitting, where a single I/O request is split into multiple ones.

Re: What Does a Database for SSDs Look Like?

#102

Post by loeg » Sat, Dec 20, 2025, 6:38 PM UTC

koverstreet wrote: post #61

zokier wrote: post #5

Author could have started by surveying current state of art instead of just falsely assuming that DB devs have just been resting on the laurels for past decades. If you want to see (relational) DB for SSD just check out stuff like myrocks on zenfs+; it's pretty impressive stuff.

bcachefs's btree still beats the pants off of the entire rocksdb lineage :)

Aren't B-trees and LSM-trees fundamentally different tradeoffs? B-trees will always win in some read-biased workloads, and LSM-trees in other write-biased workloads (with B epsilon (Bε) trees somewhere in the middle).

Re: What Does a Database for SSDs Look Like?

#103

Post by Lwerewolf » Sat, Dec 20, 2025, 6:43 PM UTC

mrkeen wrote: post #2

> Design decisions like write-ahead logs, large page sizes, and buffering table writes in bulk were built around disks where I/O was SLOW, and where sequential I/O was order(s)-of-magnitude faster than random. Overall speed is irrelevant, what mattered was the relative speed difference between sequential and random access. And since there's still a massive difference between sequential and random access with SSDs, I…

Same with doing things in RAM as well. Sequential writes and cache-friendly reads, which b-trees tend to achieve for any definition of cache. Some compaction/GC/whatever step at some point. Nothing's fundamentally changed, right?

Re: What Does a Database for SSDs Look Like?

#104

Post by bee_rider » Sat, Dec 20, 2025, 6:44 PM UTC

amluto wrote: post #52

cornholio wrote: post #49

You know you need to be careful when an Amazon engineer will argue for a database architecture that fully leverages (and makes you dependent of) the strengths of their employer's product. In particular: > Commit-to-disk on a single system is both unnecessary (because we can replicate across storage on multiple systems) and inadequate (because we don’t want to lose writes even if a single system fails). This is surely…

Skipping flushing the local disk seems rather silly to me: - A modern high end SSD commits faster than the one way time to anywhere much farther than a few miles away. (Do the math. A few tens of microseconds specified write latency is pretty common. NVDIMMs (a sadly dying technology) can do even better. The speed of light is only so fast. - Unfortunate local correlated failures happen. IMO it’s quite nice to be able…

This is an aside, but has anyone tried NVDIMMs as the disk, behind in-package HBM for ram? No idea if it would be any good, just kind of a funny thought. It’s like everything shifted one slot closer to the cores, haha, nonvolatile memory where the RAM use to live, memory pretty close to the core.

Re: What Does a Database for SSDs Look Like?

#105

Post by koverstreet » Sat, Dec 20, 2025, 6:51 PM UTC

loeg wrote: post #102

koverstreet wrote: post #61

Earlier quoted context omitted.

bcachefs's btree still beats the pants off of the entire rocksdb lineage :)

Aren't B-trees and LSM-trees fundamentally different tradeoffs? B-trees will always win in some read-biased workloads, and LSM-trees in other write-biased workloads (with B epsilon (Bε) trees somewhere in the middle).

For on disk data structures, yes.

LSM-trees do really badly at multithreaded update workloads, and compaction overhead is really problematic when there isn't much update locality.

On the other hand, having most of your index be constant lets you use better data structures. Binary search is really bad.

For pure in memory indexes, according to the numbers I've seen it's actually really hard to beat a pure (heavily optimized) b-tree; for in-memory you use a much smaller node size than on disk (I've seen 64 bytes, I'd try 256 if I was writing one).

For on disk, you need to use a bigger node size, and then binary search is a problem. And 4k-8k as is still commonly used is much too small; you can do a lockless or mostly lockless in-memory b-tree, but not if it's persistent, so locking overhead, cache lookups, all become painful for persistent b-trees at smaller node sizes, not to mention access time on cache miss.

So the reason bcachefs's (and bcache's) btree is so fast is that we use much bigger nodes, and we're actually a hybrid compacting data structure. So we get the benefits of LSM-trees (better data structures to avoid binary search for most of a lookup) without the downsides, and having the individual nodes be (small, simple) compacting data structures is what makes big btree nodes (avoiding locking overhead, access time on node traversal) practical.

B-epsilon btrees are dumb, that's just taking the downsides of both - updating interior nodes in fastpaths kills multithreaded performance.

Re: What Does a Database for SSDs Look Like?

#106

Post by loeg » Sat, Dec 20, 2025, 6:54 PM UTC

raggi wrote: post #9

It may not matter for clouds with massive margins but there are substantial opportunities for optimizing wear.

We care about wear to the extent we can get the expected 5 years out of SSDs as a capital asset, but below that threshold it doesn't really matter to us.

Re: What Does a Database for SSDs Look Like?

#107

Post by vegabook » Sat, Dec 20, 2025, 7:03 PM UTC

mrkeen wrote: post #2

> Design decisions like write-ahead logs, large page sizes, and buffering table writes in bulk were built around disks where I/O was SLOW, and where sequential I/O was order(s)-of-magnitude faster than random. Overall speed is irrelevant, what mattered was the relative speed difference between sequential and random access. And since there's still a massive difference between sequential and random access with SSDs, I…

pity Optane which solved for this quite well, was discontinued.

Re: What Does a Database for SSDs Look Like?

#108

Post by ncruces » Sat, Dec 20, 2025, 7:13 PM UTC

Palomides wrote: post #94

hawk_ wrote: post #89

Earlier quoted context omitted.

If you tested this on macos, be careful. The fsync on it lies.

nope, linux python script that writes a little data and calls os.fsync

What's a little data?

In many situations, fsync flushes everything, including totally uncorrelated stuff that might be running on your system.

Re: What Does a Database for SSDs Look Like?

#109

Post by didgetmaster » Sat, Dec 20, 2025, 7:22 PM UTC

Back in college (for me the 80s), I learned that storing table data in rows would greatly increase performance due to high seek times on hard disks. SELECT * FROM table WHERE ... could read in the entire row in a single seek. This was very valuable when your table has 100 columns.

However; a different query (e.g. SELECT name, phone_number FROM table) might result in fewer seeks if the data is stored by column instead of by row.

The article only seems to address data structures with respect to indexes, and not for the actual table data itself.

Re: What Does a Database for SSDs Look Like?

#110

Post by evanelias » Sat, Dec 20, 2025, 7:33 PM UTC

koverstreet wrote: post #98

evanelias wrote: post #79

Earlier quoted context omitted.

Rocksdb / myrocks is heavily used by Meta at extremely massive scale. For sake of comparison, what's the largest real-world production deployment of bcachefs?

We're talking about database performance here, not deployment numbers. And personally, I don't much care what Meta does, they're not pushing the envelope on reliability anywhere that I know of.

Many other companies besides Meta use RocksDB; they're just the largest.

Production adoption at scale is always relevant as a measure of stability, as well as a reflection of whether a solution is applicable to general-purpose workloads.

There's more to the story than just raw performance anyway; for example Meta's migration to MyRocks was motivated by superior compression compared to other alternatives.