Database Maintenance Health Checks: SQL Server and PostgreSQL
Every production database accumulates the same class of problems over time. Indexes fragment as rows are inserted and deleted unevenly. Dead tuples pile up faster than background processes can reclaim them. Queries that once ran against a small table now scan tens of millions of rows because nobody added an index when the table grew. And somewhere in the application layer, a prepared statement that worked perfectly at launch is now scanning every partition of a table that has grown to five hundred partitions, because the plan was generated once and never revisited.
Left unchecked, these issues compound in ways that are hard to untangle after the fact. A fragmented index slows reads, slow reads delay vacuum, a stalled vacuum causes bloat, and bloat makes the missing index problem measurably worse. The two scripts in this post are designed to surface all of these issues in a single pass, so you can see the full picture before deciding where to act.
What We Are Checking and Why
Before diving into the scripts, it is worth understanding the five categories each one targets and what makes them worth checking systematically rather than reactively.
Fragmented indexes are the most visible form of storage decay. As rows are inserted, updated, and deleted, index pages fill unevenly. On SQL Server, fragmentation above 30% warrants a full REBUILD while anything between 10 and 30% usually calls for a REORGANIZE. On PostgreSQL, the equivalent concept is index bloat — dead index entries that accumulate until a REINDEX sweeps them away.
Page splits are what cause fragmentation in the first place. When a new row needs to be inserted into a full B-tree page, SQL Server splits that page into two, leaving both of them half empty. A high rate of page splits often signals a poor fill factor setting on the index, or an insert pattern that consistently targets the same region of the key space. PostgreSQL surfaces this indirectly through table and index bloat metrics rather than a dedicated page split counter.
Vacuum settings are a PostgreSQL concern with no direct SQL Server equivalent. PostgreSQL’s MVCC model never overwrites rows in place. It writes a new row version and marks the old one dead, leaving reclamation entirely to autovacuum. If autovacuum is misconfigured, disabled per table, or simply falling behind under write load, dead tuples accumulate and bloat grows. In the most severe case the database approaches transaction ID wraparound, which will cause a full outage if not addressed. This is the one issue in this post that can take a database completely offline with almost no warning.
Missing indexes are easy to over-report, and most tools do exactly that. Every query planner is happy to suggest an index that would help its specific query, but adding indexes has real costs because every write to the table must update every index on it. The scripts here filter aggressively, surfacing only indexes where the estimated benefit relative to actual query workload is large enough to clearly outweigh the write overhead.
Prepared statements on partitioned tables represent the most subtle problem on this list. Partitioned tables only deliver their performance promise when the query planner can eliminate irrelevant partitions at planning time. With prepared statements, the plan is generated once and reused across subsequent executions. If the partition key predicate is missing, wrapped in a function that makes it non-SARGable, or if PostgreSQL selects a generic plan after five executions that ignores partition bounds entirely, every execution silently scans all partitions. A query that should read one partition reads fifty, and nothing in the application logs will tell you why.
SQL Server: Maintenance Health Check
The script below targets SQL Server 2016 and later. Run it against the specific database you want to inspect, and ensure the executing account holds the VIEW SERVER STATE permission for DMV access.
Three tuning variables at the top of the script let you control the signal to noise ratio. Raise @MinPageCount to skip smaller indexes with limited impact, and raise @MinImpactScore if the missing index results are too numerous for your workload.
-- ============================================================
-- SQL SERVER DATABASE MAINTENANCE HEALTH CHECK
-- Run on SQL Server 2016+ (some DMVs require 2019+ as noted)
-- ============================================================
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @MinFragmentationPct FLOAT = 10.0;
DECLARE @MinPageCount INT = 500;
DECLARE @MinImpactScore FLOAT = 100000.0;
DECLARE @MinAvgFullScanCost FLOAT = 1.0;
PRINT '================================================================';
PRINT 'SQL SERVER MAINTENANCE HEALTH CHECK — ' + CONVERT(VARCHAR, GETDATE(), 120);
PRINT '================================================================';
-- 1. FRAGMENTED INDEXES
-- Uses sys.dm_db_index_physical_stats (LIMITED mode for speed)
-- 10-30% frag => REORGANIZE; >30% => REBUILD recommended
PRINT '--- [1] FRAGMENTED INDEXES ---';
SELECT
DB_NAME() AS database_name,
OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
ips.index_type_desc,
CAST(ips.avg_fragmentation_in_percent AS DECIMAL(5,1)) AS fragmentation_pct,
ips.page_count,
CASE
WHEN ips.avg_fragmentation_in_percent >= 30 THEN 'REBUILD'
WHEN ips.avg_fragmentation_in_percent >= 10 THEN 'REORGANIZE'
ELSE 'OK'
END AS recommended_action
FROM sys.dm_db_index_physical_stats(
DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.indexes i
ON i.object_id = ips.object_id
AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent >= @MinFragmentationPct
AND ips.page_count >= @MinPageCount
AND i.index_id > 0
ORDER BY ips.avg_fragmentation_in_percent DESC;
-- 2. PAGE SPLITS & FORWARDED FETCHES
-- High forwarded_fetch_count on a heap = lots of row movement.
-- High leaf_allocation_count from operational stats = page splits on index.
PRINT '--- [2] PAGE SPLITS & FORWARDED FETCHES ---';
SELECT
OBJECT_SCHEMA_NAME(ios.object_id) AS schema_name,
OBJECT_NAME(ios.object_id) AS table_name,
'HEAP' AS index_type,
ios.forwarded_fetch_count,
ios.range_scan_count,
'High forwarded_fetch_count indicates row forwarding on a heap. '
+ 'Consider adding a clustered index.' AS recommendation
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
JOIN sys.indexes i
ON i.object_id = ios.object_id
AND i.index_id = ios.index_id
AND i.type = 0
WHERE ios.forwarded_fetch_count > 1000
ORDER BY ios.forwarded_fetch_count DESC;
SELECT
OBJECT_SCHEMA_NAME(ios.object_id) AS schema_name,
OBJECT_NAME(ios.object_id) AS table_name,
i.name AS index_name,
ios.leaf_allocation_count AS page_splits_proxy,
ios.nonleaf_allocation_count AS nonleaf_splits,
ios.leaf_insert_count,
ios.leaf_update_count,
'High leaf_allocation_count relative to inserts/updates suggests frequent '
+ 'page splits. Consider increasing fillfactor or rebuilding the index.'
AS recommendation
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
JOIN sys.indexes i
ON i.object_id = ios.object_id
AND i.index_id = ios.index_id
WHERE i.type > 0
AND ios.leaf_allocation_count > 1000
AND (ios.leaf_insert_count + ios.leaf_update_count) > 0
AND (CAST(ios.leaf_allocation_count AS FLOAT)
/ NULLIF(ios.leaf_insert_count + ios.leaf_update_count, 0)) > 0.1
ORDER BY ios.leaf_allocation_count DESC;
-- 3. HIGH-IMPACT MISSING INDEXES
-- Only surfaces indexes with a meaningful estimated impact score.
-- Impact = avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)
PRINT '--- [3] HIGH-IMPACT MISSING INDEXES ---';
SELECT TOP 30
ROUND(
migs.avg_total_user_cost
* migs.avg_user_impact
* (migs.user_seeks + migs.user_scans), 0) AS impact_score,
migs.user_seeks,
migs.user_scans,
ROUND(migs.avg_user_impact, 1) AS avg_benefit_pct,
OBJECT_SCHEMA_NAME(mid.object_id) AS schema_name,
OBJECT_NAME(mid.object_id) AS table_name,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id)
+ '_missing_' + CAST(mid.index_handle AS VARCHAR) + '] ON '
+ OBJECT_SCHEMA_NAME(mid.object_id) + '.' + OBJECT_NAME(mid.object_id)
+ ' (' + ISNULL(mid.equality_columns, '')
+ CASE WHEN mid.inequality_columns IS NOT NULL
AND mid.equality_columns IS NOT NULL THEN ', ' ELSE '' END
+ ISNULL(mid.inequality_columns, '') + ')'
+ CASE WHEN mid.included_columns IS NOT NULL
THEN ' INCLUDE (' + mid.included_columns + ')' ELSE '' END
+ ';' AS suggested_ddl
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig
ON mig.index_handle = mid.index_handle
JOIN sys.dm_db_missing_index_group_stats migs
ON migs.group_handle = mig.index_group_handle
WHERE mid.database_id = DB_ID()
AND ROUND(
migs.avg_total_user_cost
* migs.avg_user_impact
* (migs.user_seeks + migs.user_scans), 0) >= @MinImpactScore
ORDER BY impact_score DESC;
-- 4. CACHED PLANS DOING FULL SCANS ON PARTITIONED TABLES
-- Looks for plans in cache that reference partitioned tables
-- AND contain Table Scan or Clustered Index Scan operators.
PRINT '--- [4] CACHED PLANS: FULL SCANS ON PARTITIONED TABLES ---';
;WITH PartitionedTables AS (
SELECT DISTINCT
OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS full_name
FROM sys.partitions p
JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE p.partition_number > 1
OR EXISTS (
SELECT 1 FROM sys.partition_schemes ps
JOIN sys.indexes i2 ON i2.data_space_id = ps.data_space_id
WHERE i2.object_id = i.object_id
)
)
SELECT
qs.execution_count,
qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS avg_logical_reads,
qs.total_elapsed_time / NULLIF(qs.execution_count, 0) / 1000 AS avg_elapsed_ms,
qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000 AS avg_cpu_ms,
pt.full_name AS partitioned_table,
SUBSTRING(st.text, 1, 500) AS sql_text_preview,
qp.query_plan AS execution_plan_xml,
'Review plan XML for TableScan / Index Scan without partition elimination. '
+ 'Ensure predicates on the partition key are SARGable and that '
+ 'sp_executesql parameters match the partition key data type exactly.'
AS recommendation
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
JOIN PartitionedTables pt
ON st.text LIKE '%' + pt.full_name + '%'
WHERE cp.objtype IN ('Prepared', 'Proc', 'Adhoc')
AND CAST(qp.query_plan AS NVARCHAR(MAX))
LIKE '%TableScan%'
OR CAST(qp.query_plan AS NVARCHAR(MAX))
LIKE '%PhysicalOp="Clustered Index Scan"%'
AND qs.total_logical_reads / NULLIF(qs.execution_count, 0) > 10000
ORDER BY avg_logical_reads DESC;
-- 5. UNUSED INDEXES
-- Indexes being maintained on every write but never used for reads.
PRINT '--- [5] UNUSED INDEXES ---';
SELECT
OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
i.type_desc,
ISNULL(us.user_seeks, 0) AS user_seeks,
ISNULL(us.user_scans, 0) AS user_scans,
ISNULL(us.user_lookups,0) AS user_lookups,
ISNULL(us.user_updates,0) AS user_updates,
'DROP INDEX [' + i.name + '] ON ['
+ OBJECT_SCHEMA_NAME(i.object_id) + '].['
+ OBJECT_NAME(i.object_id) + '];' AS suggested_drop_ddl
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
ON us.object_id = i.object_id
AND us.index_id = i.index_id
AND us.database_id = DB_ID()
WHERE i.object_id > 100
AND i.index_id > 1
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND ISNULL(us.user_seeks, 0) = 0
AND ISNULL(us.user_scans, 0) = 0
AND ISNULL(us.user_lookups,0) = 0
AND ISNULL(us.user_updates,0) > 500
ORDER BY us.user_updates DESC;
PRINT 'Health check complete.';
PRINT 'NOTE: DMV stats reset on SQL Server restart.';The missing index output includes a ready to run CREATE INDEX statement for each suggestion. Treat these as strong leads rather than instructions to execute directly. Always verify that a suggested index does not duplicate an existing one, and consider consolidating multiple suggestions on the same table into a single composite index before applying anything. For the full scan results in section 4, the execution_plan_xml column can be opened directly in SSMS to visually confirm which operator is causing the scan and whether partition elimination is appearing in the plan at all.
PostgreSQL: Maintenance Health Check
The PostgreSQL script targets version 13 and later and should be run as a superuser or a role with the pg_monitor privilege available since PostgreSQL 14. For the best results, two extensions should be installed before running the script.
-- Add to postgresql.conf before installing the extension:
-- shared_preload_libraries = 'pg_stat_statements'
-- Then run in your database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgstattuple;pg_stat_statements powers the slow query analysis in sections 3 and 4. pgstattuple gives accurate index bloat figures at the page level and without it the script falls back to a heuristic estimate, which is still useful but considerably less precise on tables with variable row widths.
-- ============================================================
-- POSTGRESQL DATABASE MAINTENANCE HEALTH CHECK
-- Compatible with PostgreSQL 13+
-- Run as superuser or a role with pg_monitor privilege (PG14+)
-- ============================================================
\timing on
-- 1. INDEX BLOAT
-- Uses pgstattuple if available, otherwise heuristic estimate.
-- High bloat => REINDEX CONCURRENTLY recommended.
\echo '--- [1] INDEX BLOAT ---'
DO $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pgstattuple') THEN
RAISE NOTICE 'pgstattuple available — using accurate bloat stats';
ELSE
RAISE NOTICE 'pgstattuple not installed. Falling back to heuristic estimate.';
RAISE NOTICE 'Install with: CREATE EXTENSION pgstattuple;';
END IF;
END $$;
WITH constants AS (
SELECT
current_setting('block_size')::NUMERIC AS bs,
23 AS hdr,
4 AS ma
),
bloat_calc AS (
SELECT
schemaname, tablename, indexname, bs,
CEIL(
(reltuples * (6 + ma
- CASE WHEN index_max_width < ma THEN index_max_width ELSE ma END
+ nulldatawidth + hdr
))::NUMERIC / (bs - 20)
) AS est_pages_live,
relpages AS actual_pages
FROM (
SELECT
n.nspname AS schemaname, t.relname AS tablename,
ix.relname AS indexname, ix.relpages, ix.reltuples,
c.bs, c.hdr, c.ma,
COALESCE((SELECT AVG(sta.stawidth) FROM pg_statistic sta
WHERE sta.starelid = t.oid), 50) AS index_max_width,
COALESCE((SELECT SUM(sta.stawidth) FROM pg_statistic sta
WHERE sta.starelid = t.oid), 50) AS nulldatawidth
FROM pg_class ix
JOIN pg_index id ON id.indexrelid = ix.oid
JOIN pg_class t ON t.oid = id.indrelid
JOIN pg_namespace n ON n.oid = ix.relnamespace
CROSS JOIN constants c
WHERE ix.relkind = 'i'
AND ix.relpages > 100
AND n.nspname NOT IN ('pg_catalog','pg_toast','information_schema')
) sub
)
SELECT
schemaname, tablename, indexname,
actual_pages,
GREATEST(est_pages_live, 1) AS est_pages_live,
ROUND(
100.0 * (actual_pages - GREATEST(est_pages_live, 1))
/ NULLIF(actual_pages, 0), 1
) AS bloat_pct,
CASE
WHEN actual_pages > GREATEST(est_pages_live, 1)
THEN 'REINDEX CONCURRENTLY ' || schemaname || '.' || indexname || ';'
ELSE 'OK'
END AS recommended_action
FROM bloat_calc
WHERE actual_pages > GREATEST(est_pages_live, 1)
AND ROUND(
100.0 * (actual_pages - GREATEST(est_pages_live, 1))
/ NULLIF(actual_pages, 0), 1
) >= 20
ORDER BY bloat_pct DESC
LIMIT 50;
-- 2. VACUUM & AUTOVACUUM HEALTH
-- 2a. Tables overdue for vacuum or analyze
-- 2b. Tables with custom autovacuum storage options
-- 2c. Transaction ID wraparound risk
\echo '--- [2a] TABLES OVERDUE FOR VACUUM / ANALYZE ---'
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
c.reltuples::BIGINT AS est_row_count,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
psut.n_dead_tup AS dead_tuples,
CASE WHEN c.reltuples > 0
THEN ROUND(100.0 * psut.n_dead_tup / c.reltuples, 1)
ELSE 0 END AS dead_pct,
psut.last_vacuum, psut.last_autovacuum,
psut.last_analyze, psut.last_autoanalyze,
CASE
WHEN psut.n_dead_tup > 10000
AND (psut.last_autovacuum IS NULL
OR psut.last_autovacuum < NOW() - INTERVAL '3 days')
THEN 'VACUUM ANALYZE ' || n.nspname || '.' || c.relname || ';'
WHEN psut.last_analyze IS NULL AND psut.last_autoanalyze IS NULL
THEN 'ANALYZE ' || n.nspname || '.' || c.relname || ';'
ELSE 'OK'
END AS recommendation
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_stat_user_tables psut ON psut.relid = c.oid
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog','pg_toast','information_schema')
AND (psut.n_dead_tup > 5000
OR psut.last_analyze IS NULL
OR psut.last_autoanalyze IS NULL)
ORDER BY dead_tuples DESC
LIMIT 40;
\echo '--- [2b] TABLES WITH CUSTOM AUTOVACUUM SETTINGS ---'
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
unnest(c.reloptions) AS storage_option
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.reloptions IS NOT NULL AND c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog','information_schema')
AND EXISTS (
SELECT 1 FROM unnest(c.reloptions) opt
WHERE opt LIKE 'autovacuum%' OR opt LIKE 'toast.autovacuum%'
)
ORDER BY n.nspname, c.relname;
\echo '--- [2c] TRANSACTION ID WRAPAROUND RISK ---'
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
age(c.relfrozenxid) AS xid_age,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
CASE
WHEN age(c.relfrozenxid) > 1500000000
THEN '*** CRITICAL — VACUUM FREEZE IMMEDIATELY ***'
WHEN age(c.relfrozenxid) > 1000000000
THEN 'WARNING — schedule VACUUM FREEZE soon'
ELSE 'OK'
END AS wraparound_risk
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','t')
AND age(c.relfrozenxid) > 500000000
ORDER BY age(c.relfrozenxid) DESC;
-- 3. HIGH-IMPACT MISSING INDEXES
-- Sequential scan rate vs index usage on large tables.
-- Drill-down via pg_stat_statements for actual slow queries.
\echo '--- [3] HIGH-IMPACT MISSING INDEXES ---'
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
c.reltuples::BIGINT AS est_rows,
pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
psut.seq_scan,
psut.seq_tup_read,
psut.idx_scan,
CASE WHEN psut.idx_scan > 0
THEN ROUND(psut.seq_scan::NUMERIC / psut.idx_scan, 2)
ELSE psut.seq_scan END AS seq_to_idx_ratio,
psut.seq_tup_read / NULLIF(psut.seq_scan, 0) AS avg_rows_per_seq_scan,
'High sequential scan rate. Identify query predicates via '
|| 'pg_stat_statements and add targeted indexes.' AS recommendation
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_stat_user_tables psut ON psut.relid = c.oid
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog','pg_toast','information_schema')
AND c.reltuples > 10000
AND psut.seq_scan > 100
AND (psut.idx_scan = 0
OR psut.seq_scan::FLOAT / NULLIF(psut.idx_scan, 0) > 0.5)
AND psut.seq_tup_read / NULLIF(psut.seq_scan, 0) > 1000
ORDER BY psut.seq_tup_read DESC
LIMIT 30;
\echo '--- [3b] TOP SEQ-SCAN QUERIES (pg_stat_statements) ---'
SELECT
ROUND(total_exec_time::NUMERIC, 0) AS total_exec_ms,
calls,
ROUND(mean_exec_time::NUMERIC, 2) AS avg_exec_ms,
ROUND(rows::NUMERIC / NULLIF(calls, 0), 0) AS avg_rows_returned,
LEFT(query, 300) AS query_preview
FROM pg_stat_statements
WHERE query ILIKE '%SELECT%'
AND query NOT ILIKE '%pg_stat%'
AND mean_exec_time > 500
AND rows / NULLIF(calls, 0) > 500
ORDER BY total_exec_time DESC
LIMIT 20;
-- 4. PREPARED STATEMENTS ON PARTITIONED TABLES
-- Partition pruning failures stem from: mismatched parameter types,
-- non-SARGable predicates, or generic plans ignoring partition bounds.
\echo '--- [4a] PARTITIONED TABLES IN THIS DATABASE ---'
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
CASE p.partstrat
WHEN 'r' THEN 'RANGE'
WHEN 'l' THEN 'LIST'
WHEN 'h' THEN 'HASH'
ELSE p.partstrat::TEXT END AS partition_strategy,
pg_get_partkeydef(c.oid) AS partition_key,
(SELECT COUNT(*) FROM pg_inherits pi
WHERE pi.inhparent = c.oid) AS partition_count,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_partitioned_table p ON p.partrelid = c.oid
WHERE n.nspname NOT IN ('pg_catalog','information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC;
\echo '--- [4b] SESSION PREPARED STATEMENTS ON PARTITIONED TABLES ---'
SELECT
ps.name AS prepared_stmt_name,
ps.statement AS sql_text,
ps.parameter_types,
pt.nspname || '.' || pt.relname AS partitioned_table,
'Run EXPLAIN (ANALYZE, BUFFERS) on this statement to verify '
|| 'partition pruning is occurring.' AS action
FROM pg_prepared_statements ps
CROSS JOIN LATERAL (
SELECT n.nspname, c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_partitioned_table p ON p.partrelid = c.oid
WHERE ps.statement ILIKE '%' || c.relname || '%'
LIMIT 1
) pt
ORDER BY ps.name;
\echo '--- [4c] HIGH-COST QUERIES ON PARTITIONED TABLES (pg_stat_statements) ---'
WITH partitioned AS (
SELECT n.nspname || '.' || c.relname AS full_name, c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_partitioned_table p ON p.partrelid = c.oid
WHERE n.nspname NOT IN ('pg_catalog','information_schema')
)
SELECT
ss.calls,
ROUND(ss.mean_exec_time::NUMERIC, 2) AS avg_exec_ms,
ROUND(ss.total_exec_time::NUMERIC, 0) AS total_exec_ms,
ROUND(ss.rows::NUMERIC / NULLIF(ss.calls,0), 0) AS avg_rows,
ROUND(ss.shared_blks_read::NUMERIC
/ NULLIF(ss.calls, 0), 0) AS avg_blks_read,
p.full_name AS partitioned_table,
LEFT(ss.query, 400) AS query_preview,
'Run EXPLAIN (ANALYZE, BUFFERS) and check for Seq Scan across '
|| 'multiple partitions. Verify the partition key predicate is '
|| 'present and SARGable. Check plan_cache_mode if using PREPARE.'
AS recommendation
FROM pg_stat_statements ss
CROSS JOIN LATERAL (
SELECT p2.full_name, p2.relname
FROM partitioned p2
WHERE ss.query ILIKE '%' || p2.relname || '%'
LIMIT 1
) p
WHERE ss.mean_exec_time > 200
AND ss.rows / NULLIF(ss.calls, 0) > 1000
ORDER BY ss.total_exec_time DESC
LIMIT 25;
-- 5. TABLE BLOAT SUMMARY
-- Dead tuple accumulation as a percentage of total rows.
\echo '--- [5] TABLE BLOAT SUMMARY ---'
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
psut.n_live_tup AS live_rows,
psut.n_dead_tup AS dead_rows,
CASE WHEN (psut.n_live_tup + psut.n_dead_tup) > 0
THEN ROUND(100.0 * psut.n_dead_tup
/ (psut.n_live_tup + psut.n_dead_tup), 1)
ELSE 0 END AS dead_pct,
psut.last_autovacuum,
'VACUUM ANALYZE ' || n.nspname || '.' || c.relname || ';'
AS suggested_action
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_stat_user_tables psut ON psut.relid = c.oid
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog','pg_toast','information_schema')
AND psut.n_dead_tup > 1000
AND CASE WHEN (psut.n_live_tup + psut.n_dead_tup) > 0
THEN 100.0 * psut.n_dead_tup
/ (psut.n_live_tup + psut.n_dead_tup)
ELSE 0 END >= 5.0
ORDER BY dead_pct DESC
LIMIT 40;
\echo 'Health check complete.'
\echo 'Stats reset on pg_stat_reset() or cluster restart.'
\echo 'For partition scan issues, also review the plan_cache_mode GUC.'The most urgent result to check is section 2c, the wraparound risk query. A table with an xid_age above 1.5 billion needs attention before anything else on this list — run VACUUM FREEZE on it immediately and investigate why autovacuum failed to keep up. For the partition scan results in section 4c, the query_preview column gives you enough of the query to run EXPLAIN (ANALYZE, BUFFERS) manually and confirm whether partition pruning is actually happening. The tell is seeing Seq Scan on tablename_p0001, tablename_p0002 repeated for every partition rather than a targeted scan against one or two.
Running These Regularly
A single execution of either script gives you a snapshot, but the DMVs and statistics views they query accumulate data since the last server restart or stats reset. The most useful signal comes from running them against a database that has been under representative production load for at least several days.
On SQL Server, consider scheduling the missing index and unused index sections to run weekly and logging results to a history table. DMV data resets on every restart, so capturing it before planned maintenance windows preserves the context you need to make good decisions. On PostgreSQL, a pg_cron job that calls pg_stat_reset() on a known monthly schedule means you always know the age of your statistics and can reason about trends rather than isolated snapshots. Separately, keep an eye on pg_stat_bgwriter to catch autovacuum falling behind under write load before it becomes a vacuum debt problem.
On both platforms, never apply missing index suggestions or drop unused indexes without validating against the full workload. A missing index DMV only knows about queries that have run since the last restart, and an unused index might be critical for an end of month report that simply has not executed yet in this cycle. The scripts surface the candidates. The judgment about what to act on remains yours.