Docs / Connections: PostgreSQL

Ask PostgreSQL

Attach the server and ask the way you would ask a colleague. Verbiqo decides what to read, reads it with the monitoring role, and answers from what it read.

Attach

In Explain / Ask… with nothing selected, or in verbiqo chat:

/pg orders

Verbiqo shows the version, primary or standby, and how busy it is. Connect to the primary to see its standbys; to a standby to ask about that standby.

What to ask

Every check and the words that bring it in: PostgreSQL checks.

Questions no check covers

Available from Verbiqo 0.1.26. This is in the next release; 0.1.25 does not have it yet.

Why it helps: Ask anything reasonable about the server in PostgreSQL’s terms, not only what Verbiqo’s checks cover, and get the answer read from the server rather than told how to read it yourself.

Ask List tables. What columns does orders have? Which users can log in? Which tables are in schema billing?

1. What exists

   Verbiqo first reads what is there: the schemas with how many tables each has, the tables and views with their sizes (every name up to about 200; above that, the counts and the largest), and anything the question names. Names and sizes only, never rows.

2. The model reads

   The model writes its reads with the real names, and Verbiqo checks and runs each one, as for every question. If the model explains how to do it instead (psql’s \dt, for example), Verbiqo sends it back once to read.

3. Which one?

   When the answer depends on which one you mean, such as two tables called orders in different schemas, the model may ask you, offering only names that exist.

4. Long lists

   A read returns at most 500 rows, and the answer says when a list was cut. Say show the next 500 to continue: Verbiqo reads the next part itself.

Limits:

• One answer does not list thousands of names. A large list comes in parts: by schema, by a name pattern, or 500 at a time.
• Only what the monitoring account can see. PostgreSQL shows every table through pg_catalog, even to a role with no rights on them. Where it cannot see something, the answer says so rather than treating it as absent.
• A limited number of reads per question. A broad question may be answered in part, and the answer says what was not read.
• Checks keep their questions. A question one of Verbiqo’s checks covers (settings, slow queries, replication, sizes, whether a change helped, a pasted error or statement) takes that check, as before. The wording decides: For example, a question about indexes (list indexes on orders) takes the performance check.
• Organization limits. Where your organization limits how much Verbiqo reads per question, the list of what exists is not read first; the rest still applies.

Make the server faster

Work in this order. The first steps usually give the most.

1. Let Verbiqo see enough

   Ask What should I enable? It lists what is missing, such as pg_stat_statements or query plans, with the statements to turn each on.

2. Queries

   What are the slowest queries? Are any indexes missing? Then Why is this slow? for the worst of them.

3. Vacuum and tables

   Which tables need vacuuming? Is anything bloated? Table settings such as fillfactor are sized from each table’s own figures.

4. Settings

   What should I tune? Say what the machine is if no Prometheus source covers it: 32 GB of memory, 8 cores, SSD.

5. Check the result

   Apply what you choose, wait a few days, and ask Did the changes help? Then start again from step 2.

Verbiqo never changes the server. Every recommendation is a statement for you to review and run.

Check that a change helped

1. Review

   Ask What should I tune? Verbiqo saves a reading of the server on this computer: the settings, what it recommended, and the figures to compare later.

2. Apply

   Run the statements you choose. Reload or restart as each one says.

3. Wait

   Give it at least a day of normal work; a week if the workload has a weekly cycle.

4. Ask

   Ask Did the changes help? on the same computer, with the same connection.

Under the answer, Verbiqo shows:

• which recommendations you applied, applied differently, did not apply, or that still wait for a restart;
• when they took effect;
• a before-and-after table, measured per transaction and per call, so a busier week does not look like a slower one;
• a verdict for each change.

Each setting is judged by what it acts on:

After building an index

Ask Did the index help? or Is the new index used? Verbiqo keeps a reading of the indexes and table scans whenever it reviews settings, and when it suggests an index (at most once every 12 hours). For each index built since an earlier reading, it shows:

• whether PostgreSQL uses it, and how often;
• the time of the queries on its table, before and after, for the same work;
• how often the table is still read in full;
• a verdict: improved, worse or no measurable change for the queries on the table, or not enough data.

An index that has not been used says so, with the hours of work it was available. An index left invalid by a failed build is named, with the advice to drop it and build it again. Indexes dropped since the last reading are listed. Two indexes built on one table at the same time are each credited with their own use, but share the queries’ time, and Verbiqo says it cannot tell them apart. Where your organization limits how much Verbiqo reads per question, indexes are not read for this, and the follow-up covers settings only.

Good to know:

• Change a few related settings at a time. When two changes move the same measure, Verbiqo names both and says it cannot tell them apart.
• The answer cannot overrule a verdict. The model may explain it, but if it claims a different result, that sentence is removed.
• A review soon after a change does not recommend a value that is already set, and does not push a changed setting further on figures from before the change.
• A statistics reset between readings is noticed (pg_stat_reset(), pg_stat_statements_reset(), a crash): the comparison counts from the reset. If only some databases were reset, their totals are left out rather than misread. A clean restart keeps the statistics and changes nothing.
• Too little work, no verdict. A window with fewer than 1,000 transactions (asked again soon after, or a quiet weekend) gives not enough data: a few transactions can show any change.
• The readings stay on this computer. They are never sent anywhere; the last 20 per connection are kept.
• Settings readings from before this release still work. Index results need a reading taken by this release before the index was built.
• Stated machine sizes (32 GB, 8 cores, SSD) are used for the rest of the conversation, and every finding says they were stated, not measured. An attached Prometheus source measures them instead.

Other databases on the server

Clusters: primary and standbys

Attach any node and ask about the cluster. Verbiqo reports the other nodes as the attached one sees them (from a primary, its standbys with their state and replay lag; from a standby, its upstream) and names each as not read: a question reaches only what is attached, so it never signs in to a node you did not attach. To read another node, attach it in a conversation of its own. 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 Patroni metrics from an attached Prometheus source it says whether Patroni agrees with who is really replicating.

Asking a replica

A replica answers for itself. Its query statistics (pg_stat_statements, if it is loaded there too), sessions, locks and per-table vacuum and dead-row figures cover only what runs on it; a replica neither writes nor vacuums, so the primary’s slow writes, blocking and vacuum are not visible from it. Verbiqo says so under any answer about queries, locks or vacuum on a replica, and an answer may not call those clear (nothing needs vacuuming) from there. Plans, schema, indexes and replay lag read the same as on the primary. For writes, blocking and vacuum, attach the primary with a read-only role.

Explain a slow statement

Paste it into the question. $1 placeholders are fine:

Why is this slow? SELECT * FROM orders WHERE status = $1 ORDER BY created_at DESC LIMIT 1

1. Plan

   Verbiqo asks PostgreSQL for the plan. The statement itself never runs.

2. Read it

   It looks for full scans that keep few rows, sorts too big for memory, and row estimates far from reality.

3. Test an index

   With hypopg installed, it checks that a suggested index would be used before you build it.

A slow UPDATE, DELETE or INSERT

Why is this slow? DELETE FROM orders WHERE created_at < $1

A read-only role cannot plan a write, so Verbiqo splits it in two:

• Finding the rows. It plans the SELECT that finds the same rows, with the same tables, joins and WHERE. This is where most slow writes spend their time.
• Writing each row. It reads the table’s indexes, triggers and foreign keys, and points out:
  • foreign keys from other tables with no index on their side, so each deleted row scans the other table (the most common cause of a slow DELETE), with the index to create;
  • updated columns that are indexed, so the update cannot be HOT and writes to every index.

Plans need PostgreSQL 16 and pg_read_all_data (how to turn them on). Without them, Verbiqo says so; the checks on indexes, triggers and foreign keys still work.

Your data is not touched. The statement you paste is never run. Verbiqo only asks for plans, which are estimates, and reads the catalog. Everything runs in a read-only transaction that PostgreSQL itself keeps from writing, and EXPLAIN ANALYZE, which would run the statement, is refused on your tables. For actual row counts and times, turn on auto_explain and attach the logs. All the guard rails: What Verbiqo can and cannot do on your server.

Permission errors

Paste the error as you got it, from any tool or the server log. For example:

ERROR: permission denied for schema app_schema [Failed SQL: CREATE TABLE app_schema.template]

1. Read the error

   Verbiqo finds what was refused (a schema, table, sequence, function or database) and the statement that failed, and from that works out the privilege needed: CREATE to create in a schema, INSERT to insert into a table, and so on.

2. Ask if it is not clear

   An error rarely names the role. Verbiqo then asks which role the connection uses, and lists the login roles that lack the privilege, and those that have it, so you can pick one. If the name matches objects in two schemas, or nothing in this database, it asks about that first.

3. Read the catalog

   For that role it reads the object’s owner and grants, the role’s group memberships and which roles hold the privilege now.

4. Answer

   Either the role lacks it, with the one GRANT that should fix it and the group other roles get it through, or the role has it now, so the failure came from another role, another database or server, or before a grant was made.

You can name the role up front to skip the question: … it connects as deployer, or paste a log line that shows user=deployer.

How far to trust an answer

Why it helps: it tells you which parts of an answer Verbiqo read and checked, and which are the model’s own, so you know what to verify before you run anything.

• Figures come from what Verbiqo read. A number in the answer that no reading backs (a rate, a size, a ratio) goes back to the model to correct; if it still has nothing behind it, the answer says it could not be validated. A value the answer proposes for a setting is a suggestion, not a reading, and is allowed.
• A change’s verdict is Verbiqo’s. The model may explain it; a sentence claiming a different result is removed (Check that a change helped).

Ask How many transactions a second is this server doing, and what is the cache hit ratio?

Every figure in the answer should match a finding or a reading under it. That is the quickest way to see where a number came from before you act on it.

What happened earlier

A database shows the present. Attach its logs and metrics in the same conversation:

/splunk prod
/pg orders
/prom prod

Ask what happened last night? and Verbiqo adds a timeline from every source: restarts, reloads, promotions, failovers, backups and log events, each with its source. It places events in time; it does not say one caused another.

Example: commits that took seconds

1. Logs

   Any problems on the orders cluster in the last 24 hours? The slow-statement finding names COMMIT.

2. Database

   Commits took up to 15 seconds yesterday around 3:40pm. Is synchronous replication the cause?

3. Metrics

   Did Patroni fail over yesterday? A switchover at the same time is the likely cause.

From a terminal

verbiqo chat --pg orders "why is this replica falling behind"
verbiqo pg run orders replication
verbiqo pg query orders "SELECT datname, numbackends FROM pg_stat_database"

Reports for run: blocking-tree, long-running, idle-in-transaction, table-bloat, unused-indexes, cache-hit, replication, connections, settings-of-note. query runs one statement under the same rules as a question: a read-only transaction, catalog and statistics views only.