Re: BUG #5946: Long exclusive lock taken by vacuum (not full)

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Christopher Browne <cbbrowne(at)gmail(dot)com>, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com>, pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #5946: Long exclusive lock taken by vacuum (not full)
Date: 2011-03-25 19:26:33
Message-ID: 1301080686-sup-7539@alvh.no-ip.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Excerpts from Tom Lane's message of vie mar 25 13:10:21 -0300 2011:
> Christopher Browne <cbbrowne(at)gmail(dot)com> writes:

> > If there's almost always something that wants to access the table,
> > which would be hardly surprising, in view that the table is being
> > updated with sufficient regularity that it's got 10GB+ of dead space
> > at the end, then you're eventually going to *HAVE* to vacuum it.
> > Transaction rollover, and what not.
>
> It is already vacuumed. The only remaining step is to release free
> space back to the OS; and if he's got a lot of churn in the table,
> it's not entirely clear that that's a productive activity anyway.

Right: future new tuples could need to extend the table, which would
turn out not to be necessary because these pages at the end of the table
are there and registered on the FSM. Truncating the table could turn
out to be conterproductive because you would allocate a new block
shortly, causing FS fragmentation.

What happened to the idea of preallocating tables to a size defined by
the user at CREATE TABLE time, and extending it in chunks instead of a
block at a time, with the idea of reducing fragmentation? Was it
rejected, or just not implemented?

The other possibly useful idea was to let this truncate step to be run
as a separate activity, without having to complete a vacuum cycle on the
table.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2011-03-25 19:43:58 Re: BUG #5946: Long exclusive lock taken by vacuum (not full)
Previous Message Devrim GÜNDÜZ 2011-03-25 17:04:08 Re: checking on bug #5917