Re: pg_reorg in core?

From: Andres Freund <andres(at)2ndquadrant(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>
Subject: Re: pg_reorg in core?
Date: 2012-09-24 23:13:15
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Tuesday, September 25, 2012 12:55:35 AM Josh Berkus wrote:
> On 9/24/12 3:43 PM, Simon Riggs wrote:
> > On 24 September 2012 17:36, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> >>>> For me, the Postgres user interface should include
> >>
> >> I don't see why we don't have REINDEX CONCURRENTLY now.
> >
> > Same reason for everything on (anyone's) TODO list.
> Yes, I'm just pointing out that it would be a very small patch for
> someone, and that AFAIK it didn't make it on the TODO list yet.
Its not *that* small.

1. You need more than you can do with CREATE INDEX CONCURRENTLY and DROP INDEX
CONCURRENTLY because the index can e.g. be referenced by a foreign key
constraint. So you need to replace the existing index oid with a new one by
swapping the relfilenodes of both after verifying several side conditions
(indcheckxmin, indisvalid, indisready).

It would probably have to look like:

- build new index with indisready = false
- newindex.indisready = true
- wait
- newindex.indisvalid = true
- wait
- swap(oldindex.relfilenode, newindex.relfilenode)
- oldindex.indisvalid = false
- wait
- oldindex.indisready = false
- wait
- drop new index with old relfilenode

Every wait indicates an externally visible state which you might encounter/need
to cleanup...

To make it viable to use that systemwide it might be necessary to batch the
individual steps together for multiple indexes because all that waiting is
going to suck if you do it for every single table in the database while you
also have longrunning queries...

2. no support for concurrent on system tables (not easy for shared catalogs)

3. no support for the indexes of exlusion constraints (not hard I think)


Andres Freund
PostgreSQL Development, 24x7 Support, Training & Services

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2012-09-24 23:46:18 Re: DROP INDEX CONCURRENTLY is not really concurrency safe & leaves around undroppable indexes
Previous Message Andrew Dunstan 2012-09-24 22:59:12 Oid registry