view_pgFirstAid.sql

create or replace
function pgfirstaid_pg_stat_statements_checks()
returns table (
 severity TEXT,
 category TEXT,
 check_name TEXT,
 object_name TEXT,
 issue_description TEXT,
 current_value TEXT,
 recommended_action TEXT,
 documentation_link TEXT,
 severity_order INTEGER
) as $$
begin
 if not exists (
 select
 1
 from
 pg_extension
 where
 extname = 'pg_stat_statements') then
 return;
 end if;

 -- Wrap pg_stat_statements workload queries: if the extension is installed
 -- but the underlying shared_preload_libraries setting never loaded it,
 -- the planner raises object_not_in_prerequisite_state. Match the
 -- handling in view_pgFirstAid_managed.sql so the function returns
 -- cleanly in that case instead of failing the whole health check run.
 begin
 return query
with pss as (
 select
 queryid,
 query,
 calls,
 total_exec_time,
 mean_exec_time,
 rows
 from
 pg_stat_statements
 where
 calls > 0
 order by
 total_exec_time desc
 limit 10)
 select
 'MEDIUM' as severity,
 'Query Health' as category,
 'Top 10 Queries by Total Execution Time' as check_name,
 'queryid: ' || pss.queryid::text as object_name,
 'Queries with the highest total execution time are usually the best optimization targets for overall workload improvement' as issue_description,
 'calls: ' || pss.calls || ', total_exec_time_ms: ' || round(pss.total_exec_time::numeric, 2) ||
 ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 2) || ', rows: ' || pss.rows ||
 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
 'Run EXPLAIN (ANALYZE, BUFFERS) and focus on reducing total runtime for these fingerprints first' as recommended_action,
 'https://www.postgresql.org/docs/current/pgstatstatements.html \
 https://www.postgresql.org/docs/current/using-explain.html \
 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
 3 as severity_order
 from
 pss;

 return query
 select
 'MEDIUM' as severity,
 'Query Health' as category,
 'High Mean Execution Time Queries' as check_name,
 'queryid: ' || pss.queryid::text as object_name,
 'Queries with high average runtime and enough call volume are underperforming and likely user-visible' as issue_description,
 'calls: ' || pss.calls || ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 2) ||
 ', total_exec_time_ms: ' || round(pss.total_exec_time::numeric, 2) ||
 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
 'Add or improve indexes and rewrite query predicates to reduce per-execution latency' as recommended_action,
 'https://www.postgresql.org/docs/current/pgstatstatements.html \
 https://www.postgresql.org/docs/current/using-explain.html \
 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
 3 as severity_order
 from
 pg_stat_statements pss
 where
 pss.calls >= 20
 and pss.mean_exec_time > 100
 order by
 pss.mean_exec_time desc
 limit 10;

 return query
with pss as (
 select
 queryid,
 query,
 calls,
 temp_blks_read,
 temp_blks_written,
 total_exec_time
 from
 pg_stat_statements
 where
 (temp_blks_read + temp_blks_written) > 0
 order by
 (temp_blks_read + temp_blks_written) desc
 limit 10)
 select
 'MEDIUM' as severity,
 'Query Health' as category,
 'Top 10 Queries by Temp Block Spills' as check_name,
 'queryid: ' || pss.queryid::text as object_name,
 'Frequent temp block usage points to sort or hash operations spilling to disk and causing avoidable latency' as issue_description,
 'calls: ' || pss.calls || ', temp_blks_read: ' || pss.temp_blks_read ||
 ', temp_blks_written: ' || pss.temp_blks_written || ', total_exec_time_ms: ' ||
 round(pss.total_exec_time::numeric, 2) || ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
 'Reduce row width, improve index support for sort or group patterns, and tune work_mem cautiously' as recommended_action,
 'https://www.postgresql.org/docs/current/pgstatstatements.html \
 https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM \
 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
 3 as severity_order
 from
 pss;

 return query
 select
 'MEDIUM' as severity,
 'Query Health' as category,
 'Low Cache Hit Ratio Queries' as check_name,
 'queryid: ' || pss.queryid::text as object_name,
 'Low buffer cache hit ratio indicates heavy physical reads and likely missing indexes or poor filtering' as issue_description,
 'calls: ' || pss.calls || ', cache_hit_pct: ' || round(
 100.0 * pss.shared_blks_hit / NULLIF(pss.shared_blks_hit + pss.shared_blks_read, 0),
 2
 ) || ', shared_blks_read: ' || pss.shared_blks_read || ', shared_blks_hit: ' || pss.shared_blks_hit ||
 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
 'Prioritize index tuning and query filtering to reduce disk reads for these statements' as recommended_action,
 'https://www.postgresql.org/docs/current/pgstatstatements.html \
 https://www.postgresql.org/docs/current/using-explain.html \
 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
 3 as severity_order
 from
 pg_stat_statements pss
 where
 pss.calls >= 20
 and (pss.shared_blks_hit + pss.shared_blks_read) > 0
 and (100.0 * pss.shared_blks_hit / NULLIF(pss.shared_blks_hit + pss.shared_blks_read, 0)) < 90
 order by
 (100.0 * pss.shared_blks_hit / NULLIF(pss.shared_blks_hit + pss.shared_blks_read, 0)) asc
 limit 10;

 return query
 select
 'MEDIUM' as severity,
 'Query Health' as category,
 'High Runtime Variance Queries' as check_name,
 'queryid: ' || pss.queryid::text as object_name,
 'High runtime variance can indicate plan instability, skewed data distribution, or parameter sensitivity' as issue_description,
 'calls: ' || pss.calls || ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 2) ||
 ', stddev_exec_time_ms: ' || round(pss.stddev_exec_time::numeric, 2) ||
 ', total_exec_time_ms: ' || round(pss.total_exec_time::numeric, 2) || ', query: ' ||
 left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
 'Check plan stability with EXPLAIN (ANALYZE, BUFFERS), update statistics, and review parameterized execution paths' as recommended_action,
 'https://www.postgresql.org/docs/current/pgstatstatements.html \
 https://www.postgresql.org/docs/current/routine-vacuuming.html \
 https://www.postgresql.org/docs/current/using-explain.html' as documentation_link,
 3 as severity_order
 from
 pg_stat_statements pss
 where
 pss.calls >= 20
 and pss.stddev_exec_time > pss.mean_exec_time
 order by
 pss.stddev_exec_time desc
 limit 10;

 return query
 select
 'MEDIUM' as severity,
 'Query Health' as category,
 'High Calls Low Value Queries' as check_name,
 'queryid: ' || pss.queryid::text as object_name,
 'Very high call volume with low per-call value can create avoidable overhead and crowd out expensive work' as issue_description,
 'calls: ' || pss.calls || ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 3) ||
 ', total_exec_time_ms: ' || round(pss.total_exec_time::numeric, 2) ||
 ', rows_per_call: ' || round((pss.rows::numeric / NULLIF(pss.calls, 0)), 2) ||
 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
 'Batch repeated requests, cache stable lookups, and reduce N+1 query patterns in the application layer' as recommended_action,
 'https://www.postgresql.org/docs/current/pgstatstatements.html \
 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
 3 as severity_order
 from
 pg_stat_statements pss
 where
 pss.calls >= 5000
 and pss.mean_exec_time <= 2
 and (pss.rows::numeric / NULLIF(pss.calls, 0)) <= 2
 order by
 pss.calls desc
 limit 10;

 return query
 select
 'MEDIUM' as severity,
 'Query Health' as category,
 'High Rows Per Call Queries' as check_name,
 'queryid: ' || pss.queryid::text as object_name,
 'High rows returned per execution often indicates over-fetching or missing selective filters' as issue_description,
 'calls: ' || pss.calls || ', rows_per_call: ' || round((pss.rows::numeric / NULLIF(pss.calls, 0)), 2) ||
 ', total_rows: ' || pss.rows || ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 2) ||
 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
 'Add tighter predicates, pagination, and narrower SELECT lists to reduce unnecessary row transfer' as recommended_action,
 'https://www.postgresql.org/docs/current/pgstatstatements.html \
 https://www.postgresql.org/docs/current/queries-limit.html \
 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
 3 as severity_order
 from
 pg_stat_statements pss
 where
 pss.calls >= 20
 and (pss.rows::numeric / NULLIF(pss.calls, 0)) > 10000
 order by
 (pss.rows::numeric / NULLIF(pss.calls, 0)) desc
 limit 10;

 return query
 select
 'MEDIUM' as severity,
 'Query Health' as category,
 'High Shared Block Reads Per Call Queries' as check_name,
 'queryid: ' || pss.queryid::text as object_name,
 'High shared block reads per call usually points to heavy table or index scans and poor locality' as issue_description,
 'calls: ' || pss.calls || ', shared_blks_read_per_call: ' || round((pss.shared_blks_read::numeric / NULLIF(pss.calls, 0)), 2) ||
 ', shared_blks_read: ' || pss.shared_blks_read || ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 2) ||
 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
 'Use EXPLAIN (ANALYZE, BUFFERS) to add selective indexes and reduce pages read per execution' as recommended_action,
 'https://www.postgresql.org/docs/current/pgstatstatements.html \
 https://www.postgresql.org/docs/current/using-explain.html \
 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
 3 as severity_order
 from
 pg_stat_statements pss
 where
 pss.calls >= 20
 and (pss.shared_blks_read::numeric / NULLIF(pss.calls, 0)) > 1000
 order by
 (pss.shared_blks_read::numeric / NULLIF(pss.calls, 0)) desc
 limit 10;

 return query
 select
 'MEDIUM' as severity,
 'Query Health' as category,
 'Top Queries by WAL Bytes Per Call' as check_name,
 'queryid: ' || pss.queryid::text as object_name,
 'High WAL generation per execution can indicate heavy write amplification and expensive update patterns' as issue_description,
 'calls: ' || pss.calls || ', wal_bytes_per_call: ' || round(
 ((to_jsonb(pss)->>'wal_bytes')::numeric / NULLIF(pss.calls, 0)),
 2
 ) || ', wal_bytes_total: ' || round((to_jsonb(pss)->>'wal_bytes')::numeric, 2) ||
 ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 2) ||
 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
 'Reduce row churn, batch writes where possible, and review index maintenance cost for heavy write queries' as recommended_action,
 'https://www.postgresql.org/docs/current/pgstatstatements.html \
 https://www.postgresql.org/docs/current/wal-intro.html \
 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
 3 as severity_order
 from
 pg_stat_statements pss
 where
 pss.calls >= 20
 and coalesce((to_jsonb(pss)->>'wal_bytes')::numeric, 0) > 0
 and ((to_jsonb(pss)->>'wal_bytes')::numeric / NULLIF(pss.calls, 0)) > 1048576
 order by
 ((to_jsonb(pss)->>'wal_bytes')::numeric / NULLIF(pss.calls, 0)) desc
 limit 10;

 return query
 select
 'LOW' as severity,
 'Query Health' as category,
 'Not In With Subquery' as check_name,
 'queryid: ' || pss.queryid::text as object_name,
 'Query uses NOT IN (SELECT ...). The SQL NULL semantics trap returns zero rows if the subquery returns any NULL: even if the row exists. Common bug class per the Postgres Mistakes book sec. 2.1.' as issue_description,
 'calls: ' || pss.calls || ', total_exec_time_ms: ' || round(pss.total_exec_time::numeric, 2) ||
 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
 'Rewrite as NOT EXISTS (SELECT 1 FROM ... WHERE ...), which handles NULLs correctly. Alternatively filter NULLs in the subquery with WHERE col IS NOT NULL.' as recommended_action,
 'https://www.postgresql.org/docs/current/functions-comparison.html \
 https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-EXPRESSIONS' as documentation_link,
 4 as severity_order
 from
 pg_stat_statements pss
 where
 pss.calls >= 1
 and pss.query ~* '^\s*(select|insert|update|delete|with|merge)'
 and pss.query ~* '\mNOT\s+IN\s*\(\s*(WITH|SELECT)'
 order by
 pss.total_exec_time desc
 limit 10;
 exception when object_not_in_prerequisite_state then
 return;
 end;
end;
$$ language plpgsql;

-- Adding dropping of the view instead of replace because of conversion issues with new health checks.
-- This way we start with a fresh view.
drop view if exists v_pgfirstAid;

create or replace
function _pg_firstaid_checkpoint_stats()
returns text
language plpgsql
stable
as $$
declare
 v_timed bigint;
 v_forced bigint;
begin
 if current_setting('server_version_num')::int >= 170000 then
 select num_timed, num_requested
 into v_timed, v_forced
 from pg_stat_checkpointer;
 else
 select checkpoints_timed, checkpoints_req
 into v_timed, v_forced
 from pg_stat_bgwriter;
 end if;

 return 'timed: ' || v_timed::text ||
 ', forced: ' || v_forced::text ||
 ', forced ratio: ' ||
 case
 when v_timed + v_forced = 0 then '0%'
 else round(100.0 * v_forced / (v_timed + v_forced), 1)::text || '%'
 end;
end;
$$;

create view v_pgfirstAid as
select
 health_results.severity,
 health_results.category,
 health_results.check_name,
 health_results.object_name,
 health_results.issue_description,
 health_results.current_value,
 health_results.recommended_action,
 health_results.documentation_link,
 health_results.severity_order
from (
-- CRITICAL: Tables without primary keys
 select
	'CRITICAL' as severity,
	'Table Health' as category,
	'Missing Primary Key' as check_name,
	quote_ident(pt.schemaname) || '.' || quote_ident(tablename) as object_name,
	'Table missing a primary key, which can cause replication issues and/or poor performance' as issue_description,
	'No primary key defined' as current_value,
	'Add a primary key or unique constraint with NOT NULL columns' as recommended_action,
	'https://www.postgresql.org/docs/current/ddl-constraints.html' as documentation_link,
	1 as severity_order
from
	pg_tables pt
where
	pt.schemaname not like all(array['information_schema', 'pg_catalog', 'pg_toast', 'pg_temp%'])
	and not exists (
	select
		1
	from
		pg_constraint pc
	join pg_class c on
		pc.conrelid = c.oid
	join pg_namespace n on
		c.relnamespace = n.oid
	where
		pc.contype = 'p'
		and n.nspname = pt.schemaname
		and c.relname = pt.tablename
 )
union all
-- CRITICAL: Large indexes never used for reads consuming significant space
	select
	'CRITICAL' as severity,
	'Table Health' as category,
	'Unused Large Index' as check_name,
	quote_ident(psi.schemaname) || '.' || quote_ident(psio.indexrelname) as object_name,
	'Large index never used for reads, consuming disk space and potentially impacting write performance' as issue_description,
	pg_size_pretty(pg_relation_size(psi.indexrelid)) || ' (0 scans)' as current_value,
	'Consider dropping this index if truly unused after monitoring usage patterns. Never drop an index without validating usage!' as recommended_action,
	'https://www.postgresql.org/docs/current/sql-dropindex.html' as documentation_link,
	1 as severity_order
from
	pg_stat_user_indexes psi
join pg_statio_user_indexes psio on
	psi.indexrelid = psio.indexrelid
join pg_index pgi on
	pgi.indexrelid = psi.indexrelid
where
	idx_scan = 0
	and not pgi.indisunique
	and not pgi.indisexclusion
	and pg_relation_size(psi.indexrelid) > 104857600 -- 100MB
union all
-- MEDIUM: Large unique/constraint indexes never used for reads
	select
	'MEDIUM' as severity,
	'Table Health' as category,
	'Unread Large Constraint-Backing Index' as check_name,
	quote_ident(psi.schemaname) || '.' || quote_ident(psio.indexrelname) as object_name,
	'Large unique or constraint index never used for reads. It is enforced on every write, so review whether the constraint is needed.' as issue_description,
	pg_size_pretty(pg_relation_size(psi.indexrelid)) || ' (0 scans)' as current_value,
	'Confirm the underlying unique or primary-key constraint is still required. Drop the constraint if it is not necessary.' as recommended_action,
	'https://www.postgresql.org/docs/current/ddl-constraints.html' as documentation_link,
	3 as severity_order
from
	pg_stat_user_indexes psi
join pg_statio_user_indexes psio on
	psi.indexrelid = psio.indexrelid
join pg_index pgi on
	pgi.indexrelid = psi.indexrelid
where
	idx_scan = 0
	and (pgi.indisunique or pgi.indisexclusion)
	and pg_relation_size(psi.indexrelid) > 104857600 -- 100MB
union all
-- HIGH: Inactive Replication slots
(with q as (
select
		slot_name,
		plugin,
		database,
		restart_lsn,
		case
			when active is true then 'active'
			else 'inactive'
		end as "status",
		pg_size_pretty(
 pg_wal_lsn_diff(
 pg_current_wal_lsn(), restart_lsn)) as "retained_wal",
		pg_size_pretty(safe_wal_size) as "safe_wal_size"
from
		pg_replication_slots
where
		active = false
 )
select
	'HIGH' as severity,
	'Replication Health' as category,
	'Inactive Replication Slots' as check_name,
	'Slot name:' || slot_name as object_name,
	'Target replication slot is inactive' as issue_description,
	'Retained wal:' || retained_wal || ' database:' || database as current_value,
	'If the replication slot is no longer needed, drop the slot' as recommended_action,
	'https://www.morling.dev/blog/mastering-postgres-replication-slots' as documentation_link,
	2 as severity_order
from
	q)
union all
-- credit: https://www.morling.dev/blog/mastering-postgres-replication-slots/ -- Thank you Gunnar Morling!
-- HIGH: Tables with high bloat
(with q as (
select
		current_database(),
		schemaname,
		tblname,
		bs * tblpages as real_size,
		(tblpages-est_tblpages)* bs as extra_size,
		case
			when tblpages > 0
		and tblpages - est_tblpages > 0
 then 100 * (tblpages - est_tblpages)/ tblpages::float
		else 0
	end as extra_pct,
			fillfactor,
			case
				when tblpages - est_tblpages_ff > 0
 then (tblpages-est_tblpages_ff)* bs
		else 0
	end as bloat_size,
			case
				when tblpages > 0
			and tblpages - est_tblpages_ff > 0
 then 100 * (tblpages - est_tblpages_ff)/ tblpages::float
			else 0
		end as bloat_pct,
				is_na
	from
				(
		select
					ceil( reltuples / ( (bs-page_hdr)/ tpl_size ) ) + ceil( toasttuples / 4 ) as est_tblpages,
					ceil( reltuples / ( (bs-page_hdr)* fillfactor /(tpl_size * 100) ) ) + ceil( toasttuples / 4 ) as est_tblpages_ff,
					tblpages,
					fillfactor,
					bs,
					tblid,
					schemaname,
					tblname,
					heappages,
					toastpages,
					is_na
		from
					(
			select
						( 4 + tpl_hdr_size + tpl_data_size + (2 * ma)
 - case
							when tpl_hdr_size%ma = 0 then ma
					else tpl_hdr_size%ma
				end
 - case
							when ceil(tpl_data_size)::int%ma = 0 then ma
					else ceil(tpl_data_size)::int%ma
				end
 ) as tpl_size,
						bs - page_hdr as size_per_block,
						(heappages + toastpages) as tblpages,
						heappages,
						toastpages,
						reltuples,
						toasttuples,
						bs,
						page_hdr,
						tblid,
						schemaname,
						tblname,
						fillfactor,
						is_na
			from
						(
				select
							tbl.oid as tblid,
							ns.nspname as schemaname,
							tbl.relname as tblname,
							tbl.reltuples,
							tbl.relpages as heappages,
							coalesce(toast.relpages, 0) as toastpages,
							coalesce(toast.reltuples, 0) as toasttuples,
							coalesce(substring(
 array_to_string(tbl.reloptions, ' ')
 from 'fillfactor=([0-9]+)')::smallint, 100) as fillfactor,
							current_setting('block_size')::numeric as bs,
							case
								when version()~ 'mingw32'
							or version()~ '64-bit|x86_64|ppc64|ia64|amd64' then 8
							else 4
						end as ma,
								24 as page_hdr,
								23 + case
									when MAX(coalesce(s.null_frac, 0)) > 0 then ( 7 + count(s.attname) ) / 8
							else 0::int
						end
 + case
									when bool_or(att.attname = 'oid' and att.attnum < 0) then 4
							else 0
						end as tpl_hdr_size,
								sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) as tpl_data_size,
								bool_or(att.atttypid = 'pg_catalog.name'::regtype)
							or sum(case when att.attnum > 0 then 1 else 0 end) <> count(s.attname) as is_na
						from
									pg_attribute as att
						join pg_class as tbl on
									att.attrelid = tbl.oid
						join pg_namespace as ns on
									ns.oid = tbl.relnamespace
						left join pg_stats as s on
									s.schemaname = ns.nspname
							and s.tablename = tbl.relname
							and s.inherited = false
							and s.attname = att.attname
						left join pg_class as toast on
									tbl.reltoastrelid = toast.oid
						where
									not att.attisdropped
							and tbl.relkind in ('r', 'm')
						group by
									1,
									2,
									3,
									4,
									5,
									6,
									7,
									8,
									9,
									10
						order by
									2,
									3
 ) as s
 ) as s2
) as s3)
select
	'HIGH' as severity,
	'Table Health' as category,
	'Table Bloat (Detailed)' as check_name,
	quote_ident(schemaname) || '.' || quote_ident(tblname) as object_name,
	'Table has significant bloat (>50%) affecting performance and storage' as issue_description,
	'Real size: ' || pg_size_pretty(real_size::bigint) ||
 ', Bloat: ' || pg_size_pretty(bloat_size::bigint) ||
 ' (' || ROUND(bloat_pct::numeric, 2) || '%)' as current_value,
	'Run VACUUM FULL to reclaim space' as recommended_action,
	'https://www.postgresql.org/docs/current/sql-vacuum.html,
 https://github.com/ioguix/pgsql-bloat-estimation/' as documentation_link,
	2 as severity_order
from
	q
where
	bloat_pct > 50.0
	and bloat_size > pg_size_bytes('10 MB')
	and schemaname not like all(array['information_schema', 'pg_catalog', 'pg_toast', 'pg_temp%'])
order by
	quote_ident(schemaname),
	quote_ident(tblname))
union all
--Credit: https://github.com/ioguix/pgsql-bloat-estimation -- Jehan-Guillaume (ioguix) de Rorthais!
-- HIGH: Tables never analyzed
 select
	'HIGH' as severity,
	'Table Health' as category,
	'Missing Statistics' as check_name,
	quote_ident(schemaname) || '.' || quote_ident(relname) as object_name,
	'Table has never been analyzed, query planner missing statistics' as issue_description,
	'Last analyze: Never' as current_value,
	'Run ANALYZE on this table or enable auto-analyze' as recommended_action,
	'https://www.postgresql.org/docs/current/sql-analyze.html' as documentation_link,
	2 as severity_order
from
	pg_stat_user_tables pt
where
	last_analyze is null
	and last_autoanalyze is null
	and n_tup_ins + n_tup_upd + n_tup_del > 1000
union all
-- HIGH: Tables larger than 100GB
(with ts as (
select
	table_schema,
	table_name,
	pg_relation_size('"' || table_schema || '"."' || table_name || '"') as size_bytes,
	pg_size_pretty(pg_relation_size('"' || table_schema || '"."' || table_name || '"')) as size_pretty
from
	information_schema.tables
where
	table_type = 'BASE TABLE'
	and pg_relation_size('"' || table_schema || '"."' || table_name || '"') > 107374182400
	-- 100GB in bytes
order by
	size_bytes desc)
select
	'HIGH' as severity,
	'Table Health' as category,
	'Tables larger than 100GB' as check_name,
	ts.table_schema || '"."' || ts.table_name as object_name,
	'The following table' as description,
	 ts.size_pretty as current_value,
	'I suggest looking into partitioning tables. Do you need all of this data? Can some of it be archived into something like S3?' as recommended_action,
	'https://www.heroku.com/blog/handling-very-large-tables-in-postgres-using-partitioning/' as documentation_link,
	2 as severity_order
from
	ts)
union all
-- HIGH: Duplicate or redundant indexes
-- Compare actual index structure (columns, operator class) not string definitions
 select
	'HIGH' as severity,
	'Table Health' as category,
	'Duplicate Index' as check_name,
	quote_ident(n1.nspname) || '.' || c1.relname || ': ' || i1.relname || ' & ' || i2.relname as object_name,
	'Multiple indexes with identical column sets and operator classes' as issue_description,
	'Indexes: ' || i1.relname || ', ' || i2.relname as current_value,
	'Review and consolidate duplicate indexes and focus on keeping the most efficient one' as recommended_action,
	'https://www.postgresql.org/docs/current/indexes-multicolumn.html' as documentation_link,
	2 as severity_order
from
	pg_index idx1
join pg_class i1 on idx1.indexrelid = i1.oid
join pg_class c1 on idx1.indrelid = c1.oid
join pg_namespace n1 on c1.relnamespace = n1.oid
join pg_index idx2 on
	idx1.indrelid = idx2.indrelid -- same table
	and idx1.indexrelid < idx2.indexrelid -- avoid duplicates
	and idx1.indkey = idx2.indkey -- same columns
	and idx1.indclass = idx2.indclass -- same operator classes
	and idx1.indoption = idx2.indoption -- same options
join pg_class i2 on idx2.indexrelid = i2.oid
where
	n1.nspname not like all(array['information_schema', 'pg_catalog', 'pg_toast', 'pg_temp%'])
union all
-- HIGH: Autovacuum disabled on table
(select
 'HIGH' as severity,
 'Table Health' as category,
 'Autovacuum Disabled On Table' as check_name,
 quote_ident(n.nspname) || '.' || quote_ident(c.relname) as object_name,
 'Autovacuum is disabled at the table level. Bloat, transaction ID wraparound, and outdated planner statistics will accumulate without manual intervention.' as issue_description,
 pg_size_pretty(pg_relation_size(c.oid)) as current_value,
 'Re-enable autovacuum: ALTER TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' SET (autovacuum_enabled = true). If this table genuinely needs autovacuum off, document the reason and schedule manual VACUUM ANALYZE.' as recommended_action,
 'https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS' as documentation_link,
 2 as severity_order
from pg_class c
join pg_namespace n on c.relnamespace = n.oid
where c.relkind in ('r', 'p')
 and n.nspname not in ('pg_catalog', 'information_schema', 'pg_toast')
 and n.nspname not like 'pg_temp_%'
 and c.reloptions is not null
 and exists (
 select 1 from unnest(c.reloptions) opt
 where split_part(opt, '=', 1) = 'autovacuum_enabled'
 and split_part(opt, '=', 2) = 'false'
 )
order by pg_relation_size(c.oid) desc
limit 100)
union all
-- HIGH: Table with more than 200 columns
(with cc as (
select
	table_schema,
	table_name,
	COUNT(*) as column_count
from
	information_schema.columns
where
	table_schema not in ('pg_catalog', 'information_schema')
group by
	table_schema,
	table_name
order by
	column_count desc)
select
	'HIGH' as severity,
	'Table Health' as category,
	'Table with more than 200 columns' as check_name,
	 cc.table_schema || '.' || cc.table_name as object_name,
	'Postgres has a hard 1600 column limit, but that also includes columns you have dropped. Continuing to widen your table can impact performance.' as issue_description,
	 cc.column_count::text as current_value,
	'Yikes-it is about time you put a hard stop on widing your tables and begin breaking this table into several tables. I once worked on a table with over 300 columns before.......' as recommended_action,
	'https://www.tigerdata.com/learn/designing-your-database-schema-wide-vs-narrow-postgres-tables \
	 https://nerderati.com/postgresql-tables-can-have-at-most-1600-columns \
 https://www.postgresql.org/docs/current/limits.html' as documentation_link,
	2 as severity_order
from
	cc
where
	cc.column_count > 200)
union all
-- MEDIUM: Blocked and Blocking Queries
(with bq as (
select
	blocked.pid as blocked_pid,
	blocked.query as blocked_query,
	blocking.pid as blocking_pid,
	blocking.query as blocking_query,
	now() - blocked.query_start as blocked_duration
from
	pg_locks blocked_locks
join pg_stat_activity blocked on
	blocked.pid = blocked_locks.pid
join pg_locks blocking_locks
on
	blocking_locks.transactionid = blocked_locks.transactionid
and blocking_locks.pid != blocked_locks.pid
join pg_stat_activity blocking on
	blocking.pid = blocking_locks.pid
where
	not blocked_locks.granted)
select
	'MEDIUM' as severity,
	'Query Health' as category,
	'Current Blocked/Blocking Queries' as check_name,
	'Blocked PID: ' || bq.blocked_pid || chr(10) ||
 'Blocked Query: ' || bq.blocked_query as object_name,
	'The following query is being blocked by an already running query' as issue_description,
	'Blocking PID: ' || bq.blocking_pid || chr(10) ||
	'Blocking Query: ' || bq.blocking_query as current_value,
	'Blocked queries are part of concurrency behavior. However, it is always recommended to monitor long running blocking queries. The Crunchy Data article recommended has an excellent walk through and suggested steps on how to tackle unnecessary blocking queries' as recommended_action,
	'https://www.postgresql.org/docs/current/explicit-locking.html' as documentation_link,
	3 as severity_order
from
	bq)
union all
-- MEDIUM: Tables with outdated statistics
(with s as (
select
		current_setting('autovacuum_analyze_scale_factor')::float8 as analyze_factor,
		current_setting('autovacuum_analyze_threshold')::float8 as analyze_threshold,
		current_setting('autovacuum_vacuum_scale_factor')::float8 as vacuum_factor,
		current_setting('autovacuum_vacuum_threshold')::float8 as vacuum_threshold
 ),
	tt as (
select
		n.nspname,
		c.relname,
		c.oid as relid,
		t.n_dead_tup,
		t.n_mod_since_analyze,
		c.reltuples * s.vacuum_factor + s.vacuum_threshold as v_threshold,
		c.reltuples * s.analyze_factor + s.analyze_threshold as a_threshold
from
		s,
		pg_class c
join pg_namespace n on
		c.relnamespace = n.oid
join pg_stat_all_tables t on
		c.oid = t.relid
where
		c.relkind = 'r'
and n.nspname not like all(array['information_schema', 'pg_catalog', 'pg_toast', 'pg_temp%'])
 )
select
	'MEDIUM' as severity,
	'Table Health' as category,
	'Outdated Statistics' as check_name,
	quote_ident(nspname) || '.' || quote_ident(relname) as object_name,
	'Table statistics are outdated, which can lead to poor query plans' as issue_description,
	'Dead tuples: ' || n_dead_tup || ' (threshold: ' || round(v_threshold) || '), ' ||
 'Modifications since analyze: ' || n_mod_since_analyze || ' (threshold: ' || round(a_threshold) || ')' as current_value,
	case
		when n_dead_tup > v_threshold
	and n_mod_since_analyze > a_threshold then 'Run VACUUM ANALYZE'
	when n_dead_tup > v_threshold then 'Run VACUUM'
	when n_mod_since_analyze > a_threshold then 'Run ANALYZE'
end as recommended_action,
	'https://www.postgresql.org/docs/current/routine-vacuuming.html,
 https://www.depesz.com/2020/01/29/which-tables-should-be-auto-vacuumed-or-auto-analyzed/' as documentation_link,
	3 as severity_order
from
	tt
where
	n_dead_tup > v_threshold
or n_mod_since_analyze > a_threshold
order by
	nspname,
	relname)
union all
-- credit: https://www.depesz.com/2020/01/29/which-tables-should-be-auto-vacuumed-or-auto-analyzed -- Thanks depesz!
-- MEDIUM: Low index usage efficiency
 select
	'MEDIUM' as severity,
	'Table Health' as category,
	'Low Index Efficiency' as check_name,
	quote_ident(schemaname) || '.' || quote_ident(indexrelname) as object_name,
	'Index has low scan to tuple read ratio indicating poor selectivity' as issue_description,
	'Scans: ' || idx_scan || ', Tuples: ' || idx_tup_read ||
 ' (Ratio: ' || ROUND(idx_tup_read::numeric / nullif(idx_scan, 0), 2) || ')' as current_value,
	'Review index definition and query patterns, consider partial indexes' as recommended_action,
	'https://www.postgresql.org/docs/current/indexes-partial.html' as documentation_link,
	3 as severity_order
from
	pg_stat_user_indexes psi
where
	idx_scan > 100
	and idx_tup_read::numeric / nullif(idx_scan, 0) > 1000
union all
-- MEDIUM: Replication slots with high wal retation (90% of max wal)
(with q as (
select
		slot_name,
		plugin,
		database,
		restart_lsn,
		case
			when active is true then 'active'
			else 'inactive'
		end as "status",
		pg_size_pretty(
 pg_wal_lsn_diff(
 pg_current_wal_lsn(), restart_lsn)) as "retained_wal",
		pg_size_pretty(safe_wal_size) as "safe_wal_size"
from
		pg_replication_slots
where
		pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) >= (safe_wal_size * 0.9)
)
select
		'MEDIUM' as severity,
		'Replication Health' as category,
		'Replication Slots Near Max Wal Size' as check_name,
		'Slot name:' || slot_name as object_name,
		'Target replication slot has retained close to 90% of the max wal size' as issue_description,
		'Retained wal:' || retained_wal || ' safe_wal_size:' || safe_wal_size as current_value,
		'Consider implementing a heartbeat table or using pg_logical_emit_message()' as recommended_action,
		'https://www.morling.dev/blog/mastering-postgres-replication-slots' as documentation_link,
		3 as severity_order
from
		q)
union all
-- MEDIUM: Large sequential scans
 select
	'MEDIUM' as severity,
	'Query Health' as category,
	'Excessive Sequential Scans' as check_name,
	quote_ident(schemaname) || '.' || quote_ident(relname) as object_name,
	'Table has high sequential scan activity, may benefit from additional indexes' as issue_description,
	'Sequential scans: ' || seq_scan || ', Tuples read: ' || seq_tup_read as current_value,
	'Analyze query patterns and consider adding appropriate indexes' as recommended_action,
	'https://www.postgresql.org/docs/current/using-explain.html' as documentation_link,
	3 as severity_order
from
	pg_stat_user_tables
where
	seq_scan > 1000
	and seq_tup_read > seq_scan * 10000
union all
-- MEDIUM: Table with more than 50 columns
(with cc as (
select
	table_schema,
	table_name,
	COUNT(*) as column_count
from
	information_schema.columns tc
where
	table_schema not in ('pg_catalog', 'information_schema')
group by
	table_schema,
	table_name
order by
	column_count desc)
select
	'MEDIUM' as severity,
	'Table Health' as category,
	'Table with more than 50 columns' as check_name,
	 cc.table_schema || '.' || cc.table_name as object_name,
	'Postgres has a hard 1600 column limit, but that also includes columns you have dropped. Continuing to widen your table can impact performance.' as issue_description,
	 cc.column_count::text as current_value,
	'The most straightforward recommendation is to split your table into more tables connected via foreign keys. However, your situation may very based on the type of data stored. Consider the documentation links to learn more.' as recommended_action,
	'https://www.tigerdata.com/learn/designing-your-database-schema-wide-vs-narrow-postgres-tables \
	 https://nerderati.com/postgresql-tables-can-have-at-most-1600-columns \
 https://www.postgresql.org/docs/current/limits.html' as documentation_link,
	3 as severity_order
from
	cc
where
	cc.column_count between 50 and 199)
union all
-- MEDIUM: Connection and lock monitoring
 select
	'MEDIUM' as severity,
	'System Health' as category,
	'High Connection Count' as check_name,
	'Database Connections' as object_name,
	'High number of active connections may impact performance' as issue_description,
	COUNT(*)::text || ' active connections' as current_value,
	'Monitor connection pooling and consider adjusting max_connections' as recommended_action,
	'https://www.postgresql.org/docs/current/runtime-config-connection.html' as documentation_link,
	3 as severity_order
from
	pg_stat_activity
where
	state = 'active'
group by
	1,
	2,
	3,
	4,
	5,
	7,
	8,
	9
having
	COUNT(*) > 50
union all
-- MEDIUM: Tables larger than 50GB
(with ts as (
select
	table_schema,
	table_name,
	pg_relation_size('"' || table_schema || '"."' || table_name || '"') as size_bytes,
	pg_size_pretty(pg_relation_size('"' || table_schema || '"."' || table_name || '"')) as size_pretty
from
	information_schema.tables
where
	table_type = 'BASE TABLE'
	and pg_relation_size('"' || table_schema || '"."' || table_name || '"') between 53687091200 and 107374182400
order by
	size_bytes desc)
select
	'MEDIUM' as severity,
	'Table Health' as category,
	'Tables larger than 50GB' as check_name,
	ts.table_schema || '"."' || ts.table_name as object_name,
	'The following table' as description,
	 ts.size_pretty as current_value,
	'Tables larger than 50GB should be monitored and reviewed if a data archiving or removal process should be implemented. I suggest looking into partitioning tables, if possible.' as recommended_action,
	'https://www.heroku.com/blog/handling-very-large-tables-in-postgres-using-partitioning/' as documentation_link,
	3 as severity_order
from
	ts)
union all
-- MEDIUM: Queries running longer than 5 minutes
 select
	'MEDIUM' as severity,
	'Query Health' as category,
	'Long Running Queries' as check_name,
	concat_ws(' | ',
 'pid: ' || pgs.pid::text,
 'usename: ' || pgs.usename,
 'datname: ' || pgs.datname,
 'client_address: ' || pgs.client_addr::text,
 'state: ' || pgs.state,
 'duration: ' || to_char(now() - query_start, 'HH24:MI:SS')
 ) as object_name,
	'The following query has been running for more than 5 minutes. Might be helpful to see if this is expected behavior' as issue_description,
	query as current_value,
	'Review query using EXPLAIN ANALYZE to identify any bottlenecks, such as full table scans, missing indexes, etc' as recommended_action,
	'https://www.postgresql.org/docs/current/using-explain.html' as documentation_link,
	3 as severity_order
from
	pg_stat_activity pgs
where
	state = 'active'
	and now() - query_start > interval '5 minutes'
union all
-- MEDIUM: pg_stat_statements extension missing
select
	'MEDIUM' as severity,
	'Query Health' as category,
	'pg_stat_statements Extension Missing' as check_name,
	'pg_stat_statements' as object_name,
	'pg_stat_statements is not installed, so query fingerprint and workload-level performance checks are unavailable' as issue_description,
	'Extension not found in pg_extension' as current_value,
	'Self-hosted: add pg_stat_statements to shared_preload_libraries, restart PostgreSQL, then run CREATE EXTENSION pg_stat_statements; AWS RDS: add pg_stat_statements to the parameter group shared_preload_libraries, reboot, then CREATE EXTENSION; GCP Cloud SQL: enable cloudsql.enable_pg_stat_statements, restart if required, then CREATE EXTENSION; Azure Database for PostgreSQL: add pg_stat_statements to shared_preload_libraries, restart, then CREATE EXTENSION' as recommended_action,
	'https://www.postgresql.org/docs/current/pgstatstatements.html \
	 https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.html \
	 https://cloud.google.com/sql/docs/postgres/flags \
	 https://learn.microsoft.com/azure/postgresql/flexible-server/concepts-server-parameters' as documentation_link,
	3 as severity_order
where
	not exists (
	select
		1
	from
		pg_extension
	where
		extname = 'pg_stat_statements'
	)
union all
-- MEDIUM: pg_stat_statements dependent checks
(select
	*
from
	pgfirstaid_pg_stat_statements_checks())
union all
-- MEDIUM: Top 10 expensive active queries by runtime
(with eq as (
select
	pgs.pid,
	pgs.usename,
	pgs.datname,
	pgs.client_addr,
	now() - pgs.query_start as runtime,
	pgs.query
from
	pg_stat_activity pgs
where
	pgs.state = 'active'
	and pgs.query_start is not null
	and pgs.pid <> pg_backend_pid()
	and now() - pgs.query_start > interval '30 seconds'
order by
	runtime desc
limit 10)
select
	'MEDIUM' as severity,
	'Query Health' as category,
	'Top 10 Expensive Active Queries' as check_name,
	concat_ws(' | ',
		'pid: ' || eq.pid::text,
		'usename: ' || eq.usename,
		'datname: ' || eq.datname,
		'client_address: ' || coalesce(eq.client_addr::text, 'local'),
		'runtime: ' || to_char(eq.runtime, 'HH24:MI:SS')
	) as object_name,
	'Top 10 active queries running longer than 30 seconds, ordered by runtime. Long-running active queries can signal lock waits, missing indexes, or inefficient plans' as issue_description,
	left(regexp_replace(eq.query, E'[\n\r\t]+', ' ', 'g'), 500) as current_value,
	'Review these queries with EXPLAIN (ANALYZE, BUFFERS) and reduce lock waits or full scans' as recommended_action,
	'https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW \
	 https://www.postgresql.org/docs/current/using-explain.html \
	 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
	3 as severity_order
from
	eq)
union all
-- MEDIUM: Lock-wait-heavy active queries
(with lw as (
select
	pid,
	usename,
	datname,
	client_addr,
	wait_event,
	query_start,
	now() - query_start as runtime,
	query
from
	pg_stat_activity
where
	state = 'active'
	and wait_event_type = 'Lock'
	and query_start is not null
	and now() - query_start > interval '30 seconds'
	and pid <> pg_backend_pid()
order by
	runtime desc
limit 10)
select
	'MEDIUM' as severity,
	'Query Health' as category,
	'Lock-Wait-Heavy Active Queries' as check_name,
	concat_ws(' | ',
		'pid: ' || lw.pid::text,
		'usename: ' || lw.usename,
		'datname: ' || lw.datname,
		'client_address: ' || coalesce(lw.client_addr::text, 'local'),
		'wait_event: ' || coalesce(lw.wait_event, 'unknown'),
		'runtime: ' || to_char(lw.runtime, 'HH24:MI:SS')
	) as object_name,
	'Active queries waiting on locks for extended time can block throughput and cause cascading latency' as issue_description,
	left(regexp_replace(lw.query, E'[\n\r\t]+', ' ', 'g'), 500) as current_value,
	'Reduce transaction duration, enforce consistent lock ordering, and investigate blockers first' as recommended_action,
	'https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW \
	 https://www.postgresql.org/docs/current/explicit-locking.html' as documentation_link,
	3 as severity_order
from
	lw)
union all
-- MEDIUM: Idle in transaction over 5 minutes
(select
	'MEDIUM' as severity,
	'Query Health' as category,
	'Idle In Transaction Over 5 Minutes' as check_name,
	concat_ws(' | ',
		'pid: ' || psa.pid::text,
		'usename: ' || psa.usename,
		'datname: ' || psa.datname,
		'client_address: ' || coalesce(psa.client_addr::text, 'local'),
		'idle_duration: ' || to_char(now() - psa.state_change, 'HH24:MI:SS')
	) as object_name,
	'Sessions left idle in transaction hold snapshots and locks longer than necessary, which can hurt query performance and vacuum progress' as issue_description,
	left(regexp_replace(psa.query, E'[\n\r\t]+', ' ', 'g'), 500) as current_value,
	'Commit or rollback promptly and move application processing outside transaction boundaries' as recommended_action,
	'https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW \
	 https://www.postgresql.org/docs/current/routine-vacuuming.html' as documentation_link,
	3 as severity_order
from
	pg_stat_activity psa
where
	psa.state = 'idle in transaction'
	and psa.state_change is not null
	and now() - psa.state_change > interval '5 minutes'
	and psa.pid <> pg_backend_pid()
order by
	now() - psa.state_change desc)
union all
-- LOW: Roles that have never logged in (with LOGIN rights)
(WITH ur AS (
 SELECT
 r.rolname AS role_name,
 r.rolcreaterole,
 r.rolcanlogin,
 r.rolsuper,
 r.rolvaliduntil,
 array_agg(m.rolname) FILTER (WHERE m.rolname IS NOT NULL) AS member_of
 FROM
 pg_roles r
 LEFT JOIN
 pg_auth_members am ON am.member = r.oid
 LEFT JOIN
 pg_roles m ON m.oid = am.roleid
 WHERE
 r.rolcanlogin = true
 AND r.rolname NOT LIKE 'pg_%'
 AND r.rolname NOT IN ('postgres', 'rds_superuser', 'rdsadmin', 'azure_superuser', 'cloudsqlsuperuser')
 AND NOT EXISTS (
 SELECT 1
 FROM pg_stat_activity psa
 WHERE psa.usename = r.rolname
 )
 AND (
 SELECT coalesce(max(backend_start), '1970-01-01')
 FROM pg_stat_activity
 WHERE usename = r.rolname
 ) = '1970-01-01'
 GROUP BY
 r.rolname, r.rolcreaterole, r.rolcanlogin, r.rolsuper, r.rolvaliduntil
)
SELECT
 'LOW' AS severity,
 'Security Health' AS category,
 'Role Never Logged In' AS check_name,
 ur.role_name AS object_name,
 'Role has LOGIN privilege but has never connected (since stats reset). ' ||
 CASE WHEN ur.rolsuper THEN 'WARNING: Has SUPERUSER privilege. ' ELSE '' END ||
 CASE WHEN ur.rolvaliduntil IS NOT NULL THEN 'Expires: ' || ur.rolvaliduntil::text ELSE 'No expiration set' END AS issue_description,
 'Member of: ' || coalesce(array_to_string(ur.member_of, ', '), 'none') AS current_value,
 'Review if this role is still needed. Consider removing LOGIN privilege or dropping the role if unused.' AS recommended_action,
 'https://www.postgresql.org/docs/current/sql-droprole.html' AS documentation_link,
 4 AS severity_order
FROM
 ur
ORDER BY
 ur.rolsuper DESC,
 ur.role_name)
union all
-- HIGH: listen_addresses wildcard
select
 'HIGH' as severity,
 'Security Health' as category,
 'listen_addresses Wildcard' as check_name,
 'System' as object_name,
 'listen_addresses includes *, so PostgreSQL accepts connections on every network interface. This exposes the server to every network reachable to the host.' as issue_description,
 current_setting('listen_addresses') as current_value,
 'Set listen_addresses to the specific IP(s) the server should bind to (e.g., localhost,127.0.0.1,10.0.0.5). Requires a PostgreSQL restart.' as recommended_action,
 'https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-LISTEN-ADDRESSES' as documentation_link,
 2 as severity_order
where current_setting('listen_addresses') ~ '(^|,)\s*\*\s*(,|$)'
union all
-- LOW: Missing indexes on foreign keys
 select
	'LOW' as severity,
	'Table Health' as category,
	'Missing FK Index' as check_name,
	n.nspname || '.' || t.relname || '.' || string_agg(a.attname, ', ') as object_name,
	'Foreign key constraint missing supporting index for efficient joins' as issue_description,
	'FK constraint without index' as current_value,
	'Consider adding index on foreign key columns for better join performance' as recommended_action,
	'https://www.postgresql.org/docs/current/ddl-constraints.html' as documentation_link,
	4 as severity_order
from
	pg_constraint c
join pg_class t on
	c.conrelid = t.oid
join pg_namespace n on
	t.relnamespace = n.oid
join pg_attribute a on
	a.attrelid = t.oid
	and a.attnum = any(c.conkey)
where
	c.contype = 'f'
	and n.nspname not like all(array['information_schema', 'pg_catalog', 'pg_toast', 'pg_temp%'])
	and not exists (
	select
		1
	from
		pg_index i
	where
		i.indrelid = c.conrelid
		and i.indkey::int2[] @> c.conkey::int2[]
 )
group by
	n.nspname,
	t.relname,
	c.conname,
	1,
	2,
	3,
	5,
	6,
	7,
	8,
	9
union all
-- LOW: Indexes with low usage
(WITH lui AS (
 SELECT
 quote_ident(schemaname) || '.' || quote_ident(indexrelname) AS index_name,
 quote_ident(schemaname) || '.' || quote_ident(relname) AS table_name,
 pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
 pg_relation_size(indexrelid) AS index_size_bytes,
 idx_scan,
 idx_tup_read,
 idx_tup_fetch
 FROM
 pg_stat_user_indexes
 WHERE
 idx_scan > 0
 AND idx_scan < 100
 AND pg_relation_size(indexrelid) > 1024 * 1024 -- > 1MB
)
SELECT
 'LOW' AS severity,
 'Index Health' AS category,
 'Index With Very Low Usage' AS check_name,
 lui.index_name AS object_name,
 'Index on ' || lui.table_name || ' has been scanned only ' || lui.idx_scan ||
 ' times since stats reset. May not be worth the maintenance overhead.' AS issue_description,
 'Scans: ' || lui.idx_scan || ', Tuples read: ' || lui.idx_tup_read ||
 ', Size: ' || lui.index_size AS current_value,
 'Monitor usage over a full business cycle before removing. Verify index is not used for constraints or infrequent but critical queries.' AS recommended_action,
 'https://www.postgresql.org/docs/current/monitoring-stats.html' AS documentation_link,
 4 AS severity_order
FROM
 lui
ORDER BY
 lui.index_size_bytes desc)
union all
-- LOW: Tables with no recent acitivty
(WITH it AS (
 SELECT
 schemaname || '.' || relname AS table_name,
 pg_size_pretty(pg_total_relation_size(relid)) AS table_size,
 pg_total_relation_size(relid) AS table_size_bytes,
 coalesce(seq_scan, 0) + coalesce(idx_scan, 0) AS total_scans,
 coalesce(n_tup_ins, 0) + coalesce(n_tup_upd, 0) + coalesce(n_tup_del, 0) AS total_writes,
 greatest(last_vacuum, last_autovacuum, last_analyze, last_autoanalyze) AS last_maintenance
 FROM
 pg_stat_user_tables
 WHERE
 schemaname NOT LIKE ALL(ARRAY['pg_catalog', 'information_schema', 'pg_toast', 'pg_temp%'])
 AND coalesce(seq_scan, 0) + coalesce(idx_scan, 0) = 0
 AND coalesce(n_tup_ins, 0) + coalesce(n_tup_upd, 0) + coalesce(n_tup_del, 0) = 0
)
SELECT
 'LOW' AS severity,
 'Table Health' AS category,
 'Table With No Activity Since Stats Reset' AS check_name,
 quote_ident(it.table_name) AS object_name,
 'Table has had no reads or writes since stats were last reset. Last maintenance: ' ||
 coalesce(it.last_maintenance::text, 'never') AS issue_description,
 'Total scans: ' || it.total_scans || ', Total writes: ' || it.total_writes ||
 ', Size: ' || it.table_size AS current_value,
 'Review if this table is still needed. Check pg_stat_reset() history to determine stats age. Consider archiving or dropping if no longer in use.' AS recommended_action,
 'https://www.postgresql.org/docs/current/monitoring-stats.html' AS documentation_link,
 4 AS severity_order
FROM
 it
ORDER BY
 it.table_size_bytes DESC)
union all
-- LOW: Check for truely empty tables in the database
(WITH et AS (
 SELECT
 n.nspname || '.' || c.relname AS table_name,
 pg_size_pretty(pg_total_relation_size(c.oid)) AS table_size,
 pg_total_relation_size(c.oid) AS table_size_bytes,
 s.last_vacuum,
 s.last_analyze,
 c.reltuples::bigint AS estimated_rows
 FROM
 pg_class c
 JOIN
 pg_namespace n ON n.oid = c.relnamespace
 LEFT JOIN
 pg_stat_user_tables s ON s.relid = c.oid
 WHERE
 c.relkind = 'r'
 AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
 AND c.reltuples = 0
 AND s.n_live_tup = 0
)
SELECT
 'LOW' AS severity,
 'Table Health' AS category,
 'Empty Table' AS check_name,
 et.table_name AS object_name,
 'Table contains no rows. Last vacuum: ' || coalesce(et.last_vacuum::text, 'never') ||
 ', Last analyze: ' || coalesce(et.last_analyze::text, 'never') AS issue_description,
 '0 rows, Size: ' || et.table_size AS current_value,
 'Review if this table is still needed. May be an abandoned table, pending migration, or staging table that was never cleaned up.' AS recommended_action,
 'https://www.postgresql.org/docs/current/routine-vacuuming.html' AS documentation_link,
 4 AS severity_order
FROM
 et
ORDER BY
 et.table_size_bytes desc)
union all
-- LOW: Tables with zero or only one column
(with sct as (
select
	pc.oid::regclass::text as table_name,
	pg_size_pretty(pg_table_size(pc.oid)) as table_size,
	pg_table_size(pc.oid) as table_size_bytes,
	count(a.attname) as column_count
from
	pg_catalog.pg_class pc
inner join
 pg_catalog.pg_namespace nsp on
	nsp.oid = pc.relnamespace
left join
 pg_catalog.pg_attribute a on
	a.attrelid = pc.oid
	and a.attnum > 0
	and not a.attisdropped
where
	pc.relkind in ('r', 'p')
	and not pc.relispartition
	and nsp.nspname not in ('pg_catalog', 'information_schema', 'pg_toast')
group by
	pc.oid
having
	count(a.attname) <= 1
)
select
	'LOW' as severity,
	'Table Health' as category,
	'Table With Single Or No Columns' as check_name,
	quote_ident(sct.table_name) as object_name,
	'Table has ' || sct.column_count || ' column(s). This may indicate an abandoned table, incomplete migration, or design issue.' as issue_description,
	sct.column_count || ' column(s), Size: ' || sct.table_size as current_value,
	'Review if this table is still needed. Consider removing if unused or completing the schema if it was left incomplete.' as recommended_action,
	'https://www.postgresql.org/docs/current/ddl.html /
https://github.com/mfvanek/pg-index-health-sql/blob/master/sql/tables_with_zero_or_one_column.sql' as documentation_link,
	4 as severity_order
from
	sct)
union all
-- HIGH: Timestamp without time zone
(select
 'HIGH' as severity,
 'Table Health' as category,
 'Timestamp Without Time Zone' as check_name,
 quote_ident(n.nspname) || '.' || quote_ident(c.relname) || '.' || quote_ident(a.attname) as object_name,
 'Column uses timestamp without time zone. PostgreSQL silently strips timezone info on storage; cross-timezone reads and DST transitions can produce wrong results.' as issue_description,
 format_type(a.atttypid, a.atttypmod) as current_value,
 'Migrate to timestamptz: ALTER TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE timestamptz USING ' || quote_ident(a.attname) || ' AT TIME ZONE ''UTC''. Verify application code handles timezone-aware values.' as recommended_action,
 'https://www.postgresql.org/docs/current/datatype-datetime.html' as documentation_link,
 2 as severity_order
from pg_attribute a
join pg_class c on a.attrelid = c.oid
join pg_namespace n on c.relnamespace = n.oid
where a.atttypid = 'pg_catalog.timestamp'::regtype
 and a.attnum > 0
 and not a.attisdropped
 and c.relkind in ('r', 'p')
 and n.nspname not in ('pg_catalog', 'information_schema', 'pg_toast')
 and n.nspname not like 'pg_temp_%'
order by n.nspname, c.relname, a.attnum
limit 100)
union all
-- LOW: Varchar with length limit
(select
 'LOW' as severity,
 'Table Health' as category,
 'Varchar With Length Limit' as check_name,
 quote_ident(table_schema) || '.' || quote_ident(table_name) || '.' || quote_ident(column_name) as object_name,
 'Column declared as varchar(n). The book recommends text when in doubt; length limits can cause silent truncation and require length migrations later.' as issue_description,
 data_type || '(' || character_maximum_length || ')' as current_value,
 'If the length limit is intentional (validation), keep it. Otherwise migrate to text: ALTER TABLE ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || ' ALTER COLUMN ' || quote_ident(column_name) || ' TYPE text USING ' || quote_ident(column_name) || '::text.' as recommended_action,
 'https://www.postgresql.org/docs/current/datatype-character.html' as documentation_link,
 4 as severity_order
from information_schema.columns
where table_schema not in ('pg_catalog', 'information_schema', 'pg_toast')
 and table_schema not like 'pg_temp_%'
 and data_type = 'character varying'
 and character_maximum_length is not null
order by table_schema, table_name, ordinal_position
limit 100)
union all
-- LOW: Serial column legacy
(select
 'LOW' as severity,
 'Table Health' as category,
 'Serial Column Legacy' as check_name,
 quote_ident(n.nspname) || '.' || quote_ident(c.relname) || '.' || quote_ident(a.attname) as object_name,
 'Column uses a serial-style nextval() default. The modern equivalent is GENERATED AS IDENTITY, which integrates better with replication, ownership, and pg_dump.' as issue_description,
 pg_get_expr(d.adbin, d.adrelid) as current_value,
 'Migrate to identity: ALTER TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' ALTER COLUMN ' || quote_ident(a.attname) || ' ADD GENERATED BY DEFAULT AS IDENTITY. Sequence ownership transfers automatically.' as recommended_action,
 'https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-GENERATED-IDENTITY' as documentation_link,
 4 as severity_order
from pg_attrdef d
join pg_attribute a on d.adrelid = a.attrelid and d.adnum = a.attnum
join pg_class c on a.attrelid = c.oid
join pg_namespace n on c.relnamespace = n.oid
where pg_get_expr(d.adbin, d.adrelid) ~ '^nextval'
 and a.attnum > 0 and not a.attisdropped
 and c.relkind in ('r', 'p')
 and n.nspname not in ('pg_catalog', 'information_schema', 'pg_toast')
 and n.nspname not like 'pg_temp_%'
 and a.attidentity = ''
order by n.nspname, c.relname, a.attnum
limit 100)
union all
-- LOW: Rules on tables
(select
 'LOW' as severity,
 'Database Health' as category,
 'Rules On Tables' as check_name,
 quote_ident(n.nspname) || '.' || quote_ident(c.relname) as object_name,
 'Table has a non-view rule attached. Rules on tables are an old mechanism; triggers are clearer and more flexible. Rules on views (which implement views themselves) are useful and expected.' as issue_description,
 r.rulename as current_value,
 'Review whether this rule can be replaced with a trigger. Run \d ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' to see the rule definition, then DROP RULE ' || quote_ident(r.rulename) || ' ON ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' if safe.' as recommended_action,
 'https://www.postgresql.org/docs/current/rules.html' as documentation_link,
 4 as severity_order
from pg_rewrite r
join pg_class c on r.ev_class = c.oid
join pg_namespace n on c.relnamespace = n.oid
where c.relkind = 'r'
 and n.nspname not in ('pg_catalog', 'information_schema', 'pg_toast')
 and n.nspname not like 'pg_temp_%'
order by n.nspname, c.relname, r.rulename
limit 100)
union all
-- LOW: Connections IDLE for 1 > hour
(with ic as (
select
	pid,
	usename,
	application_name,
	client_addr,
	state,
	state_change,
	now() - state_change as idle_duration
from
	pg_stat_activity
where
	state = 'idle'
	and state_change < now() - interval '1 hour'
	and pid <> pg_backend_pid()
)
select
	'LOW' as severity,
	'Connection Health' as category,
	'Idle Connections Over 1 Hour' as check_name,
	ic.usename || ' (PID: ' || ic.pid || ')' as object_name,
	'Connection has been idle for ' ||
 extract(epoch from ic.idle_duration)::int / 3600 || ' hours ' ||
 (extract(epoch from ic.idle_duration)::int % 3600) / 60 || ' minutes. ' ||
 'Application: ' || coalesce(ic.application_name, 'unknown') ||
 ', Client: ' || coalesce(ic.client_addr::text, 'local') as issue_description,
	ic.idle_duration::text as current_value,
	'Review if this connection is still needed. Consider implementing connection pooling (PgBouncer), setting idle_session_timeout, or terminating with pg_terminate_backend(' || ic.pid || ')' as recommended_action,
	'https://www.postgresql.org/docs/current/runtime-config-client.html' as documentation_link,
	4 as severity_order
from
	ic)
union all
-- INFO: Database size and growth
 select
	'INFO' as severity,
	'Database Health' as category,
	'Database Size' as check_name,
	current_database() as object_name,
	'Current database size information' as issue_description,
	pg_size_pretty(pg_database_size(current_database())) as current_value,
	'Monitor growth trends and plan capacity accordingly' as recommended_action,
	'https://www.postgresql.org/docs/current/diskusage.html' as documentation_link,
	5 as severity_order
union all
-- INFO: Version and configuration
 select
	'INFO' as severity,
	'System Info' as category,
	'PostgreSQL Version' as check_name,
	'System' as object_name,
	'Current PostgreSQL version and basic configuration' as issue_description,
	version() as current_value,
	'Keep PostgreSQL updated and review configuration settings' as recommended_action,
	'https://www.postgresql.org/docs/current/upgrading.html' as documentation_link,
	5 as severity_order
union all
-- INFO: Installed Extensions
 select
	'INFO' as severity,
	'System Info' as category,
	'Installed Extension' as check_name,
	'System' as object_name,
	'Installed Postgres Extension' as issue_description,
	 pe.extname || ':' || pe.extversion as current_value,
	'Before updating to the latest minor/major version of PG, verify extension compatability' as recommended_action,
	'https://youtu.be/mpEdQm3TpE0?si=VMcHBo1VnDfGZvtI&t=937' as documentation_link,
	--Link is from a fantastic talk from SCALE 22x on bugging pg_extension maintainers!
	5 as severity_order
from
	pg_extension pe
union all
-- INFO: Server Uptime
 select
	'INFO' as severity,
	'System Info' as category,
	'Server Uptime' as check_name,
	'System' as object_name,
	'Current Uptime of Server' as issue_description,
	(current_timestamp - pg_postmaster_start_time())::text as current_value,
	'No Recommendation - Informational' as recommended_action,
	'N/A' as documentation_link,
	5 as severity_order
union all
-- INFO: Log Directory
(with ld as (
select
	current_setting('log_directory') as log_directory
 )
select
	'INFO' as severity,
	'System Info' as category,
	'Is Logging Enabled' as check_name,
	'System' as object_name,
	'If no log file is present, this indicates logging is not enabled' as issue_description,
	ld.log_directory as current_value,
	'Logging enabled will assist with troubleshooting future issues. Dont you like logs?' as recommended_action,
	'For self-hosting: https://www.postgresql.org/docs/current/runtime-config-logging.html /
 For AWS Aurora/RDS: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.html /
 For GCP Cloud SQL: https://docs.cloud.google.com/sql/docs/postgres/flags /
 For Azure Database for PostgreSQL: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-server-parameters
 ' as documentation_link,
	5 as severity_order
from
	ld)
union all
-- INFO: Log File(s) Size(s)
(with ls as (
select
	ROUND(sum(stat.size) / (1024.0 * 1024.0), 2) || ' MB' as size_mb
from
	pg_ls_dir(current_setting('log_directory')) as logs
cross join lateral
	 pg_stat_file(current_setting('log_directory') || '/' || logs) as stat)
select
	'INFO' as severity,
	'System Info' as category,
	'Size of ALL Logfiles combined' as check_name,
	'System' as object_name,
	'Monitoring your logfile size will prevent from filling up storage (or expanding your storage in cloud managed). This can also lead to the server cashing when the logfile cannot be saved.' as issue_description,
	ls.size_mb as current_value,
	'Set log_rotation_age and size for proper rotation of log files. This will prevent runaway log sizes.' as recommended_action,
	'For self-hosting:https://www.postgresql.org/docs/current/runtime-config-logging.html /
 For AWS Aurora/RDS: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.html /
 For GCP Cloud SQL: https://docs.cloud.google.com/sql/docs/postgres/flags /
 For Azure Database for PostgreSQL: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-server-parameters
 ' as documentation_link,
	5 as severity_order
from
	ls)
union all
-- INFO: shared_buffers current value
select
 'INFO' as severity,
 'System Health' as category,
 'shared_buffers Setting' as check_name,
 'System' as object_name,
 'Current value of shared_buffers. Recommended: ~25% of total system RAM for dedicated database servers.' as issue_description,
 current_setting('shared_buffers') as current_value,
 'No action needed if already tuned. For dedicated DB servers with 8GB+ RAM, target 25% of total RAM. Changes require a PostgreSQL restart.' as recommended_action,
 'https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-SHARED-BUFFERS' as documentation_link,
 5 as severity_order
union all
-- HIGH: shared_buffers still at 128MB PostgreSQL default
select
 'HIGH' as severity,
 'System Health' as category,
 'shared_buffers At Default' as check_name,
 'System' as object_name,
 'shared_buffers is set to the PostgreSQL default of 128MB. On any real workload this is almost certainly too low.' as issue_description,
 current_setting('shared_buffers') as current_value,
 'Set shared_buffers to approximately 25% of total system RAM (e.g., 2GB on an 8GB server). Requires a PostgreSQL restart.' as recommended_action,
 'https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-SHARED-BUFFERS' as documentation_link,
 2 as severity_order
where pg_size_bytes(current_setting('shared_buffers')) = pg_size_bytes('128MB')
union all
-- INFO: work_mem current value
select
 'INFO' as severity,
 'System Health' as category,
 'work_mem Setting' as check_name,
 'System' as object_name,
 'Current value of work_mem. Allocated per sort/hash operation per session: multiply by max_connections and parallel workers to estimate peak memory consumption.' as issue_description,
 current_setting('work_mem') || ' (max_connections: ' || current_setting('max_connections') || ')' as current_value,
 'For OLTP workloads, 16-32MB is a common starting point. Monitor pg_stat_statements for temp file spills to determine if higher is warranted. Use SET work_mem per-session for large one-off queries rather than setting globally.' as recommended_action,
 'https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM' as documentation_link,
 5 as severity_order
union all
-- MEDIUM: work_mem still at 4MB PostgreSQL default
select
 'MEDIUM' as severity,
 'System Health' as category,
 'work_mem At Default' as check_name,
 'System' as object_name,
 'work_mem is set to the PostgreSQL default of 4MB. On modern hardware this often causes unnecessary sort and hash spills to disk.' as issue_description,
 current_setting('work_mem') as current_value,
 'Consider raising work_mem to 16-32MB for OLTP workloads. Be aware that work_mem is allocated per operation per session: high concurrency multiplies total memory usage.' as recommended_action,
 'https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM' as documentation_link,
 3 as severity_order
where pg_size_bytes(current_setting('work_mem')) = pg_size_bytes('4MB')
union all
-- MEDIUM: Query duration logging disabled
select
 'MEDIUM' as severity,
 'System Health' as category,
 'Query Duration Logging Disabled' as check_name,
 'System' as object_name,
 'log_min_duration_statement is set to -1, so no slow-query log is produced. Without duration tracking, regressions and runaway queries go undetected.' as issue_description,
 '-1 (disabled)' as current_value,
 'Set log_min_duration_statement to a positive threshold (e.g., 1000ms) to log every query taking longer. Pair with log_line_prefix=%m [%p] %u@%d and consider auto_explain for plan capture.' as recommended_action,
 'https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT' as documentation_link,
 3 as severity_order
where current_setting('log_min_duration_statement') = '-1'
union all
-- INFO: effective_cache_size current value
select
 'INFO' as severity,
 'System Health' as category,
 'effective_cache_size Setting' as check_name,
 'System' as object_name,
 'Current value of effective_cache_size. Tells the query planner how much memory is available for disk caching. Does not allocate memory: purely advisory.' as issue_description,
 current_setting('effective_cache_size') as current_value,
 'Set to ~50-75% of total system RAM (shared_buffers + expected OS page cache). Underestimates cause the planner to prefer nested loops over index scans.' as recommended_action,
 'https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE' as documentation_link,
 5 as severity_order
union all
-- INFO: maintenance_work_mem current value
select
 'INFO' as severity,
 'System Health' as category,
 'maintenance_work_mem Setting' as check_name,
 'System' as object_name,
 'Current value of maintenance_work_mem. Used by VACUUM, CREATE INDEX, ALTER TABLE, and each autovacuum worker.' as issue_description,
 current_setting('maintenance_work_mem') as current_value,
 'Consider 256MB-1GB on modern hardware. Higher values speed up index builds and autovacuum on large tables. Changes take effect immediately for new sessions.' as recommended_action,
 'https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM' as documentation_link,
 5 as severity_order
union all
-- INFO: Transaction ID wraparound risk per database
select
 'INFO' as severity,
 'System Health' as category,
 'Transaction ID Wraparound Risk' as check_name,
 datname as object_name,
 'Age of the oldest unfrozen transaction ID in this database. PostgreSQL must freeze XIDs before reaching ~2.1 billion to prevent data loss from wraparound.' as issue_description,
 datname || ': XID age ' || trim(to_char(age(datfrozenxid), 'FM999,999,999,990')) ||
 ' (' || round(age(datfrozenxid)::numeric * 100 / 2000000000, 1)::text ||
 '% of wraparound window, ~' ||
 trim(to_char(greatest(2000000000::bigint - age(datfrozenxid)::bigint, 0), 'FM999,999,999,990')) ||
 ' remaining)' as current_value,
 'Run VACUUM FREEZE on databases approaching high XID age. Ensure autovacuum is enabled and not blocked. Monitor databases with age > 500,000,000.' as recommended_action,
 'https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND' as documentation_link,
 5 as severity_order
from
 pg_database
where
 datallowconn = true
union all
-- INFO: Checkpoint statistics (PG15/16: pg_stat_bgwriter, PG17+: pg_stat_checkpointer)
select
 'INFO' as severity,
 'System Health' as category,
 'Checkpoint Stats' as check_name,
 'System' as object_name,
 'Checkpoint activity since stats last reset. Forced checkpoints occur when WAL fills up before the scheduled interval: high ratios suggest max_wal_size may be too small. PG15/16 reads from pg_stat_bgwriter; PG17+ reads from pg_stat_checkpointer.' as issue_description,
 _pg_firstaid_checkpoint_stats() as current_value,
 'If forced checkpoints are consistently above 50% of total, consider increasing max_wal_size. Reset stats with: SELECT pg_stat_reset_shared(''' ||
 case
 when current_setting('server_version_num')::int >= 170000 then 'checkpointer'
 else 'bgwriter'
 end ||
 ''').' as recommended_action,
 'https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-BGWRITER-VIEW' as documentation_link,
 5 as severity_order
union all
-- INFO: Server role (primary vs standby)
select
 'INFO' as severity,
 'System Info' as category,
 'Server Role' as check_name,
 'System' as object_name,
 'Whether this server is operating as a primary or standby replica. Context for interpreting other checks: some checks are only relevant on standbys.' as issue_description,
 case
 when pg_is_in_recovery() then 'Standby (replica)'
 else 'Primary'
 end as current_value,
 'No action needed: informational.' as recommended_action,
 'https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE' as documentation_link,
 5 as severity_order
union all
-- INFO: Connection utilization
select
 'INFO' as severity,
 'System Health' as category,
 'Connection Utilization' as check_name,
 'System' as object_name,
 'Current connection usage as a percentage of max_connections. Includes all connection states, not just active queries.' as issue_description,
 count(*)::text || ' total / ' || current_setting('max_connections') || ' max (' ||
 round(100.0 * count(*) / current_setting('max_connections')::int, 1)::text || '% used)' as current_value,
 'If consistently above 80%, consider a connection pooler such as PgBouncer. Reserve headroom for superuser connections (superuser_reserved_connections).' as recommended_action,
 'https://www.postgresql.org/docs/current/runtime-config-connection.html' as documentation_link,
 5 as severity_order
from
 pg_stat_activity
union all
-- HIGH: Known CVEs affecting the running PostgreSQL version. Curated from
-- https://www.postgresql.org/support/security/: affected_min and fixed_in
-- are server_version_num integers (e.g. 150004 for 15.4). Covers PG 15-18.
(with cve_data(cve_id, cvss, summary, affected_min, fixed_in, doc_link) as (
 values
 -- GENERATED cves BEGIN (do not edit; regenerate via tools/generate_cve_sql.py)
        ('CVE-2023-5869', 8.8, 'pg_dump integer overflow (32-bit builds) can execute arbitrary code at restore', 150000, 150005, 'https://www.postgresql.org/support/security/CVE-2023-5869/'),
        ('CVE-2023-5869', 8.8, 'pg_dump integer overflow (32-bit builds) can execute arbitrary code at restore', 160000, 160001, 'https://www.postgresql.org/support/security/CVE-2023-5869/'),
        ('CVE-2024-10979', 8.8, 'PL/Perl environment variable changes execute arbitrary code', 150000, 150009, 'https://www.postgresql.org/support/security/CVE-2024-10979/'),
        ('CVE-2024-10979', 8.8, 'PL/Perl environment variable changes execute arbitrary code', 160000, 160005, 'https://www.postgresql.org/support/security/CVE-2024-10979/'),
        ('CVE-2024-10979', 8.8, 'PL/Perl environment variable changes execute arbitrary code', 170000, 170001, 'https://www.postgresql.org/support/security/CVE-2024-10979/'),
        ('CVE-2024-7348', 8.8, 'pg_dump --restore uses an invalid type OID, allowing arbitrary SQL execution from a crafted dump', 150000, 150008, 'https://www.postgresql.org/support/security/CVE-2024-7348/'),
        ('CVE-2024-7348', 8.8, 'pg_dump --restore uses an invalid type OID, allowing arbitrary SQL execution from a crafted dump', 160000, 160004, 'https://www.postgresql.org/support/security/CVE-2024-7348/'),
        ('CVE-2025-8714', 8.8, 'PostgreSQL pg_dump lets superuser of origin server execute arbitrary code in psql client', 150000, 150014, 'https://www.postgresql.org/support/security/CVE-2025-8714/'),
        ('CVE-2025-8714', 8.8, 'PostgreSQL pg_dump lets superuser of origin server execute arbitrary code in psql client', 160000, 160010, 'https://www.postgresql.org/support/security/CVE-2025-8714/'),
        ('CVE-2025-8714', 8.8, 'PostgreSQL pg_dump lets superuser of origin server execute arbitrary code in psql client', 170000, 170006, 'https://www.postgresql.org/support/security/CVE-2025-8714/'),
        ('CVE-2025-8715', 8.8, 'PostgreSQL pg_dump newline in object name executes arbitrary code in psql client and in restore target server', 150000, 150014, 'https://www.postgresql.org/support/security/CVE-2025-8715/'),
        ('CVE-2025-8715', 8.8, 'PostgreSQL pg_dump newline in object name executes arbitrary code in psql client and in restore target server', 160000, 160010, 'https://www.postgresql.org/support/security/CVE-2025-8715/'),
        ('CVE-2025-8715', 8.8, 'PostgreSQL pg_dump newline in object name executes arbitrary code in psql client and in restore target server', 170000, 170006, 'https://www.postgresql.org/support/security/CVE-2025-8715/'),
        ('CVE-2026-14662', 8.8, 'PostgreSQL tsvector and tsquery undersize allocations, via integer wraparound', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-14662/'),
        ('CVE-2026-14662', 8.8, 'PostgreSQL tsvector and tsquery undersize allocations, via integer wraparound', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-14662/'),
        ('CVE-2026-14662', 8.8, 'PostgreSQL tsvector and tsquery undersize allocations, via integer wraparound', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-14662/'),
        ('CVE-2026-14662', 8.8, 'PostgreSQL tsvector and tsquery undersize allocations, via integer wraparound', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-14662/'),
        ('CVE-2026-14664', 8.8, 'PostgreSQL regexp heap buffer overflow executes arbitrary code', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-14664/'),
        ('CVE-2026-14664', 8.8, 'PostgreSQL regexp heap buffer overflow executes arbitrary code', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-14664/'),
        ('CVE-2026-14664', 8.8, 'PostgreSQL regexp heap buffer overflow executes arbitrary code', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-14664/'),
        ('CVE-2026-14664', 8.8, 'PostgreSQL regexp heap buffer overflow executes arbitrary code', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-14664/'),
        ('CVE-2026-14669', 8.8, 'PostgreSQL to_char heap buffer overflow executes arbitrary code', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-14669/'),
        ('CVE-2026-14669', 8.8, 'PostgreSQL to_char heap buffer overflow executes arbitrary code', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-14669/'),
        ('CVE-2026-14669', 8.8, 'PostgreSQL to_char heap buffer overflow executes arbitrary code', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-14669/'),
        ('CVE-2026-14669', 8.8, 'PostgreSQL to_char heap buffer overflow executes arbitrary code', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-14669/'),
        ('CVE-2026-14670', 8.8, 'PostgreSQL plperl tied object heap buffer overflow executes arbitrary code', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-14670/'),
        ('CVE-2026-14670', 8.8, 'PostgreSQL plperl tied object heap buffer overflow executes arbitrary code', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-14670/'),
        ('CVE-2026-14670', 8.8, 'PostgreSQL plperl tied object heap buffer overflow executes arbitrary code', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-14670/'),
        ('CVE-2026-14670', 8.8, 'PostgreSQL plperl tied object heap buffer overflow executes arbitrary code', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-14670/'),
        ('CVE-2026-14671', 8.8, 'PostgreSQL refint plan cache type confusion executes arbitrary code', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-14671/'),
        ('CVE-2026-14671', 8.8, 'PostgreSQL refint plan cache type confusion executes arbitrary code', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-14671/'),
        ('CVE-2026-14671', 8.8, 'PostgreSQL refint plan cache type confusion executes arbitrary code', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-14671/'),
        ('CVE-2026-14671', 8.8, 'PostgreSQL refint plan cache type confusion executes arbitrary code', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-14671/'),
        ('CVE-2026-14676', 8.8, 'PostgreSQL pg_stat_statements heap buffer overflow executes arbitrary code', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-14676/'),
        ('CVE-2026-14677', 8.8, 'PostgreSQL 32-bit pltcl and plperl undersize allocations, via integer wraparound', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-14677/'),
        ('CVE-2026-14677', 8.8, 'PostgreSQL 32-bit pltcl and plperl undersize allocations, via integer wraparound', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-14677/'),
        ('CVE-2026-14677', 8.8, 'PostgreSQL 32-bit pltcl and plperl undersize allocations, via integer wraparound', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-14677/'),
        ('CVE-2026-14677', 8.8, 'PostgreSQL 32-bit pltcl and plperl undersize allocations, via integer wraparound', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-14677/'),
        ('CVE-2026-14680', 8.8, 'PostgreSQL type confusion via "internal" arguments', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-14680/'),
        ('CVE-2026-14680', 8.8, 'PostgreSQL type confusion via "internal" arguments', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-14680/'),
        ('CVE-2026-14680', 8.8, 'PostgreSQL type confusion via "internal" arguments', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-14680/'),
        ('CVE-2026-14680', 8.8, 'PostgreSQL type confusion via "internal" arguments', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-14680/'),
        ('CVE-2026-15741', 8.8, 'PostgreSQL expression deparse allows SQL injection via EXTRACT argument', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-15741/'),
        ('CVE-2026-15741', 8.8, 'PostgreSQL expression deparse allows SQL injection via EXTRACT argument', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-15741/'),
        ('CVE-2026-15741', 8.8, 'PostgreSQL expression deparse allows SQL injection via EXTRACT argument', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-15741/'),
        ('CVE-2026-15741', 8.8, 'PostgreSQL expression deparse allows SQL injection via EXTRACT argument', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-15741/'),
        ('CVE-2026-15742', 8.8, 'PostgreSQL fuzzystrmatch writes effectively-arbitrary addresses, via integer wraparound', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-15742/'),
        ('CVE-2026-15742', 8.8, 'PostgreSQL fuzzystrmatch writes effectively-arbitrary addresses, via integer wraparound', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-15742/'),
        ('CVE-2026-15742', 8.8, 'PostgreSQL fuzzystrmatch writes effectively-arbitrary addresses, via integer wraparound', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-15742/'),
        ('CVE-2026-15742', 8.8, 'PostgreSQL fuzzystrmatch writes effectively-arbitrary addresses, via integer wraparound', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-15742/'),
        ('CVE-2026-16238', 8.8, 'PostgreSQL type confusion in pg_restore_attribute_stats() executes arbitrary code', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-16238/'),
        ('CVE-2026-16239', 8.8, 'Type confusion in cursor CLOSE + DECLARE executes arbitrary code via portal/cursor re-creation with different types', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-16239/'),
        ('CVE-2026-16239', 8.8, 'Type confusion in cursor CLOSE + DECLARE executes arbitrary code via portal/cursor re-creation with different types', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-16239/'),
        ('CVE-2026-16239', 8.8, 'Type confusion in cursor CLOSE + DECLARE executes arbitrary code via portal/cursor re-creation with different types', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-16239/'),
        ('CVE-2026-16239', 8.8, 'Type confusion in cursor CLOSE + DECLARE executes arbitrary code via portal/cursor re-creation with different types', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-16239/'),
        ('CVE-2026-18408', 8.8, 'PostgreSQL psql \unrestrict lets superuser of pg_dump origin server execute arbitrary code in psql client', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-18408/'),
        ('CVE-2026-18408', 8.8, 'PostgreSQL psql \unrestrict lets superuser of pg_dump origin server execute arbitrary code in psql client', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-18408/'),
        ('CVE-2026-18408', 8.8, 'PostgreSQL psql \unrestrict lets superuser of pg_dump origin server execute arbitrary code in psql client', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-18408/'),
        ('CVE-2026-18408', 8.8, 'PostgreSQL psql \unrestrict lets superuser of pg_dump origin server execute arbitrary code in psql client', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-18408/'),
        ('CVE-2026-19385', 8.8, 'pg_dump heap buffer overflow executes arbitrary code', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-19385/'),
        ('CVE-2026-19385', 8.8, 'pg_dump heap buffer overflow executes arbitrary code', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-19385/'),
        ('CVE-2026-19385', 8.8, 'pg_dump heap buffer overflow executes arbitrary code', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-19385/'),
        ('CVE-2026-19385', 8.8, 'pg_dump heap buffer overflow executes arbitrary code', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-19385/'),
        ('CVE-2026-2004', 8.8, 'PostgreSQL intarray missing validation of type of input to selectivity estimator executes arbitrary code', 150000, 150016, 'https://www.postgresql.org/support/security/CVE-2026-2004/'),
        ('CVE-2026-2004', 8.8, 'PostgreSQL intarray missing validation of type of input to selectivity estimator executes arbitrary code', 160000, 160012, 'https://www.postgresql.org/support/security/CVE-2026-2004/'),
        ('CVE-2026-2004', 8.8, 'PostgreSQL intarray missing validation of type of input to selectivity estimator executes arbitrary code', 170000, 170008, 'https://www.postgresql.org/support/security/CVE-2026-2004/'),
        ('CVE-2026-2004', 8.8, 'PostgreSQL intarray missing validation of type of input to selectivity estimator executes arbitrary code', 180000, 180002, 'https://www.postgresql.org/support/security/CVE-2026-2004/'),
        ('CVE-2026-2005', 8.8, 'PostgreSQL pgcrypto heap buffer overflow executes arbitrary code', 150000, 150016, 'https://www.postgresql.org/support/security/CVE-2026-2005/'),
        ('CVE-2026-2005', 8.8, 'PostgreSQL pgcrypto heap buffer overflow executes arbitrary code', 160000, 160012, 'https://www.postgresql.org/support/security/CVE-2026-2005/'),
        ('CVE-2026-2005', 8.8, 'PostgreSQL pgcrypto heap buffer overflow executes arbitrary code', 170000, 170008, 'https://www.postgresql.org/support/security/CVE-2026-2005/'),
        ('CVE-2026-2005', 8.8, 'PostgreSQL pgcrypto heap buffer overflow executes arbitrary code', 180000, 180002, 'https://www.postgresql.org/support/security/CVE-2026-2005/'),
        ('CVE-2026-2006', 8.8, 'PostgreSQL missing validation of multibyte character length executes arbitrary code', 150000, 150016, 'https://www.postgresql.org/support/security/CVE-2026-2006/'),
        ('CVE-2026-2006', 8.8, 'PostgreSQL missing validation of multibyte character length executes arbitrary code', 160000, 160012, 'https://www.postgresql.org/support/security/CVE-2026-2006/'),
        ('CVE-2026-2006', 8.8, 'PostgreSQL missing validation of multibyte character length executes arbitrary code', 170000, 170008, 'https://www.postgresql.org/support/security/CVE-2026-2006/'),
        ('CVE-2026-2006', 8.8, 'PostgreSQL missing validation of multibyte character length executes arbitrary code', 180000, 180002, 'https://www.postgresql.org/support/security/CVE-2026-2006/'),
        ('CVE-2026-6473', 8.8, 'PostgreSQL server undersizes allocations, via integer wraparound', 150000, 150018, 'https://www.postgresql.org/support/security/CVE-2026-6473/'),
        ('CVE-2026-6473', 8.8, 'PostgreSQL server undersizes allocations, via integer wraparound', 160000, 160014, 'https://www.postgresql.org/support/security/CVE-2026-6473/'),
        ('CVE-2026-6473', 8.8, 'PostgreSQL server undersizes allocations, via integer wraparound', 170000, 170010, 'https://www.postgresql.org/support/security/CVE-2026-6473/'),
        ('CVE-2026-6473', 8.8, 'PostgreSQL server undersizes allocations, via integer wraparound', 180000, 180004, 'https://www.postgresql.org/support/security/CVE-2026-6473/'),
        ('CVE-2026-6475', 8.8, 'PostgreSQL pg_basebackup and pg_rewind can overwrite unrelated files of origin superuser choice', 150000, 150018, 'https://www.postgresql.org/support/security/CVE-2026-6475/'),
        ('CVE-2026-6475', 8.8, 'PostgreSQL pg_basebackup and pg_rewind can overwrite unrelated files of origin superuser choice', 160000, 160014, 'https://www.postgresql.org/support/security/CVE-2026-6475/'),
        ('CVE-2026-6475', 8.8, 'PostgreSQL pg_basebackup and pg_rewind can overwrite unrelated files of origin superuser choice', 170000, 170010, 'https://www.postgresql.org/support/security/CVE-2026-6475/'),
        ('CVE-2026-6475', 8.8, 'PostgreSQL pg_basebackup and pg_rewind can overwrite unrelated files of origin superuser choice', 180000, 180004, 'https://www.postgresql.org/support/security/CVE-2026-6475/'),
        ('CVE-2026-6477', 8.8, 'PostgreSQL libpq lo_* functions let server superuser overwrite client stack memory', 150000, 150018, 'https://www.postgresql.org/support/security/CVE-2026-6477/'),
        ('CVE-2026-6477', 8.8, 'PostgreSQL libpq lo_* functions let server superuser overwrite client stack memory', 160000, 160014, 'https://www.postgresql.org/support/security/CVE-2026-6477/'),
        ('CVE-2026-6477', 8.8, 'PostgreSQL libpq lo_* functions let server superuser overwrite client stack memory', 170000, 170010, 'https://www.postgresql.org/support/security/CVE-2026-6477/'),
        ('CVE-2026-6477', 8.8, 'PostgreSQL libpq lo_* functions let server superuser overwrite client stack memory', 180000, 180004, 'https://www.postgresql.org/support/security/CVE-2026-6477/'),
        ('CVE-2026-6637', 8.8, 'PostgreSQL refint allows stack buffer overflow and SQL injection', 150000, 150018, 'https://www.postgresql.org/support/security/CVE-2026-6637/'),
        ('CVE-2026-6637', 8.8, 'PostgreSQL refint allows stack buffer overflow and SQL injection', 160000, 160014, 'https://www.postgresql.org/support/security/CVE-2026-6637/'),
        ('CVE-2026-6637', 8.8, 'PostgreSQL refint allows stack buffer overflow and SQL injection', 170000, 170010, 'https://www.postgresql.org/support/security/CVE-2026-6637/'),
        ('CVE-2026-6637', 8.8, 'PostgreSQL refint allows stack buffer overflow and SQL injection', 180000, 180004, 'https://www.postgresql.org/support/security/CVE-2026-6637/'),
        ('CVE-2026-14679', 8.2, 'PostgreSQL stack buffer overflow in argument match writes 0x0 and 0x1 to server memory', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-14679/'),
        ('CVE-2026-14679', 8.2, 'PostgreSQL stack buffer overflow in argument match writes 0x0 and 0x1 to server memory', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-14679/'),
        ('CVE-2026-14679', 8.2, 'PostgreSQL stack buffer overflow in argument match writes 0x0 and 0x1 to server memory', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-14679/'),
        ('CVE-2026-14679', 8.2, 'PostgreSQL stack buffer overflow in argument match writes 0x0 and 0x1 to server memory', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-14679/'),
        ('CVE-2026-2007', 8.2, 'PostgreSQL pg_trgm heap buffer overflow writes pattern onto server memory', 180000, 180002, 'https://www.postgresql.org/support/security/CVE-2026-2007/'),
        ('CVE-2025-1094', 8.1, 'PostgreSQL quoting APIs miss neutralizing quoting syntax in text that fails encoding validation', 150000, 150011, 'https://www.postgresql.org/support/security/CVE-2025-1094/'),
        ('CVE-2025-1094', 8.1, 'PostgreSQL quoting APIs miss neutralizing quoting syntax in text that fails encoding validation', 160000, 160007, 'https://www.postgresql.org/support/security/CVE-2025-1094/'),
        ('CVE-2025-1094', 8.1, 'PostgreSQL quoting APIs miss neutralizing quoting syntax in text that fails encoding validation', 170000, 170003, 'https://www.postgresql.org/support/security/CVE-2025-1094/'),
        ('CVE-2026-14668', 8.1, 'PostgreSQL ctid type confusion in selectivity estimator discloses derivative of arbitrary read', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-14668/'),
        ('CVE-2026-14668', 8.1, 'PostgreSQL ctid type confusion in selectivity estimator discloses derivative of arbitrary read', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-14668/'),
        ('CVE-2026-14668', 8.1, 'PostgreSQL ctid type confusion in selectivity estimator discloses derivative of arbitrary read', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-14668/'),
        ('CVE-2026-14668', 8.1, 'PostgreSQL ctid type confusion in selectivity estimator discloses derivative of arbitrary read', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-14668/'),
        ('CVE-2026-6464', 8.1, 'PostgreSQL psql COPY FROM STDIN early failure processes data lines as psql commands', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-6464/'),
        ('CVE-2026-6464', 8.1, 'PostgreSQL psql COPY FROM STDIN early failure processes data lines as psql commands', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-6464/'),
        ('CVE-2026-6464', 8.1, 'PostgreSQL psql COPY FROM STDIN early failure processes data lines as psql commands', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-6464/'),
        ('CVE-2026-6464', 8.1, 'PostgreSQL psql COPY FROM STDIN early failure processes data lines as psql commands', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-6464/'),
        ('CVE-2024-0985', 8.0, 'Non-owner REFRESH MATERIALIZED VIEW CONCURRENTLY executes arbitrary SQL', 150000, 150006, 'https://www.postgresql.org/support/security/CVE-2024-0985/'),
        ('CVE-2024-0985', 8.0, 'Non-owner REFRESH MATERIALIZED VIEW CONCURRENTLY executes arbitrary SQL', 160000, 160002, 'https://www.postgresql.org/support/security/CVE-2024-0985/'),
        ('CVE-2023-39417', 7.5, 'Extension script @substitutions@ within quoting allows SQL injection', 150000, 150004, 'https://www.postgresql.org/support/security/CVE-2023-39417/'),
        ('CVE-2023-39417', 7.5, 'Extension script @substitutions@ within quoting allows SQL injection', 160000, 160001, 'https://www.postgresql.org/support/security/CVE-2023-39417/'),
        ('CVE-2026-6479', 7.5, 'PostgreSQL SSL/GSS init causes denial of service, via uncontrolled recursion', 150000, 150018, 'https://www.postgresql.org/support/security/CVE-2026-6479/'),
        ('CVE-2026-6479', 7.5, 'PostgreSQL SSL/GSS init causes denial of service, via uncontrolled recursion', 160000, 160014, 'https://www.postgresql.org/support/security/CVE-2026-6479/'),
        ('CVE-2026-6479', 7.5, 'PostgreSQL SSL/GSS init causes denial of service, via uncontrolled recursion', 170000, 170010, 'https://www.postgresql.org/support/security/CVE-2026-6479/'),
        ('CVE-2026-6479', 7.5, 'PostgreSQL SSL/GSS init causes denial of service, via uncontrolled recursion', 180000, 180004, 'https://www.postgresql.org/support/security/CVE-2026-6479/'),
        ('CVE-2023-2454', 7.2, 'CREATE SCHEMA ... schema_element defeats protective search_path changes', 150000, 150003, 'https://www.postgresql.org/support/security/CVE-2023-2454/'),
        ('CVE-2026-6471', 7.2, 'Logical decoding can dlopen arbitrary file via choice of decoder plugin (REPLICATION privilege required)', 150000, 150019, 'https://www.postgresql.org/support/security/CVE-2026-6471/'),
        ('CVE-2026-6471', 7.2, 'Logical decoding can dlopen arbitrary file via choice of decoder plugin (REPLICATION privilege required)', 160000, 160015, 'https://www.postgresql.org/support/security/CVE-2026-6471/'),
        ('CVE-2026-6471', 7.2, 'Logical decoding can dlopen arbitrary file via choice of decoder plugin (REPLICATION privilege required)', 170000, 170011, 'https://www.postgresql.org/support/security/CVE-2026-6471/'),
        ('CVE-2026-6471', 7.2, 'Logical decoding can dlopen arbitrary file via choice of decoder plugin (REPLICATION privilege required)', 180000, 180005, 'https://www.postgresql.org/support/security/CVE-2026-6471/'),
        ('CVE-2026-6476', 7.2, 'PostgreSQL pg_createsubscriber allows SQL injection via subscription name', 170000, 170010, 'https://www.postgresql.org/support/security/CVE-2026-6476/'),
        ('CVE-2026-6476', 7.2, 'PostgreSQL pg_createsubscriber allows SQL injection via subscription name', 180000, 180004, 'https://www.postgresql.org/support/security/CVE-2026-6476/')
-- GENERATED cves END
),
running_version as (
 select current_setting('server_version_num')::int as version_num
)
select
 'HIGH' as severity,
 'Security Health' as category,
 'Known CVE Affecting Your Version' as check_name,
 c.cve_id as object_name,
 'CVSS ' || c.cvss::text || ': ' || c.summary as issue_description,
 'server_version_num=' || r.version_num::text ||
 ' (' || current_setting('server_version') || ')' ||
 ', first fixed in version_num=' || c.fixed_in::text as current_value,
 'Upgrade to first PostgreSQL minor >= ' || c.fixed_in::text ||
 ' (or apply distro backport). See ' || c.doc_link as recommended_action,
 c.doc_link as documentation_link,
 2 as severity_order
from cve_data c
cross join running_version r
where r.version_num >= c.affected_min
 and r.version_num < c.fixed_in)
union all
-- MEDIUM: Notable known bugs affecting the running PostgreSQL version. Curated
-- from https://www.postgresql.org/docs/release/: non-CVE fixes users hit in
-- production. affected_min/fixed_in are server_version_num integers.
(with issue_data(issue_id, summary, affected_min, fixed_in, doc_link) as (
 values
 -- GENERATED bugs BEGIN (do not edit; regenerate via tools/generate_cve_sql.py)
        ('PG15-025228104', 'Fix crash after out-of-memory failure partway through creation of a cache entry for a text search dictionary (Tom Lane) §', 150000, 150019, 'https://www.postgresql.org/docs/release/15.19/'),
        ('PG15-0fb88979b', 'Fix memory-safety bugs in processing of incorrect ispell/hunspell dictionary files (Andrey Rachitskiy) §', 150000, 150019, 'https://www.postgresql.org/docs/release/15.19/'),
        ('PG15-127a0673f', 'Fix buffer overrun in regexp match/split functions (Masahiko Sawada) §', 150000, 150019, 'https://www.postgresql.org/docs/release/15.19/'),
        ('PG15-1bec6b1c1', 'In contrib/ltree, fix integer overflow in comparisons (Ayush Tiwari) §', 150000, 150019, 'https://www.postgresql.org/docs/release/15.19/'),
        ('PG15-1e9e62193', 'Fix possible PANIC due to concurrent drop of pgstats entries when track_functions is enabled (Sami Imseih, Michael Paquier) § § §', 150000, 150019, 'https://www.postgresql.org/docs/release/15.19/'),
        ('PG15-9b2a6ccc4', 'In PL/Perl, avoid NULL pointer dereference crash when working with an invalid PostgreSQL::InServer::ARRAY object', 150000, 150019, 'https://www.postgresql.org/docs/release/15.19/'),
        ('PG15-bc5775149', 'Fix crash with namespace nodes in contrib/xml2''s xpath_nodeset() function', 150000, 150019, 'https://www.postgresql.org/docs/release/15.19/'),
        ('PG15-d39b9eed0', 'Prevent satisfies_hash_partition() from crashing with VARIADIC NULL', 150000, 150019, 'https://www.postgresql.org/docs/release/15.19/'),
        ('PG15-0b196d3db', 'Prevent buffer overruns when parsing an affix file for an Ispell dictionary', 150000, 150018, 'https://www.postgresql.org/docs/release/15.18/'),
        ('PG15-34c18a225', 'In contrib/postgres_fdw, avoid crash due to premature cleanup of a failed connection', 150000, 150018, 'https://www.postgresql.org/docs/release/15.18/'),
        ('PG15-LOGREP-SUBSCRIBER-CRASH-03', 'Logical replication subscriber can crash mid-transaction on large in-progress changes', 150000, 150005, 'https://www.postgresql.org/docs/release/15.5/'),
        ('PG15-BTREE-PAGE-LEAK-02', 'B-tree page deletion can leak pages on workloads with heavy concurrent UPDATEs', 150000, 150002, 'https://www.postgresql.org/docs/release/15.2/'),
        ('PG15-INSERT-CONFLICT-VISIBLE-01', 'INSERT ... ON CONFLICT can fail to enforce unique-constraint visibility checks in some MVCC edge cases', 150000, 150001, 'https://www.postgresql.org/docs/release/15.1/'),
        ('PG16-18955d412', 'Fix crash with namespace nodes in contrib/xml2''s xpath_nodeset() function (Andrey Chernyy, Michael Paquier) §', 160000, 160015, 'https://www.postgresql.org/docs/release/16.15/'),
        ('PG16-3179253c2', 'Fix buffer overrun in regexp match/split functions (Masahiko Sawada) §', 160000, 160015, 'https://www.postgresql.org/docs/release/16.15/'),
        ('PG16-3854f4afc', 'In PL/Perl, avoid NULL pointer dereference crash when working with an invalid PostgreSQL::InServer::ARRAY object (Xing Guo) §', 160000, 160015, 'https://www.postgresql.org/docs/release/16.15/'),
        ('PG16-444038bb7', 'Fix memory-safety bugs in processing of incorrect ispell/hunspell dictionary files (Andrey Rachitskiy) §', 160000, 160015, 'https://www.postgresql.org/docs/release/16.15/'),
        ('PG16-5dbeb69bc', 'Fix buffer overrun with long time zone abbreviation in to_char() (Tom Lane) §', 160000, 160015, 'https://www.postgresql.org/docs/release/16.15/'),
        ('PG16-60abb3c73', 'Fix NULL-pointer crash when IS JSON or similar constructs have an argument that is of string category but lacks a cast to type text', 160000, 160015, 'https://www.postgresql.org/docs/release/16.15/'),
        ('PG16-6de480156', 'Prevent satisfies_hash_partition() from crashing with VARIADIC NULL', 160000, 160015, 'https://www.postgresql.org/docs/release/16.15/'),
        ('PG16-83336e3ed', 'Fix crash after out-of-memory failure partway through creation of a cache entry for a text search dictionary', 160000, 160015, 'https://www.postgresql.org/docs/release/16.15/'),
        ('PG16-aca944e33', 'In contrib/ltree, fix integer overflow in comparisons', 160000, 160015, 'https://www.postgresql.org/docs/release/16.15/'),
        ('PG16-bf4616b59', 'Fix possible PANIC due to concurrent drop of pgstats entries when track_functions is enabled', 160000, 160015, 'https://www.postgresql.org/docs/release/16.15/'),
        ('PG16-VACUUM-MEMORY-LEAK-06', 'Vacuum memory accounting leaks over many iterations of aggressive autovacuum', 160000, 160005, 'https://www.postgresql.org/docs/release/16.5/'),
        ('PG16-LOGICAL-DECODING-SUBXACT-05', 'Logical decoding can miss subtransaction changes for aborted DDL', 160000, 160003, 'https://www.postgresql.org/docs/release/16.3/'),
        ('PG16-BTREE-CORRUPT-04', 'B-tree index page splits could corrupt the tree on high-concurrency inserts', 160000, 160002, 'https://www.postgresql.org/docs/release/16.2/'),
        ('PG17-12a620686', 'Fix buffer overrun with long time zone abbreviation in to_char() (Tom Lane) §', 170000, 170011, 'https://www.postgresql.org/docs/release/17.11/'),
        ('PG17-2aa6be6e6', 'In contrib/pgcrypto, avoid double-free crash after encountering an error while using an OSSLCipher object (Yuelin Wang) §', 170000, 170011, 'https://www.postgresql.org/docs/release/17.11/'),
        ('PG17-2e0c61aed', 'Fix possible PANIC due to concurrent drop of pgstats entries when track_functions is enabled (Sami Imseih, Michael Paquier) § § §', 170000, 170011, 'https://www.postgresql.org/docs/release/17.11/'),
        ('PG17-4a49ab289', 'Fix crash with namespace nodes in contrib/xml2''s xpath_nodeset() function (Andrey Chernyy, Michael Paquier) §', 170000, 170011, 'https://www.postgresql.org/docs/release/17.11/'),
        ('PG17-52af6fef4', 'Prevent satisfies_hash_partition() from crashing with VARIADIC NULL (Robert Haas) §', 170000, 170011, 'https://www.postgresql.org/docs/release/17.11/'),
        ('PG17-5fdea3aa3', 'Fix memory-safety bugs in processing of incorrect ispell/hunspell dictionary files', 170000, 170011, 'https://www.postgresql.org/docs/release/17.11/'),
        ('PG17-634a8dcb8', 'Fix crash after out-of-memory failure partway through creation of a cache entry for a text search dictionary', 170000, 170011, 'https://www.postgresql.org/docs/release/17.11/'),
        ('PG17-8ad414831', 'Fix memory leak in parallel vacuum worker processes', 170000, 170011, 'https://www.postgresql.org/docs/release/17.11/'),
        ('PG17-c391c00d9', 'In contrib/ltree, fix integer overflow in comparisons', 170000, 170011, 'https://www.postgresql.org/docs/release/17.11/'),
        ('PG17-d0acd2535', 'Fix NULL-pointer crash when IS JSON or similar constructs have an argument that is of string category but lacks a cast to type text', 170000, 170011, 'https://www.postgresql.org/docs/release/17.11/'),
        ('PG17-PG-CREATESUBSCRIBER-08', 'pg_createsubscriber can leave slots uninitialized, breaking logical failover on first switchover', 170000, 170004, 'https://www.postgresql.org/docs/release/17.4/'),
        ('PG17-COPY-PARTITION-ROUTING-07', 'COPY FROM routing into partitioned tables can drop rows under concurrent partition attach', 170000, 170001, 'https://www.postgresql.org/docs/release/17.1/'),
        ('PG18-020426268', 'In contrib/pgcrypto, avoid double-free crash after encountering an error while using an OSSLCipher object (Yuelin Wang) §', 180000, 180006, 'https://www.postgresql.org/docs/release/18.6/'),
        ('PG18-028ee716a', 'Fix integer overflows in memory-allocation calculations in PL/Perl and PL/Tcl (Heikki Linnakangas) §', 180000, 180006, 'https://www.postgresql.org/docs/release/18.6/'),
        ('PG18-0c06ebf12', 'Prevent satisfies_hash_partition() from crashing with VARIADIC NULL (Robert Haas) §', 180000, 180006, 'https://www.postgresql.org/docs/release/18.6/'),
        ('PG18-1f8ab91c1', 'In contrib/amcheck, fix query-lifespan memory leak while verifying a GIN index (Kirill Reshke) §', 180000, 180006, 'https://www.postgresql.org/docs/release/18.6/'),
        ('PG18-35d9a6263', 'Fix NULL-pointer crash when IS JSON or similar constructs have an argument that is of string category but lacks a cast to type text (Ayush Tiwari) §', 180000, 180006, 'https://www.postgresql.org/docs/release/18.6/'),
        ('PG18-4154a1482', 'Fix memory leak in parallel vacuum worker processes', 180000, 180006, 'https://www.postgresql.org/docs/release/18.6/'),
        ('PG18-4689ea9ce', 'Fix memory-safety bugs in processing of incorrect ispell/hunspell dictionary files', 180000, 180006, 'https://www.postgresql.org/docs/release/18.6/'),
        ('PG18-5cc59834b', 'Fix possible PANIC due to concurrent drop of pgstats entries when track_functions is enabled', 180000, 180006, 'https://www.postgresql.org/docs/release/18.6/'),
        ('PG18-81b1e7916', 'Fix crash after out-of-memory failure partway through creation of a cache entry for a text search dictionary', 180000, 180006, 'https://www.postgresql.org/docs/release/18.6/'),
        ('PG18-MERGE-UPSERT-VIEW-09', 'MERGE ... WHEN NOT MATCHED against an inheritance child can produce duplicate rows under concurrent INSERT', 180000, 180001, 'https://www.postgresql.org/docs/release/18.1/')
-- GENERATED bugs END
),
running_version as (
 select current_setting('server_version_num')::int as version_num
)
select
 'MEDIUM' as severity,
 'System Health' as category,
 'Known Bug Affecting Your Version' as check_name,
 i.issue_id as object_name,
 i.summary as issue_description,
 'server_version_num=' || r.version_num::text ||
 ' (' || current_setting('server_version') || ')' ||
 ', first fixed in version_num=' || i.fixed_in::text as current_value,
 'Upgrade to first PostgreSQL minor >= ' || i.fixed_in::text ||
 ' to pick up the fix. See the linked release notes for commit and reproduction steps.' as recommended_action,
 i.doc_link as documentation_link,
 3 as severity_order
from issue_data i
cross join running_version r
where r.version_num >= i.affected_min
 and r.version_num < i.fixed_in)
) health_results
order by
 health_results.severity_order,
 health_results.category,
 health_results.check_name;