To clarify that bit at the end, HOT helps reduce t...

2010-09-12T00:56:12.198-04:00

To clarify that bit at the end, HOT helps reduce the scope of the problems with this trick, by avoiding bloat during some of the many updates here, but it can't completely eliminate the index bloat concerns. Referring to what you wrote for a second about when HOT kicks in: "...there's sufficient free space available for the tuple to stay on the same page". If the tuple stays on the same page, you didn't accomplish anything useful yet: the tuple will still be on the almost empty page you were trying to clean out. So eventually, to do what you want here, you must create a situation where HOT doesn't help.

Also, while this is a neat userland version of what had been only proposed as server side code before, your title is overpromising just a bit here. In order for the database to do the "truncated x to y pages" trick, it still has to acquire an exclusive lock on the table, as pointed out in the "Recovering Disk Space section of the documentation (and in vacuumlazy.c where this message spits out from). So this technique doesn't eliminate the need for such a lock; it just reduces the amount of time said lock has to be active for. VACUUM FULL holds an exclusive lock the whole time you're running it. Using this approach, you only need it for the shorter period when the "lazy" regular VACUUM is running at the end. To reduce the size of a relation, you must at some point have at least a brief exclusive lock on it, can't be avoided.

Yeah, I didn't bring up that discussion at lea...

2010-09-10T14:48:10.760-04:00

Yeah, I didn't bring up that discussion at least in part because it was already a long post. I'd really like something like that to exist, especially if it can be told to run through only a portion of the available bloat. In at least some cases, full table locking would be fine if it was only for a short time, as opposed to the full time required to consolidate rows, or rewrite an entire table and all its indexes.

When it was decided to change the way VACUUM FULL ...

2010-09-10T03:31:25.214-04:00

When it was decided to change the way VACUUM FULL works, there was this idea of providing a contrib module that does what you're describing. See:
thread on pgsql-hackers

Comments on End Point Blog: Reducing bloat without locking

To clarify that bit at the end, HOT helps reduce t...

Yeah, I didn't bring up that discussion at lea...

When it was decided to change the way VACUUM FULL ...