From: Chris Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: REINDEX DATABASE
Date: Wed, 27 Jul 2005 11:31:00 -0400
Message-ID: <608xzs1d1n.fsf@dba2.int.libertyrms.com> (raw)
References: <60pst51ocd.fsf@dba2.int.libertyrms.com>
<Pine.LNX.4.44.0507270856070.32187-100000@matrix.gatewaynet.com>
achill@matrix.gatewaynet.com (Achilleus Mantzios) writes:
> O Chris Browne Ýãñáøå óôéò Jul 26, 2005 :
>> You can get a savings of about 4% of the space, but at the cost of
>> taking an appreciable outage during which the database is not usable.
>
> 1st not all database is unusable, during the whole reindexdb run,
> 2nd outage outside office hours is acceptable for those who apply.
I'm not willing to assume that.
I help support some applications where "outages outside office hours"
are acceptable; I help support some other applications for which
"office hours" are 24 hours per day, 7 days per week, and such an
outage would be deemed decidedly unacceptable.
>> I wouldn't expect the 4% savings in space to lead to a particularly
>> measurable improvement in performance, certainly not one worth the
>> outage.
> I am just saying that the common saying "reindex is not needed for
> 7.4+" maybe is not true in all circumstances.
Let me quote the release notes for 7.4.1:
"Make free space map efficiently reuse empty index pages, and other
free space management improvements
In previous releases, B-tree index pages that were left empty
because of deleted rows could only be reused by rows with index
values similar to the rows originally indexed on that page. In
7.4, VACUUM records empty index pages and allows them to be reused
for any future index rows."
<http://www.postgresql.at/pg/pgsql/doc/html/release-7-4.html;
In versions earlier than 7.4, running a REINDEX periodically was
*essential* if you had update patterns consistent with the (remarkably
common) scenario described above.
This reason to reindex (which was the main reason we required
reindexing when using 7.2) has been resolved and gone away in 7.4.
There may be other factors that could mandate REINDEX; as far as I can
tell, the main such factor that remains would be where a table sees
enormous numbers of updates but is not VACUUMed often enough.
_That_ scenario isn't consistent with what you describe, as it would
be expected to involve a whole lot more than 4% growth in the size of
the database.
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/;
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-sql@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: REINDEX DATABASE
In-Reply-To: <608xzs1d1n.fsf@dba2.int.libertyrms.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