Overview

This article provides the PostgreSQL commands to improve the performance of the database.  Improving database performance can help mitigate various issues, including HTTP 502 bad gateway errors. These errors can occur due to systematic errors or operational slowdowns, such as transaction timeouts.

Pre-req

Is your DB optimized? If not, see KC.

Steps

Connect to PostgreSQL Instance:

psql -U jetpatch -h localhost jetpatchdb

Run the command to list the frequently used and large tables

select schemaname as table_schema, relname as table_name, pg_size_pretty(pg_total_relation_size(relid)) as total_size, pg_size_pretty(pg_relation_size(relid)) as data_size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as external_size from pg_catalog.pg_statio_user_tables order by pg_total_relation_size(relid) desc, pg_relation_size(relid) desc limit 10;

VACUUM

The VACUUM command will reclaim space still used by data that had been updated. In PostgreSQL, updated key-value tuples are not removed from the tables when rows are changed, so the VACUUM command should be run Periodically

Before performing VACUUM stop the tomcat service in JetPatch application server

systemctl stop tomcat

Now go back to database server and run VACUUM command

 VACUUM FULL <TABLENAME> ;

Once completed run the command and you can see the size consumed will be reduced

select schemaname as table_schema, relname as table_name, pg_size_pretty(pg_total_relation_size(relid)) as total_size, pg_size_pretty(pg_relation_size(relid)) as data_size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as external_size from pg_catalog.pg_statio_user_tables order by pg_total_relation_size(relid) desc, pg_relation_size(relid) desc limit 10;

REINDEX

REINDEX rebuilds an index using the data stored in the index's table, replacing the old copy of the index. 

It provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages.

From the list of tables in the Select statement run REINDEX for all the tables

REINDEX TABLE <TABLENAME>;

\

VACUUMING

The vacuuming removes the dead tuples and release the space that are taken by deleted row or records.

Use command

VACUUM VERBOSE

Start the tomcat in JetPatch application server:

systemctl start tomcat

Additional Steps

If the issue persists, please generate the latest manager logs and a database dump for further analysis. In the meantime, as a temporary workaround, you can increase the following property:

#default 3600
spring.transaction.timeout.sec=7200 
#default 30000
spring.datasource.hikari.connectionTimeout=60000

This adjustment should only be used as a temporary measure while we investigate the root cause. Once a permanent solution is implemented, this line should be removed.