Hacker News Reader

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)

SQLite has had a WAL (write-ahead log) since 2010. Admittedly, it is not the default. [1]

[1] https://www.sqlite.org/wal.html

* 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.

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

> new nodejs builtin

Do you mean this node js 23 sqlite api[1]?

[1] https://nodejs.org/api/sqlite.html

Been running better-sqlite3 in production for a long time and it's been fast and reliable for me.

Looking at their github stats and open issues and prs, it looks like the project is healthy.

In postgres, Is there a way to create a table that stores in ramdisk, but allows you to trigger writing to disk yourself?

I have a usecase where our application has events where it would be a good time to trigger disk IO. The changes that occur between those app level checkpoints can totally be ephemeral.

Maybe something like (untested):

- Create an unlogged table named events_active.

- At the app_checkpoint time, start a transaction:

  1. Rename events_active to events_pending_write.

  2. Create a new unlogged table, events_active, to handle writes for the next app_checkpoint.

- In a new transaction:

  1. Set events_pending_write to LOGGED. This writes the entire table into the WAL. [1]

  2. Rename to events_pending_write to events_part_20250304

  3. Attach events_part_20250304 to a partitioned events table.

For stronger consistency, combine the two transactions, but you'll block incoming writes until the transaction completes.

Unlogged tables don't guarantee Postgres uses RAM for the table. If you need a stronger guarantee, mount a directory on a ramdisk and create a table space to hold the events_active table. Then, to promote to a logged table, use a create-table-as statement to copy the table into a new partition.

[1]: https://dba.stackexchange.com/a/195829

Thank you, gives me something to play with.

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 (as well as journal_mode=wal)

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.

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

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.

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-concurrenc...

Did you try with duckdb ?

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.

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