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