Re: db grows and grows

Andrew Sullivan <andrew(at)libertyrms(dot)info> writes:
> On Wed, Jun 19, 2002 at 09:09:32PM +0200, Bjoern Metzdorf wrote:
>> During the REINDEX-run I see plenty "SELECT (waiting)" with ps. So I can
>> confirm this.

> But why would a ShareLock cause SELECT to wait?

He was doing REINDEX, which grabs an exclusive lock.

The reason CREATE INDEX can run concurrently with SELECTs is that the
SELECTs won't see (and perhaps try to use) the new index, because its
catalog entries aren't committed yet. REINDEX needs an exclusive lock
to prevent other transactions from trying to use the
index-under-reconstruction, since they certainly would see it as
an available index.

While one can think of hacks that might be able to work around that,
it's not clear to me that we should expend development time on making
REINDEX slicker --- fixing the underlying space-management problem in
btree indexes would be a better expenditure of time IMHO. REINDEX
is really intended for disaster recovery, not routine space management.

regards, tom lane