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@xxxxxxxxxx
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@xxxxxxxxx
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
