What every check says

info autovacuum

No large high-churn tables use default cleanup thresholds.

evidence: no large high-churn tables on default scale factors

no action needed

ignore if: Nothing to do: this confirms the healthy state.

warning backup.archiver

archive_mode is off on a production-shaped target, so point-in-time recovery is impossible — only the last base backup exists.

evidence: archive_mode = off (replica present: False)

Set archive_mode = on with a reliable archive_command and restart.

ignore if: this database has no recovery-point requirement.

warning backup.freshness

No base-backup activity is recorded for this database within the 7-day window, so pgdba cannot confirm any usable backup exists.

evidence: no recorded base-backup activity since the statistics reset 16.8h ago (window 7d; PITR); the backup repository itself is invisible from SQL — verify it externally

Verify the backup tooling externally and rehearse with pgdba verify-restore.

ignore if: backups are taken by tooling the server cannot see.

warning columns.waste +27 more finding(s)

This column reserves a wider number type than its values ever need, wasting space on every row.

evidence: "public"."bench_big"."id" is int8 but measured range [1, 1000000] fits int4; saves 4B/row x 1000000 rows = 3.8 MB [measured-save]

Dry-run only: ALTER TABLE "public"."bench_big" ALTER COLUMN "id" TYPE int4; -- rewrite lock; verify no FK type mismatch on referencing columns

ignore if: the extra range is reserved for future growth.

warning concurrency.idle_txn

Session 85398 (postgres via unknown app) sits 2m inside an open transaction without doing anything, holding back cleanup for every table it touched.

evidence: pid 85398 (postgres via unknown app): idle in transaction, transaction open 2m, last query: SELECT 1; no safety net configured (timeout is 0); single sample (MVCC)

Ask the owning application to commit or roll back; set idle_in_transaction_session_timeout (e.g. '5min') as the safety net. Cancel first (pg_cancel_backend), terminate only as a last resort.

ignore if: the session belongs to a long-lived intentional transaction.

critical config +8 more finding(s)

Leaving it as is means undersized buffer pool keeps forcing heap/index reads to disk.

evidence: [ACTION] shared_buffers current=128MB reload=restart restart-required. 128MB is 0.8% of RAM (16384MB); below the 15% floor, raise toward 25% on a dedicated host.

current=128MB proposed=4096MB. ALTER SYSTEM SET shared_buffers = '4096MB'; then restart Postgres.

ignore if: the current value was chosen deliberately and workload metrics look healthy.

info dummy

This self-test finding proves the check pipeline ran end to end.

evidence: dummy check ran

no action needed

ignore if: Nothing to do: this confirms the healthy state.

warning extensions +6 more finding(s)

A helper extension is missing, so the checks that depend on it run with reduced accuracy.

evidence: hypopg is not installed; dependent checks (slow-query ranking, HypoPG verification) are degraded

Self-hosted, copy-paste: CREATE EXTENSION IF NOT EXISTS "hypopg"; then verify: SELECT * FROM hypopg_list_indexes();

ignore if: you do not need the degraded checks; otherwise install the named extension.

info index.advisor

No slow query cleared the gates, so no new index would pay off right now.

evidence: no slow queries over the min-calls/min-time gates

no action needed

ignore if: Nothing to do unless slow queries appear; re-check after the workload grows.

warning index-space +1 more finding(s)

This index never speeds up reads but still slows down every write.

evidence: index public.pgdba_ex_unlogged.pgdba_ex_unlogged_pkey has idx_scan=0 and size 16384 bytes; pure write amplification

DROP INDEX CONCURRENTLY pgdba_ex_unlogged_pkey; -- dry-run text only; drops remove write-amplification but also read acceleration, so confirm zero scans over a full workload cycle first

ignore if: the index serves a rare but important query; confirm zero use across a full workload cycle first.

info matview

No repeated heavy aggregation stands out above the thresholds.

evidence: no repeated heavy aggregation/join fingerprint exceeds thresholds (100 calls, 60000 ms total)

no action needed

ignore if: Nothing to do: this confirms the healthy state.

info partitioning

This table is already split into partitions; only the old-data cleanup schedule needs attention.

evidence: public.pgdba_ex_events is already partitioned; lifecycle: detach or drop old partitions per the retention policy (e.g. monthly DROP of partitions older than N months, pg_partman can automate)

no action needed

ignore if: old partitions are already dropped or detached on schedule.

warning pgbouncer

Idle sessions hold most of the connection pool while pressure runs high, so real work can stall waiting for a slot.

evidence: [ACTION] recommend transaction-mode pgbouncer: oldest idle-in-transaction 92s. total=2 max_connections=100.

Sizing: reserve=10 (superuser/autovacuum headroom), pool budget=90, suggested default_pool_size=22 (sum of pool sizes must stay under max_connections minus headroom). Caveats: (1) server-side prepared statements break in transaction mode; (2) advisory locks do not survive across transactions; (3) LISTEN/NOTIFY needs session pooling; (4) temp tables / WITH HOLD cursors need session mode; (5) prefer SET LOCAL over SET inside pooled transactions.

ignore if: Ignore only if the spike already passed; otherwise put a transaction-mode pooler in front.

info pooler

Connection pressure and idle share are both low, so the database handles its connections directly.

evidence: 6 connections vs max_connections=100 (pressure 6%), idle share 0%: no saturation and no idle bloat

do not add pgbouncer; tune the app pool instead (max size, idle timeout, statement timeout) and re-check if connections approach 80% of max_connections

ignore if: Unless connections approach 80 percent of the maximum; re-check under peak load.

info queries.blocking

No session is currently stuck waiting on another session.

evidence: no ungranted locks with a distinct blocker pid

ignore if: Nothing to do: this confirms the healthy state.

warning queries.cache_hit +1 more finding(s)

Table reads often miss memory and hit disk, which is much slower.

evidence: heap hit ratio 0.8673 < 0.99; db_size_mb=513.3 worst_tables=[bench_big=0.610, pgdba_ex_waste=0.940, demo_orders=0.946, pgdba_ex_unlogged=0.981, pgdba_ex_churn=0.984]

Review shared_buffers and working set; see config-advisor shared_buffers guidance

ignore if: the working set genuinely exceeds memory; otherwise review memory settings.

info queries.cpu

This describes where database time goes right now: queries, disk waits, lock waits, or idle app time.

evidence: verdict=idle-heavy on_cpu=0 io=0 lock=0 idle=1 other=0 ungranted_locks=0 sample=[] top_cpu=[cpu_share=10.9% calls=1 hit=99.6% :: UPDATE pgdba_ex_churn SET v = v || $1 WHERE id % $2 < $3; cpu_share=6.2% calls=1 hit=100.0% :: INSERT INTO pgdba_ex_waste (flag, count_as_text, guid, doc) SELECT $1, g::text, md5(g::text), json_build_object($2, g % $3) FROM generate_series($4, $5) g; cpu_share=5.4% calls=1 hit=99.6% :: UPDATE pgdba_ex_churn SET v = v || $1 WHERE id % $2 < $3; cpu_share=5.3% calls=1 hit=100.0% :: INSERT INTO pgdba_ex_churn SELECT g, repeat($1, $2) FROM generate_series($3, $4) g; cpu_share=5.1% calls=11 hit=2.0% :: SELECT count(*) AS total, count(*) FILTER (WHERE "payload" ~ $1) AS matched FROM "public"."bench_big" WHERE "payload" IS NOT NULL]

Host is mostly idle/app-waiting; no CPU action needed

ignore if: the verdict matches expectations; act only on lock contention or query-bound verdicts during slow periods.

info queries.explain +4 more finding(s)

This is one of the costliest queries measured, shown with its plan summary.

evidence: plan=unavailable mean=5315.7ms total=5316ms calls=1 parameterized beyond safe replay or non-SELECT; replay needs sampled constants :: UPDATE pgdba_ex_churn SET v = v || $1 WHERE id % $2 < $3

ignore if: the call count is tiny or the plan already looks optimal.

info queries.routines +2 more finding(s)

Routine timing is switched off, so the functions burning time stay hidden.

evidence: track_functions=none: pg_stat_user_functions self_time unavailable

SET track_functions='all' (needs reload/restart) to attribute routine CPU

ignore if: routine timing is not needed; otherwise switch routine tracking on.

warning queries.slow

A few queries consume most of the database time, so fixing them first buys the biggest speedup.

evidence: top 16 slow statements by total time: mean=5315.7ms total=5316ms calls=1 rows=600000 hit=99.6% :: UPDATE pgdba_ex_churn SET v = v || $1 WHERE id % $2 < $3; mean=3015.0ms total=3015ms calls=1 rows=1000000 hit=100.0% :: INSERT INTO pgdba_ex_waste (flag, count_as_text, guid, doc) SELECT $1, g::text, md5(g::text), json_build_object($2, g % $3) FROM generate_series($4, $5) g; mean=2655.5ms total=2655ms calls=1 rows=600000 hit=99.6% :: UPDATE pgdba_ex_churn SET v = v || $1 WHERE id % $2 < $3; mean=2604.6ms total=2605ms calls=1 rows=1000000 hit=100.0% :: INSERT INTO pgdba_ex_churn SELECT g, repeat($1, $2) FROM generate_series($3, $4) g; mean=228.1ms total=2509ms calls=11 rows=11 hit=2.0% :: SELECT count(*) AS total, count(*) FILTER (WHERE "payload" ~ $1) AS matched FROM "public"."bench_big" WHERE "payload" IS NOT NULL; mean=2228.5ms total=2229ms calls=1 rows=1000000 hit=100.0% :: INSERT INTO pgdba_ex_waste (flag, count_as_text, guid, doc) SELECT $1, g::text, md5(g::text), json_build_object($2, g % $3) FROM generate_series($4, $5) g; mean=2114.8ms total=2115ms calls=1 rows=1000000 hit=100.0% :: INSERT INTO pgdba_ex_waste (flag, count_as_text, guid, doc) SELECT $1, g::text, md5(g::text), json_build_object($2, g % $3) FROM generate_series($4, $5) g; mean=2050.3ms total=2050ms calls=1 rows=1000000 hit=100.0% :: INSERT INTO pgdba_ex_churn SELECT g, repeat($1, $2) FROM generate_series($3, $4) g; mean=2045.8ms total=2046ms calls=1 rows=1000000 hit=100.0% :: INSERT INTO pgdba_ex_waste (flag, count_as_text, guid, doc) SELECT $1, g::text, md5(g::text), json_build_object($2, g % $3) FROM generate_series($4, $5) g; mean=172.9ms total=1902ms calls=11 rows=11 hit=1.1% :: SELECT avg(pg_column_size("payload"))::float AS "w_payload" FROM "public"."bench_big"; mean=1765.4ms total=1765ms calls=1 rows=600000 hit=99.6% :: UPDATE pgdba_ex_churn SET v = v || $1 WHERE id % $2 < $3; mean=1702.7ms total=1703ms calls=1 rows=600000 hit=99.6% :: UPDATE pgdba_ex_churn SET v = v || $1 WHERE id % $2 < $3; mean=1412.9ms total=1413ms calls=1 rows=1000000 hit=100.0% :: INSERT INTO pgdba_ex_churn SELECT g, repeat($1, $2) FROM generate_series($3, $4) g; mean=1385.4ms total=1385ms calls=1 rows=1000000 hit=100.0% :: INSERT INTO pgdba_ex_churn SELECT g, repeat($1, $2) FROM generate_series($3, $4) g; mean=1262.1ms total=1262ms calls=1 rows=130000 hit=100.0% :: INSERT INTO big_plain SELECT g, repeat($1, $2) FROM generate_series($3, $4) g; mean=448.4ms total=448ms calls=1 rows=300000 hit=100.0% :: INSERT INTO pgdba_sch_unused.t SELECT g, md5(g::text) FROM generate_series($1, $2) g

Run EXPLAIN (see queries.explain findings); add missing indexes or rewrite heavy filters

ignore if: Ignore queries that run rarely despite high totals; focus on high-call-count offenders.

warning queries.temp_wal +1 more finding(s)

Sorts and hashes outgrow their memory budget and spill to disk files, which slows queries.

evidence: temp_files=33 temp_bytes=234526016

Review work_mem: spills mean sorts/hashes exceed it; see config-advisor work_mem guidance

ignore if: spills are rare; otherwise review the per-query memory setting.

info query.lint.plan +14 more finding(s)

Estimated plan sequentially scans bench_big (~1000000 rows) — no usable index for this predicate shape.

evidence: Seq Scan on bench_big, Plan Rows 416667 [estimated generic plan (parameters unbound)]: SELECT avg(pg_column_size("payload"))::float AS "w_payload" FROM "public"."bench_big"

Add a supporting index for the predicate, or confirm the scan is cheaper than random I/O.

ignore if: the scan is genuinely selective or the table is hot in cache.

warning query.lint.runtime +17 more finding(s)

Measured 6836 temp blocks written — this fingerprint really spills to disk (not an estimate).

evidence: temp_blks_written=6836 over 13 calls: INSERT INTO pgdba_ex_waste (flag, count_as_text, guid, doc) SELECT $1, g::text, md5(g::text), json_build_object($2, g % $3) FROM generate_series($4, $5) g

Raise work_mem for the workload, add an index matching the sort/join keys, or reduce sorted rows.

ignore if: spills are rare relative to calls.

info query.lint.static

Statement tracking is set to top-level only, missing statements inside functions; lint coverage has a blind spot.

evidence: pg_stat_statements.track = top

SET pg_stat_statements.track = 'all' (sighup);

ignore if: in-function statements are profiled another way.

info routine.dead_code

5 routine(s) have never been called in the recorded window; unused routines still cost maintenance and review time.

evidence: never called since the statistics reset: public.pgdba_ex_definer_fn, public.pgdba_ex_unsafe_fn, public.pg_stat_statements_info, public.pg_stat_statements, public.pg_stat_statements_reset (the statistics window is unknown)

Confirm with the owning team, then drop the truly unused routines with DROP FUNCTION.

ignore if: the routines are part of a public API or run rarely by design.

info routine.definer_crossref

Function public.pgdba_ex_definer_fn runs with its owner's privileges; the security audit reviews its search_path and ownership separately.

evidence: public.pgdba_ex_definer_fn: SECURITY DEFINER routine — see the security audit family for the authoritative findings

Run the security audit family (security.definer) for the pinned-search-path review.

ignore if: the security-audit family already cleared this routine.

info routine.parallel_safety

Function public.pgdba_ex_unsafe_fn runs parallel-unsafe but shows no writes or per-call variation, so marking it parallel-safe may let queries share the work.

evidence: public.pgdba_ex_unsafe_fn: PARALLEL UNSAFE, body shows no writes or per-call markers

ALTER FUNCTION "public"."pgdba_ex_unsafe_fn" PARALLEL SAFE; — verify correctness under parallel workers first.

ignore if: the routine is rare or trivially cheap.

info routine.volatility +4 more finding(s)

Function public.pgdba_ex_definer_fn is volatile but its body shows no writes and no per-call variation, so a stricter volatility marking may unlock planner optimizations.

evidence: public.pgdba_ex_definer_fn: VOLATILE with no write or per-call markers in the body (ACID)

ALTER FUNCTION "public"."pgdba_ex_definer_fn" STABLE; — verify with tests before relying on it.

ignore if: side effects exist outside the markers we scan.

info scale.replication

No standby is connected; the database currently has no warm copy, so a primary failure means a full restore.

evidence: primary side: no rows in the replication view

ignore if: high availability is provided outside the database.

info scale.vacuum_depth

Table pgdba_ex_churn updates mostly non-indexed paths rarely (0% of updates avoid index work), so every update churns its indexes too.

evidence: pgdba_ex_churn: 0 of 600000 updates were HOT (fillfactor)

Consider lowering the page fill (fillfactor) on the hottest indexes or the table so in-page updates fit.

ignore if: write amplification is not a measured problem here.

info schema.autovacuum

No large high-churn tables use default cleanup thresholds.

evidence: no large high-churn tables at default scale factors

no action needed

ignore if: Nothing to do: this confirms the healthy state.

warning schema.bloat +1 more finding(s)

Dead row versions are piling up faster than cleanup removes them, slowing scans of this table.

evidence: table "public"."pgdba_ex_churn": dead-tuple ratio 37.5% > 20%; never vacuumed (n_live_tup=1000000, n_dead_tup=600000, size 227 MB)

VACUUM (ANALYZE) "public"."pgdba_ex_churn"; -- then tune per-table autovacuum (see schema.autovacuum) if churn recurs

ignore if: Ignore while the background cleanup worker reclaims the space on its own; act only when dead rows keep growing.

warning schema.design.keys +23 more finding(s)

Table public.pgdba_ex_child has no primary key, so logical replication and most ORMs cannot reliably target individual rows in it.

evidence: public.pgdba_ex_child: no primary key constraint

ALTER TABLE "public"."pgdba_ex_child" ADD PRIMARY KEY (<key columns>); -- pick columns that are unique and never null

ignore if: the table is append-only scratch data replaced in bulk.

info schema.design.matviews

Materialized view public.mv_ex_summary shows no maintenance history, so its freshness is unknown — verify the refresh cadence.

evidence: public.mv_ex_summary: no vacuum/analyze record in the maintenance statistics

REFRESH MATERIALIZED VIEW "public"."mv_ex_summary"; consider CONCURRENTLY with a unique index.

ignore if: refresh runs are verified elsewhere.

critical schema.design.sequences

Sequence public.pgdba_ex_tight_seq (backing pgdba_ex_nokey.a) has consumed 90% of its range; at 100% every insert into the table starts failing.

evidence: public.pgdba_ex_tight_seq: 9 of 10 used (90.0%) for pgdba_ex_nokey.a (integer)

-- Online migration path: add a bigint column, backfill in batches, swap the default, then drop the integer column. See the identity-column procedure for pgdba_ex_nokey.a.

ignore if: the table is being migrated or truncated imminently.

info schema.design.types +2 more finding(s)

Column public.pgdba_ex_waste.doc uses the json type; jsonb stores the same data in a indexable, faster-to-query form.

evidence: public.pgdba_ex_waste.doc: json type

ALTER TABLE "public"."pgdba_ex_waste" ALTER COLUMN "doc" TYPE jsonb USING "doc"::jsonb;

ignore if: exact raw-text byte preservation is required.

warning schema.design.unlogged

Unlogged table public.pgdba_ex_unlogged skips durability for speed; after a crash its contents are truncated, so anything treated as durable here is not.

evidence: public.pgdba_ex_unlogged: unlogged table (relpersistence = 'u')

ALTER TABLE "public"."pgdba_ex_unlogged" SET LOGGED;

ignore if: the contents are rebuildable cache or scratch data.

info schema.hypopg

The extension that previews index benefit without building is missing, so verified advice is unavailable.

evidence: hypopg extension not installed; heuristic candidates are unverified (unknown cost delta)

Install hypopg to verify missing-index candidates with hypothetical EXPLAIN deltas

ignore if: you only create small, cheap indexes; otherwise install it to unlock verification.

info schema.invalid_indexes

No failed index builds were found.

evidence: no invalid indexes

no action needed

ignore if: Nothing to do: this confirms the healthy state.

warning schema.missing_indexes +3 more finding(s)

A query pattern looks like it wants an index, but no filter columns were identified to build it on.

evidence: table "public"."bench_big": seq_scan=119, seq_tup_read=39000000, idx_scan=26 [heuristic-unverified; hypopg absent]; no filter columns identified

Inspect pg_stat_statements for queries against "public"."bench_big", then CREATE INDEX CONCURRENTLY on the filter/join columns; install hypopg to verify

ignore if: the table is small; inspect slow queries for filter columns before creating anything.

info schema.partitioning

No oversized unpartitioned tables were found.

evidence: no monolithic tables exceed 10GB/50M rows

no action needed

ignore if: Nothing to do: this confirms the healthy state.

info schema.redundant_indexes

No duplicate index groups were found.

evidence: no duplicate/redundant index groups found

no action needed

ignore if: Nothing to do: this confirms the healthy state.

info schema.unused_indexes

This unused index backs a uniqueness rule, so it cannot be dropped without dropping the rule first.

evidence: index "public"."pgdba_ex_unlogged_pkey" on "public"."pgdba_ex_unlogged" has idx_scan=0 (16 kB) but backs a PRIMARY KEY or UNIQUE constraint

Do not DROP directly; drop the constraint instead, dry-run only: ALTER TABLE "public"."pgdba_ex_unlogged" DROP CONSTRAINT "pgdba_ex_unlogged_pkey"; -- verify no FK depends on it

ignore if: Ignore while the uniqueness rule is required; revisit only if the rule itself goes away.

critical security +7 more finding(s)

Login role 'postgres' holds SUPERUSER, BYPASSRLS, CREATEROLE, CREATEDB, REPLICATION, so a leak of that credential reaches far beyond the data the role needs.

evidence: login role 'postgres': SUPERUSER, BYPASSRLS, CREATEROLE, CREATEDB, REPLICATION (pg_roles)

ALTER ROLE postgres NOSUPERUSER NOBYPASSRLS NOCREATEROLE NOCREATEDB NOREPLICATION;

ignore if: 'postgres' is a tightly controlled admin account that intentionally needs SUPERUSER, BYPASSRLS, CREATEROLE, CREATEDB, REPLICATION.

warning security.audit_logging

The audit-logging baseline is incomplete, so break-ins, misuse, and slow-injection probes leave a thinner trail. A critical security finding exists in this run, so the gap is escalated.

evidence: audit baseline gaps: log_statement, log_connections, log_disconnections, pgaudit

ALTER SYSTEM SET log_statement = 'ddl';; ALTER SYSTEM SET log_connections = on;; ALTER SYSTEM SET log_disconnections = on;; add pgaudit to shared_preload_libraries (restart required);

ignore if: a platform-level audit trail already covers these signals.

critical security.auth

The server accepts unencrypted connections, so credentials and data cross the network in cleartext.

evidence: server setting ssl = off

set ssl = on with valid certificates (restart required);

ignore if: all clients connect over a trusted Unix socket only.

critical security.definer

Function public.pgdba_ex_definer_fn() runs as its owner but resolves unqualified names through the caller's search_path, so a trojan object can hijack it (security-definer).

evidence: public.pgdba_ex_definer_fn(): SECURITY DEFINER, no pinned search_path in proconfig (search-path)

ALTER FUNCTION "public"."pgdba_ex_definer_fn"() SET search_path = "public", pg_temp;

ignore if: every caller is fully trusted and no attacker writes schemas.

warning security.rls

Table public.demo_orders looks multi-tenant but has no row-level security, so any role with table privileges reads every tenant's rows.

evidence: public.demo_orders: RLS off (tenant-shaped column(s): tenant_id)

ALTER TABLE "public"."demo_orders" ENABLE ROW LEVEL SECURITY; then add a policy, e.g. CREATE POLICY tenant_isolation ON "public"."demo_orders" USING (tenant_id = current_setting('app.tenant_id')::integer);

ignore if: all writers are mutually trusted on every row.

warning security.stale +31 more finding(s)

Login role 'rconc_r1_9e3d0c' has no password set; it may rely on trust/peer mapping or be an open door, and the host-based config is not visible from SQL to tell which.

evidence: role 'rconc_r1_9e3d0c': rolpassword IS NULL (host-based config not visible from SQL; verify the mapping)

set a password, map the role explicitly, or DROP ROLE "rconc_r1_9e3d0c";

ignore if: the role authenticates via peer, cert, or SSO mapping.

warning storage +23 more finding(s)

Dead row versions and empty pages are making this table larger and slower to scan.

evidence: table public.pgdba_ex_events_9: dead-tuple ratio 0.0% (0 dead / 1 live), last vacuum/autovacuum 1000000000.0 days ago

VACUUM (ANALYZE) public.pgdba_ex_events_9; then tighten per-table autovacuum (see autovacuum.per-table findings)

ignore if: Ignore while the background cleanup worker reclaims the space on its own; act only when dead rows keep growing.

warning vector.extension

The vector extension is neither installed nor offered by this server, so similarity search stays unavailable.

evidence: vector extension is neither installed nor available

manual-required: install pgvector from your provider/marketplace, then run the allowlisted install: CREATE EXTENSION IF NOT EXISTS vector;

ignore if: you have no vector workloads.