Re: Same-page UPDATEs in bloated tables

Ian Dowse wrote:

> I've been seeing an issue with 8.1.4 that seems to be caused by the
> way UPDATE operations prefer to place the new row version in the
> same page as the original row. The issue is specific to UPDATEs;
> it does not occur when each UPDATE is replaced by a DELETE/INSERT
> pair. The problem can prevent a temporarily bloated table from ever
> returning to its normal size even though all rows are frequently
> changing and regular vacuuming is taking place.
>
> A simple way to demonstrate the issue is to insert 10001 rows into
> an empty table and delete the first 10000 rows. Now, repeatedly
> performing (lazy) vacuums and UPDATEs will never result in the table
> size shrinking:

Yeah. This scenario is one of those for which "popular knowledge"
("common wisdom"? "Postgres folklore"?) tells you to do a VACUUM FULL or
equivalent (e.g. CLUSTER).

Using the same page for an updated tuple is generally a useful
optimization, so I don't think you have much hopes for having it
disabled. The INSERT+DELETE equivalent doesn't have the opportunity to
use that optimization though, which is why it has to go to the FSM and
thus get a different page to do the INSERT on.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support