How does disabling Auto-Vacuum affect Query performance in TIBCO Data Virtualization?

How does disabling Auto-Vacuum affect Query performance in TIBCO Data Virtualization?

book

Article ID: KB0070910

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization All Supported Versions

Description

The Auto-Vacuum is enabled by default in TIBCO Data Virtualization but can be disabled from the "[TIBCO-Data-Virtualization-INSTALL-DIR]/repository/data/postgresql.conf" file. This article explains the effects that this change has on TDV queries.

Issue/Introduction

This article summarizes the performance difference of a query when the Auto-Vacuum is enabled/disabled.

Environment

Windows/Linux

Resolution

In PostgreSQL, auto-vacuum is a process that automatically reclaims storage space and optimizes the performance of database tables. All it does is sleep for a while and check periodically if a table requires processing.
To disable auto-vacuum, change the below line
-----------
#autovacuum = on
-----------
To:
-----------
autovacuum = off
-----------

1. Auto-Vacuum: Enabled

Autovacuum takes care of:

  • Creates statistics for the PostgreSQL optimizer (ANALYZE)
  • Cleans out dead rows

"Creating statistics for the PostgreSQL optimizer" is a critical operation handled by Auto-Vacuum. 
Furthermore, the PostgreSQL optimizer heavily relies on statistics to estimate the number of rows returned by various operations and strives to determine the most efficient query optimization strategy.
Cleaning out unwanted dead rows is another important advantage of leaving Auto-vacuum enabled.

2. Auto-Vacuum: Disabled
Consequently, disabling Auto-Vacuum is not recommended, as it would lead to the following downsides:

  • Accumulation of dead tuples in tables,
  • Inaccurate statistics generation for the PostgreSQL optimizer,
The above factors must be taken into consideration while disabling Auto-vacuum as this will ultimately result in overall performance degradation of the queries that run against the associated PostgreSQL database.

Additional Information

https://www.cybertec-postgresql.com/en/enabling-and-disabling-autovacuum-in-postgresql/
Powered by Wolken Software