Feedback on getting rid of VACUUM FULL

Josh Berkus <josh@agliodbs.com> wrote:

a) To date, I have yet to hear a single person bring up an actual
real-life use-case where VACUUM FULL was desireable and REWRITE
would not be.

Would rewrite have handled this?:

http://archives.postgresql.org/pgsql-hackers/2009-09/msg01016.php

-Kevin

Josh Berkus wrote:

Hackers,

Here's the feedback on replacing VACUUM FULL with VACUUM REWRITE:
http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959

Of note:

a) To date, I have yet to hear a single person bring up an actual
real-life use-case where VACUUM FULL was desireable and REWRITE would
not be. Lots of people have said something hypothetical, but nobody has
come forward with a "I have this database X and several times Y
happened, and only FULL would work ...". This makes me think that there
very likey are no actual use cases where we need to preserve FULL.

Well, Andrew McNamara just posted today:
http://archives.postgresql.org/message-id/20090916063341.0735C5AC0D6@longblack.object-craft.com.au

Had VACUUM FULL not been available, though, I'm pretty sure he would've
come up with something else instead.

c) Vivek had some points about required implementation:

"However, there still must be a way to compact the tables that is mvcc
safe. From what I have read and recall, cluster is not. Thus, the vacuum
rewrite would be a mandatory feature (or cluster could be made mvcc safe)."

Is Vivek correct about this? News to me ...

No, that was fixed in 8.3.

I was just going to post that we should make a decision about this,
because ISTM there's some code in Simon's hot standby patch that is only
required to support VACUUM FULL. If we make the decision that we drop
VACUUM FULL in 8.5, we can take that part out of the patch now. It's not
a huge amount of code, but still.

I'm in favor of removing VACUUM FULL in 8.5. To replace it, we should offer:

1) VACUUM REWRITE, which is like CLUSTER but doesn't use an index, and
2) Another utility that does something like UPDATE ... WHERE ctid > ? to
move tuples to lower pages. It will be different from current VACUUM
FULL in some ways. It won't require a table lock, for example, but it
won't be able to move update chains as nicely. But it would be trivial
to write one, so I think we should offer that as a contrib module.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Wed, 2009-09-16 at 11:10 -0700, Josh Berkus wrote:

Hackers,

Here's the feedback on replacing VACUUM FULL with VACUUM REWRITE:
http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959

Of note:

a) To date, I have yet to hear a single person bring up an actual
real-life use-case where VACUUM FULL was desireable and REWRITE would
not be.

The only case is when you are out of disk space and can't afford to
write out a full set of live rows.

What I'd like to propose is VACUUM FULL CONCURRENTLY, which similar to
VACUUM CONCURRENTLY, would actually do the compaction phase, that is,
move simultaneously from two directions, from start, to find empty space
and from end to find tuples. for each sufficiently large empty space the
forward scan finds it would take one or more tuples from the reverse
scan and then "null update" those to the empty space found by the
free-space-scan beginning. it should do that in small chunks, say one
page at a time, so it will minimally interfere with OLTP loads.

Once these two scans meet, you can stop and either run an non full
vacuum, or just continue in similar fashion to non-full vacuum and do
the cleanups of indexes and heap.

You may need to repeat this a few times to get actual shrinkage but it
has the very real advantage of being usable on 24/7 systems, which
neither VACUUM FULL nor CLUSTER possess.

At some point I actually had external scripts doing similar stuff for
on-line table shrinking, the only difference being that I could not move
the tuple towards beginning right away (pg preferred in-page updates)
and had to keep doing null updates (id=id where id) until the page
number in ctid changed.

Lots of people have said something hypothetical, but nobody has
come forward with a "I have this database X and several times Y
happened, and only FULL would work ...". This makes me think that there
very likey are no actual use cases where we need to preserve FULL.

b) Several people have strongly pushed for a phased removal of FULL over
more than one PG version, with a warning message about depreciation.

c) Vivek had some points about required implementation:

"However, there still must be a way to compact the tables that is mvcc
safe. From what I have read and recall, cluster is not. Thus, the vacuum
rewrite would be a mandatory feature (or cluster could be made mvcc safe)."

Is Vivek correct about this? News to me ...

It used to be true at some point, probably not true any more.

IIRC, the problem was, that old table was not locked during rewrite and
thus some code could be updating the old heap even while the data had
been muved to the new one.

On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:

I was just going to post that we should make a decision about this,
because ISTM there's some code in Simon's hot standby patch that is only
required to support VACUUM FULL. If we make the decision that we drop
VACUUM FULL in 8.5, we can take that part out of the patch now. It's not
a huge amount of code, but still.

I'm in favor of removing VACUUM FULL in 8.5. To replace it, we should offer:

1) VACUUM REWRITE, which is like CLUSTER but doesn't use an index, and
2) Another utility that does something like UPDATE ... WHERE ctid > ? to
move tuples to lower pages. It will be different from current VACUUM
FULL in some ways. It won't require a table lock, for example, but it
won't be able to move update chains as nicely. But it would be trivial
to write one, so I think we should offer that as a contrib module.

I have not checked, but I suspect pg_reorg may already be doing
something similar http://pgfoundry.org/forum/forum.php?forum_id=1561

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:

2) Another utility that does something like UPDATE ... WHERE ctid > ? to
move tuples to lower pages. It will be different from current VACUUM
FULL in some ways. It won't require a table lock, for example, but it
won't be able to move update chains as nicely. But it would be trivial
to write one, so I think we should offer that as a contrib module.

An advantage here is that it would allow people to do a "partial vacuum
full" to gradually move tuples from the end of the relation to the
beginning. That would allow vacuums in between the updates to free the
index tuples, preventing index bloat.

Another thing to think about is that lazy vacuum only shrinks the heap
file if it happens to be able to acquire an access exclusive lock.
Because vacuum can't be run inside a transaction block, I don't think
there's currently a way to ensure that the heap file actually gets
shrunk. How about we provide some way to make it acquire an access
exclusive lock at the beginning, but still perform a lazy vacuum?

Regards,
Jeff Davis

On Wed, 2009-09-16 at 11:10 -0700, Josh Berkus wrote:

Hackers,

Here's the feedback on replacing VACUUM FULL with VACUUM REWRITE:
http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959

Of note:

a) To date, I have yet to hear a single person bring up an actual
real-life use-case where VACUUM FULL was desireable and REWRITE would
not be.

The only case is when you are out of disk space and can't afford to
write out a full set of live rows.

What I'd like to propose is VACUUM FULL CONCURRENTLY, which similar to
VACUUM CONCURRENTLY, would actually do the compaction phase, that is,
move simultaneously from two directions, from start, to find empty space
and from end to find tuples. for each sufficiently large empty space the
forward scan finds it would take one or more tuples from the reverse
scan and then "null update" those to the empty space found by the
free-space-scan beginning. it should do that in small chunks, say one
page at a time, so it will minimally interfere with OLTP loads.

Once these two scans meet, you can stop and either run an non full
vacuum, or just continue in similar fashion to non-full vacuum and do
the cleanups of indexes and heap.

You may need to repeat this a few times to get actual shrinkage but it
has the very real advantage of being usable on 24/7 systems, which
neither VACUUM FULL nor CLUSTER possess.

At some point I actually had external scripts doing similar stuff for
on-line table shrinking, the only difference being that I could not move
the tuple towards beginning right away (pg preferred in-page updates)
and had to keep doing null updates (id=id where id) until the page
number in ctid changed.

Lots of people have said something hypothetical, but nobody has
come forward with a "I have this database X and several times Y
happened, and only FULL would work ...". This makes me think that there
very likey are no actual use cases where we need to preserve FULL.

b) Several people have strongly pushed for a phased removal of FULL over
more than one PG version, with a warning message about depreciation.

c) Vivek had some points about required implementation:

"However, there still must be a way to compact the tables that is mvcc
safe. From what I have read and recall, cluster is not. Thus, the vacuum
rewrite would be a mandatory feature (or cluster could be made mvcc safe)."

Is Vivek correct about this? News to me ...

It used to be true at some point, probably not true any more.

IIRC, the problem was, that old table was not locked during rewrite and
thus some code could be updating the old heap even while the data had
been muved to the new one.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

On 9/16/09 11:20 AM, Kevin Grittner wrote:

Josh Berkus <josh@agliodbs.com> wrote:

a) To date, I have yet to hear a single person bring up an actual
real-life use-case where VACUUM FULL was desireable and REWRITE
would not be.

Would rewrite have handled this?:

http://archives.postgresql.org/pgsql-hackers/2009-09/msg01016.php

Ok, that sounds like a real use case.

However, given Heikki's post about FULL being an issue for Hot Standby,
I'm more inclined to provide a workaround ... for example, allowing
REWRITE to write to a designated tablespace, which would allow people to
use a portable drive or similar for the extra disk space.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

Hannu,

The only case is when you are out of disk space and can't afford to
write out a full set of live rows.

Well, it's actually rather specific. You need to have:

a) *Some* free disk space (FULL requires extra disk) but not enough to
copy one entire table and its indexes.

b) be already down or willing to accept the long downtime which comes
with FULL more than you're willing to go out and get some extra disk or
move your database to a new share.

There's no question that this combination is fairly circumstantial and
represents a minority of potential vacuum cases. Unfortunately, it does
seem to represent some real-life ones, so we have to take those into
account.

What I'd like to propose is VACUUM FULL CONCURRENTLY, which similar to
VACUUM CONCURRENTLY, would actually do the compaction phase, that is,
move simultaneously from two directions, from start, to find empty space
and from end to find tuples. for each sufficiently large empty space the
forward scan finds it would take one or more tuples from the reverse
scan and then "null update" those to the empty space found by the
free-space-scan beginning. it should do that in small chunks, say one
page at a time, so it will minimally interfere with OLTP loads.

How would this work with HS?

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

On Wed, 2009-09-16 at 13:20 -0500, Kevin Grittner wrote:

Josh Berkus <josh@agliodbs.com> wrote:

a) To date, I have yet to hear a single person bring up an actual
real-life use-case where VACUUM FULL was desireable and REWRITE
would not be.

Would rewrite have handled this?:

http://archives.postgresql.org/pgsql-hackers/2009-09/msg01016.php

If REWRITE is just a CLUSTER using seqscan, then no

If it is a sequence of

1. ordinary VACUUM (it can't run out of FSM anymore, no?)
2. a process moving live tuples from end (using reverse seqscan) to free
space found scanning in first-to-last direction, either one tuple at a
time or one page at a time, until the two scans meet
3. another ordinary VACUUM to actually reclaim the free space

4. repeat a few times so that tuples at the end of relation (for
whatever reason) added while doing 1-3 are also moved towards beginning

then yes, it would have taken some time, but it would have definitely
helped

It would still have caused index bloat, so to get full benefit of it,
one should have finished it up with an equivalent of CONCURRENT REINDEX.

--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

On Wed, 2009-09-16 at 11:48 -0700, Josh Berkus wrote:

Hannu,

The only case is when you are out of disk space and can't afford to
write out a full set of live rows.

Well, it's actually rather specific. You need to have:

a) *Some* free disk space (FULL requires extra disk) but not enough to
copy one entire table and its indexes.

b) be already down or willing to accept the long downtime which comes
with FULL more than you're willing to go out and get some extra disk or
move your database to a new share.

There's no question that this combination is fairly circumstantial and
represents a minority of potential vacuum cases. Unfortunately, it does
seem to represent some real-life ones, so we have to take those into
account.

Agreed.

What I'd like to propose is VACUUM FULL CONCURRENTLY, which similar to
VACUUM CONCURRENTLY, would actually do the compaction phase, that is,
move simultaneously from two directions, from start, to find empty space
and from end to find tuples. for each sufficiently large empty space the
forward scan finds it would take one or more tuples from the reverse
scan and then "null update" those to the empty space found by the
free-space-scan beginning. it should do that in small chunks, say one
page at a time, so it will minimally interfere with OLTP loads.

How would this work with HS?

Exactly the same as just doing a lot of UPDATE's which move tuples
around between pages.

It actually _is_ a lots of updates, just with extra condition that tuple
is always moved to lowest available free slot.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

Hannu,

If it is a sequence of

1. ordinary VACUUM (it can't run out of FSM anymore, no?)
2. a process moving live tuples from end (using reverse seqscan) to free
space found scanning in first-to-last direction, either one tuple at a
time or one page at a time, until the two scans meet
3. another ordinary VACUUM to actually reclaim the free space

4. repeat a few times so that tuples at the end of relation (for
whatever reason) added while doing 1-3 are also moved towards beginning

Sounds good, you want to code it for 8.5?

I could actually see two tools, one VACUUM FULL CONCURRENTLY and one
VACUUM REWRITE. The first would be "in place" and the second would be
"fast". Both should work better with HS than current VF does.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

Hannu Krosing wrote:

On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:

2) Another utility that does something like UPDATE ... WHERE ctid > ? to
move tuples to lower pages. It will be different from current VACUUM
FULL in some ways. It won't require a table lock, for example, but it
won't be able to move update chains as nicely. But it would be trivial
to write one, so I think we should offer that as a contrib module.

I have not checked, but I suspect pg_reorg may already be doing
something similar http://pgfoundry.org/forum/forum.php?forum_id=1561

Hmm, AFAICT pg_reorg is much more complex, writing stuff to a temp table
and swapping relfilenodes afterwards. More like the VACUUM REWRITE
that's been discussed.

For the kicks, I looked at what it would take to write a utility like
that. It turns out to be quite trivial, patch attached. It uses the same
principle as VACUUM FULL, scans from the end, moving tuples to
lower-numbered pages until it can't do it anymore. It requires a small
change to heap_update(), to override the preference to store the new
tuple on the same page as the old one, but other than that, it's all in
the external module.

To test:

-- Create and populate test table
CREATE TABLE foo (id int4 PRIMARY KEY);
INSERT INTO foo SELECT a FROM generate_series(1,100000) a;

-- Delete a lot of tuples from the beginning. This creates the hole that
we want to compact out.
DELETE FROM foo WHERE id < 90000;

-- Vacuum to remove the dead tuples
VACUUM VERBOSE foo;

-- Run the utility to "move" the tuples
SELECT vacuumfull('foo');

-- Vacuum table again to remove the old tuple versions of the moved rows
and truncate the file.

VACUUM VERBOSE foo;

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Wed, 2009-09-16 at 23:53 +0300, Heikki Linnakangas wrote:

Hannu Krosing wrote:

On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:

2) Another utility that does something like UPDATE ... WHERE ctid > ? to
move tuples to lower pages. It will be different from current VACUUM
FULL in some ways. It won't require a table lock, for example, but it
won't be able to move update chains as nicely. But it would be trivial
to write one, so I think we should offer that as a contrib module.

I have not checked, but I suspect pg_reorg may already be doing
something similar http://pgfoundry.org/forum/forum.php?forum_id=1561

Hmm, AFAICT pg_reorg is much more complex, writing stuff to a temp table
and swapping relfilenodes afterwards. More like the VACUUM REWRITE
that's been discussed.

For the kicks, I looked at what it would take to write a utility like
that. It turns out to be quite trivial, patch attached. It uses the same
principle as VACUUM FULL, scans from the end, moving tuples to
lower-numbered pages until it can't do it anymore. It requires a small
change to heap_update(), to override the preference to store the new
tuple on the same page as the old one, but other than that, it's all in
the external module.

Exactly as I hoped :D

One thing that would be harder to do, and which CLUSTER currently does
is introducing empty space within pages, based on fillfactor.

Doing that would need a similar, though reversed strategy. But it is
probably not something that is often needed, as a an update on page with
no free space would eventually do almost the same.

To test:

-- Create and populate test table
CREATE TABLE foo (id int4 PRIMARY KEY);
INSERT INTO foo SELECT a FROM generate_series(1,100000) a;

-- Delete a lot of tuples from the beginning. This creates the hole that
we want to compact out.
DELETE FROM foo WHERE id < 90000;

-- Vacuum to remove the dead tuples
VACUUM VERBOSE foo;

-- Run the utility to "move" the tuples
SELECT vacuumfull('foo');

-- Vacuum table again to remove the old tuple versions of the moved rows
and truncate the file.

VACUUM VERBOSE foo;

Now, if you could just make vacuumfull('foo'); run in multiple
transactions (say one per N tuples moved, or even per N seconds spent)
to make it friendlier for OLTP workloads, which then dont have to wait
for the whole thing to finish in order to proceed with update of a moved
tuple (and also to deal with deadloks from trying to move an updated
tuple) then I'd claim we have a much better VACUUM FULL :)

--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

On Wed, 2009-09-16 at 23:53 +0300, Heikki Linnakangas wrote:

For the kicks, I looked at what it would take to write a utility like
that. It turns out to be quite trivial, patch attached. It uses the same
principle as VACUUM FULL, scans from the end, moving tuples to
lower-numbered pages until it can't do it anymore. It requires a small
change to heap_update(), to override the preference to store the new
tuple on the same page as the old one, but other than that, it's all in
the external module.

It fails at initdb time for me:
FATAL: unrecognized heap_update status: 5
STATEMENT: REVOKE ALL on pg_authid FROM public;

Regards,
Jeff Davis

On Wed, Sep 16, 2009 at 4:53 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:

Hannu Krosing wrote:

On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:

2) Another utility that does something like UPDATE ... WHERE ctid > ? to
move tuples to lower pages. It will be different from current VACUUM
FULL in some ways. It won't require a table lock, for example, but it
won't be able to move update chains as nicely. But it would be trivial
to write one, so I think we should offer that as a contrib module.

I have not checked, but I suspect pg_reorg may already be doing
something similar http://pgfoundry.org/forum/forum.php?forum_id=1561

Hmm, AFAICT pg_reorg is much more complex, writing stuff to a temp table
and swapping relfilenodes afterwards. More like the VACUUM REWRITE
that's been discussed.

For the kicks, I looked at what it would take to write a utility like
that. It turns out to be quite trivial, patch attached. It uses the same
principle as VACUUM FULL, scans from the end, moving tuples to
lower-numbered pages until it can't do it anymore. It requires a small
change to heap_update(), to override the preference to store the new
tuple on the same page as the old one, but other than that, it's all in
the external module.

To test:

-- Create and populate test table
CREATE TABLE foo (id int4 PRIMARY KEY);
INSERT INTO foo SELECT a FROM generate_series(1,100000) a;

-- Delete a lot of tuples from the beginning. This creates the hole that
we want to compact out.
DELETE FROM foo WHERE id < 90000;

-- Vacuum to remove the dead tuples
VACUUM VERBOSE foo;

-- Run the utility to "move" the tuples
SELECT vacuumfull('foo');

-- Vacuum table again to remove the old tuple versions of the moved rows
and truncate the file.

VACUUM VERBOSE foo;

I think this should be in core, not a contrib module.

I also wonder whether we should consider teaching regular VACUUM to do
a little of this every time it's run. Right now, once your table gets
bloated, it stays bloated forever, until you intervene. Making it
slowly get better by itself would reduce the number of people who live
with the problem for a month or a year before writing in to say
"Access to this table seems really slow...".

...Robert

On Wed, Sep 16, 2009 at 1:42 PM, Josh Berkus <josh@agliodbs.com> wrote:

On 9/16/09 11:20 AM, Kevin Grittner wrote:

Josh Berkus <josh@agliodbs.com> wrote:

a) To date, I have yet to hear a single person bring up an actual
real-life use-case where VACUUM FULL was desireable and REWRITE
would not be.

Would rewrite have handled this?:

http://archives.postgresql.org/pgsql-hackers/2009-09/msg01016.php

Ok, that sounds like a real use case.

However, given Heikki's post about FULL being an issue for Hot Standby,
I'm more inclined to provide a workaround ... for example, allowing
REWRITE to write to a designated tablespace, which would allow people to
use a portable drive or similar for the extra disk space.

if you have a portable drive at hand you can create a tablespace in
that dirve, move the table to that tablespace, return to the old
tablespace and drop the new tblspc... ok, one command for all that
could be handy but not a need...

the real problem is when you *don't* have more space... i have been
recently in that situation and vaccum full was a life saver but the
only reason that server came to that situation was a horribly fsm
configuration and a bad design that forces an incredible amount of
updates...

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Robert Haas <robertmhaas@gmail.com> wrote:

I think this should be in core, not a contrib module.

+1

I also wonder whether we should consider teaching regular VACUUM to
do a little of this every time it's run. Right now, once your table
gets bloated, it stays bloated forever, until you intervene. Making
it slowly get better by itself would reduce the number of people who
live with the problem for a month or a year before writing in to say
"Access to this table seems really slow...".

+1 if feasible. That would be a very nice feature.

-Kevin

Robert Haas wrote:

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:

Hannu Krosing wrote:

On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:

2) Another utility that does something like UPDATE ... WHERE ctid > ? to

I also wonder whether we should consider teaching regular VACUUM to do
a little of this every time it's run. Right now, once your table gets

Having it be built into VACUUM would surprise me a bit, but I wonder
if autovacuum could detect when such a tuple-mover would be useful,
and run one before it does a VACUUM if needed.

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:

For the kicks, I looked at what it would take to write a utility like
that. It turns out to be quite trivial, patch attached.

I don't think you've really thought this through; particularly not this:

+ rel = heap_open(relid, AccessShareLock);

You can NOT modify a relation with only AccessShareLock, and frankly
I doubt you should be doing this with less than exclusive lock. Which
would make the thing quite unpleasant to use in practice.

regards, tom lane