Skip to content

Database tools

Query insights and index advisor

Tested with:Databasezy API v1 · zb CLI 0.1

Query insights show which statements take the most time in a PostgreSQL or TimescaleDB instance, how that changes over time, and why a statement is slow. Open an instance’s Query insights page, or use the API under …/instances/{id}/insights.

Every 5 minutes Databasezy reads pg_stat_statements inside your database, through the cell’s console proxy, as your application role (never a superuser), and stores what changed since the previous read:

Per statement and 5-minute bucketNotes
Calls, total and mean execution timeFrom the difference between two reads
p95 execution time (estimated)pg_stat_statements keeps no histogram: the p95 is the mean plus 1.645 standard deviations, never above the slowest call seen. It reads low for long-tailed latencies
Rows, shared blocks hit and readCache hit ratio = hit / (hit + read)

Only the 50 statements with the most execution time in each interval are stored, and only statements that were called. History is kept 8 days; the page shows the last hour, 24 hours or 7 days. The first read after collection starts (or after pg_stat_statements_reset()) is a baseline, so data appears after the second read.

A statement regressed when its mean execution time in the last quarter of the range is at least twice its mean before, at least 10 ms more, with at least 10 calls in each part.

Statements run by other database roles (for example the Data API’s anon and authenticated) are counted in a total but not listed: pg_stat_statements shows a statement’s text only to the role that ran it, and Databasezy does not give your application role pg_read_all_stats (it would also reveal the platform’s own statements).

Statement text is normalized before it is stored or shown: every string, number, dollar-quoted, bit or hex literal becomes a parameter ($1, $2, …), comments are removed (they often carry user names or request ids), and a typed literal such as DATE '2026-01-01' becomes $1::DATE. Utility statements (ALTER ROLE … PASSWORD …, SET, VACUUM) are never stored. A text that could read two ways (a backslash before a quote, which depends on standard_conforming_strings) is withheld rather than guessed. Table, column and alias names are kept.

  • A sleeping, paused or stopped instance is never contacted, so collecting never wakes it and never keeps it awake. Its history up to then stays visible.
  • Secure placement (HIPAA): nothing about the instance’s statements is stored outside the instance. The page reads pg_stat_statements live when you open it (cumulative since its last reset), normalizes the text and stores nothing; there is no history chart and no regression detection, and collection cannot be turned on. If an instance moves to secure placement, its earlier history is deleted.
  • Turn collection off on the page (or PUT …/insights/settings with {"enabled": false}, needs instances:operate): the stored statistics and texts are deleted.

Select a statement to see its history and its plan. Explain plans the normalized statement; nothing runs. A statement with parameters ($1, …) gets its generic plan from a helper that comes with hypopg (zb_index_advisor.explain(): the statement is prepared and explained with plan_cache_mode = force_generic_plan); without hypopg only statements without parameters can be explained, or use Explain analyze with values.

Explain analyze executes the statement with the parameter values you type, inside a read-only transaction, and is offered only for read-only statements (a SELECT without INTO, without a data-modifying WITH and without FOR UPDATE / FOR SHARE). It needs the permission to open a read-write console (credentials:reveal) and a recent second factor, never a staff impersonation session, and it is recorded in the audit log. The values are sent once and never stored. Every statement is bounded by the console’s 30-second statement timeout.

Run the index advisor plans the top statements of the last day again with hypothetical indexes (HypoPG): one single-column B-tree index on each column of the tables a statement reads that no valid index starts with. The indexes the planner then uses are the recommendations, with the planner’s estimated cost reduction (an estimate, not a measurement), the index’s estimated size and the statements it helps. Hypothetical indexes exist only in memory for that one call; nothing is created.

Preview and apply opens the same change preview as the table editor. A recommendation is CREATE INDEX CONCURRENTLY, which builds the index without blocking writes, so it is applied on its own, outside a transaction. It must finish within the console’s 30-second statement limit: build indexes on large tables from your own SQL client. A cancelled concurrent build leaves an invalid index that is maintained on every write but never used; the advisor lists invalid indexes with a fix that drops them.

The advisor runs as your application role in a read-only transaction. hypopg’s functions are granted to your application role only (not to PUBLIC, so not to the Data API’s roles).

Query traffic defaults and live query control

Section titled “Query traffic defaults and live query control”

On PostgreSQL and TimescaleDB, open DB roles > Query traffic to view role defaults and current query activity. You can set statement_timeout (milliseconds), work_mem (kB) and a connection limit for managed readonly/readwrite database users. Their desired settings are stored durably and applied by the database operator. The table shows observed values and a separate requested budget while changes are Pending; it refreshes until they converge. Clearing a managed user’s connection limit restores the operator default of 50, rather than unlimited connections.

You can also change defaults for customer login roles that the application login can administer directly. The memory maximum follows the instance size. Your app login can change its own timeout and memory defaults; PostgreSQL does not let it change its own connection limit. Platform and replication roles remain protected. Group roles have no login, so their defaults do not apply when a connection uses SET ROLE.

Timeout and memory settings apply to new connections to this database and survive failover. They are login defaults: applications can override them with SQL SET, and existing sessions retain their settings. Clearing a timeout or memory field removes its database-specific role override, falling back to inherited role/database defaults. work_mem applies to each sort or hash operation, so one query can use it several times. When transaction pooling is on, a role connection limit counts database backends rather than pooler client connections.

Refresh activity to see up to 200 oldest running queries and open transactions visible to your application login. The list shows role, PID, duration, state and wait event; it never returns statement text or query values. Cancel query stops the current statement. Terminate session closes the connection and rolls back its open transaction. Both require confirmation, credentials:reveal, a recent MFA sign-in and PostgreSQL’s native signal permissions. Platform backends are protected. If the selected query has ended or changed, the action returns a conflict; refresh before choosing another query. Query signals and directly administered role changes run through the scoped console proxy. Managed budgets use durable control-plane events and each user’s own in-cell login. All changes are audited.

The API uses GET .../database/traffic, PUT .../database/traffic/budgets/{role} and POST .../database/traffic/queries/{pid}/signal. Managed budget writes return 202, with desired_budget and pending distinguishing requested settings from observed defaults. Send the role and both backend_start and query_start timestamps from the activity response unchanged when requesting a signal, together with "action": "cancel" or "terminate".

EngineSourceNotes
MySQL, MariaDBperformance_schema statement digests, read as the platform’s monitoring login zb_monitor (SELECT on the digest table only)MariaDB keeps performance_schema off by default: turn on the performance_schema parameter (the instance restarts); until then the page says the source is off. A statement first seen since the previous read is counted whole, so a statement you run once shows up after the next read
ClickHousesystem.query_log of your application loginRead bytes and peak memory per call
MongoDBThe database profiler (system.profile), slow operations onlySet operationProfiling.mode to slowOp on the Parameters page; command shapes only (users.find({"email": ?})), never values

The collection block of the API answer says what the collector last did: state, last_sample_at, last_attempt_at (its last visit, whatever came of it) and last_error, the class of the last failed read (a SQLSTATE, a database error number or a code such as monitor_unavailable, empty_result, bad_result), never a message.

A read that works and finds nothing to sample is reported too, so collection never looks healthy next to an empty report: the state is no_statements_yet and last_error says which case it is.

last_errorWhat it means
source_emptyThe statistics source is on and holds no statement at all: on MySQL or MariaDB, performance_schema is on but its digest table is empty, so the server is recording no digests or the statistics were just reset
no_statements_yetThe source holds statements, none of them yours: only the engine’s and the platform’s own, utility statements, or text that could not be normalized with certainty
EndpointPermission
GET …/insights/queries?range=1h|24h|7d&sort=total_time|mean_time|p95_time|calls|rows|shared_blks_read&limit=1..100instances:read
GET …/insights/queries/{query_id}?range=instances:read (409 for secure placement: no history)
POST …/insights/queries/{query_id}/explain {"analyze": false}instances:read; with "analyze": true and "params": [...]: credentials:reveal and MFA. 409 insights-unsupported on other engines; 422 for a query_id that is not a statement id from the list (a signed 64-bit integer)
POST …/insights/index-advice {"query_ids": [...]} (optional, at most 10)instances:read
PUT …/insights/settings {"enabled": true|false}instances:operate

… is /v1/orgs/{org_id}/instances/{instance_id}. A recommendation’s operations go to POST …/database/changes/preview and POST …/database/changes like any schema change.