Docs / Connections: PostgreSQL

PostgreSQL checks

Every check Verbiqo can run on a PostgreSQL server, grouped by area. You never name a check: you ask in plain words; the model says which areas the question needs, and Verbiqo runs those checks first. Checks that find something serious, such as wraparound or IDs running out, are reported on any question.

How to read this page. Each section is one area. Why it helps says what you get from it. Ask is a question to type that runs it. The table under it lists what Verbiqo checks there: you do not run those one by one. When a check finds a problem, it appears under the answer as a finding, with its evidence and a suggested fix.

How to ask for each area

Attach the server with /pg and the connection’s name, then ask. Any of the words in the middle column brings that area in; a question can bring in several.

Right now: sessions, waits and locks

Why it helps: When something is slow or stuck now, it shows what the database is doing and what is holding it up, so you know whether the database is the cause and what to clear.

Ask The app is slow, what is the database doing?

Verbiqo samples the active sessions for 10 seconds (3 to 30, see sampling) and reports:

Queries and indexes

Why it helps: Shows which statements cost the most in total and where an index would help, so you fix the few queries that matter most.

Ask Which queries cost the most? Are any indexes missing?

From pg_stat_statements and the table and index statistics. pg_stat_statements covers every database on the server, so each statement names its database and role; one counted once per database and role is shown once, with its figures summed. A statement that runs in another database says so, and is not planned or given an index from the database Verbiqo is connected to: name its database in the question to look at its tables.

Index tests use hypothetical indexes: nothing is built, and they are cleared before Verbiqo answers. Without hypopg, only the full-scan suggestions are made, as before.

A statement’s plan

Why it helps: Explains why one statement is slow from its plan, without running it, so you can fix that statement.

Ask Why is this slow? SELECT * FROM orders WHERE status = $1

Paste a statement into the question. Verbiqo asks PostgreSQL for the plan without running it (it needs PostgreSQL 16 and pg_read_all_data, see Explain a slow statement). For an UPDATE, DELETE or INSERT, it plans the search for the rows and reads what writing each row costs.

Replication and availability

Why it helps: Shows whether replicas keep up, WAL is archived and IDs are safe from wraparound, so you hear about a failover or data-loss risk before it happens.

Ask Why is this replica falling behind?

PgBouncer

Why it helps: Shows whether clients wait in the pooler, and why, so you can tell a pool size problem from a slow database.

Ask Why are clients waiting for a connection?

When the attached address is a PgBouncer, Verbiqo reads its console (the virtual database pgbouncer at the same host, port and credentials; the account must be in stats_users or admin_users). Nothing beyond the attached address is reached; a direct server has no console and the read is skipped.

The whole cluster: standbys, Patroni and backups

Why it helps: Shows every node’s role and state, Patroni’s view and backups, so you know whether the cluster survives losing a node.

Ask Is the cluster healthy? Who is the leader?

From the attached node, Verbiqo reports the others as that node sees them (from a primary, its standbys with their state and lag; from a standby, its upstream). It does not connect to them: a question reaches only what is attached, so another node is read by attaching it in a conversation of its own (see Clusters). If the question itself asks for it, for example read the other nodes with this connection’s credentials, Verbiqo reaches the nodes the attached one reports, at the addresses it gives and with the same credentials; a saved connection is still never used. With a Prometheus source attached in the same conversation it adds Patroni and pgBackRest. A standby it did not read still shows its state, sync mode and replay lag as the primary sees them. A logical replication client (Debezium, a subscriber) is reported as a consumer with its slot and lag, not as a node to attach, and is not counted as a standby.

Vacuum, bloat and writes

Why it helps: Finds wasted space, vacuum falling behind and heavy WAL, so tables stay small and fast and disks do not fill.

Ask Is anything bloated? Why is WAL growing so fast?

Settings and memory

Why it helps: Judges each setting against what the server actually does, with the statement to change it, so you tune from evidence rather than rules of thumb.

Ask What should I tune on this server?

Settings are judged against what the server has been doing. Sizes that depend on the machine (memory, cores, storage) come from a Prometheus source attached in the same conversation (/prom name), or from what the question states (32 GB of memory, 8 cores, SSD); each finding says which. To see whether a change helped, see After a change.

IDs, indexes and statistics

Why it helps: Finds IDs about to run out, duplicate indexes and stale statistics: problems that build quietly until inserts fail or plans go wrong.

Ask Are any sequences close to running out?

These are read in one database at a time: the one connected to, or the one the question names. When the server has other databases (the default postgres alone does not count), Verbiqo says which it read and which it did not, and an answer may not call the whole server clear (“nothing needs vacuuming”) from one. Ask about all databases to read each.

Security

Why it helps: Shows who can do too much, what connects unencrypted and what anyone may change, so you close the gaps an audit would find.

Ask Security review of this database

The same checks are controls in the security review, which covers every saved connection at once and maps each to CIS Controls, ISO 27001 and SOC 2.

What Verbiqo can see

Why it helps: Lists what Verbiqo can and cannot see on this server, with the statement that turns each missing item on, so its answers are as complete as possible.

Ask What should I enable?

Ask what should I enable? for each optional extension and setting (pg_stat_statements, pgstattuple, hypopg, track_io_timing and others) marked ready or not, with the statements for your server. The monitoring role itself is in Set up PostgreSQL.

Ready-made reports

A report is one fixed reading of one area: no question, no model, the same output every time. Use one to look at an area yourself, or to share what it shows.

From a terminal, it prints the report:

verbiqo pg run orders blocking-tree

In verbiqo chat, it loads the report into the conversation so you can ask about it. help lists the reports:

/pg orders blocking-tree
/pg orders help

In the desktop apps, there is no report command: ask in your own words, and Verbiqo reads what the question needs.

Capacity checks for PostgreSQL are on Capacity checks, and what Verbiqo finds in the PostgreSQL log is on Log findings.