Re-run pg_stat_statements after a full business day of real traffic - the 2026-09-10 sample is unusable
A pg_stat_statements review was run on 2026-09-10 ~09:35Z and its conclusions CANNOT be acted on. Recording why, so the numbers are not re-quoted later as if they characterised Mars. THREE INVALIDATING CONFOUNDS: 1. WRITES WERE IMPOSSIBLE FOR THE WHOLE WINDOW. Stats were reset at 09:28:15Z by the MARS-700 project restart; read-only did not clear until ~09:35Z. The review's central finding - 'shared_blks_written = 0 across all statements, so the workload is primarily reading, not sustained writes' - is a restatement of the outage, not a property of the workload. Same for 'WAL activity was also very small in the sample'. 2. THE TOP CONSUMERS ARE THE INCIDENT RESPONSE, NOT THE APPLICATION. The 32-second pg_total_relation_size database-size query, the catalog/ownership introspection, the schema/column metadata reads and the function-metadata query that spilled ~8.9 MiB to temp are db-mars-cc's and db-mars-ca's diagnostic queries plus Elazar's open dashboard. The review's advice to 'avoid opening multiple dashboard sessions during peak load' is, in context, advice against diagnosing an outage. 3. SEVEN MINUTES OF SAMPLE, with several top entries at 1-3 calls. DO NOT ACT ON THE ADVISOR OUTPUT FROM THAT WINDOW. It reported 49 unindexed foreign keys and 76 unused indexes. Index usage counters reset at 09:28Z with everything else, so EVERY index looks unused - dropping on that basis would remove live indexes and increase table scans, making I/O worse. The unindexed-FK list is more durable (it is structural, not usage-based) but still needs its own review. WHAT TO DO: re-read pg_stat_statements after at least a full business day of normal student/teacher traffic, ideally a week, then rank by total_exec_time and by shared_blks_read+written. Report stats_reset in the same read every time - a snapshot without it is uninterpretable, which is the whole lesson here. Only then are index-add or index-drop decisions answerable. Not related to the MARS-700 cause (a WAL-G archiver failing on one segment for 34h). Query optimisation would not have prevented it, fixed it, or prevented a recurrence.
Activity
-
First user-visible statement_timeout casualties since the MARS-694 cutover, and the tail is ENVIRONMENTAL, not one bad query. Two /mi-cursada RSC requests were canceled at 2026-09-10T19:44:05.744Z and 19:44:11.994Z (appEvents 46cf20d7-b643-4ea7-909d-1a0b57e90bc7 and 62811e8f-49b7-4c3c-9662-006beebc757e, both action=unhandled-server-error, message 'canceling statement due to statement timeout'), followed at 19:44:13.404Z by the generic client Server Components render error ea38d4cf-8531-46dc-8209-67b702f570ac. Students saw an error page. Read the pg_stat_statements shape before blaming a query: in that same window a trivial pg_available_extensions catalog read shows max 35330.3ms over 6 calls and a large-objects report query 32093.4ms over 1 - queries with no relation to app traffic. That is a DB-WIDE contention episode, so the correct reading is that the 10s cap converts an environmental stall into user-visible 500s, where before the cutover the same stall produced a slow page or an ECHECKOUTTIMEOUT. NOT the MARS-698 nightly-stall pattern: cron.job_run_details over the surrounding 90 minutes is entirely clean, jobid 6 every 5 minutes at 0.0-0.2s, zero failures. The exposure worth tracking is get_layout_state, because it runs on EVERY authenticated page render: 125 calls, mean 177.2ms, stddev 794.2ms, max 8901.9ms - a mean four percent of the cap with a max at eighty-nine percent of it. A second entry for the same statement shows 38 calls, mean 45.9ms, max 163.7ms, so the heavy tail is not intrinsic to the function. Nothing in it needs a rewrite on this evidence; what it needs is the pg_stat_statements re-read after a full business day (reset was 09:28:15Z today) with max_exec_time on get_layout_state as the headline number.
-
ANSWERED on a full business day, 2026-09-10 09:28:15Z reset to 2026-09-11 10:25Z, ~25h. db-mars-cc's re-read. The DB-wide-contention reading is CONFIRMED on a far stronger instrument than the catalog-read example that first suggested it. DECISIVE: SELECT * FROM pgbouncer.get_auth($1) shows 10029 calls, mean 9.8ms, max 4666.7ms - a 474x spread on the cheapest query in the system, a single-row auth lookup on every pooler connection. No query SHAPE can produce 4.7 seconds from that; only environmental contention can, and 10k calls rules out coincidence in a way the 6-call catalog read could not. SUPPORTING - the tail hits every class indiscriminately, which is the actual signature: INSERT appEvents 552 calls / 43.2ms mean / 3942.9ms max (91x); INSERT practicaImages 226 / 41.5 / 3884.8 (94x); classify_i 702 / 46.6 / 3786.8 (81x); pg_available_extensions 6 calls / 10696.8ms mean / 35330.3ms max. Trivial inserts, catalog reads and auth lookups spiking alongside app queries is DB-wide by definition. get_layout_state is NOT a rewrite target, and the evidence is sharper than 'it looks fine': 333 calls (was 125), mean 140.7ms (was 177.2ms), max 8901.9ms - IDENTICAL to the earlier max to the decimal. A frozen max while call count nearly triples means ZERO new tail events during the business day, so the 8901.9ms is one stale outlier from the early post-restart window. Back-computing the 208 newer calls gives a 118.8ms mean against 177.2ms before. It is getting faster with more samples. WHAT THIS DOES NOT SETTLE, and should not be recorded as settled: the CAUSE of the contention. Every measurement here establishes that it is environmental and not ours; none of it identifies what the environment is doing. Mars runs on Nano compute. That is the obvious candidate and it is deliberately unverified - Elazar priced and rejected Large on 2026-09-10 ($0.88 -> $110.10/month, +$109.22). Do not re-propose it as if the tradeoff were unexamined.
-
Closing per pm-mars-cc (PTD item 6). Conclusion: the flagged slow shapes are DB-wide contention, not intrinsically slow queries. Decisive control: pgbouncer.get_auth($1) at 10029 calls, mean 9.8ms, max 4666.7ms - a 474x spread on a query that is trivially cheap by construction, which no query shape can explain. get_layout_state is specifically NOT a rewrite target: 125 to 333 calls with max_exec_time frozen at 8901.9ms to the decimal and mean falling 177.2 to 140.7ms (back-computed 118.8ms across the 208 newer calls) - zero new tail events and the query is getting faster, so the max is a stale outlier. Note the assignedAgent field reads db-mars-ca and is stale; the work was done by db-mars-cc. The unindexed-FK structural review that this WI also mentioned is NOT closed here - it is tracked at MARS-122, which I triaged today to status=deferred priority=4 with the evidence that no current top statement filters on any of the 49 affected columns.