Monitoring Queries on Postgres

Question

I have an web application that executes query against a RDS Postgres Database. For this application, we use a Trunk based development and our developers can and should deploy anything on master branch directly to production. During the day, when we are operating in a low workload we can't see any performance degradation on database, but at night ( we operate a courier service), when we experiment huge workload we can have some performance degradation...

My question is: How should I monitor this kind of behaviour? I don't want to impose to run a stress test before deploy to production.

I would like to have a tool that can monitor our database and inform like: "Take care! You have a new query (or a slow query) on your database caused by Pull Request 1234".

Answer 1

If you are on RDS for PostgreSQL 10, or can upgrade to that version, then you can use Performance Insights to monitor your running instance, to see which queries are generating load on your instance, and what wait states those queries are in. You can find more info here: https://aws.amazon.com/rds/performance-insights/

Full disclosure: I am the Product Manager for Amazon Aurora PostgreSQL, which was the first db engine to support Performance Insights.

Answer 2

The simple solution is to use the pg_stat_statements extension. It can show you the queries that consumed the most total run time at one glance.

To activate it, add it to shared_preload_libraries in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Then restart PostgreSQL.

pg_stat_statements will collect statistics for all statements executed. You can view the data with the pg_stat_statements view:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- for example, the top 10 time consuming statements
SELECT total_exec_time, calls, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;