SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
EXPLAIN
to analyze your queries:EXPLAIN SELECT * FROM your_table WHERE your_column = 'your_value';
VACUUM
and ANALYZE
to clean up dead rows and update statistics for better query planning:VACUUM VERBOSE ANALYZE your_table;
checkpoint_timeout
and max_wal_size
in postgresql.conf
to reduce checkpoint frequency:checkpoint_timeout
max_wal_size
pg_wal
) to a separate disk to reduce I/O on the main disk. This requires changes in postgresql.conf
:wal_dir
to a directory on a separate disk.iostat
is useful:iostat -dx 2
(Perfect for DevOps & SREs)
(Perfect for DevOps & SREs)