I have a situation where I have a temporary table (because of how it is
being used) that creates a lot of (legitimate) I/O activity. The table
contains information which is transient and tends to stay in shared buffers
because of frequency of use, and it is only necessary to commit the changes
to disk every hour or so (mainly for backup purposes).
I was wondering if I could use CREATE TEMPORARY TABLE <tblname> ... ON
COMMIT PRESERVE ROWS to basically defer the commit of changes to hard disk
until I say 'COMMIT'. The database is non-logging; don't know if use of
COMMIT will be affected by that or not.
If the above won't get me what I want, does anyone have suggestions of
another way to get the same effect? The effect I want is a temporary table
that can be updated in RAM, but not create a lot of I/O activity to the
disk.