From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: "VACUUM FULL ANALYZE" vs. Autovacuum Contention
Date: Fri, 08 Jul 2011 13:12:46 -0400
Message-ID: <4E173A8E.80900@2ndQuadrant.com> (raw)
In-Reply-To: <CAJtzaR9PS_rb04MxSy=Zp9wYYwoEDBCC9V=+ySScMXihUZPmwQ@mail.gmail.com>
References: <CAJtzaR8azbzwnu=X=v6qd2WBBBY0cEofZbnNYePxm5ROZ76LmA@mail.gmail.com>
<4E16258A.4050007@2ndQuadrant.com>
<CAJtzaR9PS_rb04MxSy=Zp9wYYwoEDBCC9V=+ySScMXihUZPmwQ@mail.gmail.com>
On 07/08/2011 12:46 PM, D C wrote:
> That said, it sounds like if we switched to daily "trucates" of each
> table (they can be purged entirely each day) rather than "delete
> froms", then there truly would not be any reason to use "vacuum
> full". Does that sound plausible?
That's exactly right. If you can re-arrange this data to be truncated
instead of deleted, this entire problem should go away. There is also a
nice optimization you should know about; if you do this:
BEGIN;
TRUNCATE t;
COPY t FROM ...
COMMIT;
In single-node systems (no standby slave), this can work much faster
than a normal load. It's able to skip the pg_xlog WAL writes in this
situation.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
Comprehensive and Customized PostgreSQL Training Classes:
http://www.2ndquadrant.us/postgresql-training/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndQuadrant.com
Subject: Re: "VACUUM FULL ANALYZE" vs. Autovacuum Contention
In-Reply-To: <4E173A8E.80900@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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