4 ms·

I do t have time to test myself now, but it would be interesting to see a proper benchmark. We all know it's not suitable for high write concurrency, but SQLite

I do t have time to test myself now, but it would be interesting to see a proper benchmark. We all know it's not suitable for high write concurrency, but SQLite should be a very good amount faster for reads because of the lack of overhead. But how much faster is it really?

adzm 9mo ago
as an in memory database, I got around 40,000,000 reads per second. Using WAL and a file rather than in memory, around 900,000 reads per second. This is single threaded, for a simple integer ID to string Value query, and a few years old at this point, and only minor config optimizations eg not even using memory mapped io and a ~3gb database with a million or so rows on a Windows machine. The performance really is amazing.
- NorwegianDude 9mo ago
  40 million reads per second, on a single core? 40 million reads/s is 25 ns per read, that is faster than any RAM I know of.
  - yencabulator 9mo ago
    It's not like every read would make a separate trip all the way to RAM, caches are a thing and SIMD pipelines/parallelizes comparisons within a hash bucket quite well. Lookups from a hash map should amortize to something like 5-20ns per lookup these days. Abseil's Swiss Tables for C++ and Rust's Hashbrown both should reach that.
    - NorwegianDude 9mo ago
      If you're looking up values from a 3 GB DB, most would have to hit RAM. Lookups form a hash map can be fast, but SQLite does quite a bit more than just a hash map lookup, and it would usually hit RAM, not L3 cache.
      - yencabulator 9mo ago
        Parent comment said "with a million or so rows". I looked up numbers for benchmarks with ~1M entries in the hashmap. 1M 64-bit integers is only 8MB, that's still a small keyspace.
        adzm 9mo ago
        Perhaps relevant is that probably only 25% of the IDs that get passed to the select actually have values and this is using one thread for the benchmark. It's too convoluted to share, but indeed when using the in memory database on a lower spec laptop currently I still get up to 20-30m reads per second, pretty close to the 40m on the big box.
solumunus 9mo ago
Orders of magnitude I would imagine. Very significantly faster.
jacobobryant 9mo ago
In some informal benchmarks I wrote using queries + data from a web app I develop, sqlite queries were about 5x faster than postgres.

adzm 9mo ago

as an in memory database, I got around 40,000,000 reads per second. Using WAL and a file rather than in memory, around 900,000 reads per second. This is single threaded, for a simple integer ID to string Value query, and a few years old at this point, and only minor config optimizations eg not even using memory mapped io and a ~3gb database with a million or so rows on a Windows machine. The performance really is amazing.

NorwegianDude 9mo ago
40 million reads per second, on a single core? 40 million reads/s is 25 ns per read, that is faster than any RAM I know of.
- yencabulator 9mo ago
  It's not like every read would make a separate trip all the way to RAM, caches are a thing and SIMD pipelines/parallelizes comparisons within a hash bucket quite well. Lookups from a hash map should amortize to something like 5-20ns per lookup these days. Abseil's Swiss Tables for C++ and Rust's Hashbrown both should reach that.
  - NorwegianDude 9mo ago
    If you're looking up values from a 3 GB DB, most would have to hit RAM. Lookups form a hash map can be fast, but SQLite does quite a bit more than just a hash map lookup, and it would usually hit RAM, not L3 cache.
    - yencabulator 9mo ago
      Parent comment said "with a million or so rows". I looked up numbers for benchmarks with ~1M entries in the hashmap. 1M 64-bit integers is only 8MB, that's still a small keyspace.
      - adzm 9mo ago
        Perhaps relevant is that probably only 25% of the IDs that get passed to the select actually have values and this is using one thread for the benchmark. It's too convoluted to share, but indeed when using the in memory database on a lower spec laptop currently I still get up to 20-30m reads per second, pretty close to the 40m on the big box.

solumunus 9mo ago

Orders of magnitude I would imagine. Very significantly faster.

jacobobryant 9mo ago

In some informal benchmarks I wrote using queries + data from a web app I develop, sqlite queries were about 5x faster than postgres.