DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=PpAzFMRUsZSTlfoSn8FoOOZ3OMUxFExzi9OohlEVOezZ/MQbr2J3lp+2w8ZieAKUnaC/krPhxxrerHqbKIym/ktu9gDzVtKWzBz2tanO0AU8VnRWybOWvjiGF+097OyuakpCy12yluhVeoFwtDFmhkKlvX+2Wul8pDKpASssuxQ=
Message-ID: <72e966b00509032316166ff0cf@mail.gmail.com>
Date: Sun, 4 Sep 2005 00:16:10 -0600
From: Jan Peterson <jan.l.peterson@gmail.com>
Reply-To: jan.l.peterson@gmail.com
To: pgsql-performance@postgresql.org
Subject: poor VACUUM performance on large tables
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hello,

We have been experiencing poor performance of VACUUM in our production
database.  Relevant details of our implementation are as follows:

1.  We have a database that grows to about 100GB.
2.  The database is a mixture of large and small tables.
3.  Bulk data (stored primarily in pg_largeobject, but also in various
TOAST tables) comprises about 45% of our data.
4.  Some of our small tables are very active, with several hundred
updates per hour.
5.  We have a "rolling delete" function that purges older data on a
periodic basis to keep our maximum database size at or near 100GB.

Everything works great until our rolling delete kicks in.  Of course,
we are doing periodic VACUUMS on all tables, with frequent VACUUMs on
the more active tables.  The problem arises when we start deleting the
bulk data and have to VACUUM pg_largeobject and our other larger
tables.  We have seen VACUUM run for several hours (even tens of
hours).  During this VACUUM process, our smaller tables accumulate
dead rows (we assume because of the transactional nature of the
VACUUM) at a very rapid rate.  Statistics are also skewed during this
process and we have observed the planner choosing sequential scans on
tables where it is obvious that an index scan would be more efficient.

We're looking for ways to improve the performance of VACUUM.  We are
already experimenting with Hannu Krosing's patch for VACUUM, but it's
not really helping (we are still faced with doing a database wide
VACUUM about once every three weeks or so as we approach the
transaction id rollover point... this VACUUM has been measured at 28
hours in an active environment).

Other things we're trying are partitioning tables (rotating the table
that updates happen to and using a view to combine the sub-tables for
querying).  Unfortunately, we are unable to partition the
pg_largeobject table, and that table alone can take up 40+% of our
database storage.  We're also looking at somehow storing our large
objects externally (as files in the local file system) and
implementing a mechanism similar to Oracle's bfile functionality.  Of
course, we can't afford to give up the transactional security of being
able to roll back if a particular update doesn't succeed.

Does anyone have any suggestions to offer on good ways to proceed
given our constraints?  Thanks in advance for any help you can
provide.

        -jan-
--=20
Jan L. Peterson
<jan.l.peterson@gmail.com>