Postgres performance optimisation — 1

The first optimisation technique is to find out queries that eats up the time. PG helps you by enabling certain configuration to achieve that.

First step is to open the postgres conf file

/var/lib/pgsql/11/data/dx/postgresql.conf

Modify the value of `shared_preload_libraries`

shared_preload_libraries = ‘pg_stat_statements’

This change requires DB restart. if you are using docker containers, easiest way is to just kill the Pod

Once the Database is up, create a new extension as below

CREATE extension pg_stat_statements;

Perform a run of your application and use the below query to find out which query takes more time

select
(total_time / 1000 / 3600) as total_hours,
(total_time / 1000) as total_seconds,
(total_time / calls) as avg_millis,
calls num_calls,
query
from pg_stat_statements
order by 1 desc limit 10;

The above query will show you the top 10 time taking queries. Modify the limit as per your requirement

In case if you want a fresh start, you can reset the pg_stat_statements by below query

SELECT pg_stat_statements_reset();