Observability

What you get

The Observability tab (and GET /v1/projects/{id}/observability) reports live metrics for a project:

• Storage gauge - database size in bytes against the plan's storage limit, with usage percent
• Connection gauge - current connections against the plan's connection budget
• Active queries - what is running right now, with elapsed time
• Slow queries - statements with high mean execution time (sampled via pg_stat_statements; statements with mean time ≥ 1s over at least 5 calls surface here)

When a gauge crosses its threshold, a usage alert opens - warning at 80%, critical at 95% - and is delivered to the dashboard, your webhooks, and the org billing email. The dashboard renders open alerts inline with the gauges.

From the CLI

capydb metrics            # human-readable gauges + query samples
capydb metrics --json     # raw observability payload

Reading the gauges

• Storage creeping toward the limit: delete unused data, VACUUM, or move up a plan. Imports are blocked when the source exceeds the plan limit, and the preflight warns at 80%.
• Connections pegged at the budget: app traffic belongs on the pooled URL; check for clients with oversized local pools or long-idle direct sessions. See Connections.
• Slow queries: the samples include the statement text - EXPLAIN ANALYZE them over a direct connection, then add the index you already suspect is missing.

Query statistics in SQL

Everything above is also available from inside the database, which is where you want it when you are already in psql or debugging a query rather than in a browser tab:

SELECT * FROM capydb.capydb_slow_queries();

Top 20 statements in your database by mean execution time, normalized by pg_stat_statements. Columns:

Why not pg_stat_statements directly?

The view itself is cluster-wide - it holds the normalized query text of every database on the node, so reading it would expose other tenants' queries to you and yours to them. It is revoked, and SELECT * FROM extensions.pg_stat_statements returns permission denied for view pg_stat_statements.

capydb.capydb_slow_queries() is the way in: it runs with the privileges to read the view and returns only rows whose dbid is your database. EXECUTE on it is granted to everyone, including the role in your connection string.

Spilling to disk

work_mem is the memory one sort, hash join or materialized CTE may use before Postgres writes it to a temporary file instead. CapyDB leaves it at the Postgres default of 4MB across every plan, and that is deliberate rather than an oversight: work_mem is per operation, not per connection, so the worst case is roughly pool size × sort nodes × (parallel workers + 1) copies of it. At business sizing that already commits the whole memory budget outside shared_buffers, and the memory ceiling on a cell is a hard limit - raising the global would trade a slow query for a restarted database.

Raise it for the one statement that needs it instead:

BEGIN;
SET LOCAL work_mem = '64MB';   -- applies to this transaction only
SELECT ... ORDER BY ...;
COMMIT;

SET LOCAL is reset at COMMIT, so it is safe on a pooled connection. If a statement spills on every request, the better fix is usually an index that lets Postgres return rows already ordered, so there is no sort to spill.

When a database spills persistently, a temp_spill advisory opens on its own.

Index hygiene

The index advisor suggests indexes to add. The other half is finding the ones already there that nothing reads - every one of them is paid for on every INSERT, UPDATE and DELETE to its table, and in storage:

capydb advisor index-hygiene

It reports two kinds:

• Never scanned - no recorded reads over the whole statistics window.
• Covered by a wider index - the index's columns are the leading columns of another index on the same table, so that one can serve every scan this one can.

Each row comes with a ready-to-run DROP INDEX CONCURRENTLY statement (CONCURRENTLY matters: the plain form locks the table against writes until the drop finishes).

Two things it will not do. It never lists UNIQUE, primary-key or exclusion indexes, whatever their read count - those are constraints, and a read count says nothing about whether dropping one would let bad data in. And it reports nothing at all until a week of statistics has accumulated, because Postgres does not record when an index was created: over a shorter window, an index a monthly job uses looks exactly like a dead one. Check for that job anyway before you drop something.

No extension is needed, unlike the suggestion side.

JIT is off by default

Postgres can compile a query plan to machine code when the planner's estimated cost crosses jit_above_cost. CapyDB sets jit = off, because that threshold is an estimate - the statements that cross it are disproportionately the ones whose row estimates are already wrong, and the database then spends real CPU compiling a plan that was mis-costed. On a measured cell a page query took 750ms, 334ms of which was JIT compilation; the same statement took 404ms with JIT off, and 0.58ms once the underlying plan was fixed.

If you genuinely do run analytical queries and want it back:

ALTER SYSTEM SET jit = on;
SELECT pg_reload_conf();

CapyDB will not change it back. The default is only applied to databases where jit has never been set explicitly.

Keeping derived data fresh

Materialized views and rollup tables need something to refresh them. Rather than a cron job in your application that has to hold a connection open and be deployed somewhere, use pg_cron, which runs the schedule inside the database itself:

SELECT cron.schedule(
  'refresh-daily-rollups',
  '17 3 * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY daily_rollups$$
);

CONCURRENTLY keeps the view readable while it rebuilds (it needs a unique index on the view).