pg_sqlog

pig build pkg pg_sqlog;		# build rpm/deb

pig repo add pgsql -u   # add both repo and update cache

pig install pg_sqlog;		# install via package name, for the active PG version

pig install pg_sqlog -v 18;   # install for PG 18
pig install pg_sqlog -v 17;   # install for PG 17
pig install pg_sqlog -v 16;   # install for PG 16
pig install pg_sqlog -v 15;   # install for PG 15
pig install pg_sqlog -v 14;   # install for PG 14

CREATE EXTENSION pg_sqlog CASCADE; -- requires file_fdw

-- Current day's log
SELECT * FROM sqlog.log();

-- Specific day's log
SELECT * FROM sqlog.log('yesterday');
SELECT * FROM sqlog.log('2024-01-15');

-- Error summary
SELECT error_severity, COUNT(*) FROM sqlog.log() GROUP BY 1;

-- Top 3 slowest query patterns
SELECT
  AVG(sqlog.duration(message)) AS avg_duration,
  COUNT(*) AS count,
  sqlog.preparable_query(message) AS query_pattern
FROM sqlog.log()
WHERE message ~ '^duration'
GROUP BY 3
ORDER BY 1 DESC
LIMIT 3;

-- Query summary with timing
SELECT
  log_time::time,
  sqlog.duration(message),
  sqlog.summary(message)
FROM sqlog.log('yesterday')
WHERE message ~ '^duration';

SELECT * FROM sqlog.autovacuum() LIMIT 5;
SELECT * FROM sqlog.autoanalyze() LIMIT 5;