How to optimize/fine tune DB transaction?

Ever wondered why query execution time is extremely high even when it runs against table with less number of records?

Ever wondered why table consumes *% of actual DB size even though it has minimal number of records?

Before we begin

It is good to understand how DB transaction happens at the back-end before fine tuning.

Let’s assume a scenario where a report definition runs from UI to fetch results.

What really happens at back-end when a Report definition is executed?

• The Report definition execution request will be converted into DB query based on its configuration.
• Generated DB Query will hit the Database and checks for available active connection.
  If (available)
  {
  Executes query against the identified table and fetches results from DB
  }
  else
  {
  Wait for connection and executes query
  }
• When query is executed successfully, result set will be passed on to PEGA.

Let’s start with our actual discussion !!!

There can be few tables in our application which need frequent updates. In such scenario, database transaction will have higher execution time and table will consume most of the space in database.

We should understand few DB terminologies before we dig through the root-cause of the problem.

Tuple

In the context of relational databases, a tuple is one record (one row).

Dead Tuple

When you update or delete any row, internally it creates a new row and marks the old row as unused. The unused rows are known as dead tuples and they will never be removed from the table by itself.

Live Tuple

The most recent version of each row is referred as Live tuple

Actual table size= Dead Tuples + Live Tuples

Dead tuples are also considered as rows in table though they are not visible and will have considerable impact during query execution. To overcome this, dead tuples should be removed from the table. How to get rid of dead tuples?

Vacuum

Vacuum is a maintenance related command that removes all the dead tuples in table and resets the table size when executed against a table.

Will flushing out all rows in table remove dead tuples? — Not always

Truncate command will remove both dead & live tuples from table whereas delete command will only remove live tuples.

Most frequently updated OOTB tables can also be cleaned up using vacuum process.

Table size after performing vacuum,

Actual table size= Live Tuple

How to find Tuples count in a table?

Below query can be executed against the DB to get the list of tables along with their dead and live tuples count. Query might change based on the DB type [below sample is for PostgreSQL].

How to perform vacuum on the identified tables?

Vacuum can be performed by truncating the table or by executing the vacuum command against the table.

Do we need to execute this vacuum process manually?

It could either be manual or automatic. Vacuum process can be automated by enabling autovacuum_vacuum_scale_factor setting in database table.

We can use the below query to enable auto vacuum & set its scale factor.

ALTER TABLE <table-name>
SET (autovacuum_enabled = true,
autovacuum_vacuum_scale_factor = 0.2,
autovacuum_analyze_scale_factor = 0.1);

Auto vacuum factor when set to 0.2 indicates that vacuum process will be executed when dead tuples count in table exceeds 20% of the original table count. This can be updated based on our need.

Once done, table size and query execution time should be examined again to find the difference in terms of performance.