Historical Postgresql Connection Monitoring

conmon.sql

create schema if not exists conmon;

create table if not exists conmon.connection_activity_history (
    snapshot_id     bigint generated always as identity primary key,
    sampled_at      timestamptz not null default now(),
    total_backends  integer      not null,
    active_backends integer      not null,
    idle_backends   integer      not null,
    waiting_backends integer     not null,
    app_connections jsonb        not null,
    backend_user_counts jsonb    not null
);

create index if not exists connection_activity_history_sampled_at_idx
    on conmon.connection_activity_history (sampled_at);

create or replace function conmon.capture_connection_activity()
returns void
language plpgsql
security definer
set search_path = pg_catalog
as $$
begin
    insert into conmon.connection_activity_history (
        total_backends,
        active_backends,
        idle_backends,
        waiting_backends,
        app_connections,
        backend_user_counts
    )
    select
        total_backends,
        active_backends,
        idle_backends,
        waiting_backends,
        app_connections,
        backend_user_counts
    from (
        select
            count(*) as total_backends,
            count(*) filter (where state = 'active') as active_backends,
            count(*) filter (where state = 'idle') as idle_backends,
            count(*) filter (where wait_event_type is not null) as waiting_backends,
            (
                select coalesce(
                    jsonb_agg(
                        jsonb_build_object(
                            'application_name', coalesce(app.application_name, '<unknown>'),
                            'connections', app.connections
                        )
                        order by coalesce(app.application_name, '<unknown>')
                    ),
                    '[]'::jsonb
                )
                from (
                    select application_name, count(*) as connections
                    from pg_catalog.pg_stat_activity
                    group by application_name
                ) app
            ) as app_connections,
            (
                select coalesce(
                    jsonb_agg(
                        jsonb_build_object(
                            'usename', usr.usename,
                            'connections', usr.connections
                        )
                        order by usr.usename
                    ),
                    '[]'::jsonb
                )
                from (
                    select usename, count(*) as connections
                    from pg_catalog.pg_stat_activity
                    group by usename
                ) usr
            ) as backend_user_counts
        from pg_catalog.pg_stat_activity
    ) snapshot;
end;
$$;

select cron.schedule(
    'conmoncron',         
    '*/5 * * * *',                     
    $$select cronmon.capture_connection_activity();$$
);