Database observability#

Marula measures performance one endpoint at a time. That leaves the real query mix unseen and the slow tail unmeasured, so the database now records both. This page is the access path: what is collecting, how to read it, and what a managed Postgres will not let you turn on.

Three names matter. Everything else on this page explains them.

SELECT signal, installed, active FROM ops.db_signals;

installed means the extension's SQL objects exist. active means the server is doing the work. The two differ on a managed instance where someone ran CREATE EXTENSION pg_stat_statements without preloading the library: the views exist and reading them fails. That case is why this view is the first thing to read.

What ships where#

The stack is split in two because a database server and a migration change different things.

pg_stat_statements and auto_explain are part of the PostgreSQL contrib set, so both are already inside the pinned image. pg_cron and hypopg are not, and the hardened image the compose stack runs by default has no package manager. The overlay exists for deployments that want them; it starts from the official pgvector image instead of the hardened one, which is the trade it makes. A deployment that skips the overlay gets the statistics, the plan logs, the views and the function, and no scheduled jobs.

Every extension in the migration is created behind two guards: the server must offer it, and the command itself is wrapped so a refusal becomes a notice instead of a failed upgrade. A database that forbids an extension still upgrades; it just reports active = false.

Reading the query mix#

SELECT calls, mean_ms, total_ms, pct_time, rows_per_call, left(query, 80)
  FROM ops.query_tail
 LIMIT 20;

ops.query_tail is ordered by total time, not mean time. On a small install the expensive statement is usually a cheap one run a great many times, and mean time hides that. pct_time says how much of the measured server time one statement owns. rows_per_call is the shape check: a page query returning 20 rows and one returning 4,000 differ there before anyone reads a plan.

The view covers the current database only, and it leaves out statements that read the statistics, so the observer does not climb its own list.

Two things it does not do:

• It does not reset. pg_stat_statements accumulates until reset, so the numbers are since the last restart or reset, not a rolling window. When you want a clean window, run SELECT pg_stat_statements_reset(); yourself. A scheduled reset would delete the evidence someone is in the middle of reading.
• It does not track statements inside functions by default. PostgreSQL's default is pg_stat_statements.track = top. Set it to all on the server to see nested statements, and expect the extra bookkeeping.

The EXPLAIN signal#

ops.explain_plan() returns the plan of one statement as jsonb.

-- shape only, does not run the statement
SELECT jsonb_pretty(ops.explain_plan('SELECT * FROM tickets WHERE org_id = $1 LIMIT 20'));
 
-- measured: runs it, with buffers, under a 5s statement timeout
SELECT jsonb_pretty(ops.explain_plan('SELECT count(*) FROM tickets', with_analyze => true));

If you are writing something that consumes a plan, this is the function to call. The plan is the Plan object PostgreSQL's EXPLAIN (FORMAT JSON) returns, so #>> '{Plan,Plans,0,Node Type}' is the top node and the rest of the usual JSON path rules apply.

Three properties are deliberate:

• Invoker rights. with_analyze => true executes the statement, so a definer-rights function would let any caller write with the owner's privileges. This one can do exactly what the caller could have typed.
• A 5s timeout on the measured form. An accidental EXPLAIN ANALYZE of a whole-table aggregate stops instead of holding a connection.
• Its own search_path (public). Schema-qualify anything else, for example ops.v1_tickets.

The second parameter is named with_analyze because analyze is a reserved word and cannot be a parameter name.

For the tail that nobody asked about, auto_explain writes the plan of any statement slower than auto_explain.log_min_duration (200ms in the shipped configuration) to the server log as JSON. On compose:

docker compose logs postgres | grep 'duration:'

200ms sits above the 150ms page budget on purpose: only a statement that has already broken the budget writes a plan. Lower it to 50ms while chasing a regression, raise it on slower hardware. The full set of arguments is in deploy/docker-compose.yml and the Helm chart, and the same lines are in docs/perf.md.

Scheduled maintenance#

Where pg_cron is installed, migration 0145 schedules six jobs, ten minutes apart from 03:10 local server time:

SELECT jobname, schedule, command, active FROM cron.job ORDER BY jobid;

Each one is a plain VACUUM (ANALYZE): no FULL, no freeze override, nothing that holds an exclusive lock. One table per job and ten minutes apart so two vacuums never compete for the same I/O window. Re-running the migration replaces a job by name rather than duplicating it.

pg_cron runs SQL. It cannot run the worker's Rust sweeps, so the housekeeping loops described in docs/worker-cadence.md stay in the worker. What moved into the database is the maintenance that was never a sweep.

Trying a candidate index without building it#

docs/perf.md F4 measured a candidate index on the ticket list at 0.66ms against 31ms and deliberately did not ship it, because page-1 p95 has headroom. hypopg makes that index visible to the planner for one session without writing anything.

SELECT hypopg_create_index($$
  CREATE INDEX ON tickets (
    org_id,
    (CASE priority WHEN 'p1' THEN 0 WHEN 'p2' THEN 1 WHEN 'p3' THEN 2 ELSE 3 END),
    due_at NULLS LAST, created_at, id
  )
$$);
 
SELECT index_name FROM hypopg_list_indexes;
 
SET enable_seqscan = off;   -- only when measuring on a small fixture
SELECT ops.explain_plan($$
  SELECT id FROM tickets
   WHERE org_id = '00000000-0000-0000-0000-000000000001'::uuid
   ORDER BY (CASE priority WHEN 'p1' THEN 0 WHEN 'p2' THEN 1 WHEN 'p3' THEN 2 ELSE 3 END),
            due_at NULLS LAST, created_at, id
   LIMIT 20
$$);
 
SELECT hypopg_reset();

Two facts to keep in mind. The hypothetical index lives in the session that created it, so the create and the explain have to share a connection. And bind a literal for the org filter, not gen_random_uuid() or another volatile expression: a volatile predicate stops the planner from using any index, and the plan will say so.

Watching the F4 trigger#

docs/perf.md F4 deferred that index with a condition attached: look again when page-1 p95 reaches about two thirds of the budget, or when the sort plan regresses. Both halves are metrics now, emitted by the worker's capacity_watch sample:

The plan arm is read from ops.explain_plan on the same statement the endpoint issues, with its binds filled in, so the signal cannot drift from the query. The latency arm runs warmups and forty timed requests, which is a measurement rather than a probe: it belongs to a scheduled job, and until one runs, both series are absent.

Two rules read them. marula-capacity-trigger.rules.yaml fires at 100ms, which is two thirds of the 150ms budget, as a ticket. rules-optin/marula-capacity-plan-shape.rules.yaml watches the plan, opt-in because 0 is the shape F4 measured and accepted, not a fault. deploy/observability/runbooks/capacity-trigger.md is the response to both.

Managed Postgres#

Checked 2026-10-21 against each provider's current extension documentation. The column that matters is not "supported" but "what does enabling cost".

Three consequences worth planning for:

• On a managed instance, our migration cannot turn any of this on. Changing shared_preload_libraries is a control-plane change that ends in a restart, and several providers reboot the whole instance. Do it in a maintenance window, then let the migration create the extension objects on the next deploy. Until then ops.db_signals reports installed = false or active = false, and every consumer sees that instead of an error.
• pg_cron is the one most often held back. No large provider forbids it outright today, but it is the extension whose enablement is least like flipping a flag: it needs a preload, a restart, and a single named database to hold its job table. Small and specialised providers do not ship it at all. The self-host images have no such restriction, and deploy/postgres/Dockerfile is what keeps that true.
• hypopg is the one genuinely forbidden. Cloud SQL does not ship it. On Azure it works but blocks a major-version upgrade until it is dropped, which is an unpleasant surprise to find on upgrade day.

Sources: RDS extension list, RDS pg_cron, Azure extension considerations, Cloud SQL extensions.

PostgreSQL 18 async I/O#

PostgreSQL 18 is the first release with an asynchronous I/O subsystem. Five settings arrived with it and one existing default changed, which is worth knowing before tuning anything.

We pin io_method=worker and change nothing else. worker is the default, and pinning it is a deliberate act: the setting is start-only, so pinning it means a later release cannot change how this cluster issues I/O without us noticing. io_workers=3 is fine for the two-CPU container the compose stack runs.

On a larger host, io_workers is the setting to raise — roughly a quarter of the core count is the published advice, and the stock 3 is a Raspberry Pi default. io_uring is not worth trying inside a container: several container runtimes disable it for security reasons.

To see what a server actually resolved:

SELECT name, setting, context FROM pg_settings WHERE name LIKE 'io\_%' ORDER BY name;
SELECT * FROM pg_aios;   -- in-flight asynchronous I/O, PG18 only

io_max_concurrency is worth reading even though nobody sets it. It starts at -1 and is computed at boot from the buffer pool and process counts, so two instances with identical configuration files can run different ceilings. It is the first number to compare when two machines on the same storage behave differently.

Turning it off#

Remove the -c arguments from the server and the statistics stop being collected; the views stay and report the truth about it. Drop the extension in the usual way if you want the objects gone:

SELECT ops.db_signals;          -- what was active
DROP VIEW IF EXISTS ops.query_tail;
DROP EXTENSION IF EXISTS pg_stat_statements;   -- statistics only
DROP EXTENSION IF EXISTS hypopg;
DROP EXTENSION IF EXISTS pg_cron;              -- unschedules its jobs with it

ops.db_signals and ops.explain_plan keep working: they need no extension.