Dimitri wrote:
I understand well that it's respecting the standard and so on, but the background problem that you may see your table bloated just because there is a long running transaction appeared in another database, and if it's maintained/used/etc by another team - the problem very quickly may become human rather technical :-))
The way VACUUM and autovacuum work by default, it's OK to expect just over 20% of the database rows to be bloat from dead rows. On some systems that much overhead is still too much, but on others the system continues to operate just fine with that quantity of bloat. It's not unreasonable, and is recoverable once the long running transaction finishes.
If your application has a component to it that allows a transaction to run for so long that more than 20% of a table can be dead before it completes, you have a technical problem. The technical solution may not be simple or obvious, but you need to find one--not say "the person shouldn't have done that". Users should never have gotten an API exposed to them where it's possible for them to screw things up that badly. The usual first round of refactoring here is to figuring out how to break transactions into smaller chunks usefully, which tends to improve other performance issues too, and then they don't run for so long either.
So, why simply don't add a FORCE option to VACUUM?.. - In this case if one executes "VACUUM FORCE TABLE" will be just aware about what he's doing and be sure no one of the active transactions will be ever access this table.
See above. If you've gotten into this situation, you do not need a better hammer to smack the part of the server that is stuck. One would be almost impossible to build, and have all sorts of side effects it's complicated to explain. It's far simpler to just avoid to known and common design patterns that lead to this class of problem in the first place. This is a database application coding problem, not really a database internals one.
-- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
