4 ms·
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 applicatio
by 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 agoMaybe 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 agoThank you, gives me something to play with.