Studio edition /

Metrics

Connections, transactions, cache, table sizes, slow queries, sessions, locks and index use of a connection, over time. Studio edition.

Open it#

In the Studio edition, switch to Advanced and pick Metrics in the sidebar. The screen has five views: Overview, Tables, Queries, Activity and Indexes. The view and the time range stay in the address.

How metrics are collected#

Gresbase samples a connection while someone uses it. The first request to a connection starts its sampler, which runs one small query every GRESBASE_METRICS_INTERVAL (10s by default). It stops when the connection has had no use for GRESBASE_POOL_IDLE_TIMEOUT (10m), so idle databases cost nothing.

• Each sample reads the database size, the client sessions by state against max_connections, the counters of pg_stat_database (commits, rollbacks, cache hits and reads, temporary files, deadlocks), sessions waiting for a lock and the replication lag.
• Rates such as transactions per second are computed between two samples. After a statistics reset they start again.
• The history lasts GRESBASE_METRICS_RETENTION (24h), in memory. Long ranges are averaged into 240 points.
• GRESBASE_METRICS_ALWAYS=true samples the connection of DATABASE_URL from startup and never stops a sampler once started.
• GRESBASE_METRICS_PERSIST=true keeps the history across restarts in GRESBASE_DATA_DIR/metrics/<connection>.json, written every 30 samples and when the sampler stops. Free mode sessions are not kept.

The four variables are in Environment variables.

Overview#

Four figures on top: Database size, Connections (in use of max_connections, and how many are running a query), Transactions per second in the last sample, and Cache hit, averaged over the range.

Below, charts of connections by state (Active, Idle, Idle in transaction), commits and rollbacks per second, cache hit and database size. Time range picks 15 minutes, 1 hour, 6 hours or 24 hours, and the charts update live at every sample.

The overview warns when the cache hit ratio is below 99 %, when 80 % or more of max_connections are in use, and when the role can't see other roles' sessions. Right after the first use it says it is collecting the first samples: the charts fill in while you use the connection.

Tables#

The 500 biggest tables and materialized views, with total size, data, indexes and TOAST, live rows, dead rows with their percentage, and the last vacuum and analyze. Dead rows estimate the bloat: the space VACUUM can reclaim.

Queries#

The 200 queries of this database recorded by pg_stat_statements, with calls, total time, mean time and rows. Order by sorts them by Total time, Mean time or Calls. Open in the SQL editor opens the query there.

Reset statistics empties pg_stat_statements after a confirmation. It resets every database of the server, not only this one.

pg_stat_statements must be loaded by the server and created in the database. Without it, the view explains what is missing. For a postgres container, add the command line to the database service and recreate it:

services:
  db:
    image: postgres:17
    command: ["postgres", "-c", "shared_preload_libraries=pg_stat_statements"]

Then create the extension once in the database, as a superuser, in the SQL editor or from Extensions:

CREATE EXTENSION pg_stat_statements;

Activity#

The client sessions of the server, refreshed every 5 seconds: process id, role, state, how long the query has been running, and the query. Queries running for over a minute are listed on top. Sessions opened by Gresbase are labelled gresbase.

Lock tree shows which sessions are waiting and, under each one, the session that blocks it (pg_blocking_pids).

Each session has two buttons, and each asks for a confirmation:

• Cancel query stops the running query with an error and rolls back its transaction. The session stays connected (pg_cancel_backend).
• End session closes the connection and rolls back anything uncommitted (pg_terminate_backend). The application may reconnect.

The session that serves your own request has no buttons. In read-only mode no session has them.

Indexes#

• Unused indexes: never scanned since the statistics were reset. Unique indexes and primary keys are left out, because they enforce rules even when nobody reads them.
• Duplicate indexes: indexes on the same table that do the same.
• Big tables read sequentially: tables with at least 10,000 rows that are read sequentially more often than through an index.

Permissions#

A role for monitoring only:

GRANT pg_monitor TO gresbase;
GRANT pg_signal_backend TO gresbase;  -- only to cancel or end sessions

More in Security and networking.