pg_profile

pig build pkg pg_profile         # build DEB packages

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

CREATE EXTENSION pg_profile CASCADE;  -- requires: dblink, plpgsql

SELECT profile.take_sample();

-- Regular report between samples 1 and 2
SELECT profile.get_report(1, 2);

-- Differential report comparing two intervals
SELECT profile.get_diffreport(1, 2, 3, 4);

-- Define a remote server
SELECT profile.create_server('remote', 'host=remote_host dbname=postgres');

-- List defined servers
SELECT * FROM profile.show_servers();

-- Enable/disable a server
SELECT profile.enable_server('remote');
SELECT profile.disable_server('remote');

-- Create a baseline preserving samples 10 through 20
SELECT profile.create_baseline('incident_2024', 10, 20);

-- List baselines
SELECT * FROM profile.show_baselines();

-- Drop a baseline
SELECT profile.drop_baseline('incident_2024');

-- Set retention to 7 days for the local server
SELECT profile.set_server_max_sample_age('local', 7);

-- Show available samples
SELECT * FROM profile.show_samples();

-- Show time spent taking samples (requires pg_profile.track_sample_timings = on)
SELECT * FROM v_sample_timings;

track_activities = on
track_counts = on
track_io_timing = on
track_wal_io_timing = on      # PG 14+
track_functions = all