Defer Committing Updates on High-Activity Table

From: "Lane Van Ingen" <lvaningen(at)esncc(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Defer Committing Updates on High-Activity Table
Date: 2005-08-03 18:47:14
Message-ID: EKEMKEFLOMKDDLIALABIOEAFCBAA.lvaningen@esncc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Dario Brignardello 2005-08-03 18:50:38 Re: Blocking connection and timeout problem
Previous Message Dario Brignardello 2005-08-03 17:33:17 Re: Blocking connection and timeout problem