12 ms·
In Search of a Faster SQLite
- chistev 2y agoIn my experience, Sqlite is faster than Postgres etc. No latency.
- 0xDEAFBEAD 2y agoDoes sqlite cache pages in memory? If not, how can it be faster? Is it the IPC overhead of Postgres?
- nbevans 2y agoYes it caches pages in memory. The cache size is configurable via a PRAGMA. Postgres / MSSQL / all RDBMS is slow because of network I/O.
- 0xDEAFBEAD 2y ago>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.
- llm_nerd 2y agoThis 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.
- cryptonector 2y ago^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.
- high_byte 2y ago"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.
- feverzsj 2y agoSo, it's almost useless.
- internetter 2y agohttps://www.kryogenix.org/code/browser/why-availability/ https://www.kryogenix.org/code/browser/why-availability/
- Sammi 2y agoSo 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.
- bawolff 2y agoThe 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.
- tsegratis 2y agothey 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)
- deleted 2y ago[deleted]
- efitz 2y agoThe 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.
- deleted 2y ago[deleted]
- rmbyrro 2y ago> 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.
- 66yatman 2y agoThis shouldn't depend on clocks, just tracking Etag is more consistency proof.
- Spivak 2y agoI 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.
- refulgentis 2y agoAre 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.
- ncruces 2y agoTheir 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/ https://turso.tech/
- tracker1 2y agoOf 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.
- ncruces 2y agoI 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.
- chambers 2y agoOne 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
- chrismorgan 2y agoFYI, the word you want there is “disclosing”, not “disclaiming”.
- mirekrusin 2y agoYes, people hallucinate on this one a lot.
- sedatk 2y ago“…put a disclaimer disclosing…”
- chrismorgan 2y agoWhat exactly do you think “disclaimer” (or disclaim, or disclaiming) means?
- efilife 2y agodoes it matter?
- llm_nerd 2y agoIt 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.
- avinassh 2y agohey, 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!
- bawolff 2y agoSo 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.
- bjornsing 2y agoI 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.
- mkl 2y agoYou 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.
- deleted 2y ago[deleted]
- graemep 2y agoFrom 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.
- scheme271 2y agoOne 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.
- dvektor 2y agoLimbo 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/ https://docs.tigerbeetle.com/about/vopr/
- malkia 2y ago^^^ - 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)
- avinassh 2y ago> 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
- ec109685 2y agoThey could license the test suite from SQLite (and a lot of tests are open sourced): https://www.sqlite.org/prosupport.html#th3 https://www.sqlite.org/prosupport.html#th3
- 01HNNWZ0MV43FF 2y agoI wonder why Limbo has an installer script and isn't just `cargo install limbo`
- 01HNNWZ0MV43FF 2y agoUpdate: 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.
- zeroq 2y agoMuch better framing than the previous "yet another library rewritten in Rust"
- egeozcan 2y agosqlite is open source, but an important test harness is not. How does any alternative ensure compatibility? https://www.sqlite.org/th3.html#th3_license https://www.sqlite.org/th3.html#th3_license
- krossitalk 2y agoI 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.
- jmcqk6 2y agosqlite is actually public domain. https://sqlite.org/copyright.html 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.
- ec109685 2y agoThey 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 https://news.ycombinator.com/item?id=34480732
- avinassh 2y agobut 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 https://www.sqlite.org/copyright.html
- nikbackm 2y agoFrom 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.
- jppope 2y ago> "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.
- IshKebab 2y ago> 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.
- liontwist 2y agoRelations 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.
- LudwigNagasena 2y agoRelations 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-mapping-one-to-many-many-to-many-database-to-struct-in-golang https://stackoverflow.com/questions/54601529/efficiently-map...
- bawolff 2y agoAny tool can be used incorrectly... Im not sure what relations in relations mean. Do you just mean M:N?
- conradev 2y agoI wonder if using a different allocator in SQLite (https://www.sqlite.org/malloc.html 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.
- jitl 2y ago- 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.
- TheRealPomax 2y agoSo... 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?)
- IshKebab 2y agoThey'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.
- f30e3dfed1c9 2y agoIt 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."
- IshKebab 2y agoRead further: > In order to keep SQLite completely free and unencumbered by copyright, the project does not accept patches.
- avinassh 2y ago> 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.
- 2y ago
- meneer_oke 2y agoJust this weekend I had the perfect problem for sqlite, unfortunately 200MB and above it became unwieldy.
- deleted 2y ago[deleted]
- tomcam 2y agoI’d like to hear more about this
- samwillis 2y agoThis 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/ 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/ 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)
- topspin 2y agoThank 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.
- KingLancelot 2y ago[dead]
- sqliteoldtimr 2y agoI'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.
- hinkley 2y agoJepsen will have interesting things to say as well.
- adgjlsfhk1 2y agoIIUC 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.
- avinassh 2y ago> 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
- hinkley 2y agoI 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.
- avinassh 2y agoI 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 https://github.com/tursodatabase/limbo
- austin-cheney 2y agoIt 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.
- fulafel 2y ago> 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?)
- kruador 2y agoSQLite 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.
- fulafel 2y agoI 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").