Tracking live queries on your Postgres instance

@Tarun_Garg2, SquadVoice

Log files?

• log_statement, log_min_duration_statement has to be set to a value that enables tracking all statements
• Setting log_min_duration_statement a larger value would mean we will lose out on some queries that are executing in threshold lower than that
• Setting to a lower value(~0) will mean all kind of logs will get stored and will increase disk space of your application(we've suffered downtime due to this)
• The same kind of queries is not grouped by default because they're logs, after all, so you've to install something on top to read those log files and make sense of them

pg_stat_statements

• Provides means to track all SQL queries being executed at the server
• We've to load this module manually since it requires additional shared memories
• It stores different statistics wrt to query execution like
       – userid
       – queryid
       – query
       – total_time
       – calls
       – min_time
       – rows
       – ......etc.....
• Similar kind of queries are grouped using the queryid and hashing
• But since it is also a table, it requires space and over time it can consume considerable space

But, tracking SQL queries is not all you need.

pghero

Thank you!

@Tarun_Garg2, SquadVoice