Long running transactions

One problem that can block all vacuum-related activity on a server is a long running transaction. If you have a query running that needs to see very old information, the server can't clean up past that point until it completes. The easiest way to monitor long running transactions is watch for them in pg_stat_activity, using a query such as the following:

SELECT procpid,current_timestamp - xact_start AS xact_runtime,current_query FROM pg_stat_activity ORDER BY xact_start; 

This will show just how long any open transaction has been running for.

Get PostgreSQL 10 High Performance now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.