Hacker News Reader

Comment by wewewedxfgdf | 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)

View on HN

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

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.

> new nodejs builtin

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

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

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.

Reply on news.ycombinator.com