HN

Comments (26)

• Menu

  carlsverre
  You might be interested in taking a look at Graft (https://graft.rs/). I have been iterating in this space for the last year, and have learned a lot about it. Graft has a slightly different set of goals, one of which is to keep writes fast and small and optimize for partial replication. That said, Graft shares several design decisions, including the use of framed ZStd compression to store pages.I do like the B-tree aware grouping idea. This seems like a useful optimization for larger scan-style workloads. It helps eliminate the need to vacuum as much.Have you considered doing other kinds of optimizations? Empty pages, free pages, etc.
• edinetdb
  The database-per-tenant angle resonates. I hit a similar tradeoff building a financial data API—highly uneven access patterns where most entities are rarely queried but a few hundred get hit constantly.We went a different direction: BigQuery for durable storage, but an in-memory layer that pre-loads the "gold" aggregated tables (~26MB of pre-computed data for ~4,000 companies) at startup. Cold queries fall back to BigQuery directly; warm queries skip it entirely. Reduced our query costs by ~88%, though that's partly a function of table size being small enough to fit in memory.The scans-are-slow limitation you mention is where we'd still hit BigQuery directly anyway—full text search across 9M+ document sections can't be cached that way. So there's probably a hybrid approach where hot structured data lives in-memory and raw text stays in the warehouse.What's your strategy for tables that grow beyond what fits in a memory budget? Does the page-group layout still help when you can't fit the whole table—do you see a way to do partial pre-loading by access frequency?
• Menu

  russellthehippo
  A bit more color on what I found interesting building this:The motivating question for me was less “can SQLite read over the network?” and more “what assumptions break once the storage layer is object storage instead of a filesystem?”The biggest conceptual shift was around *layout*.What felt most wrong in naive designs was that SQLite page numbers are not laid out in a way that matches how you want to fetch data remotely. If an index is scattered across many unrelated page ranges, then “prefetch nearby pages” is kind of a fake optimization. Nearby in the file is not the same thing as relevant to the query.That pushed me toward B-tree-aware grouping. Once the storage layer starts understanding which table or index a page belongs to, a lot of other things get cleaner: more targeted prefetch, better scan behavior, less random fetching, and much saner request economics.Another thing that became much more important than I expected is that *different page types matter a lot*. Interior B-tree pages are tiny in footprint but disproportionately important, because basically every query traverses them. That changed how I thought about the system: much less as “a database file” and much more as “different classes of pages with very different value on the critical path.”The query-plan-aware “frontrun” part came from the same instinct. Reactive prefetch is fine, but SQLite often already knows a lot about what it is about to touch. If the storage layer can see enough of that early, it can start warming the right structures before the first miss fully cascades. That’s still pretty experimental, but it was one of the more fun parts of the project.A few things I learned building this:1. *Cold point reads and small joins seem more plausible than I expected.* Not local-disk fast, obviously, but plausible for the “many mostly-cold DBs” niche.2. *The real enemy is request count more than raw bytes.* Once I leaned harder into grouping and prefetch by tree, the design got much more coherent.3. *Scans are still where reality bites.* They got much less bad, but they are still the place where remote object storage most clearly reminds you that it is not a local SSD.4. *The storage backend is super important.* Different storage backends (S3, S3 Express, Tigris) have verg different round trip latencies and it's the single most important thing in determining how to tune prefetching.Anyway, happy to talk about the architecture, the benchmark setup, what broke, or why I chose this shape instead of raw-file range GETs / replication-first approaches / etc.
• Menu

  agosta
  This is awesome! With all of the projects/teams working on improving sqlite, it feels like it's just a matter of time before it becomes a better default than postgres for serious projects.I do wonder - for projects that do ultimately enforce single writer sqlite setups - it still feels to me as if it would always be better to keep the sqlite db local (and then rsync/stream backups to whatever S3 storage one prefers).The nut I've yet to see anyone crack on such setup is to figure out a way to achieve zero downtime deploys. For instance, adding a persistent disk to VMs on Render prevents zero downtime deploys (see https://render.com/docs/disks#disk-limitations-and-considera...) which is a real unfortunate side effect. I understand that the reason for this is because a VM instance is attached to the volume and needs to be swapped with the new version of said instance...There are so many applications where merely scaling up a single VM as your product grows simplifies devops / product maintenance so much that it's a very compelling choice vs managing a cluster/separate db server. But getting forced downtime between releases to achieve that isn't acceptable in a lot of cases.Not sure if it's truly a cheaply solvable problem. One potential option is to use a tool like turbolite as a parallel data store and, only during deployments, use it to keep the application running for the 10 to 60 seconds during a release swap. During this time, writes to the db are slower than usual but entirely online. And then, when your new release is live, it can sync the difference of data written to s3 back to the local db. In this way, during regular operation, we get the performance of local IO and fallback onto s3 backed sqlite during upgrades for persistent uptime.Sounds like a fraught thing to build. But man it really is hard/impossible to beat the speed of local reads!
• russellthehippo
  Also I want to acknowledge the other projects in adjacent parts of this space — raw SQLite range-request VFSes, Litestream/LiteFS-style replication approaches, libSQL/Turso, Neon, mvsqlite, etc. I took a lot of inspiration from them, thanks!
• Menu

  bob1029
  Have you considered using techniques like conditional PUT to enable multiple writers?https://aws.amazon.com/about-aws/whats-new/2024/08/amazon-s3...https://docs.aws.amazon.com/AmazonS3/latest/userguide/condit...
• Menu

  alex_hirner
  What are your thoughts on eviction, re how easy to add some basic policy?
• Menu

  jijji
  i wonder how much that costs per hour to run any normal load? what benefit does this have versuss using mysql (or any similar rdbms) for the queries? mysql/pgsql/etc is free remember, so using S3 obviously charges by the request, or am i wrong?
• inferense
  very cool!
• michaeljelly
  Really cool
• ryanjso
  super sick
• yalvhe2009
  [dead]
• yalvhe2009
  [dead]
• manudaro
  [dead]