X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7DD63528C0 for ; Wed, 3 Aug 2005 16:39:21 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 75265-03 for ; Wed, 3 Aug 2005 19:39:02 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7FB2F52CA2 for ; Wed, 3 Aug 2005 16:39:02 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j73Jd1NR003434; Wed, 3 Aug 2005 15:39:01 -0400 (EDT) To: "Lane Van Ingen" Cc: pgsql-admin@postgresql.org Subject: Re: Defer Committing Updates on High-Activity Table In-reply-to: References: Comments: In-reply-to "Lane Van Ingen" message dated "Wed, 03 Aug 2005 14:47:14 -0400" Date: Wed, 03 Aug 2005 15:39:00 -0400 Message-ID: <3433.1123097940@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/43 X-Sequence-Number: 18362 "Lane Van Ingen" 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