Skip site navigation (1) Skip section navigation (2)

Re: A bunch of minor issues

From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: Dave Page <dpage(at)postgresql(dot)org>
Cc: pgadmin-hackers(at)postgresql(dot)org, Guillaume Lelarge <guillaume(at)lelarge(dot)info>
Subject: Re: A bunch of minor issues
Date: 2007-09-26 13:16:40
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgadmin-hackers
Dave Page wrote:
> REINDEX is similar to a drop and recreate of the index in that the index
> contents are rebuilt from scratch. However, the locking considerations
> are rather different. REINDEX locks out writes but not reads of the
> index's parent table. It also takes an exclusive lock on the specific
> index being processed, which will block reads that attempt to use that
> index. In contrast, DROP INDEX momentarily takes exclusive lock on the
> parent table, blocking both writes and reads. The subsequent CREATE
> INDEX locks out writes but not reads; since the index is not there, no
> read will attempt to use it, meaning that there will be no blocking but
> reads may be forced into expensive sequential scans. Another important
> point is that the drop/create approach invalidates any cached query
> plans that use the index, while REINDEX does not.

So the advantage is that drop+create will allow all reads to run
concurrently, though they might have to use sequential scans. Hmm, I
wonder if a CREATE+DROP+rename would be even better. Could use
CONCURRENT-mode in the create as well to allow concurrent writes...

I know I know, no new features at this point :).

  Heikki Linnakangas

In response to


pgadmin-hackers by date

Next:From: Dave PageDate: 2007-09-26 13:21:18
Subject: Re: A bunch of minor issues
Previous:From: Guillaume LelargeDate: 2007-09-26 12:46:56
Subject: Re: A bunch of minor issues

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group