postgresql

postgresql.md

Queries

SELECT state, 
       pid,
       timezone('Europe/Paris', query_start), 
       usename, 
       substring(query,1, 20)
FROM pg_stat_activity
WHERE 
   query != '<IDLE>' AND 
   query NOT ILIKE '%pg_stat_activity%'
ORDER BY state, query_start asc limit 10;

SELECT relname,
       n_tup_ins as "inserts",
       n_tup_upd as "updates", 
       n_tup_del as "deletes",
       n_live_tup as "live_tuples", 
       n_dead_tup as "dead_tuples" 
FROM pg_stat_user_tables;

SELECT substring(query, 1, 20), 
       calls, 
       mean_time, 
       total_time, 
       rows, 
       100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements 
ORDER BY total_time DESC LIMIT 10;
---- 13
SELECT substring(query, 1, 20), 
       calls, 
       mean_exec_time, 
       total_exec_time, 
       rows, 
       100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements 
ORDER BY total_exec_time DESC LIMIT 10;

SELECT pg_cancel_backend(<pid of the process>)
SELECT pg_terminate_backend(<pid of the process>)

References

• https://www.percona.com/blog/2018/08/10/tuning-autovacuum-in-postgresql-and-autovacuum-internals/
• https://www.datadoghq.com/blog/postgresql-vacuum-monitoring/