In Search of a Faster SQLite | Hacker News Reader

In Search of a Faster SQLite

avi.im

355 pointsavinassh149 comments

The article discusses the specific use case of serverless computing, e.g. AWS Lambda, and how a central database doesn't always work well with apps constructed in a serverless fashion.

I was immediately interested in this post because 6-7 years ago I worked on this very problem- I needed to ingest a set of complex hierarchical files that could change at any time, and I needed to "query" them to extract particular information. FaaS is expensive for computationally expensive tasks, and it also didn't make sense to load big XML files and parse them every time I needed to do a lookup in any instance of my Lambda function.

My solution was to have a central function on a timer that read and parsed the files every couple of minutes, loaded the data into a SQLite database, indexed it, and put the file in S3.

Now my functions just downloaded the file from S3, if it was newer than the local copy or on a cold start, and did the lookup. Blindingly fast and no duplication of effort.

One of the things that is not immediately obvious from Lambda is that it has a local /tmp directory that you can read from and write to. Also the Python runtime includes SQLite; no need to upload code besides your function.

I'm excited that work is going on that might make such solutions even faster; I think it's a very useful pattern for distributed computing.

We have that issue at work, though I solved it by including the sqlite database within the container image that we use. We then deploy the new container image (with the same code as before, but with a revised database file) at most every fifteen minutes.

This gives you an atomic point at which you are 100% confident all instances are using the same database, and by provisioning concurrency, you can also avoid a "thundering herd" of instances all fetching from the file on S3 at startup (which can otherwise lead to throttling).

Of course, that's only feasible if it's acceptable that your data can be stale for some number of minutes, but if you're caching the way you are, and periodically checking S3 for an updated database, it probably is.

> "thundering herd" of instances all fetching from the file on S3 at startup (which can otherwise lead to throttling).

Have any "thundering herd" problems with S3, including throttling, actually been seen?

I think S3 is advertised to have no concurrent connection limit, and support up to at least 5,500 GETs per second (per "prefix", which I'm confused about what that means exactly in practice). I don't think S3 ever applies intentional throttling, although of course if you exceed it's capacity to deliver data you will see "natural" throttling.

Do you have a fleet big enough that you might be exceeding those limits, or have people experienced problems even well under these limits, or is it just precautionary?

I asked the S3 team what “prefix” meant at reinvent, and my current understanding is “whatever starting length of key gives a reasonable cardinality for your objects”.

So if your keys are 2024/12/03/22-45:24 etc, I would expect the prefix to be first 7 characters. If your keys are UUIDs I’d assume first two or three. For ULIDs I’d assume first 10. I this there’s a function that does stat analysis on key samples to figure out reasonable sharding.

Yep. Works similarly with google cloud storage buckets. It seems like the indexing function they use for splitting/distributing/sharding access looks at your objects keys and finds a common prefix to do this.

The problem with a date based key like the one you used (that's very common) is that if you read a lot of files that tend to be from the same date (for example: for data analysis you read all the files from one day or week, not files randomly distributed) all those files are going to share the same prefix and are going to be located in the same shard, reducing performance until the load is so high that Google splits that index in parts and begins to distribute your data in other shards.

For this reason they recommend to think your key name beforehand and split that prefix using some sort of random hash in a reasonable location of your key:

https://cloud.google.com/storage/docs/request-rate#naming-co...

It would be nice if S3 provided similar public guidance. For instance:

> Adding a random string after a common prefix still allows auto-scaling to work, but…

No way to know if that's true of S3's algorithm too without them revealing it.

Yep, seems to hint something in the first paragraph of a performance tip [0] but it doesn't specify how does it choose prefixes, or how many prefixes does it shard, or anything...

  0: https://docs.aws.amazon.com/AmazonS3/latest/userguide/optimizing-performance.html

I have never seen this explained, so thank you! Sounds like it's kind of "up to S3 and probably not predictable by you" -- which at least explains why it wasn't clear!

If you don't have "a lot" of keys, then you probably have only one prefix, maybe? Without them documenting the target order of magnitude of their shards?

I would assume so, the extreme case being just one key, which of course has only one partition. But see https://youtu.be/NXehLy7IiPM (2024 Reinvent S3 deep dive) - there’s still replication happening on single objects. So it’s still sort of sharded, but I do think key partitions where groups of keys have shared choke points based on sort order exist.

Sorry--the throttling was at the AWS Lambda layer, not S3. We were being throttled because we'd deploy a new container image and suddenly thousands of new containers are all simultaneously trying to pull the database file from S3.

We aim to return a response in the single digit milliseconds and sometimes get tens of thousands of requests per second, so even if it only takes a second or two to fetch that file from S3, the request isn't getting served while it's happening, and new requests are coming in.

You very quickly hit your Lambda concurrency limit and get throttled just waiting for your instances to fetch the file, even though logically you're doing exactly what you planned to.

By having the file exist already in the container image, you lean on AWS's existing tools for a phased rollout to replace portions of your deployment at a time, and every one is responding in single digit milliseconds from its very first request.

EDIT: The same technique could be applied for other container management systems, but for stuff like Kubernetes or ECS, it might be simpler to use OP's method with a readiness check that only returns true if you fetched the file successfully. And maybe some other logic to do something if your file gets too stale, or you're failing to fetch updates for some reason.

Are lambdas guaranteed to be warm on the first request to serve in single digit milliseconds?

They can be with provisioned concurrency.

If provisioned concurrency is used, then fetching the database at startup would be a valid strategy given you could still achieve flat response times.

That's true. I prefer this approach because it removes that additional thing (the request to S3) that can be slow or fail at runtime. Or "initialization" time, I guess, depending on how you look at it.

Yes, I've been throttled many times by S3. My largest database is ingesting ~5PB/day and that turns into a lot of files in S3. At one point we changed our S3 key scheme to not have hashes up front, which unlocked some simplicity in control plane operations like deleting old files; we did this on the strength of the announcement from AWS that you no longer needed to get clever with prefixes.

This was incorrect at our scale, and we had to switch back.

I wrote a tool to handle micro blobs specifically because we were being heavily rate limited by S3 for both writes and reads. We got about 3k/s per bucket before S3 rate limiting started kicking in hard.

Granted we also used said tool to bundle objects together in a way that required sezo state to track so that we could fetch them as needed cheaply and efficiently so it wasn't a pure S3 issue.

Interesting, thanks! PUT is advertised at 3500/s, so with a combo load, you were at least within range of advertised limits. I have not approached that scale so didn't know, it was a real question!

Yeah I was processing a bunch of iceberg catalog data, it was pretty trivial to get to this point on both PUTs and GETs with our data volume, I was doing 400,000 requests/m and of course my testing was writing to one prefix :)

I actually versioned my database file - I had a small metadata table with version number and creation time.

Then in the output from each of my other functions, I included the database version number. So all my output could be subsequently normalized by re-running the same input versus an arbitrary version of the database file.

Have you looked at the user_version pragma? I've been able to avoid use of special metadata tables with this.

> One of the things that is not immediately obvious from Lambda is that it has a local /tmp directory that you can read from and write to.

The other big thing a lot of people don't know about Python on Lambda is that your global scope is also persisted for that execution context's lifetime like /tmp is. I ran into issues at one point with Lambdas that processed a high volume of data getting intermittent errors connecting to S3. An AWS engineer told me to cache my boto3 stuff (session, client, resources, etc.) in the global namespace, and that solved the problem overnight.

It is so painful to see so many people make wrong use of S3.

Is there a list of correct uses of s3 we can all follow?

Yep. Right here. [0].

Generally people ignore the per PUT and GET pricing on S3 along with the higher latency since it's a "global" service. If your objects are small then you're almost always benefited from using DynamoDB as the GET pricing and latency are far more favorable, as long as you don't mind the region dependency or the multi region setup.

[0]: https://docs.aws.amazon.com/AmazonS3/latest/userguide/optimi...

storing massive amounts of unstructured data

> Now my functions just downloaded the file from S3, if it was newer than the local copy

if you have strong consistency requirements, this doesn't work. synchronizing clocks reliably between different servers is surprisingly hard. you might end up working with stale data. might work for use cases that can accept eventual consistency.

This shouldn't depend on clocks, just tracking Etag is more consistency proof.

If you have strong consistency requirements, then it doesn't work by the very nature of making multiple copies of the database. Even if the clocks are perfect. (Though the clocks are probably close enough that it doesn't matter.)

One of the announcements from AWS this year at Re:invent is that they now can guarantee that the instances clocks are synced within microseconds of each other. Close enough that you can rely on it for distributed timekeeping.

I don't really know if that matters for this use case. Just by the very nature of source_data -> processing -> dest_data taking nonzero time anything consuming dest_data must already be tolerant of some amount of lag. And how it's coded guarantees you can never observe dest_data going new -> old -> new.

Wouldnt e-tag version numbers also work? Or just havkng .jsom with version metadata next to the db blob? No need to sync clocks. Just GET the small db-ver.json and compare version details?

I versioned the databases and my use case didn’t require strong consistency.

> My solution was to have a central function on a timer that read and parsed the files every couple of minutes, loaded the data into a SQLite database, indexed it, and put the file in S3.

I really love using S3 as a storage backend!

One small comment: it may be worth disclaiming that one of the two cited researchers is the author's boss.

It's a small detail, but I mistakenly thought the author and the researchers were unrelated until I read a bit more

FYI, the word you want there is “disclosing”, not “disclaiming”.

“…put a disclaimer disclosing…”

What exactly do you think “disclaimer” (or disclaim, or disclaiming) means?

does it matter?

It does matter, particular if ESL peeps use this language to train their biological neural networks.

To disclaim, or a disclaimer, is a denial of something. It is the opposite of a claim, but is a disclaim.

In this case someone is doing the opposite.

Yes, people hallucinate on this one a lot.

hey, thats fair. I have mentioned that I work at Turso in my blog's about page, but I don't expect everyone to check that. I have updated the post to include a disclosure, thanks!

"The benefits become noticeable only at p999 onwards; for p90 and p99, the performance is almost the same as SQLite."

I hate to be a hater, and I love sqlite and optimizations, but this is true.

The benchmark seems a bit weird. Fetch 100 results from a table with no filtering,sorting,or anything? That feels like the IO is going to be really small anyways.

they compare threads and coroutines for limbo. threads have much worse p90 latencies since they context switch.... im not sure they can draw any conclusions except that coroutines are faster (of course)

So, it's almost useless.

https://www.kryogenix.org/code/browser/why-availability/

So this isn't faster for people running a monolith on one machine. This is only gives faster tail latency in congested multitenant scenarios. So only a narrow gain in a narrow scenario. Cool and all, all progress is good progress, but also not relevant for me or a lot of people.

This is neat, but it's weird how such trivial things (in this case "a coroutine has a smaller context switching overhead than a thread, though it often is only relevant in synthetic scenarios with the tiniest quanta") now merit "a paper". Professionally delivered in PDF form with loads of citations.

I think this is a side effect of the arXiv AI-paper explosion where everyone is "publishing" "papers" on such prompt engineering magic as "delimiting my letters with spaces made it count them slightly more accurately", etc, this stunning piece of research having a dozen authors across three educational institutions and two corporations.

One of the nice things about sqlite is that there is a very extensive test suite that extensively tests it. The question is whether the rewrite have something similar or will it get the similar testing? Especially if it uses fast but hard to write and potentially buggy features like io_uring.

> One of the nice things about sqlite is that there is a very extensive test suite that extensively tests it.

Yes, that sets a high bar for us. We plan to use Deterministic Simulation Testing and Antithesis to reach the rigorous testing standards of SQLite.

Limbo comes with a simulator too

Limbo is very much a WIP but there is already a large test suite of compatibility tests that run along with sqlite, and DST (Deterministic Simulation Testing) that [0] Tiger Beetle has largely pioneered, is being designed from the beginning. Sqlite compatibility in particular seems to be very important.

[0] https://docs.tigerbeetle.com/about/vopr/

They could license the test suite from SQLite (and a lot of tests are open sourced): https://www.sqlite.org/prosupport.html#th3

^^^ - this was my first reaction too. I wonder how they would ensure the same level of quality (e.g. not just safe code due to Rust)

This is a great article.

There was a previous attempt to bring async io to Postgres, but sadly it went dormant: https://commitfest.postgresql.org/34/3316/

A more recent proposal was to make it possible to swap out the storage manager for a custom one without having to fork the codebase. I.e. extensions can provide an alternative. https://commitfest.postgresql.org/49/4428/

This would allow for custom ones that do async IO to any custom storage layer.

There are a lot of interested parties in the new proposal (it's come out of Neon, as they run a fork with a custom storage manager). With the move to separate compute from storage this becomes something many Postgres orgs will want to be able to do.

A change of core to use async io becomes slightly less relevant when you can swap out the whole storage manager.

(Note that the storage manager only handles pages in the heap tables, not the WAL. There is more exploration needed there to make the WAL extendable/replaceable)

Thank you for pointing this out.

A librados based storage manager would be a game changer. The scalability and availability story of Postgres would be rewritten.

I am the author of this blog post and I didn't expect to see it on the front page! For disclosure, I work at Turso and one of the authors, Pekka, is from Turso.

This paper came out in April 2024 when Limbo was in its nascent stages. It has seen many improvements since then, one being support for Deterministic Simulation Testing.

repo: https://github.com/tursodatabase/limbo

It sounds like most of the answer suggested by the paper is asynchronous IO, so maybe I am misunderstanding something.

There is a lot, I mean A LOT as in huge and tremendous amount, of overhead in managing data via any form of SQL versus just writing to files. The overhead pays for itself if the size of the data is large enough and the cost of read and write operations is high enough.

Given those factors couldn't similar performance improvements be achieved at far lower cost by piping data via streams to opened files using an asynchronous interface like an event loop or child processes? That would eliminate the blocking of synchronous operations and so much of the CPU overhead associated with query interpretation during writes. There would still be a cost to precise data extraction at read time though.

If just using file system operations all operational overhead only occurs at execution time. For example managing and reading data still incurs CPU cost, but there is virtually no management cost to replicating a database if that replication is just a matter of copying files as opposed to the more complex operations concerned with replicating a SQL database.

So silly question - if i understand right, the idea is you can do other stuff while i/o is working async.

When working on a database, don't you want to wait for the transaction to complete before continuing on? How does this affect durability of transactions? Or do i just have the wrong mental model for this.

I think the OP is about a runtime that runs hundreds of programs concurrently. When one program is waiting for a transaction other programs can execute.

You don't need io_uring for that - the usual synchronous file operations will cause the OS to switch away from processes while they wait for disk, if there are other processes needing to do work. OP's design is for when you have other work to do in the same process.

When I said “runtime” and “program” I meant it. If I had meant process I would probably have used that word.

Okay, I see what you mean. To me "program" usually implies process, even in a runtime.

From the paper it looks like this is for read heavy workloads (testing write performance is "future work") and I think for network file systems which will add latency.

The complex thing with a transactional db is that many concurrent transactions (should be) executed simultaneously, and that mix that single query tx and the one that loads 1 million rows.

The sqlite model is that only one write transaction can be run at a time. That's kind of a defining trade-off because it allows simplifying locking.

Pekka already experimented with MVCC and I expect it to make it to Limbo at some point to enable multiple concurrent writers

Mvcc will create multiple persistent files on disk which is very un+sqlite like

Are we sure edge computing providers have io_uring enabled? It is disabled in inter alia, ChromeOS and Android, because it's been a significant source of vulnerabilities. Seems deadly in a multi tenant environment.

Not an expert at all: I wonder if getting the perf requires trading-off some (advertised) safety. IO uring has been noted to be confusing with async in Rust, https://news.ycombinator.com/item?id=41992975

I'm reminded of how Confluent advertised Kafka as a database. They quietly externalized key guarantees of an RDBMS onto their customers, who were then saddled with implementing those guarantees in application level logic. By obscuring the trade-offs, Confluent made developers feel they could have their cake and eat it too.

IMHO, most of io_uring's performance should come from reducing transition between kernel and userspace. There doesn't need to be a safety tradeoff there (although in practice, there have been several safety issues). There may be a trade off against having a simple concurrency model; without io_uring you can't really request an accept and not handle the response that comes back, because a syscall is necessarily synchronous from the point of view of the caller; the calling thread can't continue until it gets a response, even if that's EINTR.

Out of my league / knowledge, but a tidbit that you might understand better: last time I mentioned this, someone said something about SELinux can't do security checks and implied it's some fundamental mismatch, rather than some work SELinux can do

Well I'm a little outside my league on SELinux, but as I understand it, SELinux a way to limit syscalls by configuration.

io_uring operations are similar to syscalls, but not exactly the same thing, so if I've read up correctly, I think SELinux originally may not have covered operations --- if the config allowed you to use io_uring, you could bypass SELinux restrictions, and the easiest/safest thing to do for that is to restrict io_uring. I think this may have been resolved, it looks like there was work on SELinux controls for io_uring back in 2021 [1] that looks like it got merged, but I haven't really followed this. There's also the issue that what happened in Linus's tree 2021 doesn't necessarily reflect what's in common kernels in 2024; some distributions are built from ancient branches.

Based on the language in the pull request, I would think you'd end up needing to have near duplicate policy entries for regular syscalls and io_uring operations, which sounds tedious (but the whole SELinux thing sounds tedious to me, so I'm maybe not the right person to ask :D )

[1] https://lore.kernel.org/lkml/CAHC9VhRJ=fHzMHM6tt8JqkZa4bf0h7...

Anything new is going to have people poking and prodding at it. It doesn't mean that the concept is 'bad'.

Linux has desperately needed an interface like io_uring for decades. Async IO was in Windows NT 3.1 in 1993.

>Async IO was in Windows NT 3.1 in 1993

To be fair, Windows tried and abandoned 2 different methods of doing it before landing on the current IO Completion ports.

Might've replied to the wrong comment: I don't think io_uring is bad, and the comment doesn't contain 'bad', and I certainly don't think async IO is bad :)

Their goal is to run this on their own cloud.

Despite their lofty claims about community building, their projects are very much about forwarding their use case.

Given that SQLite is public domain, they're not required to give anything back. So, it's very cool that they're making parts of their tech FOSS.

But I've yet to see anything coming from them that isn't “just because we need it, and SQLite wouldn't do it for us.”

There's little concern about making things useful to others, and very little community consensus about any of it.

https://turso.tech/

I think you've taken the most cynical view possible.

SQLite is open source but not open contribution. So if they "won't do it for us" and "we need it", what else are they supposed to do? They're offering SQLite in the cloud, obviously they need to offer a streaming backup solution. Is there something wrong in building that?

Alternatively, do you want them to reimplement features in SQLite already built out by the SQLite team?

Really sounds like you're complaining about getting an open source, MIT licensed, open contribution bit of software for free that you're under no obligation to use. And here you are complaining about "community consensus".

I may be able to shed some light.

It seems they proposed a way to resolve the contradiction I raised (io_uring isn't supported on cloud providers) with the ground reality in the company's blog post.

Namely, that:

* it is intended for edge function deployment.

* the paper they mention that is informing their decision to rewrite is based on perf improvements in the longtail by using io_uring.

I framed it as "Are we sure...?", but truth is, I know providers don't have io_uring support. This is relatively straightforward to derive from edge functions are well-known to be multi-tenant.

A cursory search shows unresolved AWS tickets re: this, and multiple announcements from Google about how it has been disabled in gCloud.

Thus, it is likely they understood I was framing it politely, and that there was a clear contradiction here, hence their reply to me, raising a potential resolution for that contradiction, a resolution I hadn't considered.

I don't see anything complaining, or bringing up, or implicitly denying, all the other stuff ("open source", "MIT licensed", "open contribution", "software for free" "under no obligation to use."). In fact, they explicitly indicated they completely agree with that view ("it's very cool that they're making parts of their tech FOSS.")

The Turso company provisions infrastructure using fly.io, which uses firecracker VM (which as of now does support io_uring afaict). They are using the term “serverless” liberally maybe? Goodness knows we all do.

io_uring isn't supported on cloud providers for now, but it may not be the case in future. Which is fine, because Limbo won't be ready for a few years at least.

The problem is that SQLite already exists, and is public domain. It's a fantastic piece of software engineering that has had a lot of time and effort put into making it great.

The pitch for these SQLite alternatives is:

- SQLite is public domain so there's no problem with us rewriting it

- We're going to rewrite it in Rust because that's going to make it inherently better (and don't question this)

- We're going to MIT license our rewrite because there's various reasons why people would rather use MIT-licensed code than public domain code (but SQLite being public domain means we get to do all of this rewriting and re-licensing to begin with)

- Also we're going to extend SQLite to be “cloud-capable” because that's our business use-case, even though it's completely at odds with SQLite's intended use-case

- Also we're going to foster a “community” around our rewrite(-in-progress) (because people still think this is something desirable for some reason, as though a nonzero part of SQLite's greatness is that it operates entirely without having do deal with “nerd drama” that such “communities” inevitably breed)

- Also, we replaced that pesky, unsavory-to-our-sensibilities “Code of Ethics” with a bog-standard “Code of Conduct”—because, again: “community”

- But we're not going to even attempt to replicate the rigorous, arduous, and closed-source testing that goes into developing SQLite (which really makes up the bulk of its engineering effort)

- We've made some progress toward all of this, but it's nowhere near done yet

- But we're announcing it now anyway, because “hype” and “community” and “modern”

- Also, look at our microbenchmarks that show that our unfinished SQLite rewrite is already faster than SQLite (because we haven't reimplemented everything from SQLite yet) (and also we don't plan to reimplement everything from SQLite anyway)

I find it really odd that I can only seems to find criticism of any of this here on HN, and in a couple of reddit threads. It's kind of like when there was that Godot controversy awhile back, and some people made a fork and announced it, despite being identical aside from the Code of Conduct. Merely announcing a fork/rewrite of existing open-source software as a means of "building hype" and “creating a community”, while benefiting from untold man-hours of work done by others, strikes me personally as rather untoward behavior—regardless of whether the reasoning behind said fork/rewrite is ideological (Redot) or business (libSQL/“Limbo”).

Software—especially software used to create other software—should be lauded for its engineering efforts, rather than its ability to “build hype” or “build and foster online ‘community’ ‘engagement’”. If SQLite was abandonware, then it would be a somewhat different story, but SQLite is an amazing piece of software—perhaps the most widely-used on the planet—that is still being actively developed and supported. So, piggybacking on its success, without having concrete, demonstrable improvements to its functionality, comes across as… well, I can't think of another polite, applicable adjective than “untoward”.

> we're not going to even attempt to replicate the rigorous, arduous, and closed-source testing

Almost everything you've said is wrong, but this one is trivially easy to prove wrong. You say "not even going to attempt" but they plan to take testing seriously from the get go. They say:

> Since this is a reimplementation, doesn’t that mean that testing is now even harder? The reality is that it is the other way around. Since we are reimplementing it from scratch, we are doing it with Deterministic Simulation Testing (DST) built-in from the get-go. We have both added DST facilities to the core of the database, and partnered with Antithesis to achieve a level of reliability in the database that lives up to SQLite’s reputation.

> Deterministic Simulation Testing is a paradigm made famous by the folks at TigerBeetle, that we at Turso already dipped our toes into with our server-side code. With DST, we believe we can achieve an even higher degree of robustness than SQLite, since it is easier to simulate unlikely scenarios in a simulator, test years of execution with different event orderings, and upon finding issues, reproduce them 100% reliably.

Did you make your false claim in ignorance of their announcement post (https://turso.tech/blog/introducing-limbo-a-complete-rewrite...), or did you make it knowing full well that they're trying hard to make a well tested library?

---

But fuck it, I got time. I'll tackle the other dubious claims you've made as well.

> Also, we replaced that pesky, unsavory-to-our-sensibilities “Code of Ethics”

They haven't replaced it because Limbo is a new project. LibSQL is a fork of an existing project but again, they haven't replaced the "Code of Ethics" in the repo (https://sqlite.org/src/dir?ci=trunk) because it never existed in the repo, just on the sqlite.org website.

So they "replaced" nothing. They simply added their own code of conduct, which (I agree with you) is bog standard for a project on Github.

---

> Announcing before it's ready

Yes? That's normal? You're objecting to a project even existing on Github if it's not 100% done? They specifically explain why they've done it. They're building in the open, they're seeing excitement from outside contributors who want to contribute. So they're announcing to make more potential contributors aware this project exists.

Only someone with a warped mind could find something objectionable about starting small and iterating with the community.

---

> Also we're going to extend SQLite to be “cloud-capable” because that's our business use-case, even though it's completely at odds with SQLite's intended use-case

Nothing you've said makes me think you understand the social side of building software, but this makes me think you don't understand the technical side either. Perhaps you missed the link on this thread where they explain why SQLite works well in the cloud.

I'll explain for you though, in case you're unable to find the link at the top of this page. SQLite relies on random access from disk, which made it a poor fit for cloud workloads when the cloud used HDDs exclusively. HDDs work much better for sequential access, which is why LSM trees were invented. So rather than reading from a random part of the local disk (10-20ms) you'd rather pay the network cost (0.5-2ms) and read from a database on a different machine that has optimised it's data storage for sequential read.

But SSDs change the game. A random read of 4k from a local SSD is 150 microseconds (https://gist.github.com/jboner/2841832), always less than the network round trip. Local SQLite running on an SSD is suddenly the lowest latency database.

For infrequently changing data that needs to be served with low latency, SQLite is the best choice in the cloud.

If you don't follow this, it's alright. I didn't expect you to.

---

> SQLite is public domain so there's no problem with us rewriting it and We're going to MIT license our rewrite because there's various reasons why people would rather use MIT-licensed code than public domain code

Yes, this is true. I don't think this was an objection from you.

---

> We're going to rewrite it in Rust because that's going to make it inherently better (and don't question this)

What should they be writing it in? I notice you don't say. You reckon they should start a new C code base in 2024?

---

> Redot comparison

Redot is a fork created solely because the creators didn't like a tweet about Godot being welcoming to LGBT game developers. LibSQL was created because they wanted to add features to the SQLite codebase and weren't allowed.

In any case the comparison is wrong. You can see the two commit histories of redot (https://github.com/Redot-Engine/redot-engine/commits/master/... libsql (https://github.com/tursodatabase/libsql/commits/main/) and see which of these is actually a community creating something new and which is a dead project simply merging in commits from upstream.

---

No one disagrees that SQLite is highly useful, rock-solid reliable software. But I disagree with you that it is perfect in every way and no one should ever second guess any decision made by the maintainers. Turso wants to try something different. Nothing wrong with trying.

> [DST and Antithesis is just as good if not better than TH3]

Remains to be proven.

> So they "replaced" nothing. They simply added their own code of conduct

This is pedantic. Yes, the Code of Ethics is on the SQLite website, and not in its source directory, so yes, technically cloning the repo and adding a Code of Conduct is not “replacing” the Code of Ethics in terms of files in repositories. Arguing this point as you have is simply inane. SQLite has a Code of Ethics, and libSQL/“Limbo” are unbeholden to the SQLite Code of Ethics and instead have a Code of Conduct. Taking umbrage with describing this as “replacing the Code of Ethics with a Code of Conduct” is just being pedantic for the sake of it.

> Only someone with a warped mind could find something objectionable about starting small and iterating with the community.

Wholly unnecessary, overemotional ad hominem. If SQLite was abandonware then there would be no issue—but it's not, it's great software that is regularly updated (without a “community”, in the sense being discussed here), so, announcing a rewrite long before it's done and declaring how much better than SQLite it is going to be comes across as rather untoward.

For many people, such as myself, the lack of “community” in the SQLite project is a selling point, rather than some kind of problem—such that when an incomplete fork/rewrite with an emphasis on “community” is announced, myself and many others see that as yet another point against it. Sure, you're going to have many naive or otherwise inexperienced developers who care more about Codes of Conduct and “community policing” and “feeling like you're part of a community” or whatever, and that's fine, go right ahead and have fun with that. But for the rest of us, who enjoy using well-made software without getting into any of that nonsense, SQLite and the way it goes about doing things will remain the superior option.

> What should they be writing it in? I notice you don't say. You reckon they should start a new C code base in 2024?

Part of what makes SQLite so useful is that it is written in C, and therefore is easy to compile and integrate into just about anything. I'm generally unfamiliar with Rust, so I don't know, maybe it's possible to make a SQLite clone in Rust with full C ABI compatibility. But if this is not the case, or this is not what “Limbo” is aiming for here, then yes, it is strictly worse in the general sense than SQLite, except for specific use-cases.

If libSQL and “Limbo” were being presented as alternatives to SQLite that are more useful for specific use-cases, then I wouldn't've felt the need to comment in the first place. The problem is when you begin undertaking a project of this enormity, baselessly assert that the thing you're trying to do is straight-up better than the existing SQLite gold standard, and even position it in the market as an objectively better replacement for SQLite for various reasons, including “community”, “modern”, and “Rust”.

Additionally, naming your SQLite rewrite library “libSQL” is also quite clearly a means of semantically positioning it as a better, “more ‘modern’”, more generic SQL library than SQLite—and that's great marketing for a specific kind of developer. When searching e.g. Twitter for “libSQL”, one will find posts where people describe things they're working on, saying things like, “Uses SQLite for database (plan to replace with libSQL soon!)”, which proves my point—they've succeeded in positioning libSQL as “a more modern SQLite”, to the point where some developers see the need to replace SQLite with libSQL just for the sake of doing so. Again, this would be totally fine if SQLite was abandonware—but, once again, it's quite the opposite of that.

Interesting that you didn't respond to the substance of my comment - the technical reasons that cloud SQLite works so well. That after making an awfully wrong categorical statement "even though it's completely at odds with SQLite's intended use-case".

> [Rust C API] ... I'm generally unfamiliar with Rust

Evidently. But then should you be writing snarky comments like "We're going to rewrite it in Rust because that's going to make it inherently better (and don't question this)". Really makes it sound like you know that the choice of Rust should be questioned.

For what it's worth, Rust codebases can be compiled to expose a C ABI that other applications can integrate with. For example, the rustls project exposes an OpenSSL compatible interface (https://www.memorysafety.org/blog/rustls-nginx-compatibility...) which makes it trivial to integrate into applications that expect OpenSSL.

> [Limbo SQLite compatibility] ... But if this is not the case, or this is not what “Limbo” is aiming for here

You know ... you could just read a little before writing so much. On https://github.com/tursodatabase/limbo it says their stated goals are - "SQLite compatibility. SQL dialect support. File format support. SQLite C API". They want to expose the exact same C API that SQLite exposes.

Does that sufficiently address your concerns around Rust codebases being used from other languages and Limbo's compatibility with SQLite?

---

> even position it in the market as an objectively better replacement for SQLite for various reasons, including “community”, “modern”, and “Rust”.

To be clear, at no point did anyone say it was "an objectively better replacement for SQLite". No one said it, because Limbo is years away from feature parity.

It seems acceptable to aim to build something better than SQLite. Having a goal is fine, because it points them in a direction. But for some reason, you're getting upset that ... they have goals? Bizarre.

And if their reach feature parity while using io_uring, then yeah it is likely that it will outperform SQLite which uses synchronous I/O.

---

> Testing

We are agreed, it remains to be seen if DST can make something as reliable SQLite's testing strategy has made SQLite. But we'll only see it if someone tries, and that is something you seem quite hostile to.

At least Limbo will do their testing in the open and we can all learn from it whether they succeed or not.

---

> Code of Conduct

I feel changing/replacing files from the repo is important, because it feels similar to replacing a LICENSE file. You can't relicense someone's work just because you feel like it. Similarly, if the Code of Ethics had been replaced in the repo, that would have felt similar to relicensing, although not the same.

Again, I'll be blunt. Do you want anyone who works on this public domain code to adopt principles like "Prefer nothing more than the love of Christ". Not being Christian, I personally prefer nearly all things to the love of Christ. I know I'm not the only developer who feels this way.

The force with which you're arguing this makes me wonder if you really want this sort of religious fervour to become more widespread in open source. Where some projects are Christian, some are Muslim and so on. Of course, then we can really segment the projects into Catholic, Protestant, Anglican, Eastern Orthodox, Sunni, Shia - really experience the full power of religion in open source software development. Wouldn't it be great when OSS projects have a code of ethics that start with "All current developers agree that there is no deity but Allah and Mohammad is his Prophet".

From a legal point of view - there is no reason to adopt this because the code is in public domain. From an ethical point of view - there is no reason for the libSQL to adopt a code that they likely personally disagree with ("all current developers agree ..."). From a practical point of view - they want to encourage contributors, not discourage them (like Hipp was and is), so there's no reason to adopt a code that deliberately drives away contributors.

I don't know how you feel because you carefully dance around that. You simply criticise the libSQL folks for anything they do. Criticising is easy, doing is difficult. So say precisely what Code libSQL and Limbo should adopt and why you think it's such a good idea.

Of course they are scratching their own itch, so to speak. Thats what companies do. I think the fact that they are doing so much in the open is the indication of good stewardship itself. I'm not sure what else they would do or release that they didn't need internally. For that matter, I'm not really aware of many significant contributions to FLOSS at all that aren't initially intended for company use, that's kinda how it works. Where I'm surprised here is how much secret sauce Turso is sharing at all.

I have no problem with them scratching their itch. That's par for the course.

I'm salty about them describing the SQLite licensing, development model, and code of ethics as almost toxic, setting up a separate entity with a website and a manifesto promising to do better, and then folding “libSQL into the Turso family” within the year.

They forked, played politics, added a few features (with some ill-considered incompatibilities), and properly documented zero of them.

And I'm salty because I'm actually interested in some of those features, and they're impossible to use without proper documentation.

I've had much better luck interacting with SQLite developers in the SQLite forum.

disclosure: I work at Turso

> code of ethics as almost toxic

This is simply not true. Can you tell me where it is being said so?

> then folding “libSQL into the Turso family” within the year.

libSQL was always part of Turso. So, I don't get your point.

> They forked, played politics, added a few features (with some ill-considered incompatibilities), and properly documented zero of them.

Again this is incorrect. There are some docs here: https://github.com/tursodatabase/libsql/tree/main/docs

I am really not sure why are you so angry about libSQL.

> This is simply not true. Can you tell me where it is being said so?

It's right there in “your” manifesto. https://turso.tech/libsql-manifesto

> We take our code of conduct seriously, and unlike SQLite, we do not substitute it with an unclear alternative. We strive to foster a community that values diversity, equity, and inclusion. We encourage others to speak up if they feel uncomfortable.

The word toxic clearly stung, but putting “unlike SQLite … we encourage others to speak up if they feel uncomfortable” in a manifesto is fine. Well, I could argue I'm just speaking up.

> libSQL was always part of Turso. So, I don't get your point.

My point is explained quite clearly in your post detailing the decision. https://turso.tech/blog/were-bringing-libsql-into-the-turso-...

> We have our own self interest in making those changes (…) But we also wanted to create a welcoming community, that is open to everybody, abides by a modern code of conduct and a clear OSS license, and reimagined what SQLite could be in broader ways than just our narrow needs.

A little latter down that line you sum it up: doing the above (living up to your grandiose claims of a more welcoming SQLite) “meant twice the investment” (aka a lot of money) and didn't pan out as a marketing play (showed engagement).

So instead of a community that “reimagined what SQLite could be in broader ways than just our narrow needs" we just get the features you had your "own self interest in making."

Which is fine, but doesn't really match the manifesto.

> Again this is incorrect. There are some docs…

Emphasis on some.

Do you have any documentation on how to build on the Virtual WAL (internal SQLite API that you simply opened up)? Or is that's still a Rust example of an implementation that simply wraps another and logs without detailing anything beyond function names?

Do you have any documentation about the new WAL API that isn't "libsql_wal_insert_begin begins WAL insertion"?

I'm sorry, but goal here isn't to make things useful to others. Which is fine really: you're doing more than you're required. But compared to SQLite developers, and their forum, it's not much.

PS: you also behaved… untowardly when you integrated SQLite3MultipleCiphers, and did this with not previous a word to the author. https://turso.tech/blog/fully-open-source-encryption-for-sql...

> One project in particular was very suitable for us, SQLite Multiple Ciphers. Since it is licensed under MIT, we have just moved the code into libSQL.

> It's right there in “your” manifesto. https://turso.tech/libsql-manifesto

Hard to believe they actually went after D. Richard Hipp--a guy I've only ever heard described as extremely warm, honest, and generous--and for his faith, no less. But then again, these are Rust people, so I guess I shouldn't be surprised, should I?

Amazon runs every Lambda function in it's own microVM.

Every simultaneous request is in its own microVM, making concurrency a non-issue / thing to optimize in most cases at the function level.

> For benchmarking, they simulate a multi-tenant serverless runtime, where each tenant gets their own embedded database. They vary the number of tenants from 1 to 100 in increments of 10. SQLite gets its own thread per tenant, and in each thread they run the query to measure.

How realistic is this? Wouldn't a serverless SQLite setup (using the existing SQLite) use a SQLite process per request (or at least a SQLite process per tenant)? This way the blocking read/write calls would have much less impact.

(You could possibly argue that you gain something with the new architecture if you can switch from processes to threads... if someone read the paper, was there an argument for it in there?)

SQLite is in-process. It never spins up another process or thread. It's just a library. Its blocking I/O means that the thread that called into SQLite can't do anything else until it completes. Though note that SQLite's underlying API is essentially a row-by-row interface - you run a query by calling sqlite3_step(), which returns when the next row has been retrieved.

SQLite does have a page cache, so recently-accessed pages will still be in the cache, allowing for the next result to frequently be returned without stalling. And the operating system's file cache may be reading ahead if it detects a sequential access pattern, so the data may be available to SQLite without blocking even before it requests it. (SQLite defaults to 1KB pages, but the OS may well perform a larger physical read than that into its cache anyway.)

Asynchronous I/O usually isn't actually any faster to complete. Indeed there might be more overhead. The benefit is that you can have fewer threads, if you architect your server around asynchronous I/O. That saves memory on thread stacks and other thread-specific storage. It can also reduce thrashing of CPU cache and context switch overhead, which can be an issue if too many threads are runnable at the same time (i.e. more threads than you have CPU cores.) It might also reduce user/kernel mode transitions.

I wasn't suggesting sqlite itself starts threads. But the quoted sentence suggests the benchmark uses a single-process/multi-thread setup so that there's a thread per tenant ("SQLite gets its own thread per tenant, and in each thread they run the query to measure").

sqlite is open source, but an important test harness is not. How does any alternative ensure compatibility?

https://www.sqlite.org/th3.html#th3_license

Obvious answer would be to buy a license for TH3 and run its tests against the alternative in question, but unfortunately (judging by https://www.sqlite.org/prosupport.html) it seems like Hwaci won't provide direct access to TH3 unless you buy an SQLite Consortium membership for $120k/year.

My understanding was that th3 mainly does correctness tests. Other test suites are open source and can be used to ensure compatibility

I argue it's not Open Source (Freedom, not Free Beer) because PRs are locked and only Hipp and close contributors can merge code. It's openly developed, but not by the community.

You can certainly argue that, but that's not what Open Source or Free Software has ever been. It's about your freedoms as a user, you are always free to fork with a different model. I think the expectation of "open contributions" is quite damaging, to the point where peple/organizations are hesitant to release their software as open source at all.

This is a case of you deciding that open source means something which it does not, never has, and will not mean.

I consider this an empty exercise, but if it pleases you to use language that way, no one can stop you.

That's not what Open Source means. The development team not being willing to review your pull requests does not limit your freedom to use sqlite in any way.

sqlite is actually public domain. https://sqlite.org/copyright.html. This is also the reason why they are closed contribution.

It's a strange combination in the free software world, but I'm grateful for it.

They aren’t closed for contribution.

From the author: “They have a really high bar”, but are accepted, occasionally: https://news.ycombinator.com/item?id=34480732

but they also have this:

> In order to keep SQLite completely free and unencumbered by copyright, the project does not accept patches. If you would like to suggest a change and you include a patch as a proof-of-concept, that would be great. However, please do not be offended if we rewrite your patch from scratch.

https://www.sqlite.org/copyright.html

From the same url:

SQLite is open-source, meaning that you can make as many copies of it as you want and do whatever you want with those copies, without limitation. But SQLite is not open-contribution. In order to keep SQLite in the public domain and ensure that the code does not become contaminated with proprietary or licensed content, the project does not accept patches from people who have not submitted an affidavit dedicating their contribution into the public domain.

All of the code in SQLite is original, having been written specifically for use by SQLite. No code has been copied from unknown sources on the internet.

I’ll go a few steps further:

- it’s only kinda open source if it’s not on GitHub,

- it’s definitely not open source if it’s not in Git,

- but it can regain its open sourceness if it has an open Discord and the devs are hanging out there.

Here, all my heuristics exposed. (I’m not claiming they’re true or sensible, just saying what my brain thinks.)

The insanity of requiring an open source project to be hosted on a proprietary for profit Microsoft social platform with git hosting makes my head hurt.

I went down a rabbit hole one week trying to figure out if there was a simple pathway to making a JSON-like format that was just a strict subset of SQLite file format. I figured for read-only workloads, like edge networking situations, that this might be useful.

There's a lot of arbitrariness to the file format though that made me quickly lose steam. But maybe someone with a more complementary form of stubbornness than mine could pull it off.

So... did they talk to the SQLite maintainer to see how much of this can be taken on board? Because it seems weird to omit that if they did, and it seems even weirder if they didn't after benchmarking showed two orders of magnitude improvement.

(Even if that information should only be a line item in the paper, I don't see one... and a post _about_ the paper should definitely have something to link to?)

They're rewriting SQLite. They're going to put their effort into that surely? Also SQLite explicitly state that they do not accept outside contributions, so there's no point trying.

It is not quite correct to say that the sqlite project does not accept outside contributions at all. The web site says "the project does not accept patches from people who have not submitted an affidavit dedicating their contribution into the public domain."

> The web site says "the project does not accept patches from people who have not submitted an affidavit dedicating their contribution into the public domain."

I have been always curious about this. Is there any more public information to this? When one submits a affidavit, do all their work become public domain? Do you highlight the code and get a affidavit with each contribution?

for e.g. in my country India, I don't think it is not possible to get such Govt approved affidavit.

The author of SQLite commented here about accepting contributions:

“They have a really high bar”, but are accepted, occasionally: https://news.ycombinator.com/item?id=34480732

Affidavits are not normally government issued.

They are a statement of what you say and they are notorized. Making a false affidavit is an offence.

India's copyright regime has the concept of public domain 60 years after the death of the author.

But you can use a CC0 license from Creative Commons to release your rights as much as possible.

Read further:

> In order to keep SQLite completely free and unencumbered by copyright, the project does not accept patches.

Except that is not what SQLite states at all. They accept outside work provided the contributors waive their rights to the code by dedicating it to the public domain, and it's a high quality contribution. Which makes a whole lot of sense when the entire planet uses it. And of course, this is an academic publication so those requirements are basically lowest bar if you're serious. And consulting with your primary source during the research and iteration phase(s) and mentioning that in your publication is kind of expected?

I quote:

> In order to keep SQLite completely free and unencumbered by copyright, the project does not accept patches.

Let's read the rest of that paragraph too:

> [...] the project does not accept patches. If you would like to suggest a change and you include a patch as a proof-of-concept, that would be great. However, please do not be offended if we rewrite your patch from scratch. [1]

As in, they don't blindly accept patches, if you do all the work and send your patch to them and go "I have done all of this without consulting you, please commit it", it'll get deleted because that's not how SQLite does external contributions. Instead, if you have high quality ideas and even some PoC code to go with that, contact SQLite and discuss whether it makes sense to integrate that. And we can even read the official response by SQLite to the continued claims that they don't accept contributions here on HN. [2]

But of course, whether SQLite accepts patches or not was never at issue, what matters is whether or not this research team contacted SQLite at all, because academic collaboration is one of the pillars of academic research. I don't see that in their paper, which should make you go "why was this step skipped?"

[1] https://www.sqlite.org/copyright.html

[2] https://news.ycombinator.com/item?id=34480732

I wonder if using a different allocator in SQLite (https://www.sqlite.org/malloc.html) would improve performance in their workload to a greater degree than any amount of Rust or io_uring.

I can understand how io_uring increases server utilization, but I fail to see how it will make any individual query faster.

- A "individual query" can be a very complex, turing-complete computer program. A single query may do >1 IO operation like read or write more than one database page. io_uring & async IO strategy would allow this work to occur concurrently.

- Even if no new op-codes are introduced and the design is basically exactly the same, io_uring could allow some amortization of syscall overhead. Doing (N ring-buffer prepares + N/10 syscalls) instead of (N syscalls) will improve your straight-line speed.

^F prof -> no results.

They should do some profiling. The SQLite team did and found that a lot of cycles are wasted on the variable length encoding of numeric values.

Async I/O is nice though, but you know, the SQLite VM already is capable of co-routines, so injecting asynchrony through that path should be doable.

^F porta -> no results. io_uring is nice but not portable, so beware.

I wonder why Limbo has an installer script and isn't just `cargo install limbo`

Update: Checked out the script and it seems to just be for convenience and maybe compatibility with OSes that Cargo can compile for but not run on.

Seeing a curl pipe script makes me worry it's going to ask for odd permissions, if I don't also see something simpler like a binary download or cargo install. There is a zip for Windows so maybe the script is just for getting the binary.

In my experience, Sqlite is faster than Postgres etc. No latency.

Does sqlite cache pages in memory?

If not, how can it be faster?

Is it the IPC overhead of Postgres?

Yes it caches pages in memory. The cache size is configurable via a PRAGMA.

Postgres / MSSQL / all RDBMS is slow because of network I/O.

>Postgres / MSSQL / all RDBMS is slow because of network I/O.

I assume in situations where you're choosing between Postgres and sqlite, everything is running on a single machine anyways.

Just this weekend I had the perfect problem for sqlite, unfortunately 200MB and above it became unwieldy.

I’d like to hear more about this

I've seen this show before. Let's async all the things IO and not pay attention to database integrity and reliably fsync'ing with storage. I look forward to drh's rebuttal.

> Let's async all the things IO and not pay attention to database integrity and reliably fsync'ing with storage

I am not sure how does this affect database integrity or reliably fsync-ing

for e.g. TigerBeetle is another rock solid database which uses async IO. I mentioning it because it is way more mature than Limbo and does a great job at durability

IIUC this is only about read performance. It's totally fine to async all your reads as long as (like SQLite does) you have a Reader-Writer lock and verify integrity properly on writes.

Jepsen will have interesting things to say as well.

Much better framing than the previous "yet another library rewritten in Rust"

> "However, the authors argue that KV doesn’t suit all problem domains. Mapping table-like data into a KV model leads to poor developer experience and (de)serialization costs. SQL would be much better, and SQLite being embedded solves this—it can be directly embedded in the serverless runtime."

The levels people will go to to so that they can use SQL never ceases to astound me.

Relations are one of the most efficient and flexible ways to represent arbitrary graphs.

In my experience Everyone goes to incredible lengths to avoid sql, in ignorance of this fact.

They store (key, value) tables they they then extract into an object graph.

Relations are cool, but SQL DBs either prohibit or make it hard to present relations inside relations, which is one of the most common ways of structuring data in everyday programming life. You can see people suggesting writing SQL functions that convert rows to json or using ORM simply to query a one-to-many relationship, that's crazy: https://stackoverflow.com/questions/54601529/efficiently-map...

Any tool can be used incorrectly...

Im not sure what relations in relations mean. Do you just mean M:N?

I mean 'tables' inside 'tables', 0NF. If I have a list of restaurants with their reviews, naturally, I have a table of restaurants, each of which contains a table of reviews. If I have a table of nested comments, naturally, I have a table of comments, each of which recursively contains a table of child comments.

Those are some of the most common use cases for data presentation; and SQL-based DBs are not flexible enough to handle them in a straightforward way.

    PRAGMA foreign_keys = ON;

> If I have a list of restaurants with their reviews, naturally, I have a table of restaurants, each of which contains a table of reviews.

    CREATE TABLE restaurants (id INTEGER PRIMARY KEY, name);
    CREATE TABLE reviews (timestamp, restaurant REFERENCES restaurants(id), stars, message);
    INSERT INTO restaurants (name) VALUES (...);
    INSERT INTO reviews (timestamp, restaurant, stars, message) VALUES (...);
    SELECT rv.timestamp, rv.stars, rv.message FROM reviews AS rv, restaurants AS rs WHERE rv.restaurant = rs.id AND rs.name = "Foo's Bar-B-Q";

> If I have a table of nested comments, naturally, I have a table of comments, each of which recursively contains a table of child comments.

    CREATE TABLE comments (id INTEGER PRIMARY KEY, parent REFERENCES comments(id), body);
    INSERT INTO comments (parent, body) VALUES (...);
    WITH RECURSIVE tree AS (
        SELECT id, parent, body, CAST(id AS TEXT) AS sequence FROM comments WHERE parent IS NULL
        UNION ALL
        SELECT c.id, c.parent, c.body, (sequence || '-' || c.id) AS sequence FROM comments AS c JOIN tree AS t ON t.id = c.parent
    )
    SELECT t.sequence, t.body FROM tree AS t LEFT JOIN comments ON t.parent = comments.id ORDER BY t.sequence;

Point being: what one "naturally" has is a matter of perspective. Trees can always be flattened into tables that reference other tables (or even themselves).

No. This is binding your data structure to a single access pattern. You can get all the reviews for a restaurant. Now what if you want all the reviews left by a particular user?

I invite you to read the original relational database paper which addresses this exact use case: A Relational Model of Data for Large Shared Data Banks

Storing such data structure would be binding to a single access pattern, I agree. But a query to retrieve such structure is simply convoluted for no reason to the point of many people resorting to hacks like json_agg.

Make a SQL view for the tree and a function in the client application which can read a tree in its linear projected form.

If SQL were flexible, I wouldn’t need to create a whole view for a single query.

You’re telling me there are two presentations of reviews (by restaurant and by user) but it’s too much work to define a query for those two views.

I guess your app can just not support that feature and keep the tree? What do you want to hear?

> Mapping table-like data into a KV model leads to poor developer experience

This is definitely true in my experience. Unless you are literally storing a hashmap, KV databases are a pain to use directly. I think they're meant to be building blocks for other databases.

SQLite is smaller than many KV store libraries to begin with, and KV is a degenerate case of relational data.

Trying to put relational data into K-V store is always going to be a crazy mess. Even if the language used was not SQL, it would still obviously be a good idea to use the right type of database for your problem space.

I mean, if SQL is a good match for the data, embedding a database engine designed to be embedded doesn't seem like too far of a reach?

Reply on news.ycombinator.com