On 15 Sep 2004, R. Willmington wrote:
> In order to reproduce the problem, i copied the above database to my
> staging system (same linux, same postgres), and ran a vacuum full
> analyze. It finished after 33 minutes. After the vacuum full the
> deletion from the 20.000 records table succeeded within 23 ms.
you should have given the verbose output from the vacuum full. you
could have seen how many dead row versions it had removed. perhaps
there were enough versions to justify 33 minutes of whatever it was
doing.
> Now, here's the problem:
> When running the VACUUM FULL on the live system, it finished after 20
> Seconds (without error messages) - and did not change anything.
it is likely other connections are preventing the row versions from
being reclaimed. again, you should have included verbose output.
> Additional information:
> The applications using the database are java - based and create
> persistent connections (from a connection pool) to the postgres
> database. These connections are on autocommit, thus, there should not
> be any opened transactions preventing the vacuum full from write -
> locking the tables.
well, to prove it disconnect all the other connections and then try the
delete. you are not really eliminating the unknowns by assuming they
don't exist.