From: Tom Lane <tgl@sss.pgh.pa.us>
To: James Cloos <cloos@jhcloos.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: When does VACUUM FULL not clean out all deleted data?
Date: Fri, 04 Jan 2008 01:03:27 -0500
Message-ID: <10656.1199426607@sss.pgh.pa.us> (raw)
In-Reply-To: <m3lk76du4c.fsf@lugabout.jhcloos.org>
References: <m3lk76du4c.fsf@lugabout.jhcloos.org>
James Cloos <cloos@jhcloos.com> writes:
> Right after the restore the db took up less than ten percent as much
> space as the backup. (For the backup I stopped pg and used rsync on
> the /var/lib/postgresql/data directory.)
> Why was the db using that extra five plus gigs?
Smells like a hadn't-been-vacuuming-often-enough problem.
Please note that vacuum-full-once-in-a-long-while is *not* a substitute
for frequent-plain-vacuum, because VACUUM FULL isn't good at reducing
index space usage --- in fact it usually makes it worse. Your numbers
didn't separate table vs index bloat, but I'm suspicious that a lot of
your problem was the latter.
regards, tom lane
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, cloos@jhcloos.com
Subject: Re: When does VACUUM FULL not clean out all deleted data?
In-Reply-To: <10656.1199426607@sss.pgh.pa.us>
* 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