Observability

DB9 includes built-in observability that is always on. Query performance is sampled automatically, slow queries are always captured, and the data is available through the CLI, REST API, and direct SQL — no extensions to install.

This page covers what you can observe, how to access it, and where the current boundaries are compared to standard PostgreSQL.

What You Can See

Metric	Available	Access
QPS and TPS	Yes	CLI, API, SQL
Latency (avg, p99)	Yes	CLI, API, SQL
Active connections (count)	Yes	CLI, API, SQL
Query samples with latency	Yes	CLI, API, SQL
Slow queries (p99-sorted)	Yes	CLI
Error count and failed queries	Yes	CLI, API, SQL
Write-conflict retries (TiKV)	Yes	SQL
HNSW index build metrics	Yes	SQL
EXPLAIN query plans	Yes	SQL
Schema, tables, indexes	Yes	CLI, SQL
Per-connection details	Node-local	SQL (pg_stat_activity)
Index usage stats	No	—
Memory/cache stats	No	—
Prometheus/OpenTelemetry export	No	—

Each server node keeps its own counters and samples, so QPS/TPS, latency, connection count, query samples, slow queries, errors, retries and HNSW metrics above cover one server node — see How Sampling Works.

CLI: db9 db inspect

The primary observability tool is db9 db inspect:

Terminal

# Summary dashboard (QPS, TPS, latency, connections, errors)
db9 db inspect <database>

# Query samples with latency breakdown
db9 db inspect <database> queries

# Combined summary + queries
db9 db inspect <database> report

# Top slow queries sorted by p99 latency
db9 db inspect <database> slow-queries

Schema introspection

Terminal

# List schemas, tables, and indexes
db9 db inspect <database> schemas
db9 db inspect <database> tables
db9 db inspect <database> indexes

All commands support --json for programmatic use. --output csv works for schemas, tables and indexes only; the summary, queries, report and slow-queries ignore it and print the table, so use --json there.

Example output

Terminal

db9 db inspect mydb

Output

Database: mydb
Window: 3600 seconds

 Metric               Value
─────────────────────────────────────
 QPS                  41.7
 TPS                  20.8
 Latency (avg)        12.5 ms
 Latency (p99)        45.2 ms
 Active Connections   8
 Statements           150000
 Commits              75000
 Errors               3
 DB Storage           n/a
 FS Size (logical)    n/a

On a new database DB Storage and FS Size (logical) print n/a. DB Storage reads the same storage scan as the tables in Storage Metrics.

SQL: System Functions

Two built-in table functions provide observability data directly in SQL:

Summary metrics

SQL

SELECT qps, tps, latency_avg_ms, latency_p99_ms, active_connections,
       statement_count, txn_commit_count, error_count
FROM _db9_sys_observability();

▶ Run

Returns a single row with the rolling 60-minute summary. Additional columns include write-conflict retry counts (retry_attempts, retry_budget_exhausted, retry_timeout_aborts) and HNSW index metrics (hnsw_graph_bytes_written, hnsw_serialize_duration_us).

Query samples

SQL

SELECT query, sample_count, error_count,
       latency_avg_ms, latency_p99_ms, latency_max_ms,
       last_seen_ms_ago
FROM _db9_sys_query_samples()
ORDER BY latency_p99_ms DESC
LIMIT 10;

▶ Run

Returns per-query aggregates for sampled queries in the current window. Up to 50 unique query groups are tracked.

Find failing queries

SQL

SELECT query, error_count, sample_count
FROM _db9_sys_query_samples()
WHERE error_count > 0
ORDER BY error_count DESC;

▶ Run

REST API

For automation, call the observability endpoint directly:

Terminal

curl -s "https://api.db9.ai/customer/databases/<database-id>/observability" \
  -H "Authorization: Bearer $TOKEN" | jq .

Returns JSON with summary (QPS, TPS, latency, connections) and samples (per-query metrics).

EXPLAIN

DB9 supports EXPLAIN for query plan inspection:

SQL

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Plan nodes print as Seq Scan, Index Scan, Hash Join, Nested Loop, Sort, Limit, HashAggregate, Unique, WindowAgg and Result, among others, and for vector search as Adaptive Vector Top-K with an HNSW Scan path. Join nodes name the join type (Hash Join Inner, Hash Join Left, Hash Anti Join), and an aggregate without GROUP BY prints as Plain in text output ("Node Type":"Aggregate" in FORMAT JSON).

EXPLAIN ANALYZE executes the query and appends summary runtime statistics to the plan — actual row count, execution time, and KV scan counters. Per-operator timing is not yet available.

FORMAT JSON is supported and returns a PostgreSQL-compatible plan document:

SQL

EXPLAIN (FORMAT JSON) SELECT 1;
-- [{"Plan":{"Async Capable":false,"Node Type":"Result","One-Time Filter":"false",
--           "Parallel Aware":false,"Plan Rows":1,"Plan Width":0,
--           "Startup Cost":0.0,"Total Cost":0.01}}]

Combining it with ANALYZE adds Actual Rows, Actual Loops, and Execution Time to the document.

EXPLAIN output requires a direct connection

db9 db sql, the SDK’s sql() method, and the runnable examples on this site currently return an empty result for EXPLAIN statements. To see plan output, generate a DSN with db9 db connect <db> and run EXPLAIN from any Postgres client.

How Sampling Works

DB9 collects observability data using in-memory sampling:

• Window: 60-minute rolling window
• Default rate: 1 in 1,000 queries sampled (0.1%)
• Always captured: errors and queries exceeding the slow threshold (200 ms default), on the node that ran them
• Max query groups: 50 unique query fingerprints
• Max sample events: 20,000 in memory
• SQL normalization: every literal value becomes ? and a comma-separated run of values collapses to ? , ..., so SELECT 'abc'::int AS x, 42 AS y is stored as SELECT ? :: INT AS x , ? AS y; | characters become spaces; text is cut at 512 bytes and ends in …
• Redaction: in a role statement the password becomes ? and everything after it is dropped — ALTER ROLE app WITH PASSWORD 'secret' CONNECTION LIMIT 5 is stored as ALTER ROLE app WITH PASSWORD ?

This is process-level, in-memory data: each server node keeps its own samples and counters. It is not persisted to TiKV and resets when the server restarts. db9 db inspect, the REST API and the SDK read one node, and _db9_sys_observability() and _db9_sys_query_samples() read the node your connection landed on, so statements served by another node are missing. With eight pgwire sessions open, none of their statements appeared in db9 db inspect while statements sent with db9 db sql did, and each node’s _db9_sys_query_samples() listed only the statements of its own sessions.

Database Status

For database metadata (not performance metrics), use:

Terminal

db9 db status <database>

Shows database name, ID, state (ACTIVE, CLONING, CREATE_FAILED), region, creation time, endpoints, and connection string.

PostgreSQL Compatibility

DB9 implements some pg_catalog views as stubs for tool compatibility:

View	Status	Notes
information_schema.tables	Functional	Standard schema introspection
pg_indexes	Functional	Index definitions
pg_stat_user_tables	Stub (NULLs)	One row per table, but every counter is NULL
pg_statistic_ext	Stub (empty)	No extended statistics
pg_stat_statements	Not available	Use _db9_sys_query_samples()
pg_stat_activity	Node-local	The PostgreSQL column shape with live values — state, query, backend_start, query_start, wait events, client_addr — but only for sessions on the server node your connection landed on; see below
pg_stat_database	Partial, node-local	One row per database; for the connected database numbackends, transaction and tuple counters are populated — per server node, so two connections can read different totals; block, timing and session counters are NULL

Tools like Prisma, Drizzle, and psql that query information_schema and pg_indexes work normally. drizzle-kit push is the exception: it also reads pg_stat_user_indexes, which does not exist (42P01), so every push into a schema that already has tables fails — see the Drizzle guide. pg_stat_statements does not exist at all — querying it raises 42P01 relation "pg_stat_statements" does not exist. pg_stat_activity resolves with state and query, so monitoring queries written for PostgreSQL run unchanged — but it is not a database-wide view, so they under-report whenever your sessions are spread across nodes.

What Is Not Available Today

• A database-wide session list — pg_stat_activity shows what each session is running, but only for sessions served by the same node as the connection you query from. With eight sessions open at once, one connection listed three of them and another listed the other five. active_connections from _db9_sys_observability() is counted per node in the same way — with eight pgwire sessions open it read 5 on one node and 3 on another, while the Active Connections row of db9 db inspect read 0 — so there is no database-wide connection count
• A database-wide view of query samples — samples and counters are kept per server node; see How Sampling Works
• Index usage statistics — pg_stat_user_tables.idx_scan and pg_stat_all_indexes.idx_scan are NULL
• Memory and cache metrics — shared buffers, work memory usage not exposed
• Slow query log to file — observability data is query-based only, not logged to disk
• Prometheus/OpenTelemetry export — no /metrics endpoint or trace export
• Full query text with parameters — only the normalized statement is stored: literal values become ? and bind values are not captured
• EXPLAIN ANALYZE per-operator timing — summary runtime stats are reported (actual rows, execution time, KV scan counters), but not per-operator timing

No external metrics export yet

DB9 does not currently export metrics to Prometheus, Datadog, or OpenTelemetry. Use db9 db inspect as your primary observability tool and build alerting around application-level metrics in your own monitoring stack.

Storage Metrics

DB9 provides built-in storage accounting through virtual tables:

SQL

-- Database-level storage breakdown
SELECT database_name, data_bytes, index_bytes, total_bytes
FROM _DB9_SYS_STORAGE_STATS;

-- Per-table storage breakdown
SELECT table_name, data_bytes, index_bytes, total_bytes
FROM _DB9_SYS_TABLE_STORAGE_STATS
ORDER BY total_bytes DESC;

▶ Run

Both tables return zero rows until a database’s first background scan, and in the current release that scan may not have run — an empty result means “not scanned yet”. Manual refresh via db9_refresh_storage_stats() is not available in the current release — it returns ERROR: feature "storage_size_scan" is unavailable (PreActivationSeal) (55000). See Storage Accounting for full details.

Next Pages

• Storage Accounting — database and table storage usage
• Limits and Quotas — all operational limits in one place
• Security and Auth — token types and role model
• Production Checklist — verify observability before going live
• CLI Reference — db9 db inspect command reference
• SQL Reference — SQL engine compatibility