pg2html is a PostgreSQL database assessment report
intended to be read by a DBA.
It collects configuration, database and object information, current activity, cumulative statistics and a number of indicators that can help identify areas worth investigating.
The report is deliberately broad: it is not a tuning recipe and it does not try to decide automatically whether a database is “healthy”. A DBA should use the report to build a picture of the system, identify anomalies and then investigate them in their operational context.
For the best results, the pg_stat_statements core extension
is strongly recommended.
The report is generated as an HTML file (pg.htm),
normally viewed together with style.css and
util.js.
Using style.css is suggested for readability,
using util.js enables extra features like table sorting.
The standard execution is:
psql [database] < pg2html.sql
The README recommends running it as the PostgreSQL OS user with superuser privileges on a supported version.
However, the script is known to work with all PostgreSQL versions from 8 onwards, with any user, and on any Postgres fork,
although not all information may be extracted and some queries might fail.
To avoid error messages in cloud-based PostgreSQL environments, where administrative users are not true superusers,
you can set the var_as_admin variable to 0.
The script generates:
pg.htmReports can be archived, for example, with:
mv pg.htm $(hostname -s).$PGDATABASE.$(date +%Y%m%d).htmThe report records the database, PostgreSQL version, reporting user, script version and report generation time. Always check these first: they define the context in which all subsequent observations have to be interpreted.
The report contains several different kinds of information.
These are settings and definitions that describe how PostgreSQL is configured.
Examples include:
A configuration value is not necessarily a problem because it differs from the PostgreSQL default. In many installations the difference is intentional.
Some sections are a snapshot taken while the report is running.
Examples include:
These values can change immediately after the report has been generated.
A DBA should therefore distinguish between:
“this was happening when I took the report”
and:
“this has been happening consistently”.
Other sections contain counters accumulated since a statistics reset or, in some cases, since the server or extension was started.
Examples include:
Always look for the statistics reset time before comparing or interpreting rates.
A large counter is not necessarily an indication of a problem. Its meaning depends on the observation interval and on the workload.
The report also calculates indicators such as:
These are useful for quickly finding things worth examining. They are not substitutes for DBA judgement.
The report is long. Reading every parameter from top to bottom is usually not the most effective approach.
A practical reading order is:
The order is not mandatory. It is intended to move from the general
situation to the areas requiring investigation.
Below, we present the content of all sections of the report.
The Summary is the first orientation point.
It gives the basic identity and scale of the database, including:
Use this section to establish the context before interpreting any other number.
For example, the same number of active sessions has a very different meaning on a small development database and on a large production system with hundreds of connections.
The server start time is particularly important when reading cumulative statistics: a recently restarted server naturally has much smaller counters than a server that has been running for months.
The Version section reports the PostgreSQL release and checks it against the release information known to pg2html.
Check:
A minor-version difference is not normally a performance diagnosis. It is primarily an operational and security/maintenance observation.
A DBA should nevertheless pay attention to old minor releases, especially when investigating a problem that might already have been fixed upstream.
The Databases section shows the databases in the cluster and their sizes.
The Schema/Object Matrix gives a high-level inventory of objects:
These sections are mainly about understanding the shape of the installation.
The number of objects can become relevant to planning and maintenance, but a large number by itself is not evidence of a performance problem.
The Constraints, Functions and Triggers sections add useful structural context.
When investigating a particular application, first identify its schema and its important tables. This makes the later space, VACUUM, index and statement sections much easier to read.
The Tablespaces and Space Usage sections show where database objects consume space.
Look at:
The important question is usually not:
“Is this object large?”
but:
“Is its size reasonable for its role, and is its size or growth relevant to the problem being investigated?”
The Biggest Objects section is particularly useful for finding the tables and indexes that deserve attention first.
The VACUUM section contains:
Do not interpret the absence of a manual VACUUM as a
problem. Autovacuum is normally the expected mechanism.
The important questions are:
The age of the statistics matters. A table that was analyzed yesterday and a table that has never been analyzed should not be treated the same way.
The High dead tuples section identifies tables with comparatively high numbers of dead tuples.
Dead tuples are a normal consequence of PostgreSQL MVCC. Their presence is not itself an error.
Investigate when dead tuples are large in relation to the table, continue to grow, or correlate with:
The report is therefore a starting point for investigation, not a
command to run VACUUM FULL.
pg2html reports estimated table and index bloat.
This distinction is important.
Bloat estimates are useful for ranking objects that may deserve investigation, but they are not an exact measurement of reclaimable disk space.
Read both:
A small table with a very high percentage may be much less important than a large table with a lower percentage but many gigabytes of excess space.
For example, the report can show both a “top size” and a “top percentage” list. They answer different questions.
Before deciding on a corrective action, investigate:
The XID section reports database and object transaction age together with indicators related to transaction ID wraparound.
This is an operationally important area.
Pay particular attention to:
A high XID age is not a performance KPI. It is a transaction-management risk.
Do not confuse a high table bloat percentage with XID wraparound risk: they are different phenomena.
The Users/Roles section provides an inventory of login roles, superusers, expiry dates, connection limits and role configuration.
Use it to answer questions such as:
The report also shows grants and selected security findings.
Treat security findings as things to investigate, not automatically
as vulnerabilities. For example, a SECURITY DEFINER
function can be perfectly legitimate, while an unrestricted
search_path may require closer examination.
The HBA section shows the effective authentication rules seen by pg2html.
Read these rules in their PostgreSQL order: the first matching rule determines authentication.
Pay attention to:
A rule such as 0.0.0.0/0 or ::/0 is not
automatically insecure: its impact depends on the database/user
restriction, authentication method and network exposure.
The HBA section should therefore be read together with the actual network architecture.
The Sessions section has several complementary views:
Start with the grouped views to understand where connections come from.
Then inspect individual sessions when something deserves attention.
Important states include:
activeidleidle in transactionAn idle session is not necessarily a problem. Connection
pools commonly maintain idle sessions.
idle in transaction deserves more attention because an
open transaction can keep old snapshots alive and interfere with
vacuuming and other operations.
For active sessions, inspect:
A long-running query is a symptom to investigate, not necessarily a bad query. Reporting, maintenance and intentional long-running operations exist.
The Locks section should be read as a snapshot.
A lock observed in the report tells you what was happening at the time of collection.
When locks are present, determine:
A single blocked session is very different from a persistent blocking chain affecting many sessions.
If the report is being used to investigate an incident, repeat the observation rather than assuming that one snapshot represents the normal state.
The Memory section collects the principal memory-related PostgreSQL settings.
Important parameters include:
shared_bufferswork_memmaintenance_work_memautovacuum_work_memeffective_cache_sizetemp_buffersDo not add these values together as if they represented one fixed memory allocation.
In particular, work_mem is not simply “memory reserved
for PostgreSQL”: it is a per-operation/workspace limit and several
operations and sessions can use it concurrently.
Memory parameters should therefore be interpreted together with:
The KPI section is an index to the most relevant observations.
It is divided into:
The Advisory Level column gives the threshold used by pg2html for that particular indicator.
It should be read as an advisory threshold, not as a universal PostgreSQL rule.
A value outside the advisory range means:
“look at this”
not:
“the database is broken”.
Examples include:
Start here when the objective is to identify current operational risks.
Examples include:
These values need context.
For example, a low TPS is not inherently bad: a database that is mostly idle may legitimately have very low TPS.
Likewise, a high statement rate is not inherently good or bad.
Use the KPIs to select the sections that deserve detailed examination.
The security KPIs summarize checks concerning items such as:
SECURITY DEFINERsearch_pathSecurity findings should normally be reviewed individually. The KPI is the index; the detailed sections provide the evidence.
This is normally the most important part of the report for a performance investigation.
It contains several complementary views:
The database statistics show activity such as:
Always consider the statistics reset time.
A hit ratio is a useful indicator of cache behaviour, but it should not be treated as a target to maximize blindly. A high hit ratio does not prove that a workload is fast, and a lower ratio may be perfectly reasonable for a workload dominated by sequential or intentionally uncached access.
If pg_stat_statements is available, this section is
particularly valuable.
Use it to identify statements that consume a significant amount of:
Look at both:
A statement executed millions of times with a small average cost may be more important than a very slow statement executed once. For this reason, the table is sorted by total execution time.
The report also provides information about the statistics interval. Check it before drawing conclusions from the counters.
The slow-query section is a shortlist, not a complete performance diagnosis.
For each candidate, consider:
The next step is normally to examine the actual SQL and, when appropriate, its execution plan.
Table statistics help connect workload with physical objects.
Look for:
Use these data together with the size and VACUUM sections.
A large table with many sequential scans is not automatically a problem. A sequential scan may be exactly what the optimizer should choose.
The useful question is whether the access pattern is appropriate for the workload and the table size.
The index section contains several useful lists:
The report also identifies tables without indexes, unique indexes or primary keys.
These lists are candidate lists, not automatic DDL recommendations.
An index with idx_scan = 0 may be unused during the
statistics interval.
Before removing it, check:
Never drop an index solely because it appears in the unused-index list.
A “missing index” finding is an indication that a particular access pattern might benefit from an index. It is not proof that an index should be created.
Validate it with:
These deserve attention because unnecessary indexes have a maintenance and storage cost.
Again, inspect the definitions and constraints before changing anything.
The partitioning section provides:
Partitioning is a design choice, not a performance improvement by definition.
When reading this section ask:
The report’s overpartitioning indicator is useful as a prompt for investigation, not as a universal limit.
The report exposes a large number of PostgreSQL parameters, including their current values and configuration source.
Do not read this section as a list of parameters that should be compared mechanically with PostgreSQL defaults.
Instead look for settings that are relevant to an observed symptom.
Examples:
A non-default parameter is evidence of a deliberate or historical configuration choice, not automatically a tuning error.
The Backup section reports the PostgreSQL physical backup/WAL archiving configuration and archiver statistics.
Check:
archive_mode = off is not intrinsically wrong. It is a
problem only when the database’s recovery/backup design requires WAL
archiving.
Always interpret this section against the actual backup and disaster-recovery architecture.
The Replication section shows replication configuration and, where applicable, primary/standby activity.
Look at:
A very small replication lag at the moment of the report is reassuring, but it does not prove that replication has never fallen behind.
Conversely, a non-zero lag is not automatically a problem: its importance depends on the recovery and availability requirements.
For a standby, also consider whether the observed state is consistent with its intended role.
The report identifies installed extensions and, depending on the PostgreSQL version and available extensions, includes additional statistics.
Some sections are explicitly version-specific.
This is important when comparing reports from different PostgreSQL versions: an absent section may simply mean that the corresponding statistic or catalog view does not exist on that release.
Additional statistics can provide deeper information about:
Treat these as supporting evidence for a diagnosis rather than as another list of KPIs.
When the report is generated because someone says “the database is slow”, a useful workflow is:
Check:
Without this, counters and rates can easily be misinterpreted.
Look at:
If the problem is not visible in the snapshot, do not assume that it did not happen. Use the cumulative statistics and historical information.
Use:
The objective is to identify the source of the workload before changing configuration.
Use statement statistics and table/index statistics.
Distinguish:
These lead to different investigations.
Look at:
Only after identifying the symptom should the corresponding parameters be examined.
This avoids the common mistake of tuning parameters simply because their values look unusual.
For a periodic DBA review, the emphasis is different.
Compare reports over time and look for trends:
A single report is a snapshot. A sequence of reports becomes a historical observation.
This is often much more useful than deciding whether one isolated number is “good” or “bad”.
When comparing reports, first verify:
Counters cannot always be compared directly.
For example, if statistics were reset between two reports, a lower counter does not mean that activity decreased.
For rates, prefer comparable observation intervals.
For instantaneous values such as current sessions or locks, remember that the reports may simply have been taken at different moments.
A threshold in the report is an advisory threshold.
Crossing it should lead to a question:
“Why is this value like this?”
not immediately to:
“What command should I run to fix it?”
PostgreSQL defaults are generic. Production systems often require different settings.
High TPS, high cache hit ratio, many connections, a large database or a large number of objects can all be perfectly normal.
Low activity may simply mean that the database is idle.
Sessions, locks, waits and replication state can change immediately.
Always consider when the statistics were reset and whether the observation period represents the normal workload.
In particular, bloat figures should be used to identify candidates for investigation, not treated as exact measurements.
An empty table can mean:
Check the surrounding context before interpreting an empty section.
When opening a new pg2html report, the following short checklist is usually enough for the first pass:
pg2html is best used as a DBA’s map of a
PostgreSQL installation.
It deliberately combines structural information, configuration, current activity, cumulative statistics and derived indicators in one report. The value is not in finding a single “health score”, but in being able to move quickly from an observation to the evidence needed to understand it.
A good reading of the report therefore follows this principle:
Use the report to find what deserves attention; use PostgreSQL itself to prove what is happening and decide what to do.