What Guardioz checks

Guardioz checks a fixed set of PostgreSQL settings, the indexes in the databases it monitors, the tables whose automatic cleanup starts late, the replication slots on the server, the archiving of transaction logs, and transactions that delay automatic cleanup. This page lists every check, so you know what Guardioz reports on your server and what it does not cover.

Monitored databases are the databases on your server that the agent reads, up to 20 of them. See What the agent reads and sends and Limits.

The results arrive in ten groups. Each group has its own row on the Findings page, for one server. The checks filter decides which rows you see: Issues only shows the groups that found something, and All checks adds the ones that found nothing. Each entry here has the same four fields: what the check looks at, the severities it can raise, what you get when it finds something, and whether it needs more than one collection (the Needs history field).

A snapshot is one set of data the agent collects and sends, and the agent sends one about once an hour. See When the agent collects. A check runs on a snapshot that carries the data it needs, and a check that does not have that data reaches no verdict.

A check that found something is shown with a severity: Critical, Warning or Suggestion. A check that ran and found nothing is shown as OK. A check that reached no verdict is shown as Not evaluated; three checks on this page can report it, and a group in that state has no row on the Findings page. See Read your findings.

Check	Category	What it covers
Reliability settings	Settings	The fsync setting
Autovacuum	Settings	The autovacuum and track_counts settings
Monitoring visibility	Settings	The track_activities setting
Performance tuning	Settings	Seven settings for memory, the transaction log and background workers
Invalid indexes	Indexes	Indexes that PostgreSQL marks as not valid, not ready or not live
Duplicate indexes	Indexes	Indexes that are an exact copy of another index on the same table
Tables behind on cleanup	Tables	Tables that let dead rows build up for a long time before automatic cleanup starts
Replication slots	Replication	Physical and logical replication slots
Transaction log archiving	Archiving	An archiver that has stopped, fails or falls behind
Open transactions and cleanup	Databases	Transactions that delay automatic cleanup

The sections that follow use these six categories. The category filter on the Findings page lists the categories present in your current results.

Settings

Guardioz evaluates eleven PostgreSQL settings, in four groups. The agent reads the value that PostgreSQL is running with, once per collection. When you change a setting that PostgreSQL applies on a reload, the next collection sees the new value, about an hour later. The agent collects the rest of the settings too, and Guardioz reports no finding about them as settings. The one exception is Transaction log archiving, which reports archive_mode turned on without archive_command or archive_library.

All settings checks also need the profile of the machine with its total memory and its processor count. When the profile lacks either, no settings check reaches a verdict on that snapshot, and the Findings page keeps the result of the last snapshot in which they ran.

Almost every setting that Guardioz reports comes with a statement to copy: an ALTER SYSTEM SET line, followed by SELECT pg_reload_conf();. For the two settings that need a restart, shared_buffers and max_worker_processes, the second line is a comment that says so. One case has no statement at all: a max_wal_size that the data disk cannot hold safely. You run the statement yourself; Guardioz changes nothing on your server. See Apply a recommendation.

Reliability settings

What it looks at: the fsync setting, reported when it is not on.

Severities: Critical.

What you get: the setting with its current value, and the statement that turns it on.

Needs history: no. The first snapshot is enough.

Autovacuum

What it looks at: two settings, autovacuum and track_counts. Each one is reported when it is off. Autovacuum workers and cost settings are not part of this check. The number of dead rows that starts cleanup of one table is judged by a different check, Tables behind on cleanup.

Severities: Critical.

What you get: each setting that is off, with the statement that turns it back on. The finding says what stops while the setting is off, and what keeps running.

Needs history: no.

Monitoring visibility

What it looks at: the track_activities setting, reported when it is off.

Severities: Warning.

What you get: the statement that turns the setting back on. The finding says what you stop seeing while it is off.

Needs history: no.

track_activities is also what makes PostgreSQL report the statement, the state and the timings of each session. The Open transactions and cleanup check does not judge a transaction whose command text it cannot read.

Performance tuning

What it looks at: seven settings. Each one is judged from facts Guardioz reads on your server:

Setting	What the check uses
shared_buffers	Total RAM, the current value, the total size of the monitored databases, and how much data the server pushes out of its cache to make room for new reads
work_mem	Total RAM, the current shared_buffers value, the connection limit (max_connections), and the temporary files that queries write to disk
maintenance_work_mem	Total RAM, the processor count, the largest table and the largest B-tree index in the monitored databases, the autovacuum_work_mem setting, and the number of autovacuum workers
effective_cache_size	Total RAM and the current value
max_worker_processes	The processor count and the current value
max_wal_size	How much transaction log (WAL) the server generates, how often a save of its progress to disk (a checkpoint) was requested instead of started by the time schedule, which includes the checkpoints that transaction-log volume forces, the checkpoint_timeout and checkpoint_completion_target settings, and the free and total space on the data disk
checkpoint_completion_target	The current value

Severities: Warning or Suggestion, per setting. The row on the Findings page shows Warning when at least one of the seven settings is at Warning, and Suggestion otherwise.

What you get: for each reported setting, the current value, the recommended value and the statement to copy. Each setting also has its own page, which adds a confidence percentage. For shared_buffers, work_mem and max_wal_size that page shows the measured data the recommendation came from; for effective_cache_size and maintenance_work_mem it shows the server’s total memory. For a value sized from your server’s own workload (shared_buffers, a raise of work_mem, max_wal_size), the confidence percentage says how much of that workload has been measured so far: it starts low and rises as the agent collects more history. For the other settings it is a fixed value of the check.

shared_buffers and max_worker_processes carry the badge Requires restart: the statement stores the new value, and PostgreSQL runs with it after a restart. When the data disk does not have safe room for a larger max_wal_size, the finding says to add disk space first and gives no statement to apply. It still shows the value the setting would need, so you can see how much room to add. The max_wal_size check uses the free space on the data disk measured in the same collection, not the copy in the weekly machine profile, because free space can change a lot in a week. When that collection could not measure the data disk, Guardioz makes no max_wal_size recommendation.

Needs history: yes, for two of the seven settings. max_wal_size, and a recommendation to raise work_mem, come from measurements taken between two collections, so both need at least two collections. The other five settings reach a verdict on the first snapshot, and so does a recommendation to lower work_mem.

shared_buffers and maintenance_work_mem also use the weekly size measurement. The first collection after the installation carries it, so they do not wait a week. See When the agent collects.

Indexes

Both index checks read the catalog of tables and indexes, which the agent collects once a day, every hour during the first 24 hours after the registration, and in every collection that you start by hand. See Manage the agent service for how to start a collection on your server. Index sizes come from the most recent weekly measurement.

An index whose size is not measured yet is still reported. The list shows a dash in the Size column, and the index page shows Not measured yet. Not measured is a result, not an error, and the rest of the finding is still valid.

Invalid indexes

What it looks at: the indexes the agent collected in the monitored databases. Guardioz reports an index that PostgreSQL marks as not valid (queries ignore it), as not ready (writes do not update it), or as not live (it is being removed). An index usually enters that state when a build or rebuild that runs without blocking normal traffic (CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY) does not finish.

Severities: Critical or Warning, per index. The level is higher when the index backs a primary key, when it enforces uniqueness or backs another constraint and has no healthy copy, or when the index is large in itself or next to its database. A copy that REINDEX CONCURRENTLY made is always Warning, whatever its size (see below).

What you get: one row per index with its database, schema, table and size, and a short phrase for the problem. Each index also has its own page, with the evidence and the statement to run: REINDEX INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, or ALTER TABLE ... DROP CONSTRAINT when a named constraint owns the index. For an index that backs a primary key, Guardioz always recommends a rebuild and never a drop. Where the index can be recreated exactly as PostgreSQL reported it, the page also gives the CREATE INDEX statement that puts it back.

For a unique index that is not a primary key and that Guardioz recommends to rebuild, the statement starts with a comment: a rebuild of a unique index fails while the table holds rows with the same key, and each failed attempt leaves one more unusable copy. The catalog does not record why an index became unusable, so find and resolve those rows first, then run the rebuild.

A copy left by REINDEX CONCURRENTLY: during a rebuild without blocking, PostgreSQL builds a new copy named after the index with _ccnew at the end, and after the swap it names the old index with _ccold, sometimes followed by a number (_ccnew1, _ccold2). When the rebuild stops early, PostgreSQL leaves that index behind, and its documented recovery is to drop it. Guardioz recognises these names and gives DROP INDEX CONCURRENTLY IF EXISTS for them, at Warning, with a comment that names the index the copy belongs to when Guardioz can tell which one it is, and no statement that puts the copy back. The catalog looks the same while a rebuild is still running, so drop the copy only when no rebuild on that table is running: a running rebuild appears in the view pg_stat_progress_create_index. Dropping the copy while its rebuild runs does not block reads or writes, but it can make PostgreSQL cancel the rebuild.

Needs history: no. The size raises the severity, and it can make a removal the better step. Without it, Guardioz judges the index from the catalog alone.

Duplicate indexes

What it looks at: healthy indexes on the same table that are an exact copy of another one: the same columns or expressions, the same index type (for example btree), the same operator classes and collations, the same sort order, the same NULLS NOT DISTINCT setting and, when an index covers only part of the table, the same condition. Indexes that only overlap are not duplicates here: an index over one column, and an index over that column plus a second one, are not reported. When a group of identical indexes contains an invalid index, that invalid index is reported by Invalid indexes, and the healthy ones in the group are not reported here, so no index appears in two findings.

Severities: Critical or Warning, per index. The level is higher for a large redundant copy.

What you get: which index Guardioz recommends you keep, and why. A dropped index has to be built again if a query turns out to need it, and that takes time on a large table. Where the index can be recreated exactly as PostgreSQL reported it, the index page gives the CREATE INDEX statement that puts it back, next to the statement that removes it. The statement that removes the redundant copy is DROP INDEX CONCURRENTLY, or ALTER TABLE ... DROP CONSTRAINT when a named constraint owns the index. A primary key is never the one that is dropped, and a unique index is never dropped in favour of an index that is not unique.

Needs history: no.

Tables

Tables behind on cleanup

Automatic cleanup (autovacuum) of a table starts, among other reasons, after a number of its rows have become dead. A dead row is a row that an update or a delete replaced and that cleanup has not removed yet. PostgreSQL calculates that number from three settings, autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor and autovacuum_vacuum_max_threshold, and from the number of rows in the table. A larger table therefore waits for more dead rows before its cleanup starts, up to the limit that autovacuum_vacuum_max_threshold sets. A table can carry its own value for each of the three settings, as a storage option of that table (storage parameters).

What it looks at: the tables that the agent collected in the monitored databases. Guardioz reports a table that lets dead rows build up for a long time before cleanup starts. For each table the check uses:

• the number of dead rows that starts cleanup of that table, taken from the table’s own storage options where it has them, and from the server’s settings otherwise
• the row estimate that PostgreSQL keeps for the table
• how fast the dead rows of that table grew between two collections
• the measured size of the table and of each of its indexes
• the settings that limit how fast autovacuum works on this server, and the memory it can use

This check does not judge a table that has autovacuum switched off in its own storage options, a table whose own autovacuum_vacuum_max_threshold is -1, or a table whose storage options Guardioz cannot read. It reports nothing about how often a small table is cleaned, about ANALYZE, about freezing of old rows, or about how much of a table is unused space (bloat).

Severities: Warning.

What you get: one row per table with its database, the dead rows at the moment of the collection, their growth per day, the number of dead rows that starts cleanup today, the estimated space the dead rows take in the table, and the time of the last autovacuum. Each table also has its own page, with the same measurements and the time each one was read.

Where Guardioz can give a safe value, the table page carries the statement that lowers the number for that table only: ALTER TABLE ... SET (autovacuum_vacuum_max_threshold = ...). It needs no restart. Under If you need it back, the same page carries the statement that restores what the table had before. Guardioz gives a value only after it checked the added cleanup work against the measured size of the table and its indexes and against the settings that limit cleanup speed on your server, so that the extra work takes only a small part of what autovacuum can do there. See Start the cleanup of a table earlier.

A reported table can also have no statement. The finding about the table is still valid, and the table page names the reason:

• the size of the table or of one of its indexes is not measured yet; Guardioz does not estimate it, and the page says when the measurement is due
• the table has a GIN index; Guardioz does not calculate a value for such a table yet
• the table has its own cleanup cost settings, or cleanup speed limiting is switched off for it
• the cleanup cost or memory settings of the server are outside the range Guardioz calculates a value for
• lowering the number on every table in the finding would add more cleanup work than the part of the server’s cleanup capacity that Guardioz keeps for such changes; then no table in the finding gets a value

Cleanup makes space available for reuse inside the table. Smaller files on disk are not guaranteed. A lower number decides that cleanup of the table starts earlier, counted in dead rows. It does not decide when autovacuum reaches the table, or in which order it works through tables.

The sentence on the row of the group says how many tables Guardioz checked, in how many databases. It also counts the tables that had no stable measurement yet and are not covered by the verdict, and it names each database that was not evaluated, with the reason. The dead-row number that PostgreSQL keeps falls when cleanup runs, so growth can be read only over a period without cleanup. A database is not evaluated when its table statistics or its table catalog arrived incomplete, for example because the database has more tables than a collection carries (Limits). It is also not evaluated while an open transaction holds cleanup back in that database, or while something running there could not be judged for that: the dead rows Guardioz sees there can be rows that PostgreSQL was not allowed to remove, so they say nothing about the table’s own settings. See Open transactions and cleanup.

This check reports Not evaluated for the whole server in these cases: the server is a standby or its role cannot be read; autovacuum is off, or track_counts is not on; the server-wide autovacuum_vacuum_max_threshold is -1; a setting the check needs could not be read; the table statistics or the table catalog have not been collected; or no database could be evaluated. The group page names the reason.

Needs history: yes. Guardioz reports a table only after it measured the growth of its dead rows between two collections, so the first snapshot reports no table. The storage options and the row estimate come from the daily catalog, and the sizes from the weekly measurement; the first collection after the installation carries both. See When the agent collects.

Replication

Replication slots

A replication slot is a marker PostgreSQL keeps for one consumer of its transaction log. While the slot exists, PostgreSQL keeps the transaction logs that this consumer still needs. A physical slot serves a standby server; a logical slot serves a subscription or another tool that reads changes.

What it looks at: the physical and logical replication slots on the server. Temporary slots are not included. Slots that this server synchronizes from a primary while it is a standby are reported only when PostgreSQL has invalidated them. The check uses:

• whether the slot is active, and how long it has been inactive
• whether it still holds transaction logs, and how much
• the status and the invalidation reason that PostgreSQL reports for it
• the max_wal_size and wal_keep_size settings
• the free and total space on the disk that holds the transaction log
• the slot’s own history over recent collections

Severities: Critical, Warning or Suggestion, per slot.

What you get: one row per slot with a short phrase for the problem, its type, database, inactive time and the transaction-log span it still requires. The status that PostgreSQL reports for the slot is on its own page. Each slot also has its own page, with what was found and the next step.

Dropping a slot cannot be undone, and it stops replication for a consumer that still needs the slot. Before you drop one, find out what uses it: the slot page names its type and its database, and the next step tells you to check the consumer - a standby server, a logical subscriber, or a change data capture tool - and any cluster manager that maintains slots. Where dropping the slot is the right step, that page carries the pg_drop_replication_slot statement to copy. A slot that belongs to the initial table synchronization of a logical subscription never carries that statement. See Apply a recommendation.

Needs history: a slot is judged from the first snapshot, because PostgreSQL reports since when a slot has been inactive. Recognizing a consumer that is inactive on a schedule, for example a nightly backup, needs a longer run of history. Until then such a slot can be reported during the hours when it is idle.

When the server has no replication slots, the check passes and says No replication slots.

Archiving

Transaction log archiving

With archive_mode enabled, PostgreSQL hands every completed transaction log file to an archive command or an archive library, and keeps the file on disk until the archiver reports it as archived. While archiving is stopped or failing, those files cannot be recycled, and the space they take in pg_wal, the directory of the transaction log, grows with every new file until archiving works again. When the file system that holds pg_wal fills up, PostgreSQL shuts down, and the database stays offline until space is freed (setting up WAL archiving).

What it looks at: the archiver of the server, one per server. In every collection the check uses:

• the files that wait to be archived: how many there are, and how long the oldest one has been waiting by the server’s own clock. The agent reads file names and times, never the contents of a file
• the counters PostgreSQL keeps for the archiver: successful and failed attempts, and the name and time of the last archived file and of the last failed one
• the settings archive_mode, archive_timeout and wal_segment_size, and whether archive_command or archive_library is set
• whether the server is a primary or a standby

Guardioz reports the archiver in these cases: the oldest waiting file has waited longer than a working archiver needs; PostgreSQL recorded a failed archive attempt since the previous collection and no successful one after it; or archive_mode is enabled while neither archive_command nor archive_library is set.

This check sees only what PostgreSQL reports on your server. It does not look into the archive, and it does not read the server log. An archive command that reports success without storing the file looks healthy to it. An OK therefore says that nothing is waiting too long and that no unresolved failure was recorded. It does not say that your archive is complete or that a backup can be restored. The agent sends only whether archive_command is set, never its text.

Severities: Critical, Warning or Suggestion. Critical is used only when the oldest file has waited for hours and a large amount of transaction log is queued behind it. While archiving is behind and catching up, the finding says so.

What you get: one row for the archiver with the number of files waiting, how much transaction log waits to be archived, how long the oldest file has been waiting, and the last successful archive. That amount is what waits to be archived on this server. It is not the space you get back, and Guardioz does not compare it with the free space on the disk. The archiver has its own page, with what was found, why it matters, every measurement behind the verdict, and the next step.

There is no statement to copy. The next step is a diagnosis that you run yourself: the lines to look for in the PostgreSQL server log, running the archive command by hand for the oldest waiting file, and pgbackrest check when pgBackRest does the archiving. When archiving is enabled with nothing configured, the next step is to set archive_command or archive_library, or to set archive_mode to off, which needs a restart. The page also says what not to do: do not free space by deleting files from pg_wal, and do not replace the archive command with one that only returns success, because both break the chain of archived logs that a recovery needs.

This check reports Not evaluated in these cases: archive_mode is off; the server is a standby with archive_mode set to on, a mode in which only a primary archives; archive_mode is on and the role of the server could not be determined; or archiving could not be measured in that snapshot. A standby with archive_mode set to always does archive, so it is evaluated.

Needs history: no, for the files that wait: their age comes from the server’s own clock, so the first snapshot is enough. Counting failed attempts needs an earlier collection to compare with. When the two collections cannot be compared, for example after the archiver statistics were reset or the role of the server changed, the finding says that failed attempts could not be counted, and names the reason.

The agent collects once an hour, so a stopped archiver appears in Guardioz within about an hour or two. PostgreSQL archives a transaction log file only after the file is complete. On a server that writes little, a stopped archiver therefore shows up only after the next file is completed.

Databases

Open transactions and cleanup

What it looks at: the sessions and background jobs running in the databases of the server, and which of them were already open at the previous collection. The check covers every database except template0 and template1, and postgres when the server has other databases, so it also covers the databases above the limit of 20 monitored databases. A transaction that started after the previous collection is not reported, so a transaction shorter than the time between two collections does not appear. Replication connections and slots, standby servers and prepared transactions are not part of this check. Plain cleanup (VACUUM), including the autovacuum worker’s own, is not counted as a holder, because PostgreSQL ignores it when it decides what can be cleaned up.

Severities: Warning or Suggestion, per database. This check is one of the three that report Not evaluated: while the server is a standby, when its role cannot be read, and when what is running in a database cannot be read or judged. Not evaluated means that Guardioz reached no verdict, not that something is wrong on your server.

What you get: one section per affected database. It lists each transaction with its process, user, application, how long it has been open, and what it means for cleanup. For most transactions it also says what to do: finish the transaction or roll it back, or, for a maintenance job, let it finish. The finding shows PostgreSQL’s estimate of dead rows - rows that an update or a delete replaced and that cleanup has not removed yet - for the tables of that database that the agent measured, and whether that estimate went up between the two collections, with both collection times in the time zone of your account and the name of that zone. When that number is not there, the finding names the reason. There is no statement to copy: Guardioz offers no statement that cancels or ends a session, and it never ends one for you.

The sentence on the row of the group first says in how many databases a transaction has been open since the previous collection, and that cleanup in those databases cannot move past it at that moment. It then says which sessions and jobs the check looked at, and which kinds of holders are not part of the check.

Needs history: yes. The check needs an earlier collection of the server to compare with, so the first result says that there is nothing to report yet. The dead-row number needs two complete collections of the database.

Not covered today

Guardioz does not check:

• Whether your database or your server is up. No check reports an outage, and no email says that snapshots stopped arriving. The status dot on the Servers page shows how long a server has been silent, and the server page lists the gaps between snapshots and the PostgreSQL starts the snapshots show: see When snapshots stop arriving.
• Free disk space on its own. The agent measures it in every collection, and the max_wal_size and replication slot checks use it, but no check warns about a disk that fills up for any reason. The replication slot check reports low free space on the transaction-log disk only when an inactive slot holds a large part of it.
• Processor, memory and disk usage of the machine over time. The machine profile records what the machine has, once a week.
• Operating-system settings, such as the kernel memory settings, transparent huge pages or the CPU frequency governor. The agent reads them, and no check evaluates them.
• Data checksum failures that PostgreSQL counts.
• Slow queries and query plans
• Unused indexes
• Index bloat and table bloat
• Indexes that are missing
• Indexes that overlap without being an exact copy
• Indexes defined on a partitioned table itself. The indexes on its partitions are checked like any other index.
• Per-table autovacuum settings other than the number of dead rows that starts cleanup: ANALYZE thresholds, freeze ages and cost settings
• Tables that autovacuum cleans more often than they need
• Autovacuum workers and cost settings
• Replication lag of a running replica
• How close a database is to transaction-ID wraparound
• What your archive of transaction logs holds, and whether a backup can be restored
• Locks and deadlocks
• Long-running queries, other than the open transactions described on this page
• How many connections a server uses
• What your server costs
• PostgreSQL settings other than the eleven on this page, and archive_mode, archive_command and archive_library, which Transaction log archiving reads

The agent collects more than these checks use, so a source on What the agent reads and sends does not mean that Guardioz reports on it. Guardioz will add query analysis.

Next steps

Read your findingsWhat the Findings page shows, and what each state means.

Apply a recommendationCopy a statement, run it, and see the finding follow the change.

When the agent collectsWhat travels in a snapshot every hour, every day and every week.