PostgreSQL without a DBA: ten checks, in the order they hurt

The order: five questions, ten checks

Order the checks by what a failure costs, not by the order of postgresql.conf. A wrong memory setting makes a server slow. A wrong durability setting can lose committed data. A forgotten replication slot can fill the disk and take the database offline. So the first three questions decide whether the database survives, and the last one decides how fast it runs.

The ten checks match the ten check groups that Guardioz documents. That is deliberate: the list is short enough to run by hand, and each item is a condition that builds up silently until users notice.

Set up a read-only role for the checks

Run every check as a dedicated login with the built-in pg_monitor role. The PostgreSQL 18 documentation says pg_monitor “allows reading/executing various monitoring views and functions” and is a member of pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables. Source: Predefined roles.

CREATE ROLE health_check LOGIN PASSWORD 'choose-a-long-password';
GRANT pg_monitor TO health_check;

That role reads every setting and every pg_stat_* view, including the sessions of other users. It does not read the rows of your tables, and it needs no superuser. Every query below is a SELECT or SHOW. None of them changes anything.

Guardioz uses the same pattern. Its installer creates a role named guardioz_monitor, grants it pg_monitor, and the agent connects over the local Unix socket in read-only sessions. Source: Guardioz requirements.

1. Can this server lose committed data?

One setting decides whether a committed transaction survives a crash. Check it first.

C1. fsync is on

With fsync on, PostgreSQL makes sure that updates reach the disk physically. The PostgreSQL 18 documentation warns that turning it off “can result in unrecoverable data corruption in the event of a power failure or system crash,” and that it is only advisable “if you can easily recreate your entire database from external data.” Source: WAL configuration.

SELECT name, setting, source FROM pg_settings WHERE name = 'fsync';

A bad result is off. The source column tells you where the value came from: the configuration file, the command line, or ALTER SYSTEM.

Fix it now, then find out who turned it off and why:

ALTER SYSTEM SET fsync = on;
SELECT pg_reload_conf();

In Guardioz: the Reliability settings check, at Critical. It reports the current value and the statement that turns fsync back on.

2. Can automatic cleanup stop?

PostgreSQL never overwrites a row in place. Updates and deletes leave dead rows behind, and autovacuum removes them. Three things stop that cleanup: autovacuum switched off, a transaction that stays open, and a table so large that the default trigger fires too late.

C2. autovacuum and track_counts are on

autovacuum runs the launcher that starts cleanup. The documentation adds that “track_counts must also be enabled for autovacuum to work,” because autovacuum decides from the statistics it collects. Both are on by default. Sources: Vacuuming, Statistics.

SELECT name, setting FROM pg_settings WHERE name IN ('autovacuum', 'track_counts');

-- tables where someone switched autovacuum off by hand
SELECT c.oid::regclass AS table_name
FROM pg_class c
WHERE c.relkind IN ('r', 'm')
  AND EXISTS (SELECT 1 FROM pg_options_to_table(c.reloptions) o
              WHERE o.option_name = 'autovacuum_enabled' AND NOT o.option_value::boolean);

A bad result is off for either setting, or any table in the second list. With autovacuum off, PostgreSQL still launches vacuums to prevent transaction ID wraparound, but routine cleanup of dead rows stops, so tables and indexes keep growing.

ALTER SYSTEM SET autovacuum = on;
ALTER SYSTEM SET track_counts = on;
SELECT pg_reload_conf();

ALTER TABLE my_table RESET (autovacuum_enabled);

In Guardioz: the Autovacuum check, at Critical. It checks the two settings. Per-table switches are outside that check, so the second query is worth running by hand.

C3. No transaction holds back cleanup

A transaction that stays open blocks cleanup on every table. The PostgreSQL 18 documentation says that “an open transaction prevents vacuuming away recently-dead tuples that may be visible only to this transaction,” so remaining idle for a long time “can contribute to table bloat.” The usual cause is an application that opens a transaction and waits, which shows as idle in transaction. Source: Client connection defaults.

SELECT pid, datname, usename, application_name, state,
       now() - xact_start AS open_for,
       left(query, 60) AS last_statement
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 10;

Look at the oldest rows. A transaction open for minutes during a batch job can be normal. One open for hours is a bug in the application or a forgotten session. The statement text appears only while track_activities is on (C7).

Fix the application first: commit or roll back promptly. For a stuck session, SELECT pg_terminate_backend(pid); ends it and rolls its transaction back. As a guard, set idle_in_transaction_session_timeout. Its default is 0, which disables it. Prepared transactions and replication slots can hold back cleanup too, as the documentation on routine vacuuming notes.

In Guardioz: the Open transactions and cleanup check, at Warning or Suggestion. It compares one collection with the next, so its first verdict arrives about an hour after install.

C4. No table waits too long for cleanup

Autovacuum starts on a table when dead rows pass a threshold: autovacuum_vacuum_threshold (50 by default) plus autovacuum_vacuum_scale_factor (0.2 by default) times the table size. On a large table, 20% is a lot of dead rows. PostgreSQL 18 adds autovacuum_vacuum_max_threshold, a cap on that number with a default of 100,000,000 rows. Sources: Vacuuming, PostgreSQL 18 release notes.

With the defaults, a table of one billion rows waits for 200 million dead rows before PostgreSQL 17 vacuums it. PostgreSQL 18 caps the wait at 100 million. A table of 100 million rows still waits for 20 million.

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

SELECT name, setting FROM pg_settings
WHERE name IN ('autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor',
               'autovacuum_vacuum_max_threshold');

Read the first query against the second. A table near the top with n_dead_tup in the millions and an old last_autovacuum is waiting for a trigger it rarely reaches. Lower the trigger for that table only, with a storage parameter such as ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = ...), and leave the server default alone.

In Guardioz: the Tables behind on cleanup check, at Warning. Where it can calculate a safe value, the finding carries the statement that lowers the trigger for that table.

3. Can the disk fill without warning?

Two features keep WAL files on disk on purpose: replication slots and WAL archiving. When either one stalls, pg_wal grows until something stops it. The PostgreSQL 18 documentation is direct about the end state: “If the file system containing pg_wal/ fills up, PostgreSQL will do a PANIC shutdown. No committed transactions will be lost, but the database will remain offline until you free some space.” Source: Continuous archiving.

C5. No replication slot holds WAL

A replication slot keeps every WAL file its consumer has not read yet. With the default max_slot_wal_keep_size of -1, “replication slots may retain an unlimited amount of WAL files.” A slot left behind by a removed replica or an old logical replication test keeps WAL forever. Source: Replication configuration.

SELECT slot_name, slot_type, database, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_held
FROM pg_replication_slots
WHERE NOT temporary
ORDER BY restart_lsn NULLS LAST;

A bad result is a slot with active = false and a large wal_held. The wal_status column says how far it went: extended means max_wal_size is exceeded but the files are still kept, and lost means the slot is no longer usable. Source: pg_replication_slots.

If nothing consumes the slot, drop it with SELECT pg_drop_replication_slot('slot_name');. The consumer then has to be set up again. To cap the damage in future, set max_slot_wal_keep_size, knowing that a standby that falls further behind loses the WAL it needs.

In Guardioz: the Replication slots check, at Critical, Warning, or Suggestion. It flags physical and logical slots that are inactive or hold too much WAL.

C6. WAL archiving works

With archive_mode on, PostgreSQL keeps each WAL file until the archiver copies it away. If archive_command and archive_library are both empty, archiving is paused, “but the server continues to accumulate WAL segment files.” If the command keeps failing, “the pg_wal/ directory will continue to fill with WAL segment files until the situation is resolved.” Sources: WAL configuration, Continuous archiving.

SELECT current_setting('archive_mode') AS archive_mode,
       current_setting('archive_command') <> ''
         OR current_setting('archive_library') <> '' AS archiver_set,
       (SELECT count(*) FROM pg_ls_archive_statusdir()
         WHERE name LIKE '%.ready') AS files_waiting,
       (SELECT min(modification) FROM pg_ls_archive_statusdir()
         WHERE name LIKE '%.ready') AS oldest_waiting_since,
       last_archived_time, failed_count, last_failed_time
FROM pg_stat_archiver;

A bad result is archive_mode on with archiver_set false, a growing files_waiting, an old oldest_waiting_since, or a recent last_failed_time. The function pg_ls_archive_statusdir() is available to pg_monitor by default. Source: System administration functions.

Read the server log for the archive command's error, fix the destination, and watch files_waiting fall. If archiving is not needed at all, turn archive_mode off, which needs a restart.

In Guardioz: the Transaction log archiving check, at Critical, Warning, or Suggestion. It reports an archiver that has stopped, fails, or falls behind, and archive_mode on without a command or library.

4. Can you see what is running?

When something goes wrong, the first question is what the server is running right now. One setting decides whether PostgreSQL can answer it.

C7. track_activities is on

track_activities “enables the collection of information on the currently executing command of each session.” It is on by default, and only superusers, roles with pg_read_all_stats, and the session owner can see the data. Source: Statistics.

SELECT name, setting FROM pg_settings WHERE name = 'track_activities';

With it off, pg_stat_activity shows sessions without their statements, and the open-transaction check in C3 loses its evidence. Turn it on with ALTER SYSTEM SET track_activities = on; and SELECT pg_reload_conf();.

In Guardioz: the Monitoring visibility check, at Warning. The finding says what you stop seeing while it is off.

5. Is the server slower than its hardware allows?

Only now look at speed. Two index problems waste work on every write, and eight settings decide how well PostgreSQL uses the machine.

C8. No invalid indexes

An index becomes invalid when CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY fails. The PostgreSQL 18 documentation says such an index “will be ignored for querying purposes because it might be incomplete,” yet “it will still consume update overhead.” A failed unique index also “continues to enforce its uniqueness constraint afterwards.” Source: CREATE INDEX.

SELECT n.nspname AS schema, t.relname AS table_name, c.relname AS index_name,
       i.indisvalid, i.indisready, i.indislive, i.indisprimary, i.indisunique,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT (i.indisvalid AND i.indisready AND i.indislive)
ORDER BY pg_relation_size(c.oid) DESC;

Any row is a finding. The documented recovery is to drop the index and build it again, or to run REINDEX INDEX CONCURRENTLY. Three rules from the Guardioz checks reference make that choice safer:

1. Rebuild an index that backs a primary key. Never drop it.
2. Before you rebuild a unique index, find and resolve the duplicate rows. A rebuild fails while they exist, and each failed attempt leaves one more unusable copy.
3. Indexes named with _ccnew or _ccold are leftovers of an interrupted REINDEX CONCURRENTLY. Drop them only when pg_stat_progress_create_index shows no rebuild running on that table.

In Guardioz: the Invalid indexes check, at Critical or Warning. Each index gets its own page with the evidence and the statement to run: rebuild, drop, or drop the owning constraint.

C9. No duplicate indexes

A duplicate index costs disk space and slows every insert and update on its table, and the planner gains nothing from it. Count only exact copies: the same columns or expressions, the same index type, operator classes, collations, sort order, NULLS NOT DISTINCT setting, and partial-index condition. An index on one column and another on that column plus a second one are not duplicates.

SELECT i.indrelid::regclass AS table_name,
       array_agg(i.indexrelid::regclass
                 ORDER BY i.indisprimary DESC, i.indisunique DESC,
                          i.indexrelid::regclass::text) AS identical_indexes,
       pg_size_pretty(sum(pg_relation_size(i.indexrelid))) AS total_size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indisvalid AND i.indisready AND i.indislive
GROUP BY i.indrelid, c.relam, i.indkey::text, i.indclass::text, i.indcollation::text,
         i.indoption::text, i.indnullsnotdistinct,
         coalesce(pg_get_expr(i.indexprs, i.indrelid), ''),
         coalesce(pg_get_expr(i.indpred, i.indrelid), '')
HAVING count(*) > 1;

Keep the first index in each row. The query lists an index that backs a primary key or a unique constraint first. Save the definition of each copy with SELECT pg_get_indexdef('index_name'::regclass);, then drop it with DROP INDEX CONCURRENTLY, which does not block reads or writes.

In Guardioz: the Duplicate indexes check, at Critical or Warning. It names the index to keep, says why, and gives the statement that puts a dropped copy back.

C10. Eight settings fit the server

Eight settings decide most of how PostgreSQL uses memory, WAL, and processors. Their right values depend on the machine and, for three of them, on the measured workload. The table lists what each value depends on, from the inputs that the Guardioz performance tuning check documents.

SELECT name, setting, unit, source, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem', 'maintenance_work_mem',
               'max_worker_processes', 'max_wal_size', 'checkpoint_completion_target',
               'log_hostname')
ORDER BY name;

Read source to see which values anyone ever changed, and pending_restart to see changes still waiting for a restart. The defaults for max_wal_size and checkpoint_completion_target are 1 GB and 0.9. Two deep dives on the Guardioz blog cover single settings: max_wal_size, explained and the logging setting that slows down new connections.

A RAM-based rule gets four of these eight close enough. The others need counters from the running server. Part 2 sorts tuning tools by whether they read those counters, and Part 3 shows what an AI assistant misses when it sees only the hardware.

In Guardioz: the Performance tuning check, at Warning or Suggestion. Each setting gets a recommended value, a confidence percentage, and an ALTER SYSTEM SET statement. shared_buffers and max_worker_processes carry a Requires restart badge.

What these ten checks do not cover

Ten checks are a floor, not a full audit. This list mirrors the Not covered today section of the Guardioz checks reference. A database administrator checks these too, so run them by hand.

Wraparound deserves the same weight as question 1. The documentation says that with fewer than three million transactions left before wraparound, the server refuses to assign new transaction IDs, and “only read-only transactions can be started.” Source: Routine vacuuming.

Run them once by hand, or every hour

A pass by hand tells you how the server looks today. It does not tell you what changed since last week, which finding matters most, or whether a value fits the workload. Those answers need history.

Guardioz is not an uptime monitor and not real-time monitoring. It does not tell you that the server stopped, and it draws no second-by-second graphs. Source: How Guardioz works.

Key facts at a glance

Defaults and quotes from the PostgreSQL 18 documentation and the Guardioz documentation, checked on November 4, 2026.

Frequently asked questions

What should I check first on a PostgreSQL server I inherited?

Check what can lose data or stop the server before you look at speed. In order: fsync is on, autovacuum and track_counts are on, no transaction stays open for hours, no replication slot or failing archiver keeps WAL on the disk, and track_activities is on. Then look for invalid and duplicate indexes and review eight memory, WAL, worker, and logging settings. A role with pg_monitor can run every check read-only.

Is it ever safe to turn fsync off in PostgreSQL?

Only when you can rebuild the whole database from another source. The PostgreSQL 18 documentation says that turning fsync off can result in unrecoverable data corruption after a power failure or system crash, and that it is only advisable if you can easily recreate your entire database from external data. On a production server, keep it on.

Is it safe to turn off autovacuum in PostgreSQL?

No, not on a production server. Without autovacuum, dead rows from updates and deletes stay in tables and indexes until someone runs VACUUM by hand. PostgreSQL still launches vacuums to prevent transaction ID wraparound, but routine cleanup stops. Autovacuum also needs track_counts, which collects the statistics it decides from.

What does idle in transaction mean in PostgreSQL?

It means a session opened a transaction and is waiting for the client, with the transaction still open. The PostgreSQL 18 documentation warns that an open transaction prevents vacuuming away recently-dead tuples and can contribute to table bloat, and it may also hold locks. The setting idle_in_transaction_session_timeout ends such sessions after a time you choose. Its default, zero, disables it.

Why is my PostgreSQL disk filling up with WAL files?

The two common causes are a replication slot that no consumer reads and a WAL archiver that fails or falls behind. With the default max_slot_wal_keep_size of -1, a slot may retain an unlimited amount of WAL. A failing archive command leaves pg_wal to fill until the problem is fixed. If the file system that holds pg_wal fills up, PostgreSQL does a PANIC shutdown and stays offline until you free space.

How do I find invalid indexes in PostgreSQL?

Query pg_index for indexes where indisvalid, indisready, or indislive is false. An index usually ends up invalid when CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY fails. Queries ignore it, but it still adds write overhead, and an invalid unique index still enforces uniqueness. Recover with REINDEX INDEX CONCURRENTLY, or drop it and build it again.

What privileges does a PostgreSQL health check need?

The built-in pg_monitor role is enough for the checks on this page. It is a member of pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables, so it can read every setting and every pg_stat view, including other users' sessions. It does not give access to the rows of your tables. No superuser is needed.

How often should I run a PostgreSQL health check?

Run the full pass after every configuration change, upgrade, or failover, and on a schedule in between. The conditions on this list build up over hours or days. That is why Guardioz collects about once an hour instead of every few seconds, according to its documentation.

Do I need a DBA to run PostgreSQL in production?

Not for routine health. You need one named person who owns these ten checks and acts on the findings. A PostgreSQL expert still pays off for query tuning, major upgrades, replication design, and restore drills.

Sources

PostgreSQL 18 documentation, checked on November 4, 2026.

• Write Ahead Log configuration (fsync, max_wal_size, archive_command)
• Automatic vacuuming configuration
• Run-time statistics (track_activities, track_counts)
• Client connection defaults (idle_in_transaction_session_timeout)
• Replication configuration (max_slot_wal_keep_size)
• Resource consumption
• Predefined roles (pg_monitor)
• System administration functions (pg_ls_archive_statusdir)
• CREATE INDEX (building indexes concurrently)
• Routine vacuuming (transaction ID wraparound)
• pg_replication_slots
• Continuous archiving
• The cumulative statistics system
• pg_stat_statements
• pgstattuple
• Release 18

Guardioz, checked on November 4, 2026.

• What Guardioz checks
• Requirements
• What the agent reads and sends
• How Guardioz works
• Pricing
• max_wal_size, explained (Guardioz blog)
• The logging setting that slows down new connections (Guardioz blog)