System tables

NYXDB exposes operational state as ordinary system.* relations. Most are live or retained in-memory projections and refresh on the system tick; they are not a replacement for external durable monitoring.

Inventory

The access/governance surfaces are Experimental and described in detail below. Their in-memory system.* projections are not the durable record: the user, endpoint, and policy definitions persist through canonical catalog DDL.

There is no system.tables or system.functions relation in the current engine. Use SHOW TABLES and SHOW FUNCTIONS.

Live queries

system.queries is keyed by query_id:

SELECT query_id, statement, state, started_ms, elapsed_us,
       ttfb_us, queue_us, cpu_us, rows_scanned, bytes_scanned,
       rows_returned, memory_bytes, peak_memory_bytes,
       cancel_requested
FROM system.queries
ORDER BY elapsed_us DESC;

It also carries:

• shard/granule pruning;
• disk reads, part/cache/decode work;
• WAL, commit, and PSI timings;
• end-to-end setup/pipeline/materialization;
• per-operator self time;
• stream events, pending work, subscribers, and watermark lag; and
• committed-tail staging/overlay diagnostics.

Timing fields are diagnostic buckets. Snapshots may be taken while work advances and phases can overlap; do not require every field to sum exactly to wall time.

Completed queries

SELECT ended_ms, query_id, statement, ok, error, exec_us,
       queue_us, rows_scanned, rows_returned, peak_memory_bytes,
       cancel_requested
FROM system.query_log
ORDER BY ended_ms DESC
LIMIT 100;

The default count retention is 10,000 rows (--query-log-max-rows). Age retention defaults to unlimited and can be bounded with --query-log-retention-ms. Count and age trimming publish retractions to live subscribers.

cancel_requested=1 records that explicit SQL/wire cancellation set the query's cooperative flag. A peer disconnect instead retains the terminal error NYXDB_EXEC_CANCELLED: client disconnected; use the error text with the flag rather than treating every cancelled terminal as the same cause.

An exact-aggregate budget rejection retains completed rows_scanned, the sticky NYXDB_EXEC_BUDGET_EXCEEDED: ... memory ... reason, and a nonzero peak_memory_bytes. Current memory and the process-wide exact-native counter are released before completion and are not exposed as completed-query columns. Do not invent a global_exact_memory_bytes metric; use the startup effective-limit log plus per-query evidence.

Subscriptions

SELECT subscription_id, query_id, client, started_ms,
       deltas_out, bytes_out, psi_lane, psi_source_relation,
       psi_pending_events, psi_pending_bytes, psi_overflow_count,
       psi_delivery_lag_p50_us, psi_delivery_lag_p95_us,
       psi_delivery_lag_p99_us
FROM system.subscriptions
ORDER BY psi_pending_bytes DESC;

One shared streaming query may appear once in system.queries with multiple subscribers, while system.subscriptions has one row per subscriber transport.

Application access and operator governance

Use the definition-oriented SHOW commands for review:

SHOW ENDPOINTS;
SHOW AUTH PROVIDERS;
SHOW RATE LIMITS;
SHOW GRANTS;
SHOW OPERATOR GRANTS;
SHOW USERS;

SHOW AUTH PROVIDERS and SHOW RATE LIMITS are the current introspection surfaces for those catalogs; there is no corresponding system table. The other commands share their definition columns with the following projections:

SELECT name, subscribers, executions_total, bytes_out_total,
       uuid, version
FROM system.endpoints
ORDER BY name;

SELECT relation, relation_uuid, role, verbs
FROM system.grants;

SELECT relation, relation_uuid, operator_role, verbs
FROM system.operator_grants;

SELECT name, role, created_at, uuid, version
FROM system.users;

The two grant tables deliberately share no authorization state. On the current end-user wire, only read and subscribe policies on logical views are consumable; other application target/verb rows are future-facing catalog vocabulary. Named non-admin operators use the separate operator domain for relation reads and subscriptions, while write and management work remains admin-only. That enforcement covers raw query/stream operations and the canonical stored query behind operator-class OP_ENDPOINT_EXEC and OP_ENDPOINT_SUB: every scanned base relation requires read or subscribe. Those operator-class invocations remain exempt from application endpoint quotas.

system.governance_log records endpoint denied and killed decisions, auth gate revoked events, operator login and login_failed outcomes, and user_created, user_altered, or user_dropped changes:

SELECT ts_ms, event, user, role, relation, relation_uuid,
       dimension, allowed, observed, source, event_id
FROM system.governance_log
ORDER BY ts_ms DESC
LIMIT 200;

STREAM SELECT * FROM system.governance_log;

This log is process-local and volatile: it is excluded from journal and snapshot recovery and starts empty after every restart. Its current fixed capacity is 10,000 rows with drop-oldest behavior; no public startup flag changes that retention. A live subscriber receives retractions for evicted rows. Export the stream to durable monitoring when it is part of an audit or incident record.

See Operator governance for provisioning, granting, rollout, and incident procedures, and Application access SQL for definition syntax.

Storage

SELECT part_id, relation, shard_id, rows, size_bytes,
       min_key, max_key, disk, created_at_ms
FROM system.parts;

SELECT name, path, pool, role, used_bytes, max_bytes, parts,
       effective_durability, durability_state, missing_replicas
FROM system.disks;

SELECT name, serve_pool, durable, delta, wal, compaction, ttl,
       tables_using, effective_durability, durability_state,
       degraded_parts
FROM system.storage_policies;

missing_replicas describes local placement health in the experimental storage topology. It does not mean a second NYXDB node exists.

Transforms and memory

SELECT name, source, target, state, start, reflected_position,
       lag, rows_emitted, last_error
FROM system.transforms;

SELECT sequence, rank, bytes, allocations, trigger, backtrace
FROM system.memory_profile
ORDER BY sequence DESC, rank ASC;

An empty memory-profile relation means profiling is unavailable or no dump has been published.

Snapshot semantics

Each system-table read is coherent for that relation. Multiple reads across different system tables are not one atomic snapshot. Record collection time and engine build identity when correlating them.

--system-tick-ms defaults to 1,000 ms; inputs below 100 ms clamp to 100 ms. Reducing the tick increases refresh and PSI update work. Validate the cost before changing it.

The pre-GA telemetry schemas follow append-only column evolution, but they are not yet a GA cross-version contract. Collect by column name and version dashboards with the engine revision.