Measures the throughput on the WAL over 5 seconds using postgres builtin stuff

wal_throughput.sql

DO $$
DECLARE
    start_lsn pg_lsn;
    end_lsn pg_lsn;
    wal_bytes_diff numeric;
    time_diff numeric;
    bytes_per_second numeric;
    start_time timestamp;
    end_time timestamp;
BEGIN
    -- Step 1: Capture initial WAL LSN and timestamp
    SELECT pg_current_wal_lsn(), clock_timestamp() INTO start_lsn, start_time;

    -- Step 2: Sleep for 5 seconds
    PERFORM pg_sleep(5);

    -- Step 3: Capture final WAL LSN and timestamp after sleep
    SELECT pg_current_wal_lsn(), clock_timestamp() INTO end_lsn, end_time;

    -- Step 4: Calculate the difference in WAL bytes and time
    wal_bytes_diff := pg_wal_lsn_diff(end_lsn, start_lsn);
    time_diff := EXTRACT(EPOCH FROM (end_time - start_time)); -- Time in seconds

    -- Step 5: Calculate WAL generation rate in bytes per second
    bytes_per_second := wal_bytes_diff / time_diff;

    -- Output the result
    RAISE NOTICE 'WAL generation rate: % bytes per second', trunc(bytes_per_second, 0);
END;
$$;