pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Defer Committing Updates on High-Activity Table
2+ messages / 2 participants
[nested] [flat]

* Defer Committing Updates on High-Activity Table
@ 2005-08-03 18:47 Lane Van Ingen <lvaningen@esncc.com>
  2005-08-03 19:39 ` Re: Defer Committing Updates on High-Activity Table Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Lane Van Ingen @ 2005-08-03 18:47 UTC (permalink / raw)
  To: 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.





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Defer Committing Updates on High-Activity Table
  2005-08-03 18:47 Defer Committing Updates on High-Activity Table Lane Van Ingen <lvaningen@esncc.com>
@ 2005-08-03 19:39 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2005-08-03 19:39 UTC (permalink / raw)
  To: Lane Van Ingen <lvaningen@esncc.com>; +Cc: pgsql-admin

"Lane Van Ingen" <lvaningen@esncc.com> writes:
> 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).

If it's actually a temp table, then the data isn't in shared buffers;
it's kept in backend-local buffers, and isn't written to disk at all
unless the backend needs to evict a page from those buffers to make
room for more temp data.

What you probably really need is to enlarge the number of buffers
available for temp-table data.  This number is user-configurable
as of Postgres 8.1, but unfortunately is hard-wired at a pretty
small value in existing releases.  If you're desperate you could
try increasing NLocBuffer in src/backend/storage/buffer/localbuf.c,
but I'm not sure how well that will work --- the pre-8.1 code is
not designed to perform well with large values of NLocBuffer.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-08-03 19:39 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-03 18:47 Defer Committing Updates on High-Activity Table Lane Van Ingen <lvaningen@esncc.com>
2005-08-03 19:39 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox