Something went wrong. Try again.
Something went wrong. Try again.
pgFirstAid pgFirstAid.sql
66 kB · 1745 lines
SQL
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746create or replacefunction 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;
begin return querywith 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 querywith 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; exception when object_not_in_prerequisite_state then return; end;end;$$ language plpgsql;
-- Helper: returns formatted checkpoint stats compatible with PG15/16 (pg_stat_bgwriter)-- and PG17+ (pg_stat_checkpointer, which replaced the checkpoint columns in pg_stat_bgwriter)create or replacefunction _pg_firstaid_checkpoint_stats()returns textlanguage plpgsqlstableas $$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 or replacefunction pg_firstAid()returns table ( severity TEXT, category TEXT, check_name TEXT, object_name TEXT, issue_description TEXT, current_value TEXT, recommended_action TEXT, documentation_link TEXT) as $$begin-- Create temporary table to collect all health check results create temp table health_results ( 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 );-- CRITICAL: Tables without primary keys insert into health_results 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_orderfrom pg_tables ptwhere 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 );-- CRITICAL: Unused indexes consuming significant space insert into health_results 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 unused index 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_orderfrom pg_stat_user_indexes psijoin pg_statio_user_indexes psio on psi.indexrelid = psio.indexrelidwhere idx_scan = 0 and pg_relation_size(psi.indexrelid) > 104857600;-- 100MB-- HIGH: Inactive Replication slots insert into health_resultswith 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_orderfrom qorder by slot_name;-- credit: https://www.morling.dev/blog/mastering-postgres-replication-slots/ -- Thank you Gunnar Morling!-- HIGH: Tables with high bloatinsert into health_resultswith 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_orderfrom qwhere bloat_pct > 50.0 and schemaname not like all(array['information_schema', 'pg_catalog', 'pg_toast', 'pg_temp%'])order by quote_ident(schemaname), quote_ident(tblname);--Credit: https://github.com/ioguix/pgsql-bloat-estimation -- Jehan-Guillaume (ioguix) de Rorthais!-- HIGH: Tables never analyzed insert into health_results 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_orderfrom pg_stat_user_tables ptwhere last_analyze is null and last_autoanalyze is null and n_tup_ins + n_tup_upd + n_tup_del > 1000;-- HIGH: Tables larger than 100GBwith 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_prettyfrom information_schema.tableswhere table_type = 'BASE TABLE' and pg_relation_size('"' || table_schema || '"."' || table_name || '"') > 107374182400 -- 100GB in bytesorder by size_bytes desc) insert into health_results 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_orderfrom ts;-- HIGH: Duplicate or redundant indexes-- Compare actual index structure (columns, operator class) not string definitions insert into health_results 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_orderfrom pg_index idx1join pg_class i1 on idx1.indexrelid = i1.oidjoin pg_class c1 on idx1.indrelid = c1.oidjoin pg_namespace n1 on c1.relnamespace = n1.oidjoin 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 optionsjoin pg_class i2 on idx2.indexrelid = i2.oidwhere n1.nspname not like all(array['information_schema', 'pg_catalog', 'pg_toast', 'pg_temp%']);-- HIGH: Table with more than 200 columnswith cc as (select table_schema, table_name, COUNT(*) as column_countfrom information_schema.columnswhere table_schema not in ('pg_catalog', 'information_schema')group by table_schema, table_nameorder by column_count desc)insert into health_resultsselect '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 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_orderfrom ccwhere cc.column_count > 200;-- MEDIUM: Blocked and Blocking Querieswith 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_durationfrom pg_locks blocked_locksjoin pg_stat_activity blocked on blocked.pid = blocked_locks.pidjoin pg_locks blocking_lockson blocking_locks.transactionid = blocked_locks.transactionid and blocking_locks.pid != blocked_locks.pidjoin pg_stat_activity blocking on blocking.pid = blocking_locks.pidwhere not blocked_locks.granted)insert into health_resultsselect '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_orderfrom bq;-- MEDIUM: Tables with outdated statisticsinsert into health_results 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_orderfrom ttwhere n_dead_tup > v_threshold or n_mod_since_analyze > a_thresholdorder by nspname, relname;-- credit: https://www.depesz.com/2020/01/29/which-tables-should-be-auto-vacuumed-or-auto-analyzed -- Thanks depesz!-- MEDIUM: Low index usage efficiency insert into health_results 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_orderfrom pg_stat_user_indexes psiwhere idx_scan > 100 and idx_tup_read::numeric / nullif(idx_scan, 0) > 1000;-- MEDIUM: Replication slots with high wal retation (90% of max wal)insert into health_resultswith 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_orderfrom qorder by slot_name;-- MEDIUM: Large sequential scans insert into health_results 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_orderfrom pg_stat_user_tableswhere seq_scan > 1000 and seq_tup_read > seq_scan * 10000;-- MEDIUM: Table with more than 50 columnswith cc as (select table_schema, table_name, COUNT(*) as column_countfrom information_schema.columns tcwhere table_schema not in ('pg_catalog', 'information_schema')group by table_schema, table_nameorder by column_count desc)insert into health_resultsselect '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 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_orderfrom ccwhere cc.column_count between 50 and 199;-- MEDIUM: Connection and lock monitoring insert into health_results 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_orderfrom pg_stat_activitywhere state = 'active'group by 1, 2, 3, 4, 5, 7, 8, 9having COUNT(*) > 50;-- MEDIUM: Tables larger than 50GBwith 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_prettyfrom information_schema.tableswhere table_type = 'BASE TABLE' and pg_relation_size('"' || table_schema || '"."' || table_name || '"') between 53687091200 and 107374182400order by size_bytes desc)insert into health_results 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_orderfrom ts;-- MEDIUM: Queries running longer than 5 minutes insert into health_results 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_orderfrom pg_stat_activity pgswhere state = 'active' and now() - query_start > interval '5 minutes'order by (now() - query_start) desc;-- MEDIUM: pg_stat_statements extension missinginsert into health_resultsselect '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_orderwhere not exists ( select 1 from pg_extension where extname = 'pg_stat_statements' );-- MEDIUM: pg_stat_statements dependent checksinsert into health_resultsselect *from pgfirstaid_pg_stat_statements_checks();-- MEDIUM: Top 10 expensive active queries by runtimewith eq as (select pgs.pid, pgs.usename, pgs.datname, pgs.client_addr, now() - pgs.query_start as runtime, pgs.queryfrom pg_stat_activity pgswhere 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 desclimit 10)insert into health_resultsselect '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_orderfrom eq;-- MEDIUM: Lock-wait-heavy active querieswith lw as (select pid, usename, datname, client_addr, wait_event, query_start, now() - query_start as runtime, queryfrom pg_stat_activitywhere 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 desclimit 10)insert into health_resultsselect '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_orderfrom lw;-- MEDIUM: Idle in transaction over 5 minutesinsert into health_resultsselect '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_orderfrom pg_stat_activity psawhere 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;-- LOW: Missing indexes on foreign keys insert into health_results 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_orderfrom pg_constraint cjoin pg_class t on c.conrelid = t.oidjoin pg_namespace n on t.relnamespace = n.oidjoin 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;-- LOW: Connections IDLE for > 1 hour with ic as (select pid, usename, application_name, client_addr, state, state_change, now() - state_change as idle_durationfrom pg_stat_activitywhere state = 'idle' and state_change < now() - interval '1 hour' and pid <> pg_backend_pid() ) insert into health_results 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_orderfrom ic;-- LOW: Tables with zero or only one columnwith 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_countfrom pg_catalog.pg_class pcinner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespaceleft join pg_catalog.pg_attribute a on a.attrelid = pc.oid and a.attnum > 0 and not a.attisdroppedwhere 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)insert into health_resultsselect '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_orderfrom sctorder by sct.table_size_bytes desc;-- LOW: Tables with no recent acitivtywith 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_maintenancefrom pg_stat_user_tableswhere 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)insert into health_resultsselect '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_orderfrom itorder by it.table_size_bytes desc;-- 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_offrom pg_roles rleft join pg_auth_members am on am.member = r.oidleft join pg_roles m on m.oid = am.roleidwhere 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)insert into health_resultsselect '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_orderfrom urorder by ur.rolsuper desc, ur.role_name;-- LOW: Indexes with low usagewith 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_fetchfrom pg_stat_user_indexeswhere idx_scan > 0 and idx_scan < 100 and pg_relation_size(indexrelid) > 1024 * 1024 -- > 1MB)insert into health_resultsselect '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_orderfrom luiorder by lui.index_size_bytes desc;-- LOW: Check for truely empty tables in the databasewith 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_rowsfrom pg_class cjoin pg_namespace n on n.oid = c.relnamespaceleft join pg_stat_user_tables s on s.relid = c.oidwhere c.relkind = 'r' and n.nspname not in ('pg_catalog', 'information_schema', 'pg_toast') and c.reltuples = 0 and s.n_live_tup = 0)insert into health_resultsselect '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_orderfrom etorder by et.table_size_bytes desc;-- INFO: Database size and growth insert into health_results 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;-- INFO: Version and configuration insert into health_results 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;-- INFO: shared_buffers current valueinsert into health_resultsselect '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;-- HIGH: shared_buffers still at 128MB PostgreSQL defaultinsert into health_resultsselect '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_orderwhere pg_size_bytes(current_setting('shared_buffers')) = pg_size_bytes('128MB');
-- INFO: work_mem current valueinsert into health_resultsselect '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;
-- MEDIUM: work_mem still at 4MB PostgreSQL defaultinsert into health_resultsselect '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_orderwhere pg_size_bytes(current_setting('work_mem')) = pg_size_bytes('4MB');
-- INFO: effective_cache_size current valueinsert into health_resultsselect '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;
-- INFO: maintenance_work_mem current valueinsert into health_resultsselect '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;
-- INFO: Transaction ID wraparound risk per databaseinsert into health_resultsselect '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_orderfrom pg_databasewhere datallowconn = true;
-- INFO: Checkpoint statistics (PG15/16: pg_stat_bgwriter, PG17+: pg_stat_checkpointer)insert into health_resultsselect '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;
-- INFO: Server role (primary vs standby)insert into health_resultsselect '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;
-- INFO: Connection utilizationinsert into health_resultsselect '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_orderfrom pg_stat_activity;
-- INFO: Installed Extensions insert into health_results 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_orderfrom pg_extension pe;-- INFO: Server Uptime insert into health_results 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() as current_value, 'No Recommendation - Informational' as recommended_action, 'N/A' as documentation_link, 5 as severity_order;-- INFO: Log Directory with ld as (select current_setting('log_directory') as log_directory ) insert into health_results 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_orderfrom ld;-- INFO: Log File(s) Size(s)begin with ls as (select ROUND(sum(stat.size) / (1024.0 * 1024.0), 2) || ' MB' as size_mbfrom pg_ls_dir(current_setting('log_directory')) as logscross join lateral pg_stat_file(current_setting('log_directory') || '/' || logs) as stat) insert into health_results 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_orderfrom ls;exceptionwhen insufficient_privilege then insert into health_results select 'INFO' as severity, 'System Info' as category, 'Size of ALL Logfiles combined' as check_name, 'System' as object_name, 'Unable to check log file sizes - insufficient privileges' as issue_description, 'Permission denied for pg_ls_dir' as current_value, 'If this is a managed instance (ex:AWS RDS), you will not be able to view this information from SQL. For RDS, use AWS CLI: aws rds describe-db-log-files --db-instance-identifier <instance-name>. Otherwise, grant pg_read_server_files role or run as superuser to enable this check.' as recommended_action, 'For AWS Aurora/RDS: https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-log-files.html / For GCP Cloud SQL: https://docs.cloud.google.com/sql/docs/postgres/logging / For Azure Database for PostgreSQL: https://learn.microsoft.com/en-us/cli/azure/postgres/flexible-server/server-logs?view=azure-cli-latest ' as documentation_link, 5 as severity_order;end;-- Return results ordered by severity return QUERY select hr.severity, hr.category, hr.check_name, hr.object_name, hr.issue_description, hr.current_value, hr.recommended_action, hr.documentation_linkfrom health_results hrorder by hr.severity_order, hr.category, hr.check_name;-- Clean up drop table health_results;end;
$$ language plpgsql;