3 ms·

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 mea

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
    - Sammi 2y ago
      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.
deleted 2y ago
[deleted]
MrLeap 2y ago
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.
- sa46 2y ago
  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 https://dba.stackexchange.com/a/195829
  - MrLeap 2y ago
    Thank you, gives me something to play with.

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
  - Sammi 2y ago
    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.

deleted 2y ago

[deleted]

MrLeap 2y ago

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.

sa46 2y ago
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 https://dba.stackexchange.com/a/195829
- MrLeap 2y ago
  Thank you, gives me something to play with.