4 ms·

Just a single data point but... I am looking at replacing a custom database with SQL. The application will only ever run on one server, so Sqlite3 was a conte

Just a single data point but... I am looking at replacing a custom database with SQL. The application will only ever run on one server, so Sqlite3 was a contender. The database is very read mostly, which makes it a strong contender. The custom database is extraordinary fast at some things, so it's a complex decision. I needed benchmarks. I chose something that mirrors how data is accessed in the custom database. The existing database does have several tables, but it's hard to use so most accesses are single table, indexed by rowid, with maybe 100,000,000 rows. So I benchmarked a single table, indexed on primary key only, 100,000,000 small rows. Since it was easy because I could use the same SQL I benchmarked both Sqlite3 and Postgresql. I expected Sqlite3 to beat Postgresql on single row access / updates, and Postgresql3 to get the upper hand on multi row access / updates. I was surprised to see Sqlite3 was about twice as fast as Postgresql on everything, including inserting the 100,000,000 records. It was always single writer. Had I tested multiple writers I expect I would have seen really flex its muscles. In case your wondering, the custom database is 100 to 1,000 times faster than Sqlite3 at accessing a single record. Getting that performance was achieved with some big tradeoffs, which make it so hard to code for it creates reliability problems.

simlevesque 2y ago
Did you try with duckdb ?
- rstuart4133 2y ago
  Not yet. It's on my list. Mostly out of curiosity, because it isn't an OLAP application. While it is read mostly there are single row updates, just not enough to make multiple writers a frequent event.
remram 2y ago
Is that SQLite with default settings? There are a few tunables for SQLite like mmap_size and page_size which help in most situations: https://sqlite.org/mmap.html https://sqlite.org/mmap.html (as well as journal_mode=wal)
- rstuart4133 2y ago
  Postgresql was a default install. The only sqlite3 customisation was: "PRAGMA main.journal_mode = WAL". In particular I suspect people are asking whether I used exclusive mode or unsafe writes for Sqlite3. The answer is no. Apart from that, everything was done in "reasonable sized transactions", with the same size being used for both Postgresql and Sqlite3. Necessarily so, because I ran the same SQL statements over both. For single row fetches or updates the transaction was a single row. When I was testing how fast I could update a batch of 10 rows, all 10 were done in a single transaction. (Aside: the transaction overhead far outweighed the cost of one statement. Updating 100 rows only took twice the time of updating 1.) When creating the database of 100,000,000 rows, they were inserted in transactions of 10,000 rows. I was emulating how our system inserts records into this 100,000,000 row table. It uses an almost ascending primary key, which makes life easier for the underlying b-tree. I've found emulating that sort of thing is important for realistic benchmarks. That's because on this particular table the primary key is (current-unix-time, 32-bit-random-int). current-unix-time was monotonically increasing, but in any given second some 100 randomly ordered 32-bit-random-int's were used. From memory Sqlite3 inserted 100k records per second, Postgresql some number less than that. But don't pay much attention to the absolute numbers, as it was on spinning rust while the real system was doing other things (again because the real system had the same constraints). Only my Macbook with NVME it was several times faster in absolute terms, but Sqlite3 and Postgresql maintained their relative positions.
Quiark 2y ago
How much of it was because you didn't go through TCP/IP with sqlite?
wewewedxfgdf 2y ago
Databases are configured for different priorities out of the box. For example when you insert a row that result in a write to the WAL. That's a data safety measure sqlite unlikely to be taking. You can configure Postgres to perform better with different tradeoffs - here's some thing to investigate: - synchronous_commit=off - wal_level=minimal - wal_writer_delay=200ms - checkpoint_completion_target=0.9 - autovacuum=off - bgwriter_delay=10000ms - shared_buffers=512MB - work_mem=64MB - UNLOGGED tables - run from RAM Disk (/dev/shm)
- toxik 2y ago
  SQLite has had a WAL (write-ahead log) since 2010. Admittedly, it is not the default. [1] [1] https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
  - Sammi 2y ago
    * Not the default in the original c library ... except it is the default in language wrappers like better-sqlite3 for nodejs. It's probably unlikely that you're consuming the upstream sqlite c lib directly with its intentionally antiquated defaults. You're much more likely using a provider that has tweaked the defaults to more modern standards.
    - jamal-kumar 2y ago
      I found the old wrappers to be really messed up and buggy compared to the new nodejs builtin that works charmingly well Do you know any details about those tweaks you mention because it would be very nice to know what edge cases I gotta watch out for using any of these
      - cute_boi 2y ago
        > new nodejs builtin Do you mean this node js 23 sqlite api[1]? [1] https://nodejs.org/api/sqlite.html https://nodejs.org/api/sqlite.html
      - 2y ago
RestartKernel 2y ago
Are you able to elaborate on your custom database? Not that I'm in the position to offer any advice, but you got me curious about it.
Salgat 2y ago
Is something like redis with aof enabled an option? Assuming the rows aren't holding too much data.
zhwu 2y ago
This recent blog actually looks into the case with multiple writers and the distribution for the time for a writer to take the lock: https://blog.skypilot.co/abusing-sqlite-to-handle-concurrency/ https://blog.skypilot.co/abusing-sqlite-to-handle-concurrenc...

simlevesque 2y ago

Did you try with duckdb ?

rstuart4133 2y ago
Not yet. It's on my list. Mostly out of curiosity, because it isn't an OLAP application. While it is read mostly there are single row updates, just not enough to make multiple writers a frequent event.

remram 2y ago

Is that SQLite with default settings? There are a few tunables for SQLite like mmap_size and page_size which help in most situations: https://sqlite.org/mmap.html https://sqlite.org/mmap.html (as well as journal_mode=wal)

rstuart4133 2y ago
Postgresql was a default install. The only sqlite3 customisation was: "PRAGMA main.journal_mode = WAL". In particular I suspect people are asking whether I used exclusive mode or unsafe writes for Sqlite3. The answer is no. Apart from that, everything was done in "reasonable sized transactions", with the same size being used for both Postgresql and Sqlite3. Necessarily so, because I ran the same SQL statements over both. For single row fetches or updates the transaction was a single row. When I was testing how fast I could update a batch of 10 rows, all 10 were done in a single transaction. (Aside: the transaction overhead far outweighed the cost of one statement. Updating 100 rows only took twice the time of updating 1.) When creating the database of 100,000,000 rows, they were inserted in transactions of 10,000 rows. I was emulating how our system inserts records into this 100,000,000 row table. It uses an almost ascending primary key, which makes life easier for the underlying b-tree. I've found emulating that sort of thing is important for realistic benchmarks. That's because on this particular table the primary key is (current-unix-time, 32-bit-random-int). current-unix-time was monotonically increasing, but in any given second some 100 randomly ordered 32-bit-random-int's were used. From memory Sqlite3 inserted 100k records per second, Postgresql some number less than that. But don't pay much attention to the absolute numbers, as it was on spinning rust while the real system was doing other things (again because the real system had the same constraints). Only my Macbook with NVME it was several times faster in absolute terms, but Sqlite3 and Postgresql maintained their relative positions.

Quiark 2y ago

How much of it was because you didn't go through TCP/IP with sqlite?

wewewedxfgdf 2y ago

Databases are configured for different priorities out of the box. For example when you insert a row that result in a write to the WAL. That's a data safety measure sqlite unlikely to be taking. You can configure Postgres to perform better with different tradeoffs - here's some thing to investigate: - synchronous_commit=off - wal_level=minimal - wal_writer_delay=200ms - checkpoint_completion_target=0.9 - autovacuum=off - bgwriter_delay=10000ms - shared_buffers=512MB - work_mem=64MB - UNLOGGED tables - run from RAM Disk (/dev/shm)

toxik 2y ago
SQLite has had a WAL (write-ahead log) since 2010. Admittedly, it is not the default. [1] [1] https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
- Sammi 2y ago
  * Not the default in the original c library ... except it is the default in language wrappers like better-sqlite3 for nodejs. It's probably unlikely that you're consuming the upstream sqlite c lib directly with its intentionally antiquated defaults. You're much more likely using a provider that has tweaked the defaults to more modern standards.
  - jamal-kumar 2y ago
    I found the old wrappers to be really messed up and buggy compared to the new nodejs builtin that works charmingly well Do you know any details about those tweaks you mention because it would be very nice to know what edge cases I gotta watch out for using any of these
    - cute_boi 2y ago
      > new nodejs builtin Do you mean this node js 23 sqlite api[1]? [1] https://nodejs.org/api/sqlite.html https://nodejs.org/api/sqlite.html
    - 2y ago

RestartKernel 2y ago

Are you able to elaborate on your custom database? Not that I'm in the position to offer any advice, but you got me curious about it.

Salgat 2y ago

Is something like redis with aof enabled an option? Assuming the rows aren't holding too much data.

zhwu 2y ago

This recent blog actually looks into the case with multiple writers and the distribution for the time for a writer to take the lock: https://blog.skypilot.co/abusing-sqlite-to-handle-concurrency/ https://blog.skypilot.co/abusing-sqlite-to-handle-concurrenc...