Postgres Queries Statistics and Indexes Monitoring

[andrewsignori-aot]

As we are increasing the database load on PROD, we should monitor query execution to determine the most expensive ones and find opportunities to create indexes or change queries in a way that takes advantage of exciting indexes.

• Check how to monitor queries on Postgres to track statistics of SQL planning and execution
  • Check if pg_stat_statements extension is available and can be used to track statistics of SQL planning and execution. Please see below a possible example to identify some queries.

SELECT query,
       total_exec_time,
       calls,
       mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC;

• Check how they determine the most executed and most expensive queries.
• Create a process to execute this analysis from time to time.
• Check how to detect Fragmentation and address it.
  • Check if extensions like pgstattuple are available or if there are other similar ones.
  • Create a plan to verify and address possible fragmentation issues.
    • Some examples of commands would be VACUUM, REINDEX, and others.
• Check with the Government community if there are solutions already in place to achieve the same.