Re: Configure Postgres From SQL

On Mon, 2010-07-12 at 14:57 +0100, Thom Brown wrote:
> On 12 July 2010 14:50, Tom Wilcox <hungrytom(at)gmail(dot)com> wrote:
> > Hi Thom,
> >
> > I am performing update statements that are applied to a single table that is
> > about 96GB in size. These updates are grouped together in a single
> > transaction. This transaction runs until the machine runs out of disk space.

As you are updating this table, you are leaving dead tuples behind for
each of the updates that are not hot updates and the table is getting
bloated. That is most likely why you are running out of disk space.

Turning off fsync will not help you with this. What will help you is
trying to get the database to use hot updates instead, or batching the
updates and letting the table get vacuumed often enough so that the dead
tuples can get marked for re-use.

Hot updates would be very beneficial, even if batch updating. They will
happen if their is no index on the updated column and there is enough
space in the physical page to keep the tuple on the same page. You can
adjust the fillfactor to try and favour this.

You can check if you are doing hot updates by looking at
pg_stat_user_tables for the number of hot updates.

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.