justin.randoneering.dev
This repository has no description
66 kB
1746 lines
1create or replace
2function pgfirstaid_pg_stat_statements_checks()
3returns table (
4 severity TEXT,
5 category TEXT,
6 check_name TEXT,
7 object_name TEXT,
8 issue_description TEXT,
9 current_value TEXT,
10 recommended_action TEXT,
11 documentation_link TEXT,
12 severity_order INTEGER
13) as $$
14begin
15 if not exists (
16 select
17 1
18 from
19 pg_extension
20 where
21 extname = 'pg_stat_statements') then
22 return;
23 end if;
24
25 begin
26 return query
27with pss as (
28 select
29 queryid,
30 query,
31 calls,
32 total_exec_time,
33 mean_exec_time,
34 rows
35 from
36 pg_stat_statements
37 where
38 calls > 0
39 order by
40 total_exec_time desc
41 limit 10)
42 select
43 'MEDIUM' as severity,
44 'Query Health' as category,
45 'Top 10 Queries by Total Execution Time' as check_name,
46 'queryid: ' || pss.queryid::text as object_name,
47 'Queries with the highest total execution time are usually the best optimization targets for overall workload improvement' as issue_description,
48 'calls: ' || pss.calls || ', total_exec_time_ms: ' || round(pss.total_exec_time::numeric, 2) ||
49 ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 2) || ', rows: ' || pss.rows ||
50 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
51 'Run EXPLAIN (ANALYZE, BUFFERS) and focus on reducing total runtime for these fingerprints first' as recommended_action,
52 'https://www.postgresql.org/docs/current/pgstatstatements.html \
53 https://www.postgresql.org/docs/current/using-explain.html \
54 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
55 3 as severity_order
56 from
57 pss;
58
59 return query
60 select
61 'MEDIUM' as severity,
62 'Query Health' as category,
63 'High Mean Execution Time Queries' as check_name,
64 'queryid: ' || pss.queryid::text as object_name,
65 'Queries with high average runtime and enough call volume are underperforming and likely user-visible' as issue_description,
66 'calls: ' || pss.calls || ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 2) ||
67 ', total_exec_time_ms: ' || round(pss.total_exec_time::numeric, 2) ||
68 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
69 'Add or improve indexes and rewrite query predicates to reduce per-execution latency' as recommended_action,
70 'https://www.postgresql.org/docs/current/pgstatstatements.html \
71 https://www.postgresql.org/docs/current/using-explain.html \
72 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
73 3 as severity_order
74 from
75 pg_stat_statements pss
76 where
77 pss.calls >= 20
78 and pss.mean_exec_time > 100
79 order by
80 pss.mean_exec_time desc
81 limit 10;
82
83 return query
84with pss as (
85 select
86 queryid,
87 query,
88 calls,
89 temp_blks_read,
90 temp_blks_written,
91 total_exec_time
92 from
93 pg_stat_statements
94 where
95 (temp_blks_read + temp_blks_written) > 0
96 order by
97 (temp_blks_read + temp_blks_written) desc
98 limit 10)
99 select
100 'MEDIUM' as severity,
101 'Query Health' as category,
102 'Top 10 Queries by Temp Block Spills' as check_name,
103 'queryid: ' || pss.queryid::text as object_name,
104 'Frequent temp block usage points to sort or hash operations spilling to disk and causing avoidable latency' as issue_description,
105 'calls: ' || pss.calls || ', temp_blks_read: ' || pss.temp_blks_read ||
106 ', temp_blks_written: ' || pss.temp_blks_written || ', total_exec_time_ms: ' ||
107 round(pss.total_exec_time::numeric, 2) || ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
108 'Reduce row width, improve index support for sort or group patterns, and tune work_mem cautiously' as recommended_action,
109 'https://www.postgresql.org/docs/current/pgstatstatements.html \
110 https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM \
111 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
112 3 as severity_order
113 from
114 pss;
115
116 return query
117 select
118 'MEDIUM' as severity,
119 'Query Health' as category,
120 'Low Cache Hit Ratio Queries' as check_name,
121 'queryid: ' || pss.queryid::text as object_name,
122 'Low buffer cache hit ratio indicates heavy physical reads and likely missing indexes or poor filtering' as issue_description,
123 'calls: ' || pss.calls || ', cache_hit_pct: ' || round(
124 100.0 * pss.shared_blks_hit / NULLIF(pss.shared_blks_hit + pss.shared_blks_read, 0),
125 2
126 ) || ', shared_blks_read: ' || pss.shared_blks_read || ', shared_blks_hit: ' || pss.shared_blks_hit ||
127 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
128 'Prioritize index tuning and query filtering to reduce disk reads for these statements' as recommended_action,
129 'https://www.postgresql.org/docs/current/pgstatstatements.html \
130 https://www.postgresql.org/docs/current/using-explain.html \
131 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
132 3 as severity_order
133 from
134 pg_stat_statements pss
135 where
136 pss.calls >= 20
137 and (pss.shared_blks_hit + pss.shared_blks_read) > 0
138 and (100.0 * pss.shared_blks_hit / NULLIF(pss.shared_blks_hit + pss.shared_blks_read, 0)) < 90
139 order by
140 (100.0 * pss.shared_blks_hit / NULLIF(pss.shared_blks_hit + pss.shared_blks_read, 0)) asc
141 limit 10;
142
143 return query
144 select
145 'MEDIUM' as severity,
146 'Query Health' as category,
147 'High Runtime Variance Queries' as check_name,
148 'queryid: ' || pss.queryid::text as object_name,
149 'High runtime variance can indicate plan instability, skewed data distribution, or parameter sensitivity' as issue_description,
150 'calls: ' || pss.calls || ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 2) ||
151 ', stddev_exec_time_ms: ' || round(pss.stddev_exec_time::numeric, 2) ||
152 ', total_exec_time_ms: ' || round(pss.total_exec_time::numeric, 2) || ', query: ' ||
153 left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
154 'Check plan stability with EXPLAIN (ANALYZE, BUFFERS), update statistics, and review parameterized execution paths' as recommended_action,
155 'https://www.postgresql.org/docs/current/pgstatstatements.html \
156 https://www.postgresql.org/docs/current/routine-vacuuming.html \
157 https://www.postgresql.org/docs/current/using-explain.html' as documentation_link,
158 3 as severity_order
159 from
160 pg_stat_statements pss
161 where
162 pss.calls >= 20
163 and pss.stddev_exec_time > pss.mean_exec_time
164 order by
165 pss.stddev_exec_time desc
166 limit 10;
167
168 return query
169 select
170 'MEDIUM' as severity,
171 'Query Health' as category,
172 'High Calls Low Value Queries' as check_name,
173 'queryid: ' || pss.queryid::text as object_name,
174 'Very high call volume with low per-call value can create avoidable overhead and crowd out expensive work' as issue_description,
175 'calls: ' || pss.calls || ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 3) ||
176 ', total_exec_time_ms: ' || round(pss.total_exec_time::numeric, 2) ||
177 ', rows_per_call: ' || round((pss.rows::numeric / NULLIF(pss.calls, 0)), 2) ||
178 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
179 'Batch repeated requests, cache stable lookups, and reduce N+1 query patterns in the application layer' as recommended_action,
180 'https://www.postgresql.org/docs/current/pgstatstatements.html \
181 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
182 3 as severity_order
183 from
184 pg_stat_statements pss
185 where
186 pss.calls >= 5000
187 and pss.mean_exec_time <= 2
188 and (pss.rows::numeric / NULLIF(pss.calls, 0)) <= 2
189 order by
190 pss.calls desc
191 limit 10;
192
193 return query
194 select
195 'MEDIUM' as severity,
196 'Query Health' as category,
197 'High Rows Per Call Queries' as check_name,
198 'queryid: ' || pss.queryid::text as object_name,
199 'High rows returned per execution often indicates over-fetching or missing selective filters' as issue_description,
200 'calls: ' || pss.calls || ', rows_per_call: ' || round((pss.rows::numeric / NULLIF(pss.calls, 0)), 2) ||
201 ', total_rows: ' || pss.rows || ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 2) ||
202 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
203 'Add tighter predicates, pagination, and narrower SELECT lists to reduce unnecessary row transfer' as recommended_action,
204 'https://www.postgresql.org/docs/current/pgstatstatements.html \
205 https://www.postgresql.org/docs/current/queries-limit.html \
206 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
207 3 as severity_order
208 from
209 pg_stat_statements pss
210 where
211 pss.calls >= 20
212 and (pss.rows::numeric / NULLIF(pss.calls, 0)) > 10000
213 order by
214 (pss.rows::numeric / NULLIF(pss.calls, 0)) desc
215 limit 10;
216
217 return query
218 select
219 'MEDIUM' as severity,
220 'Query Health' as category,
221 'High Shared Block Reads Per Call Queries' as check_name,
222 'queryid: ' || pss.queryid::text as object_name,
223 'High shared block reads per call usually points to heavy table or index scans and poor locality' as issue_description,
224 'calls: ' || pss.calls || ', shared_blks_read_per_call: ' || round((pss.shared_blks_read::numeric / NULLIF(pss.calls, 0)), 2) ||
225 ', shared_blks_read: ' || pss.shared_blks_read || ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 2) ||
226 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
227 'Use EXPLAIN (ANALYZE, BUFFERS) to add selective indexes and reduce pages read per execution' as recommended_action,
228 'https://www.postgresql.org/docs/current/pgstatstatements.html \
229 https://www.postgresql.org/docs/current/using-explain.html \
230 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
231 3 as severity_order
232 from
233 pg_stat_statements pss
234 where
235 pss.calls >= 20
236 and (pss.shared_blks_read::numeric / NULLIF(pss.calls, 0)) > 1000
237 order by
238 (pss.shared_blks_read::numeric / NULLIF(pss.calls, 0)) desc
239 limit 10;
240
241 return query
242 select
243 'MEDIUM' as severity,
244 'Query Health' as category,
245 'Top Queries by WAL Bytes Per Call' as check_name,
246 'queryid: ' || pss.queryid::text as object_name,
247 'High WAL generation per execution can indicate heavy write amplification and expensive update patterns' as issue_description,
248 'calls: ' || pss.calls || ', wal_bytes_per_call: ' || round(
249 ((to_jsonb(pss)->>'wal_bytes')::numeric / NULLIF(pss.calls, 0)),
250 2
251 ) || ', wal_bytes_total: ' || round((to_jsonb(pss)->>'wal_bytes')::numeric, 2) ||
252 ', mean_exec_time_ms: ' || round(pss.mean_exec_time::numeric, 2) ||
253 ', query: ' || left(regexp_replace(pss.query, E'[\n\r\t]+', ' ', 'g'), 350) as current_value,
254 'Reduce row churn, batch writes where possible, and review index maintenance cost for heavy write queries' as recommended_action,
255 'https://www.postgresql.org/docs/current/pgstatstatements.html \
256 https://www.postgresql.org/docs/current/wal-intro.html \
257 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
258 3 as severity_order
259 from
260 pg_stat_statements pss
261 where
262 pss.calls >= 20
263 and coalesce((to_jsonb(pss)->>'wal_bytes')::numeric, 0) > 0
264 and ((to_jsonb(pss)->>'wal_bytes')::numeric / NULLIF(pss.calls, 0)) > 1048576
265 order by
266 ((to_jsonb(pss)->>'wal_bytes')::numeric / NULLIF(pss.calls, 0)) desc
267 limit 10;
268 exception when object_not_in_prerequisite_state then
269 return;
270 end;
271end;
272$$ language plpgsql;
273
274-- Helper: returns formatted checkpoint stats compatible with PG15/16 (pg_stat_bgwriter)
275-- and PG17+ (pg_stat_checkpointer, which replaced the checkpoint columns in pg_stat_bgwriter)
276create or replace
277function _pg_firstaid_checkpoint_stats()
278returns text
279language plpgsql
280stable
281as $$
282declare
283 v_timed bigint;
284 v_forced bigint;
285begin
286 if current_setting('server_version_num')::int >= 170000 then
287 select num_timed, num_requested
288 into v_timed, v_forced
289 from pg_stat_checkpointer;
290 else
291 select checkpoints_timed, checkpoints_req
292 into v_timed, v_forced
293 from pg_stat_bgwriter;
294 end if;
295
296 return 'timed: ' || v_timed::text ||
297 ', forced: ' || v_forced::text ||
298 ', forced ratio: ' ||
299 case
300 when v_timed + v_forced = 0 then '0%'
301 else round(100.0 * v_forced / (v_timed + v_forced), 1)::text || '%'
302 end;
303end;
304$$;
305
306create or replace
307function pg_firstAid()
308returns table (
309 severity TEXT,
310 category TEXT,
311 check_name TEXT,
312 object_name TEXT,
313 issue_description TEXT,
314 current_value TEXT,
315 recommended_action TEXT,
316 documentation_link TEXT
317) as $$
318begin
319-- Create temporary table to collect all health check results
320 create temp table health_results (
321 severity TEXT,
322 category TEXT,
323 check_name TEXT,
324 object_name TEXT,
325 issue_description TEXT,
326 current_value TEXT,
327 recommended_action TEXT,
328 documentation_link TEXT,
329 severity_order INTEGER
330 );
331-- CRITICAL: Tables without primary keys
332 insert
333 into
334 health_results
335 select
336 'CRITICAL' as severity,
337 'Table Health' as category,
338 'Missing Primary Key' as check_name,
339 quote_ident(pt.schemaname) || '.' || quote_ident(tablename) as object_name,
340 'Table missing a primary key, which can cause replication issues and/or poor performance' as issue_description,
341 'No primary key defined' as current_value,
342 'Add a primary key or unique constraint with NOT NULL columns' as recommended_action,
343 'https://www.postgresql.org/docs/current/ddl-constraints.html' as documentation_link,
344 1 as severity_order
345from
346 pg_tables pt
347where
348 pt.schemaname not like all(array['information_schema', 'pg_catalog', 'pg_toast', 'pg_temp%'])
349 and not exists (
350 select
351 1
352 from
353 pg_constraint pc
354 join pg_class c on
355 pc.conrelid = c.oid
356 join pg_namespace n on
357 c.relnamespace = n.oid
358 where
359 pc.contype = 'p'
360 and n.nspname = pt.schemaname
361 and c.relname = pt.tablename
362 );
363-- CRITICAL: Unused indexes consuming significant space
364 insert
365 into
366 health_results
367 select
368 'CRITICAL' as severity,
369 'Table Health' as category,
370 'Unused Large Index' as check_name,
371 quote_ident(psi.schemaname) || '.' || quote_ident(psio.indexrelname) as object_name,
372 'Large unused index consuming disk space and potentially impacting write performance' as issue_description,
373 pg_size_pretty(pg_relation_size(psi.indexrelid)) || ' (0 scans)' as current_value,
374 'Consider dropping this index if truly unused after monitoring usage patterns. Never drop an index without validating usage!' as recommended_action,
375 'https://www.postgresql.org/docs/current/sql-dropindex.html' as documentation_link,
376 1 as severity_order
377from
378 pg_stat_user_indexes psi
379join pg_statio_user_indexes psio on
380 psi.indexrelid = psio.indexrelid
381where
382 idx_scan = 0
383 and pg_relation_size(psi.indexrelid) > 104857600;
384-- 100MB
385-- HIGH: Inactive Replication slots
386 insert
387 into
388 health_results
389with q as (
390 select
391 slot_name,
392 plugin,
393 database,
394 restart_lsn,
395 case
396 when active is true then 'active'
397 else 'inactive'
398 end as "status",
399 pg_size_pretty(
400 pg_wal_lsn_diff(
401 pg_current_wal_lsn(), restart_lsn)) as "retained_wal",
402 pg_size_pretty(safe_wal_size) as "safe_wal_size"
403 from
404 pg_replication_slots
405 where
406 active = false
407 )
408 select
409 'HIGH' as severity,
410 'Replication Health' as category,
411 'Inactive Replication Slots' as check_name,
412 'Slot name:' || slot_name as object_name,
413 'Target replication slot is inactive' as issue_description,
414 'Retained wal:' || retained_wal || ' database:' || database as current_value,
415 'If the replication slot is no longer needed, drop the slot' as recommended_action,
416 'https://www.morling.dev/blog/mastering-postgres-replication-slots' as documentation_link,
417 2 as severity_order
418from
419 q
420order by
421 slot_name;
422-- credit: https://www.morling.dev/blog/mastering-postgres-replication-slots/ -- Thank you Gunnar Morling!
423-- HIGH: Tables with high bloat
424insert
425 into
426 health_results
427with q as (
428 select
429 current_database(),
430 schemaname,
431 tblname,
432 bs * tblpages as real_size,
433 (tblpages-est_tblpages)* bs as extra_size,
434 case
435 when tblpages > 0
436 and tblpages - est_tblpages > 0
437 then 100 * (tblpages - est_tblpages)/ tblpages::float
438 else 0
439 end as extra_pct,
440 fillfactor,
441 case
442 when tblpages - est_tblpages_ff > 0
443 then (tblpages-est_tblpages_ff)* bs
444 else 0
445 end as bloat_size,
446 case
447 when tblpages > 0
448 and tblpages - est_tblpages_ff > 0
449 then 100 * (tblpages - est_tblpages_ff)/ tblpages::float
450 else 0
451 end as bloat_pct,
452 is_na
453 from
454 (
455 select
456 ceil( reltuples / ( (bs-page_hdr)/ tpl_size ) ) + ceil( toasttuples / 4 ) as est_tblpages,
457 ceil( reltuples / ( (bs-page_hdr)* fillfactor /(tpl_size * 100) ) ) + ceil( toasttuples / 4 ) as est_tblpages_ff,
458 tblpages,
459 fillfactor,
460 bs,
461 tblid,
462 schemaname,
463 tblname,
464 heappages,
465 toastpages,
466 is_na
467 from
468 (
469 select
470 ( 4 + tpl_hdr_size + tpl_data_size + (2 * ma)
471 - case
472 when tpl_hdr_size%ma = 0 then ma
473 else tpl_hdr_size%ma
474 end
475 - case
476 when ceil(tpl_data_size)::int%ma = 0 then ma
477 else ceil(tpl_data_size)::int%ma
478 end
479 ) as tpl_size,
480 bs - page_hdr as size_per_block,
481 (heappages + toastpages) as tblpages,
482 heappages,
483 toastpages,
484 reltuples,
485 toasttuples,
486 bs,
487 page_hdr,
488 tblid,
489 schemaname,
490 tblname,
491 fillfactor,
492 is_na
493 from
494 (
495 select
496 tbl.oid as tblid,
497 ns.nspname as schemaname,
498 tbl.relname as tblname,
499 tbl.reltuples,
500 tbl.relpages as heappages,
501 coalesce(toast.relpages, 0) as toastpages,
502 coalesce(toast.reltuples, 0) as toasttuples,
503 coalesce(substring(
504 array_to_string(tbl.reloptions, ' ')
505 from 'fillfactor=([0-9]+)')::smallint, 100) as fillfactor,
506 current_setting('block_size')::numeric as bs,
507 case
508 when version()~ 'mingw32'
509 or version()~ '64-bit|x86_64|ppc64|ia64|amd64' then 8
510 else 4
511 end as ma,
512 24 as page_hdr,
513 23 + case
514 when MAX(coalesce(s.null_frac, 0)) > 0 then ( 7 + count(s.attname) ) / 8
515 else 0::int
516 end
517 + case
518 when bool_or(att.attname = 'oid' and att.attnum < 0) then 4
519 else 0
520 end as tpl_hdr_size,
521 sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) as tpl_data_size,
522 bool_or(att.atttypid = 'pg_catalog.name'::regtype)
523 or sum(case when att.attnum > 0 then 1 else 0 end) <> count(s.attname) as is_na
524 from
525 pg_attribute as att
526 join pg_class as tbl on
527 att.attrelid = tbl.oid
528 join pg_namespace as ns on
529 ns.oid = tbl.relnamespace
530 left join pg_stats as s on
531 s.schemaname = ns.nspname
532 and s.tablename = tbl.relname
533 and s.inherited = false
534 and s.attname = att.attname
535 left join pg_class as toast on
536 tbl.reltoastrelid = toast.oid
537 where
538 not att.attisdropped
539 and tbl.relkind in ('r', 'm')
540 group by
541 1,
542 2,
543 3,
544 4,
545 5,
546 6,
547 7,
548 8,
549 9,
550 10
551 order by
552 2,
553 3
554 ) as s
555 ) as s2
556) as s3)
557select
558 'HIGH' as severity,
559 'Table Health' as category,
560 'Table Bloat (Detailed)' as check_name,
561 quote_ident(schemaname) || '.' || quote_ident(tblname) as object_name,
562 'Table has significant bloat (>50%) affecting performance and storage' as issue_description,
563 'Real size: ' || pg_size_pretty(real_size::bigint) ||
564 ', Bloat: ' || pg_size_pretty(bloat_size::bigint) ||
565 ' (' || ROUND(bloat_pct::numeric, 2) || '%)' as current_value,
566 'Run VACUUM FULL to reclaim space' as recommended_action,
567 'https://www.postgresql.org/docs/current/sql-vacuum.html,
568 https://github.com/ioguix/pgsql-bloat-estimation/' as documentation_link,
569 2 as severity_order
570from
571 q
572where
573 bloat_pct > 50.0
574 and bloat_size > pg_size_bytes('10 MB')
575 and schemaname not like all(array['information_schema', 'pg_catalog', 'pg_toast', 'pg_temp%'])
576order by
577 quote_ident(schemaname),
578 quote_ident(tblname);
579--Credit: https://github.com/ioguix/pgsql-bloat-estimation -- Jehan-Guillaume (ioguix) de Rorthais!
580-- HIGH: Tables never analyzed
581 insert
582 into
583 health_results
584 select
585 'HIGH' as severity,
586 'Table Health' as category,
587 'Missing Statistics' as check_name,
588 quote_ident(schemaname) || '.' || quote_ident(relname) as object_name,
589 'Table has never been analyzed, query planner missing statistics' as issue_description,
590 'Last analyze: Never' as current_value,
591 'Run ANALYZE on this table or enable auto-analyze' as recommended_action,
592 'https://www.postgresql.org/docs/current/sql-analyze.html' as documentation_link,
593 2 as severity_order
594from
595 pg_stat_user_tables pt
596where
597 last_analyze is null
598 and last_autoanalyze is null
599 and n_tup_ins + n_tup_upd + n_tup_del > 1000;
600-- HIGH: Tables larger than 100GB
601with ts as (
602select
603 table_schema,
604 table_name,
605 pg_relation_size('"' || table_schema || '"."' || table_name || '"') as size_bytes,
606 pg_size_pretty(pg_relation_size('"' || table_schema || '"."' || table_name || '"')) as size_pretty
607from
608 information_schema.tables
609where
610 table_type = 'BASE TABLE'
611 and pg_relation_size('"' || table_schema || '"."' || table_name || '"') > 107374182400
612 -- 100GB in bytes
613order by
614 size_bytes desc)
615 insert
616 into
617 health_results
618 select
619 'HIGH' as severity,
620 'Table Health' as category,
621 'Tables larger than 100GB' as check_name,
622 ts.table_schema || '"."' || ts.table_name as object_name,
623 'The following table' as description,
624 ts.size_pretty as current_value,
625 '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,
626 'https://www.heroku.com/blog/handling-very-large-tables-in-postgres-using-partitioning/' as documentation_link,
627 2 as severity_order
628from
629 ts;
630-- HIGH: Duplicate or redundant indexes
631-- Compare actual index structure (columns, operator class) not string definitions
632 insert
633 into
634 health_results
635 select
636 'HIGH' as severity,
637 'Table Health' as category,
638 'Duplicate Index' as check_name,
639 quote_ident(n1.nspname) || '.' || c1.relname || ': ' || i1.relname || ' & ' || i2.relname as object_name,
640 'Multiple indexes with identical column sets and operator classes' as issue_description,
641 'Indexes: ' || i1.relname || ', ' || i2.relname as current_value,
642 'Review and consolidate duplicate indexes and focus on keeping the most efficient one' as recommended_action,
643 'https://www.postgresql.org/docs/current/indexes-multicolumn.html' as documentation_link,
644 2 as severity_order
645from
646 pg_index idx1
647join pg_class i1 on idx1.indexrelid = i1.oid
648join pg_class c1 on idx1.indrelid = c1.oid
649join pg_namespace n1 on c1.relnamespace = n1.oid
650join pg_index idx2 on
651 idx1.indrelid = idx2.indrelid -- same table
652 and idx1.indexrelid < idx2.indexrelid -- avoid duplicates
653 and idx1.indkey = idx2.indkey -- same columns
654 and idx1.indclass = idx2.indclass -- same operator classes
655 and idx1.indoption = idx2.indoption -- same options
656join pg_class i2 on idx2.indexrelid = i2.oid
657where
658 n1.nspname not like all(array['information_schema', 'pg_catalog', 'pg_toast', 'pg_temp%']);
659-- HIGH: Table with more than 200 columns
660with cc as (
661select
662 table_schema,
663 table_name,
664 COUNT(*) as column_count
665from
666 information_schema.columns
667where
668 table_schema not in ('pg_catalog', 'information_schema')
669group by
670 table_schema,
671 table_name
672order by
673 column_count desc)
674insert
675 into
676 health_results
677select
678 'HIGH' as severity,
679 'Table Health' as category,
680 'Table with more than 200 columns' as check_name,
681 cc.table_schema || '.' || cc.table_name as object_name,
682 '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,
683 cc.column_count as current_value,
684 '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,
685 'https://www.tigerdata.com/learn/designing-your-database-schema-wide-vs-narrow-postgres-tables \
686 https://nerderati.com/postgresql-tables-can-have-at-most-1600-columns \
687 https://www.postgresql.org/docs/current/limits.html' as documentation_link,
688 2 as severity_order
689from
690 cc
691where
692 cc.column_count > 200;
693-- MEDIUM: Blocked and Blocking Queries
694with bq as (
695select
696 blocked.pid as blocked_pid,
697 blocked.query as blocked_query,
698 blocking.pid as blocking_pid,
699 blocking.query as blocking_query,
700 now() - blocked.query_start as blocked_duration
701from
702 pg_locks blocked_locks
703join pg_stat_activity blocked on
704 blocked.pid = blocked_locks.pid
705join pg_locks blocking_locks
706on
707 blocking_locks.transactionid = blocked_locks.transactionid
708 and blocking_locks.pid != blocked_locks.pid
709join pg_stat_activity blocking on
710 blocking.pid = blocking_locks.pid
711where
712 not blocked_locks.granted)
713insert
714 into
715 health_results
716select
717 'MEDIUM' as severity,
718 'Query Health' as category,
719 'Current Blocked/Blocking Queries' as check_name,
720 'Blocked PID: ' || bq.blocked_pid || chr(10) ||
721 'Blocked Query: ' || bq.blocked_query as object_name,
722 'The following query is being blocked by an already running query' as issue_description,
723 'Blocking PID: ' || bq.blocking_pid || chr(10) ||
724 'Blocking Query: ' || bq.blocking_query as current_value,
725 '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,
726 'https://www.postgresql.org/docs/current/explicit-locking.html' as documentation_link,
727 3 as severity_order
728from
729 bq;
730-- MEDIUM: Tables with outdated statistics
731insert
732 into
733 health_results
734 with s as (
735 select
736 current_setting('autovacuum_analyze_scale_factor')::float8 as analyze_factor,
737 current_setting('autovacuum_analyze_threshold')::float8 as analyze_threshold,
738 current_setting('autovacuum_vacuum_scale_factor')::float8 as vacuum_factor,
739 current_setting('autovacuum_vacuum_threshold')::float8 as vacuum_threshold
740 ),
741 tt as (
742 select
743 n.nspname,
744 c.relname,
745 c.oid as relid,
746 t.n_dead_tup,
747 t.n_mod_since_analyze,
748 c.reltuples * s.vacuum_factor + s.vacuum_threshold as v_threshold,
749 c.reltuples * s.analyze_factor + s.analyze_threshold as a_threshold
750 from
751 s,
752 pg_class c
753 join pg_namespace n on
754 c.relnamespace = n.oid
755 join pg_stat_all_tables t on
756 c.oid = t.relid
757 where
758 c.relkind = 'r'
759 and n.nspname not like all(array['information_schema', 'pg_catalog', 'pg_toast', 'pg_temp%'])
760 )
761 select
762 'MEDIUM' as severity,
763 'Table Health' as category,
764 'Outdated Statistics' as check_name,
765 quote_ident(nspname) || '.' || quote_ident(relname) as object_name,
766 'Table statistics are outdated, which can lead to poor query plans' as issue_description,
767 'Dead tuples: ' || n_dead_tup || ' (threshold: ' || round(v_threshold) || '), ' ||
768 'Modifications since analyze: ' || n_mod_since_analyze || ' (threshold: ' || round(a_threshold) || ')' as current_value,
769 case
770 when n_dead_tup > v_threshold
771 and n_mod_since_analyze > a_threshold then 'Run VACUUM ANALYZE'
772 when n_dead_tup > v_threshold then 'Run VACUUM'
773 when n_mod_since_analyze > a_threshold then 'Run ANALYZE'
774 end as recommended_action,
775 'https://www.postgresql.org/docs/current/routine-vacuuming.html,
776 https://www.depesz.com/2020/01/29/which-tables-should-be-auto-vacuumed-or-auto-analyzed/' as documentation_link,
777 3 as severity_order
778from
779 tt
780where
781 n_dead_tup > v_threshold
782 or n_mod_since_analyze > a_threshold
783order by
784 nspname,
785 relname;
786-- credit: https://www.depesz.com/2020/01/29/which-tables-should-be-auto-vacuumed-or-auto-analyzed -- Thanks depesz!
787-- MEDIUM: Low index usage efficiency
788 insert
789 into
790 health_results
791 select
792 'MEDIUM' as severity,
793 'Table Health' as category,
794 'Low Index Efficiency' as check_name,
795 quote_ident(schemaname) || '.' || quote_ident(indexrelname) as object_name,
796 'Index has low scan to tuple read ratio indicating poor selectivity' as issue_description,
797 'Scans: ' || idx_scan || ', Tuples: ' || idx_tup_read ||
798 ' (Ratio: ' || ROUND(idx_tup_read::numeric / nullif(idx_scan, 0), 2) || ')' as current_value,
799 'Review index definition and query patterns, consider partial indexes' as recommended_action,
800 'https://www.postgresql.org/docs/current/indexes-partial.html' as documentation_link,
801 3 as severity_order
802from
803 pg_stat_user_indexes psi
804where
805 idx_scan > 100
806 and idx_tup_read::numeric / nullif(idx_scan, 0) > 1000;
807-- MEDIUM: Replication slots with high wal retation (90% of max wal)
808insert
809 into
810 health_results
811with q as (
812 select
813 slot_name,
814 plugin,
815 database,
816 restart_lsn,
817 case
818 when active is true then 'active'
819 else 'inactive'
820 end as "status",
821 pg_size_pretty(
822 pg_wal_lsn_diff(
823 pg_current_wal_lsn(), restart_lsn)) as "retained_wal",
824 pg_size_pretty(safe_wal_size) as "safe_wal_size"
825 from
826 pg_replication_slots
827 where
828 pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) >= (safe_wal_size * 0.9)
829)
830select
831 'MEDIUM' as severity,
832 'Replication Health' as category,
833 'Replication Slots Near Max Wal Size' as check_name,
834 'Slot name:' || slot_name as object_name,
835 'Target replication slot has retained close to 90% of the max wal size' as issue_description,
836 'Retained wal:' || retained_wal || ' safe_wal_size:' || safe_wal_size as current_value,
837 'Consider implementing a heartbeat table or using pg_logical_emit_message()' as recommended_action,
838 'https://www.morling.dev/blog/mastering-postgres-replication-slots' as documentation_link,
839 3 as severity_order
840from
841 q
842order by
843 slot_name;
844-- MEDIUM: Large sequential scans
845 insert
846 into
847 health_results
848 select
849 'MEDIUM' as severity,
850 'Query Health' as category,
851 'Excessive Sequential Scans' as check_name,
852 quote_ident(schemaname) || '.' || quote_ident(relname) as object_name,
853 'Table has high sequential scan activity, may benefit from additional indexes' as issue_description,
854 'Sequential scans: ' || seq_scan || ', Tuples read: ' || seq_tup_read as current_value,
855 'Analyze query patterns and consider adding appropriate indexes' as recommended_action,
856 'https://www.postgresql.org/docs/current/using-explain.html' as documentation_link,
857 3 as severity_order
858from
859 pg_stat_user_tables
860where
861 seq_scan > 1000
862 and seq_tup_read > seq_scan * 10000;
863-- MEDIUM: Table with more than 50 columns
864with cc as (
865select
866 table_schema,
867 table_name,
868 COUNT(*) as column_count
869from
870 information_schema.columns tc
871where
872 table_schema not in ('pg_catalog', 'information_schema')
873group by
874 table_schema,
875 table_name
876order by
877 column_count desc)
878insert
879 into
880 health_results
881select
882 'MEDIUM' as severity,
883 'Table Health' as category,
884 'Table with more than 50 columns' as check_name,
885 cc.table_schema || '.' || cc.table_name as object_name,
886 '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,
887 cc.column_count as current_value,
888 '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,
889 'https://www.tigerdata.com/learn/designing-your-database-schema-wide-vs-narrow-postgres-tables \
890 https://nerderati.com/postgresql-tables-can-have-at-most-1600-columns \
891 https://www.postgresql.org/docs/current/limits.html' as documentation_link,
892 3 as severity_order
893from
894 cc
895where
896 cc.column_count between 50 and 199;
897-- MEDIUM: Connection and lock monitoring
898 insert
899 into
900 health_results
901 select
902 'MEDIUM' as severity,
903 'System Health' as category,
904 'High Connection Count' as check_name,
905 'Database Connections' as object_name,
906 'High number of active connections may impact performance' as issue_description,
907 COUNT(*)::text || ' active connections' as current_value,
908 'Monitor connection pooling and consider adjusting max_connections' as recommended_action,
909 'https://www.postgresql.org/docs/current/runtime-config-connection.html' as documentation_link,
910 3 as severity_order
911from
912 pg_stat_activity
913where
914 state = 'active'
915group by
916 1,
917 2,
918 3,
919 4,
920 5,
921 7,
922 8,
923 9
924having
925 COUNT(*) > 50;
926-- MEDIUM: Tables larger than 50GB
927with ts as (
928select
929 table_schema,
930 table_name,
931 pg_relation_size('"' || table_schema || '"."' || table_name || '"') as size_bytes,
932 pg_size_pretty(pg_relation_size('"' || table_schema || '"."' || table_name || '"')) as size_pretty
933from
934 information_schema.tables
935where
936 table_type = 'BASE TABLE'
937 and pg_relation_size('"' || table_schema || '"."' || table_name || '"') between 53687091200 and 107374182400
938order by
939 size_bytes desc)
940insert
941 into
942 health_results
943 select
944 'MEDIUM' as severity,
945 'Table Health' as category,
946 'Tables larger than 50GB' as check_name,
947 ts.table_schema || '"."' || ts.table_name as object_name,
948 'The following table' as description,
949 ts.size_pretty as current_value,
950 '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,
951 'https://www.heroku.com/blog/handling-very-large-tables-in-postgres-using-partitioning/' as documentation_link,
952 3 as severity_order
953from
954 ts;
955-- MEDIUM: Queries running longer than 5 minutes
956 insert
957 into
958 health_results
959 select
960 'MEDIUM' as severity,
961 'Query Health' as category,
962 'Long Running Queries' as check_name,
963 concat_ws(' | ',
964 'pid: ' || pgs.pid::text,
965 'usename: ' || pgs.usename,
966 'datname: ' || pgs.datname,
967 'client_address: ' || pgs.client_addr::text,
968 'state: ' || pgs.state,
969 'duration: ' || to_char(now() - query_start, 'HH24:MI:SS')
970 ) as object_name,
971 'The following query has been running for more than 5 minutes. Might be helpful to see if this is expected behavior' as issue_description,
972 query as current_value,
973 'Review query using EXPLAIN ANALYZE to identify any bottlenecks, such as full table scans, missing indexes, etc' as recommended_action,
974 'https://www.postgresql.org/docs/current/using-explain.html' as documentation_link,
975 3 as severity_order
976from
977 pg_stat_activity pgs
978where
979 state = 'active'
980 and now() - query_start > interval '5 minutes'
981order by
982 (now() - query_start) desc;
983-- MEDIUM: pg_stat_statements extension missing
984insert
985 into
986 health_results
987select
988 'MEDIUM' as severity,
989 'Query Health' as category,
990 'pg_stat_statements Extension Missing' as check_name,
991 'pg_stat_statements' as object_name,
992 'pg_stat_statements is not installed, so query fingerprint and workload-level performance checks are unavailable' as issue_description,
993 'Extension not found in pg_extension' as current_value,
994 '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,
995 'https://www.postgresql.org/docs/current/pgstatstatements.html \
996 https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.html \
997 https://cloud.google.com/sql/docs/postgres/flags \
998 https://learn.microsoft.com/azure/postgresql/flexible-server/concepts-server-parameters' as documentation_link,
999 3 as severity_order
1000where
1001 not exists (
1002 select
1003 1
1004 from
1005 pg_extension
1006 where
1007 extname = 'pg_stat_statements'
1008 );
1009-- MEDIUM: pg_stat_statements dependent checks
1010insert
1011 into
1012 health_results
1013select
1014 *
1015from
1016 pgfirstaid_pg_stat_statements_checks();
1017-- MEDIUM: Top 10 expensive active queries by runtime
1018with eq as (
1019select
1020 pgs.pid,
1021 pgs.usename,
1022 pgs.datname,
1023 pgs.client_addr,
1024 now() - pgs.query_start as runtime,
1025 pgs.query
1026from
1027 pg_stat_activity pgs
1028where
1029 pgs.state = 'active'
1030 and pgs.query_start is not null
1031 and pgs.pid <> pg_backend_pid()
1032 and now() - pgs.query_start > interval '30 seconds'
1033order by
1034 runtime desc
1035limit 10)
1036insert
1037 into
1038 health_results
1039select
1040 'MEDIUM' as severity,
1041 'Query Health' as category,
1042 'Top 10 Expensive Active Queries' as check_name,
1043 concat_ws(' | ',
1044 'pid: ' || eq.pid::text,
1045 'usename: ' || eq.usename,
1046 'datname: ' || eq.datname,
1047 'client_address: ' || coalesce(eq.client_addr::text, 'local'),
1048 'runtime: ' || to_char(eq.runtime, 'HH24:MI:SS')
1049 ) as object_name,
1050 '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,
1051 left(regexp_replace(eq.query, E'[\n\r\t]+', ' ', 'g'), 500) as current_value,
1052 'Review these queries with EXPLAIN (ANALYZE, BUFFERS) and reduce lock waits or full scans' as recommended_action,
1053 'https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW \
1054 https://www.postgresql.org/docs/current/using-explain.html \
1055 https://www.tigerdata.com/blog/using-pg-stat-statements-to-optimize-queries' as documentation_link,
1056 3 as severity_order
1057from
1058 eq;
1059-- MEDIUM: Lock-wait-heavy active queries
1060with lw as (
1061select
1062 pid,
1063 usename,
1064 datname,
1065 client_addr,
1066 wait_event,
1067 query_start,
1068 now() - query_start as runtime,
1069 query
1070from
1071 pg_stat_activity
1072where
1073 state = 'active'
1074 and wait_event_type = 'Lock'
1075 and query_start is not null
1076 and now() - query_start > interval '30 seconds'
1077 and pid <> pg_backend_pid()
1078order by
1079 runtime desc
1080limit 10)
1081insert
1082 into
1083 health_results
1084select
1085 'MEDIUM' as severity,
1086 'Query Health' as category,
1087 'Lock-Wait-Heavy Active Queries' as check_name,
1088 concat_ws(' | ',
1089 'pid: ' || lw.pid::text,
1090 'usename: ' || lw.usename,
1091 'datname: ' || lw.datname,
1092 'client_address: ' || coalesce(lw.client_addr::text, 'local'),
1093 'wait_event: ' || coalesce(lw.wait_event, 'unknown'),
1094 'runtime: ' || to_char(lw.runtime, 'HH24:MI:SS')
1095 ) as object_name,
1096 'Active queries waiting on locks for extended time can block throughput and cause cascading latency' as issue_description,
1097 left(regexp_replace(lw.query, E'[\n\r\t]+', ' ', 'g'), 500) as current_value,
1098 'Reduce transaction duration, enforce consistent lock ordering, and investigate blockers first' as recommended_action,
1099 'https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW \
1100 https://www.postgresql.org/docs/current/explicit-locking.html' as documentation_link,
1101 3 as severity_order
1102from
1103 lw;
1104-- MEDIUM: Idle in transaction over 5 minutes
1105insert
1106 into
1107 health_results
1108select
1109 'MEDIUM' as severity,
1110 'Query Health' as category,
1111 'Idle In Transaction Over 5 Minutes' as check_name,
1112 concat_ws(' | ',
1113 'pid: ' || psa.pid::text,
1114 'usename: ' || psa.usename,
1115 'datname: ' || psa.datname,
1116 'client_address: ' || coalesce(psa.client_addr::text, 'local'),
1117 'idle_duration: ' || to_char(now() - psa.state_change, 'HH24:MI:SS')
1118 ) as object_name,
1119 'Sessions left idle in transaction hold snapshots and locks longer than necessary, which can hurt query performance and vacuum progress' as issue_description,
1120 left(regexp_replace(psa.query, E'[\n\r\t]+', ' ', 'g'), 500) as current_value,
1121 'Commit or rollback promptly and move application processing outside transaction boundaries' as recommended_action,
1122 'https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW \
1123 https://www.postgresql.org/docs/current/routine-vacuuming.html' as documentation_link,
1124 3 as severity_order
1125from
1126 pg_stat_activity psa
1127where
1128 psa.state = 'idle in transaction'
1129 and psa.state_change is not null
1130 and now() - psa.state_change > interval '5 minutes'
1131 and psa.pid <> pg_backend_pid()
1132order by
1133 now() - psa.state_change desc;
1134-- LOW: Missing indexes on foreign keys
1135 insert
1136 into
1137 health_results
1138 select
1139 'LOW' as severity,
1140 'Table Health' as category,
1141 'Missing FK Index' as check_name,
1142 n.nspname || '.' || t.relname || '.' || string_agg(a.attname, ', ') as object_name,
1143 'Foreign key constraint missing supporting index for efficient joins' as issue_description,
1144 'FK constraint without index' as current_value,
1145 'Consider adding index on foreign key columns for better join performance' as recommended_action,
1146 'https://www.postgresql.org/docs/current/ddl-constraints.html' as documentation_link,
1147 4 as severity_order
1148from
1149 pg_constraint c
1150join pg_class t on
1151 c.conrelid = t.oid
1152join pg_namespace n on
1153 t.relnamespace = n.oid
1154join pg_attribute a on
1155 a.attrelid = t.oid
1156 and a.attnum = any(c.conkey)
1157where
1158 c.contype = 'f'
1159 and n.nspname not like all(array['information_schema', 'pg_catalog', 'pg_toast', 'pg_temp%'])
1160 and not exists (
1161 select
1162 1
1163 from
1164 pg_index i
1165 where
1166 i.indrelid = c.conrelid
1167 and i.indkey::int2[] @> c.conkey::int2[]
1168 )
1169group by
1170 n.nspname,
1171 t.relname,
1172 c.conname,
1173 1,
1174 2,
1175 3,
1176 5,
1177 6,
1178 7,
1179 8,
1180 9;
1181-- LOW: Connections IDLE for > 1 hour
1182 with ic as (
1183select
1184 pid,
1185 usename,
1186 application_name,
1187 client_addr,
1188 state,
1189 state_change,
1190 now() - state_change as idle_duration
1191from
1192 pg_stat_activity
1193where
1194 state = 'idle'
1195 and state_change < now() - interval '1 hour'
1196 and pid <> pg_backend_pid()
1197 )
1198 insert
1199 into
1200 health_results
1201 select
1202 'LOW' as severity,
1203 'Connection Health' as category,
1204 'Idle Connections Over 1 Hour' as check_name,
1205 ic.usename || ' (PID: ' || ic.pid || ')' as object_name,
1206 'Connection has been idle for ' ||
1207 extract(epoch from ic.idle_duration)::int / 3600 || ' hours ' ||
1208 (extract(epoch from ic.idle_duration)::int % 3600) / 60 || ' minutes. ' ||
1209 'Application: ' || coalesce(ic.application_name, 'unknown') ||
1210 ', Client: ' || coalesce(ic.client_addr::text, 'local') as issue_description,
1211 ic.idle_duration::text as current_value,
1212 '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,
1213 'https://www.postgresql.org/docs/current/runtime-config-client.html' as documentation_link,
1214 4 as severity_order
1215from
1216 ic;
1217-- LOW: Tables with zero or only one column
1218with sct as (
1219select
1220 pc.oid::regclass::text as table_name,
1221 pg_size_pretty(pg_table_size(pc.oid)) as table_size,
1222 pg_table_size(pc.oid) as table_size_bytes,
1223 count(a.attname) as column_count
1224from
1225 pg_catalog.pg_class pc
1226inner join
1227 pg_catalog.pg_namespace nsp on
1228 nsp.oid = pc.relnamespace
1229left join
1230 pg_catalog.pg_attribute a on
1231 a.attrelid = pc.oid
1232 and a.attnum > 0
1233 and not a.attisdropped
1234where
1235 pc.relkind in ('r', 'p')
1236 and not pc.relispartition
1237 and nsp.nspname not in ('pg_catalog', 'information_schema', 'pg_toast')
1238 group by
1239 pc.oid
1240 having
1241 count(a.attname) <= 1
1242)
1243insert
1244 into
1245 health_results
1246select
1247 'LOW' as severity,
1248 'Table Health' as category,
1249 'Table With Single Or No Columns' as check_name,
1250 quote_ident(sct.table_name) as object_name,
1251 'Table has ' || sct.column_count || ' column(s). This may indicate an abandoned table, incomplete migration, or design issue.' as issue_description,
1252 sct.column_count || ' column(s), Size: ' || sct.table_size as current_value,
1253 'Review if this table is still needed. Consider removing if unused or completing the schema if it was left incomplete.' as recommended_action,
1254 'https://www.postgresql.org/docs/current/ddl.html /
1255https://github.com/mfvanek/pg-index-health-sql/blob/master/sql/tables_with_zero_or_one_column.sql' as documentation_link,
1256 4 as severity_order
1257from
1258 sct
1259order by
1260 sct.table_size_bytes desc;
1261-- LOW: Tables with no recent acitivty
1262with it as (
1263select
1264 schemaname || '.' || relname as table_name,
1265 pg_size_pretty(pg_total_relation_size(relid)) as table_size,
1266 pg_total_relation_size(relid) as table_size_bytes,
1267 coalesce(seq_scan, 0) + coalesce(idx_scan, 0) as total_scans,
1268 coalesce(n_tup_ins, 0) + coalesce(n_tup_upd, 0) + coalesce(n_tup_del, 0) as total_writes,
1269 greatest(last_vacuum, last_autovacuum, last_analyze, last_autoanalyze) as last_maintenance
1270from
1271 pg_stat_user_tables
1272where
1273 schemaname not like all(array['pg_catalog', 'information_schema', 'pg_toast', 'pg_temp%'])
1274 and coalesce(seq_scan, 0) + coalesce(idx_scan, 0) = 0
1275 and coalesce(n_tup_ins, 0) + coalesce(n_tup_upd, 0) + coalesce(n_tup_del, 0) = 0
1276)
1277insert
1278 into
1279 health_results
1280select
1281 'LOW' as severity,
1282 'Table Health' as category,
1283 'Table With No Activity Since Stats Reset' as check_name,
1284 quote_ident(it.table_name) as object_name,
1285 'Table has had no reads or writes since stats were last reset. Last maintenance: ' ||
1286 coalesce(it.last_maintenance::text, 'never') as issue_description,
1287 'Total scans: ' || it.total_scans || ', Total writes: ' || it.total_writes ||
1288 ', Size: ' || it.table_size as current_value,
1289 '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,
1290 'https://www.postgresql.org/docs/current/monitoring-stats.html' as documentation_link,
1291 4 as severity_order
1292from
1293 it
1294order by
1295 it.table_size_bytes desc;
1296-- LOW: Roles that have never logged in (with LOGIN rights)
1297with ur as (
1298select
1299 r.rolname as role_name,
1300 r.rolcreaterole,
1301 r.rolcanlogin,
1302 r.rolsuper,
1303 r.rolvaliduntil,
1304 array_agg(m.rolname) filter (
1305 where m.rolname is not null) as member_of
1306from
1307 pg_roles r
1308left join
1309 pg_auth_members am on
1310 am.member = r.oid
1311left join
1312 pg_roles m on
1313 m.oid = am.roleid
1314where
1315 r.rolcanlogin = true
1316 and r.rolname not like 'pg_%'
1317 and r.rolname not in ('postgres', 'rds_superuser', 'rdsadmin', 'azure_superuser', 'cloudsqlsuperuser')
1318 and not exists (
1319 select
1320 1
1321 from
1322 pg_stat_activity psa
1323 where
1324 psa.usename = r.rolname
1325 )
1326 and (
1327 select
1328 coalesce(max(backend_start), '1970-01-01')
1329 from
1330 pg_stat_activity
1331 where
1332 usename = r.rolname
1333 ) = '1970-01-01'
1334 group by
1335 r.rolname,
1336 r.rolcreaterole,
1337 r.rolcanlogin,
1338 r.rolsuper,
1339 r.rolvaliduntil
1340)
1341insert
1342 into
1343 health_results
1344select
1345 'LOW' as severity,
1346 'Security Health' as category,
1347 'Role Never Logged In' as check_name,
1348 ur.role_name as object_name,
1349 'Role has LOGIN privilege but has never connected (since stats reset). ' ||
1350 case
1351 when ur.rolsuper then 'WARNING: Has SUPERUSER privilege. '
1352 else ''
1353 end ||
1354 case
1355 when ur.rolvaliduntil is not null then 'Expires: ' || ur.rolvaliduntil::text
1356 else 'No expiration set'
1357 end as issue_description,
1358 'Member of: ' || coalesce(array_to_string(ur.member_of, ', '), 'none') as current_value,
1359 'Review if this role is still needed. Consider removing LOGIN privilege or dropping the role if unused.' as recommended_action,
1360 'https://www.postgresql.org/docs/current/sql-droprole.html' as documentation_link,
1361 4 as severity_order
1362from
1363 ur
1364order by
1365 ur.rolsuper desc,
1366 ur.role_name;
1367-- LOW: Indexes with low usage
1368with lui as (
1369select
1370 quote_ident(schemaname) || '.' || quote_ident(indexrelname) as index_name,
1371 quote_ident(schemaname) || '.' || quote_ident(relname) as table_name,
1372 pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
1373 pg_relation_size(indexrelid) as index_size_bytes,
1374 idx_scan,
1375 idx_tup_read,
1376 idx_tup_fetch
1377from
1378 pg_stat_user_indexes
1379where
1380 idx_scan > 0
1381 and idx_scan < 100
1382 and pg_relation_size(indexrelid) > 1024 * 1024
1383 -- > 1MB
1384)
1385insert
1386 into
1387 health_results
1388select
1389 'LOW' as severity,
1390 'Index Health' as category,
1391 'Index With Very Low Usage' as check_name,
1392 lui.index_name as object_name,
1393 'Index on ' || lui.table_name || ' has been scanned only ' || lui.idx_scan ||
1394 ' times since stats reset. May not be worth the maintenance overhead.' as issue_description,
1395 'Scans: ' || lui.idx_scan || ', Tuples read: ' || lui.idx_tup_read ||
1396 ', Size: ' || lui.index_size as current_value,
1397 'Monitor usage over a full business cycle before removing. Verify index is not used for constraints or infrequent but critical queries.' as recommended_action,
1398 'https://www.postgresql.org/docs/current/monitoring-stats.html' as documentation_link,
1399 4 as severity_order
1400from
1401 lui
1402order by
1403 lui.index_size_bytes desc;
1404-- LOW: Check for truely empty tables in the database
1405with et as (
1406select
1407 n.nspname || '.' || c.relname as table_name,
1408 pg_size_pretty(pg_total_relation_size(c.oid)) as table_size,
1409 pg_total_relation_size(c.oid) as table_size_bytes,
1410 s.last_vacuum,
1411 s.last_analyze,
1412 c.reltuples::bigint as estimated_rows
1413from
1414 pg_class c
1415join
1416 pg_namespace n on
1417 n.oid = c.relnamespace
1418left join
1419 pg_stat_user_tables s on
1420 s.relid = c.oid
1421where
1422 c.relkind = 'r'
1423 and n.nspname not in ('pg_catalog', 'information_schema', 'pg_toast')
1424 and c.reltuples = 0
1425 and s.n_live_tup = 0
1426)
1427insert
1428 into
1429 health_results
1430select
1431 'LOW' as severity,
1432 'Table Health' as category,
1433 'Empty Table' as check_name,
1434 et.table_name as object_name,
1435 'Table contains no rows. Last vacuum: ' || coalesce(et.last_vacuum::text, 'never') ||
1436 ', Last analyze: ' || coalesce(et.last_analyze::text, 'never') as issue_description,
1437 '0 rows, Size: ' || et.table_size as current_value,
1438 '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,
1439 'https://www.postgresql.org/docs/current/routine-vacuuming.html' as documentation_link,
1440 4 as severity_order
1441from
1442 et
1443order by
1444 et.table_size_bytes desc;
1445-- INFO: Database size and growth
1446 insert
1447 into
1448 health_results
1449 select
1450 'INFO' as severity,
1451 'Database Health' as category,
1452 'Database Size' as check_name,
1453 current_database() as object_name,
1454 'Current database size information' as issue_description,
1455 pg_size_pretty(pg_database_size(current_database())) as current_value,
1456 'Monitor growth trends and plan capacity accordingly' as recommended_action,
1457 'https://www.postgresql.org/docs/current/diskusage.html' as documentation_link,
1458 5 as severity_order;
1459-- INFO: Version and configuration
1460 insert
1461 into
1462 health_results
1463 select
1464 'INFO' as severity,
1465 'System Info' as category,
1466 'PostgreSQL Version' as check_name,
1467 'System' as object_name,
1468 'Current PostgreSQL version and basic configuration' as issue_description,
1469 version() as current_value,
1470 'Keep PostgreSQL updated and review configuration settings' as recommended_action,
1471 'https://www.postgresql.org/docs/current/upgrading.html' as documentation_link,
1472 5 as severity_order;
1473-- INFO: shared_buffers current value
1474insert into health_results
1475select
1476 'INFO' as severity,
1477 'System Health' as category,
1478 'shared_buffers Setting' as check_name,
1479 'System' as object_name,
1480 'Current value of shared_buffers. Recommended: ~25% of total system RAM for dedicated database servers.' as issue_description,
1481 current_setting('shared_buffers') as current_value,
1482 '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,
1483 'https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-SHARED-BUFFERS' as documentation_link,
1484 5 as severity_order;
1485-- HIGH: shared_buffers still at 128MB PostgreSQL default
1486insert into health_results
1487select
1488 'HIGH' as severity,
1489 'System Health' as category,
1490 'shared_buffers At Default' as check_name,
1491 'System' as object_name,
1492 'shared_buffers is set to the PostgreSQL default of 128MB. On any real workload this is almost certainly too low.' as issue_description,
1493 current_setting('shared_buffers') as current_value,
1494 'Set shared_buffers to approximately 25% of total system RAM (e.g., 2GB on an 8GB server). Requires a PostgreSQL restart.' as recommended_action,
1495 'https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-SHARED-BUFFERS' as documentation_link,
1496 2 as severity_order
1497where pg_size_bytes(current_setting('shared_buffers')) = pg_size_bytes('128MB');
1498
1499-- INFO: work_mem current value
1500insert into health_results
1501select
1502 'INFO' as severity,
1503 'System Health' as category,
1504 'work_mem Setting' as check_name,
1505 'System' as object_name,
1506 '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,
1507 current_setting('work_mem') || ' (max_connections: ' || current_setting('max_connections') || ')' as current_value,
1508 '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,
1509 'https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM' as documentation_link,
1510 5 as severity_order;
1511
1512-- MEDIUM: work_mem still at 4MB PostgreSQL default
1513insert into health_results
1514select
1515 'MEDIUM' as severity,
1516 'System Health' as category,
1517 'work_mem At Default' as check_name,
1518 'System' as object_name,
1519 '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,
1520 current_setting('work_mem') as current_value,
1521 '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,
1522 'https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM' as documentation_link,
1523 3 as severity_order
1524where pg_size_bytes(current_setting('work_mem')) = pg_size_bytes('4MB');
1525
1526-- INFO: effective_cache_size current value
1527insert into health_results
1528select
1529 'INFO' as severity,
1530 'System Health' as category,
1531 'effective_cache_size Setting' as check_name,
1532 'System' as object_name,
1533 '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,
1534 current_setting('effective_cache_size') as current_value,
1535 '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,
1536 'https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE' as documentation_link,
1537 5 as severity_order;
1538
1539-- INFO: maintenance_work_mem current value
1540insert into health_results
1541select
1542 'INFO' as severity,
1543 'System Health' as category,
1544 'maintenance_work_mem Setting' as check_name,
1545 'System' as object_name,
1546 'Current value of maintenance_work_mem. Used by VACUUM, CREATE INDEX, ALTER TABLE, and each autovacuum worker.' as issue_description,
1547 current_setting('maintenance_work_mem') as current_value,
1548 '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,
1549 'https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM' as documentation_link,
1550 5 as severity_order;
1551
1552-- INFO: Transaction ID wraparound risk per database
1553insert into health_results
1554select
1555 'INFO' as severity,
1556 'System Health' as category,
1557 'Transaction ID Wraparound Risk' as check_name,
1558 datname as object_name,
1559 '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,
1560 datname || ': XID age ' || trim(to_char(age(datfrozenxid), 'FM999,999,999,990')) ||
1561 ' (' || round(age(datfrozenxid)::numeric * 100 / 2000000000, 1)::text ||
1562 '% of wraparound window, ~' ||
1563 trim(to_char(greatest(2000000000::bigint - age(datfrozenxid)::bigint, 0), 'FM999,999,999,990')) ||
1564 ' remaining)' as current_value,
1565 '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,
1566 'https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND' as documentation_link,
1567 5 as severity_order
1568from
1569 pg_database
1570where
1571 datallowconn = true;
1572
1573-- INFO: Checkpoint statistics (PG15/16: pg_stat_bgwriter, PG17+: pg_stat_checkpointer)
1574insert into health_results
1575select
1576 'INFO' as severity,
1577 'System Health' as category,
1578 'Checkpoint Stats' as check_name,
1579 'System' as object_name,
1580 '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,
1581 _pg_firstaid_checkpoint_stats() as current_value,
1582 'If forced checkpoints are consistently above 50% of total, consider increasing max_wal_size. Reset stats with: SELECT pg_stat_reset_shared(''' ||
1583 case
1584 when current_setting('server_version_num')::int >= 170000 then 'checkpointer'
1585 else 'bgwriter'
1586 end ||
1587 ''').' as recommended_action,
1588 'https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-BGWRITER-VIEW' as documentation_link,
1589 5 as severity_order;
1590
1591-- INFO: Server role (primary vs standby)
1592insert into health_results
1593select
1594 'INFO' as severity,
1595 'System Info' as category,
1596 'Server Role' as check_name,
1597 'System' as object_name,
1598 '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,
1599 case
1600 when pg_is_in_recovery() then 'Standby (replica)'
1601 else 'Primary'
1602 end as current_value,
1603 'No action needed — informational.' as recommended_action,
1604 'https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE' as documentation_link,
1605 5 as severity_order;
1606
1607-- INFO: Connection utilization
1608insert into health_results
1609select
1610 'INFO' as severity,
1611 'System Health' as category,
1612 'Connection Utilization' as check_name,
1613 'System' as object_name,
1614 'Current connection usage as a percentage of max_connections. Includes all connection states, not just active queries.' as issue_description,
1615 count(*)::text || ' total / ' || current_setting('max_connections') || ' max (' ||
1616 round(100.0 * count(*) / current_setting('max_connections')::int, 1)::text || '% used)' as current_value,
1617 'If consistently above 80%, consider a connection pooler such as PgBouncer. Reserve headroom for superuser connections (superuser_reserved_connections).' as recommended_action,
1618 'https://www.postgresql.org/docs/current/runtime-config-connection.html' as documentation_link,
1619 5 as severity_order
1620from
1621 pg_stat_activity;
1622
1623-- INFO: Installed Extensions
1624 insert
1625 into
1626 health_results
1627 select
1628 'INFO' as severity,
1629 'System Info' as category,
1630 'Installed Extension' as check_name,
1631 'System' as object_name,
1632 'Installed Postgres Extension' as issue_description,
1633 pe.extname || ':' || pe.extversion as current_value,
1634 'Before updating to the latest minor/major version of PG, verify extension compatability' as recommended_action,
1635 'https://youtu.be/mpEdQm3TpE0?si=VMcHBo1VnDfGZvtI&t=937' as documentation_link,
1636 --Link is from a fantastic talk from SCALE 22x on bugging pg_extension maintainers!
1637 5 as severity_order
1638from
1639 pg_extension pe;
1640-- INFO: Server Uptime
1641 insert
1642 into
1643 health_results
1644 select
1645 'INFO' as severity,
1646 'System Info' as category,
1647 'Server Uptime' as check_name,
1648 'System' as object_name,
1649 'Current Uptime of Server' as issue_description,
1650 current_timestamp - pg_postmaster_start_time() as current_value,
1651 'No Recommendation - Informational' as recommended_action,
1652 'N/A' as documentation_link,
1653 5 as severity_order;
1654-- INFO: Log Directory
1655 with ld as (
1656select
1657 current_setting('log_directory') as log_directory
1658 )
1659 insert
1660 into
1661 health_results
1662 select
1663 'INFO' as severity,
1664 'System Info' as category,
1665 'Is Logging Enabled' as check_name,
1666 'System' as object_name,
1667 'If no log file is present, this indicates logging is not enabled' as issue_description,
1668 ld.log_directory as current_value,
1669 'Logging enabled will assist with troubleshooting future issues. Dont you like logs?' as recommended_action,
1670 'For self-hosting: https://www.postgresql.org/docs/current/runtime-config-logging.html /
1671 For AWS Aurora/RDS: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.html /
1672 For GCP Cloud SQL: https://docs.cloud.google.com/sql/docs/postgres/flags /
1673 For Azure Database for PostgreSQL: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-server-parameters
1674 ' as documentation_link,
1675 5 as severity_order
1676from
1677 ld;
1678-- INFO: Log File(s) Size(s)
1679begin
1680 with ls as (
1681select
1682 ROUND(sum(stat.size) / (1024.0 * 1024.0), 2) || ' MB' as size_mb
1683from
1684 pg_ls_dir(current_setting('log_directory')) as logs
1685cross join lateral
1686 pg_stat_file(current_setting('log_directory') || '/' || logs) as stat)
1687 insert
1688 into
1689 health_results
1690 select
1691 'INFO' as severity,
1692 'System Info' as category,
1693 'Size of ALL Logfiles combined' as check_name,
1694 'System' as object_name,
1695 '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,
1696 ls.size_mb as current_value,
1697 'Set log_rotation_age and size for proper rotation of log files. This will prevent runaway log sizes.' as recommended_action,
1698 'For self-hosting:https://www.postgresql.org/docs/current/runtime-config-logging.html /
1699 For AWS Aurora/RDS: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.html /
1700 For GCP Cloud SQL: https://docs.cloud.google.com/sql/docs/postgres/flags /
1701 For Azure Database for PostgreSQL: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-server-parameters
1702 ' as documentation_link,
1703 5 as severity_order
1704from
1705 ls;
1706exception
1707when insufficient_privilege then
1708 insert
1709 into
1710 health_results
1711 select
1712 'INFO' as severity,
1713 'System Info' as category,
1714 'Size of ALL Logfiles combined' as check_name,
1715 'System' as object_name,
1716 'Unable to check log file sizes - insufficient privileges' as issue_description,
1717 'Permission denied for pg_ls_dir' as current_value,
1718 '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,
1719 'For AWS Aurora/RDS: https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-log-files.html /
1720 For GCP Cloud SQL: https://docs.cloud.google.com/sql/docs/postgres/logging /
1721 For Azure Database for PostgreSQL: https://learn.microsoft.com/en-us/cli/azure/postgres/flexible-server/server-logs?view=azure-cli-latest
1722 ' as documentation_link,
1723 5 as severity_order;
1724end;
1725-- Return results ordered by severity
1726 return QUERY
1727 select
1728 hr.severity,
1729 hr.category,
1730 hr.check_name,
1731 hr.object_name,
1732 hr.issue_description,
1733 hr.current_value,
1734 hr.recommended_action,
1735 hr.documentation_link
1736from
1737 health_results hr
1738order by
1739 hr.severity_order,
1740 hr.category,
1741 hr.check_name;
1742-- Clean up
1743 drop table health_results;
1744end;
1745
1746$$ language plpgsql;