Long-running Query and Transaction Optimization

Long-running queries or transactions affect AUTOVACUUM's ability to clear earlier versions. As a result, earlier versions created during data updates may not be cleared promptly.

When accessing data, it is necessary to traverse earlier versions to determine which versions are visible to the current query snapshot. The longer a query or transaction continues, the more earlier versions will remain uncleared, which can increasingly affect access performance. In extreme scenarios, for example, when the throughput is queried based on index points with a small amount of data, an increase in earlier versions can significantly decrease the throughput, and the performance may deteriorate by more than 50%.

In addition, if the earlier version cannot be cleared in time, extra storage space is occupied, table bloat and index bloat occur, and extra I/Os increase during data access, affecting performance.

Astore indexes and table data are updated in non-in-place mode. If an index key or a non-index key is updated, an earlier index version and an earlier data version are generated. Ustore indexes are updated in non-in-place mode, and data is updated in-place mode. Updating index keys generates earlier index versions and earlier data versions. Updating non-index keys generates only earlier data versions.

Long-running queries and transactions affect the access performance of both Astore and Ustore.

In terms of service implementation, avoid long-running queries or transactions. You can access pg_stat_activity and pg_running_xacts to check whether long-running queries or transactions exist in the system, and then use pg_cancel_backend(pid int) or pg_terminate_backend(pid int) function to cancel long-running queries or transactions.