4 ms·

Build your own SQLite in Rust, Part 5: Evaluating queries

216 points by todsacerdoti 2y ago

bfrog 2y ago
This is a really cool set of articles, and while it’s not going to replace sqlite it’s fantastic to see the pieces needed to do sql with SQLite’s file format
- rockwotj 2y ago
  SQLites alternative in rust is a thing though https://github.com/tursodatabase/limbo https://github.com/tursodatabase/limbo
  - MobiusHorizons 2y ago
    perhaps instead of `successor` you could say `rust fork` or `alternative`. Successor implies the original is dead or deprecated, or no longer going to be used, which is very far from the truth.
  - koakuma-chan 2y ago
    Can't wait to get my Blazingly Fast™ Managed Cloud SQLite for only 0.0001$ per virtual compute second.
    - pigbearpig 2y ago
      I think you’re looking for LiteFS https://fly.io/blog/introducing-litefs/ https://fly.io/blog/introducing-litefs/
      - wongarsu 2y ago
        Also Cloudflare D1, announced just a couple months earlier: https://blog.cloudflare.com/introducing-d1/ https://blog.cloudflare.com/introducing-d1/
  - usrbinbash 2y ago
    "alternative" implies that this is either on a comparable level of battle-testedness and feature completeness as sqlite, or that it solves problems that sqlite has, or that is delivers substantiable, or at least noticeable advantages over sqlite. So, which of these points apply?
    - sejje 2y ago
      I don't think it implies any of that. I think it means you might use it in similar situations. Like when you want an in-memory database.
      - usrbinbash 2y ago
        No I might not. In these similar situations, I will use sqlite, unless someone can point out one of the items listed above as a reason to use something else.
        NoahKAndrews 2y ago
        Just because you don't like it doesn't make it not an alternative. Even if it's objectively worse, it's still an alternative.
        usrbinbash 2y ago
        > Just because you don't like it Read my post again. Sympathy or lack thereof don't factor into this equation. This is about battle-testedness and features. I don't make technical decisions based on emotions.
mmiao 2y ago
Making a basic database that works is not hard, but making a robust one is really hard. What makes SQLite shines is its extensive testsuite. I still don't understand the motivation of limbo, as without years of hardwork on test you can't say you are correct, and why i should pay for it...
- pornel 2y ago
  At this point it's too early to even worry about correctness, it doesn't work yet. But the years of work put into the existing project to make it robust don't mean the exact same years have to be spent on the reimplementation: - there's been work spent on discovering the right architecture and evolving the db format. A new impl can copy the end result. - hard lessons have been learned about dealing with bad disks, filesystems, fsync, flaky locks, etc. A new impl can learn from the solutions without having to rediscover them the hard way. - C projects spend some time on compatibility with C compilers, OSes, and tweaking build scripts, which are relatively easy in Rust. Testing will need a clever solution. Maybe they'll buy access to the official test suite? Maybe they'll use the original SQLite to fuzz and compare results?
  - crabmusket 2y ago
    The Limbo team seems to be leaning heavily into deterministic simulation testing (DST) and one of the cofounders on a recent podcast was very enthusiastic about the benefits of the approach. https://github.com/tursodatabase/limbo/tree/main/simulator https://github.com/tursodatabase/limbo/tree/main/simulator https://changelog.com/podcast/626 https://changelog.com/podcast/626
- n42 2y ago
  Something being hard doesn't mean it's not worth trying. It's up to the developers to decide if the outcome is worth the effort. Maybe they're right, maybe they're wrong, but either way – if they succeed, we're all better off for it, aren't we?
- mamcx 2y ago
  > without years of hardwork on test you can't say you are correct Before, I have the myth in my head that RDBMS development is like cryptography/kernels: Something not mean for humans to do. Now working as part of one (http://spacetimedb.com http://spacetimedb.com) I see now that is hard, but doable. Certainly MORE EASY than working in ERPs that is the thing that I have done for more than 20 years (now THAT is what is insane). Literally doing RDBMS is more relaxing than doing ERPs. But what I have learned is that what make RDBMS truly hard is not the usual suspect: ACID. You don't need 'years of hardwork' to reach it and to prove them is right (only some insanity when you need to target very weird os/archs and deal with faulty hardware). What is truly hard and will eat your time is sql. SQL is hard. Is *bad*. Is *very hard* to optimize, and is so anemic that your time is expended more in the query optimizer (that has hard time because sql more than anything else) and because sql is bad, I tell you, it leads to insane stuff like having a single query with 1000 joins, and people have not means to good, optimal designs (and what you have like CTE and all that is poor bandaids) so now you get a bad input and have fun. But if the RDBMS stay in the relational part and you could make a better lang to interface it then is far more easy. Still query optimizer is big part of your time, but if you have control of the input language you can do your life easy.
Animats 2y ago
I wish someone would finish a decent database in Rust. At least get it to 1.0 stable and go on from there. - Limbo: "Limbo is a work-in-progress..." - Sled [1]. Not sure what's going on there. Last release 3 years ago, but a constant stream of "alpha" versions that never get released. SQLite with Rust bindings seems to be the go-to system. Depending on C packages is often a headache when cross-compiling, though. [1] https://crates.io/crates/sled/ https://crates.io/crates/sled/
- anarki8 2y ago
  redb is what are you looking for https://github.com/cberner/redb https://github.com/cberner/redb
- koakuma-chan 2y ago
  I recommend `rusqlite` or `heed` (`lmdb` wrapper, key-value database). I think `sled` is abandoned.
- tyushk 2y ago
  SurrealDB [1] is a fairly complete database written in Rust. I've used it for fairly small web apps and it felt comfortable to work with coming from MongoDB. [1] https://github.com/surrealdb/surrealdb?tab=readme-ov-file https://github.com/surrealdb/surrealdb?tab=readme-ov-file
  - Tanjreeve 2y ago
    SurrealDB is still really coy on its performance/what it's good at/not good at to adopt for a major data project. There's lots of features but no real indication as to if I could scale them for a dataset of billions of records. I've had my fingers burnt too many times before by products with a big table of tick box features but none of them are really usable (e.g Geospatial data comes up for me a lot) Either you need to make it easy and zero friction to adopt like duckDB and let people find out themselves in an hour or two or you need to provide some sort of benchmarks + evidence that it isn't going to die on its arse the moment you put larger than memory amounts of data in. Nearly all of these projects work fine for in memory size datasets but only finding out after you've put major effort into adoptionv+integration isn't really easy for someone working with data when you have something battle tested like Postgres et Al.

216 points by todsacerdoti 2y ago

This is a really cool set of articles, and while it’s not going to replace sqlite it’s fantastic to see the pieces needed to do sql with SQLite’s file format

rockwotj 2y ago
SQLites alternative in rust is a thing though https://github.com/tursodatabase/limbo https://github.com/tursodatabase/limbo
- MobiusHorizons 2y ago
  perhaps instead of `successor` you could say `rust fork` or `alternative`. Successor implies the original is dead or deprecated, or no longer going to be used, which is very far from the truth.
- koakuma-chan 2y ago
  Can't wait to get my Blazingly Fast™ Managed Cloud SQLite for only 0.0001$ per virtual compute second.
  - pigbearpig 2y ago
    I think you’re looking for LiteFS https://fly.io/blog/introducing-litefs/ https://fly.io/blog/introducing-litefs/
    - wongarsu 2y ago
      Also Cloudflare D1, announced just a couple months earlier: https://blog.cloudflare.com/introducing-d1/ https://blog.cloudflare.com/introducing-d1/
- usrbinbash 2y ago
  "alternative" implies that this is either on a comparable level of battle-testedness and feature completeness as sqlite, or that it solves problems that sqlite has, or that is delivers substantiable, or at least noticeable advantages over sqlite. So, which of these points apply?
  - sejje 2y ago
    I don't think it implies any of that. I think it means you might use it in similar situations. Like when you want an in-memory database.
    - usrbinbash 2y ago
      No I might not. In these similar situations, I will use sqlite, unless someone can point out one of the items listed above as a reason to use something else.
      - NoahKAndrews 2y ago
        Just because you don't like it doesn't make it not an alternative. Even if it's objectively worse, it's still an alternative.
        usrbinbash 2y ago
        > Just because you don't like it Read my post again. Sympathy or lack thereof don't factor into this equation. This is about battle-testedness and features. I don't make technical decisions based on emotions.

mmiao 2y ago

Making a basic database that works is not hard, but making a robust one is really hard. What makes SQLite shines is its extensive testsuite. I still don't understand the motivation of limbo, as without years of hardwork on test you can't say you are correct, and why i should pay for it...

pornel 2y ago
At this point it's too early to even worry about correctness, it doesn't work yet. But the years of work put into the existing project to make it robust don't mean the exact same years have to be spent on the reimplementation: - there's been work spent on discovering the right architecture and evolving the db format. A new impl can copy the end result. - hard lessons have been learned about dealing with bad disks, filesystems, fsync, flaky locks, etc. A new impl can learn from the solutions without having to rediscover them the hard way. - C projects spend some time on compatibility with C compilers, OSes, and tweaking build scripts, which are relatively easy in Rust. Testing will need a clever solution. Maybe they'll buy access to the official test suite? Maybe they'll use the original SQLite to fuzz and compare results?
- crabmusket 2y ago
  The Limbo team seems to be leaning heavily into deterministic simulation testing (DST) and one of the cofounders on a recent podcast was very enthusiastic about the benefits of the approach. https://github.com/tursodatabase/limbo/tree/main/simulator https://github.com/tursodatabase/limbo/tree/main/simulator https://changelog.com/podcast/626 https://changelog.com/podcast/626
n42 2y ago
Something being hard doesn't mean it's not worth trying. It's up to the developers to decide if the outcome is worth the effort. Maybe they're right, maybe they're wrong, but either way – if they succeed, we're all better off for it, aren't we?
mamcx 2y ago
> without years of hardwork on test you can't say you are correct Before, I have the myth in my head that RDBMS development is like cryptography/kernels: Something not mean for humans to do. Now working as part of one (http://spacetimedb.com http://spacetimedb.com) I see now that is hard, but doable. Certainly MORE EASY than working in ERPs that is the thing that I have done for more than 20 years (now THAT is what is insane). Literally doing RDBMS is more relaxing than doing ERPs. But what I have learned is that what make RDBMS truly hard is not the usual suspect: ACID. You don't need 'years of hardwork' to reach it and to prove them is right (only some insanity when you need to target very weird os/archs and deal with faulty hardware). What is truly hard and will eat your time is sql. SQL is hard. Is *bad*. Is *very hard* to optimize, and is so anemic that your time is expended more in the query optimizer (that has hard time because sql more than anything else) and because sql is bad, I tell you, it leads to insane stuff like having a single query with 1000 joins, and people have not means to good, optimal designs (and what you have like CTE and all that is poor bandaids) so now you get a bad input and have fun. But if the RDBMS stay in the relational part and you could make a better lang to interface it then is far more easy. Still query optimizer is big part of your time, but if you have control of the input language you can do your life easy.

Animats 2y ago

I wish someone would finish a decent database in Rust. At least get it to 1.0 stable and go on from there. - Limbo: "Limbo is a work-in-progress..." - Sled [1]. Not sure what's going on there. Last release 3 years ago, but a constant stream of "alpha" versions that never get released. SQLite with Rust bindings seems to be the go-to system. Depending on C packages is often a headache when cross-compiling, though. [1] https://crates.io/crates/sled/ https://crates.io/crates/sled/

anarki8 2y ago
redb is what are you looking for https://github.com/cberner/redb https://github.com/cberner/redb
koakuma-chan 2y ago
I recommend `rusqlite` or `heed` (`lmdb` wrapper, key-value database). I think `sled` is abandoned.
tyushk 2y ago
SurrealDB [1] is a fairly complete database written in Rust. I've used it for fairly small web apps and it felt comfortable to work with coming from MongoDB. [1] https://github.com/surrealdb/surrealdb?tab=readme-ov-file https://github.com/surrealdb/surrealdb?tab=readme-ov-file
- Tanjreeve 2y ago
  SurrealDB is still really coy on its performance/what it's good at/not good at to adopt for a major data project. There's lots of features but no real indication as to if I could scale them for a dataset of billions of records. I've had my fingers burnt too many times before by products with a big table of tick box features but none of them are really usable (e.g Geospatial data comes up for me a lot) Either you need to make it easy and zero friction to adopt like duckDB and let people find out themselves in an hour or two or you need to provide some sort of benchmarks + evidence that it isn't going to die on its arse the moment you put larger than memory amounts of data in. Nearly all of these projects work fine for in memory size datasets but only finding out after you've put major effort into adoptionv+integration isn't really easy for someone working with data when you have something battle tested like Postgres et Al.