From: Jim Nasby <decibel@decibel.org>
To: Bruce Momjian <bruce@momjian.us>
Cc: Csaba Nagy <nagy@ecircle-ag.com>
Cc: Jeremy Haile <jhaile@fastmail.fm>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ed L. <pgsql@bluepolka.net>
Cc: Postgres general mailing list <pgsql-general@postgresql.org>
Cc: Bill Moran <wmoran@collaborativefusion.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Subject: Re: Index bloat of 4x
Date: Thu, 1 Feb 2007 22:38:07 -0500
Message-ID: <93FBF786-18A2-4BCC-AB5C-118B5451663F@decibel.org> (raw)
In-Reply-To: <200701310318.l0V3IWU08970@momjian.us>
References: <200701310318.l0V3IWU08970@momjian.us>
Is there no way to change the index code to allow for moving index
tuples from one page to another? If we could do that then presumably
we could free up substantially more pages.
On Jan 30, 2007, at 10:18 PM, Bruce Momjian wrote:
>
> Added to TODO:
>
> * Add REINDEX CONCURRENTLY, like CREATE INDEX CONCURRENTLY
>
> This is difficult because you must upgrade to an exclusive table
> lock
> to replace the existing index file. CREATE INDEX CONCURRENTLY
> does not
> have this complication. This would allow index compaction without
> downtime.
>
> I understand the problems, but the need for this seems pretty clear.
>
> ----------------------------------------------------------------------
> -----
>
> Csaba Nagy wrote:
>> On Fri, 2007-01-19 at 20:03, Jeremy Haile wrote:
>>> Is it feasible to add a "reindex concurrently" that doesn't lock the
>>> table for the rebuild, then locks the table when doing a second
>>> pass to
>>> pickup rows that were changed after the first pass? Or something
>>> like
>>> that....
>>
>> IIRC, the objection was the deadlock potential of any lock
>> upgrade, and
>> the problems of impossible cleanup on failure if something changed
>> the
>> permissions of the executing user in the meantime. That's why I
>> think it
>> would make sense if it could be done by a privileged background
>> thread
>> like the autovacuum ones, so the lock upgrade can be tried without
>> blocking, as it can take quite some time till it succeeds, and the
>> cleanup is possible due to the privileged nature of the executor.
>>
>> If there would be such a facility it would also need some policies to
>> control time windows and priorities just as for autovacuum, that's
>> why I
>> connect it in my usage-focused mind to autovacuum.
>>
>> Cheers,
>> Csaba.
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>
> --
> Bruce Momjian bruce@momjian.us
> EnterpriseDB http://www.enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
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-general@postgresql.org
Cc: decibel@decibel.org, bruce@momjian.us, nagy@ecircle-ag.com, jhaile@fastmail.fm, tgl@sss.pgh.pa.us, pgsql@bluepolka.net, wmoran@collaborativefusion.com, alvherre@commandprompt.com
Subject: Re: Index bloat of 4x
In-Reply-To: <93FBF786-18A2-4BCC-AB5C-118B5451663F@decibel.org>
* 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