WITH start_snap AS ( SELECT DISTINCT ON (tenant_key, fingerprint) tenant_key, fingerprint, total_time_ms, calls, rows_returned, collected_at FROM observability.db_top_queries WHERE tenant_key IN ('prod', 'test') AND collected_at <= '2026-04-19 13:00:18+02' ORDER BY tenant_key, fingerprint, collected_at DESC ), fallback_start AS ( SELECT DISTINCT ON (tenant_key, fingerprint) tenant_key, fingerprint, total_time_ms, calls, rows_returned, collected_at FROM observability.db_top_queries WHERE tenant_key IN ('prod', 'test') AND collected_at BETWEEN '2026-04-19 13:00:18+02' AND '2026-04-20 01:00:18+02' ORDER BY tenant_key, fingerprint, collected_at ASC ), end_snap AS ( SELECT DISTINCT ON (tenant_key, fingerprint) tenant_key, fingerprint, query, total_time_ms, calls, rows_returned, collected_at FROM observability.db_top_queries WHERE tenant_key IN ('prod', 'test') AND collected_at BETWEEN '2026-04-19 13:00:18+02' AND '2026-04-20 01:00:18+02' ORDER BY tenant_key, fingerprint, collected_at DESC ), deltas AS ( SELECT e.tenant_key, e.fingerprint, e.query, e.collected_at AS last_seen, GREATEST (COALESCE(e.total_time_ms, 0) - COALESCE(s.total_time_ms, fs.total_time_ms, 0), 0) AS total_time_ms, GREATEST (COALESCE(e.calls, 0) - COALESCE(s.calls, fs.calls, 0), 0) AS calls, GREATEST (COALESCE(e.rows_returned, 0) - COALESCE(s.rows_returned, fs.rows_returned, 0), 0) AS rows_returned FROM end_snap e LEFT JOIN start_snap s ON s.tenant_key = e.tenant_key AND s.fingerprint = e.fingerprint LEFT JOIN fallback_start fs ON fs.tenant_key = e.tenant_key AND fs.fingerprint = e.fingerprint AND s.tenant_key IS NULL ) SELECT fingerprint, MAX(query) AS query, MAX(last_seen) AS last_seen, SUM(total_time_ms) AS total_time_ms, SUM(calls) AS calls, SUM(rows_returned) AS rows_returned, CASE WHEN SUM(calls) > 0 THEN SUM(total_time_ms) / SUM(calls) ELSE 0 END AS mean_time_ms, array_agg(DISTINCT tenant_key ORDER BY tenant_key) AS tenant_keys FROM deltas WHERE total_time_ms > 0 OR calls > 0 GROUP BY fingerprint ORDER BY total_time_ms DESC, calls DESC LIMIT '20' OFFSET '0';

Date: 2026-04-20 01:00:36
Duration: 17s992ms
Database: zemm_monitoring
User: labanimal
Log file: /var/log/postgresql/postgresql-2026-04-20_000000.csv

WITH start_snap AS ( SELECT DISTINCT ON (tenant_key, fingerprint) tenant_key, fingerprint, total_time_ms, calls, rows_returned, collected_at FROM observability.db_top_queries WHERE tenant_key IN ('prod', 'test') AND collected_at <= '2026-04-13 00:00:23+02' ORDER BY tenant_key, fingerprint, collected_at DESC ), fallback_start AS ( SELECT DISTINCT ON (tenant_key, fingerprint) tenant_key, fingerprint, total_time_ms, calls, rows_returned, collected_at FROM observability.db_top_queries WHERE tenant_key IN ('prod', 'test') AND collected_at BETWEEN '2026-04-13 00:00:23+02' AND '2026-04-20 00:00:23+02' ORDER BY tenant_key, fingerprint, collected_at ASC ), end_snap AS ( SELECT DISTINCT ON (tenant_key, fingerprint) tenant_key, fingerprint, query, total_time_ms, calls, rows_returned, collected_at FROM observability.db_top_queries WHERE tenant_key IN ('prod', 'test') AND collected_at BETWEEN '2026-04-13 00:00:23+02' AND '2026-04-20 00:00:23+02' ORDER BY tenant_key, fingerprint, collected_at DESC ), deltas AS ( SELECT e.tenant_key, e.fingerprint, e.query, e.collected_at AS last_seen, GREATEST (COALESCE(e.total_time_ms, 0) - COALESCE(s.total_time_ms, fs.total_time_ms, 0), 0) AS total_time_ms, GREATEST (COALESCE(e.calls, 0) - COALESCE(s.calls, fs.calls, 0), 0) AS calls, GREATEST (COALESCE(e.rows_returned, 0) - COALESCE(s.rows_returned, fs.rows_returned, 0), 0) AS rows_returned FROM end_snap e LEFT JOIN start_snap s ON s.tenant_key = e.tenant_key AND s.fingerprint = e.fingerprint LEFT JOIN fallback_start fs ON fs.tenant_key = e.tenant_key AND fs.fingerprint = e.fingerprint AND s.tenant_key IS NULL ) SELECT fingerprint, MAX(query) AS query, MAX(last_seen) AS last_seen, SUM(total_time_ms) AS total_time_ms, SUM(calls) AS calls, SUM(rows_returned) AS rows_returned, CASE WHEN SUM(calls) > 0 THEN SUM(total_time_ms) / SUM(calls) ELSE 0 END AS mean_time_ms, array_agg(DISTINCT tenant_key ORDER BY tenant_key) AS tenant_keys FROM deltas WHERE total_time_ms > 0 OR calls > 0 GROUP BY fingerprint ORDER BY total_time_ms DESC, calls DESC LIMIT '20' OFFSET '0';

Date: 2026-04-20 00:00:37
Duration: 13s688ms
Database: zemm_monitoring
User: labanimal
Log file: /var/log/postgresql/postgresql-2026-04-20_000000.csv

WITH start_snap AS ( SELECT DISTINCT ON (tenant_key, fingerprint) tenant_key, fingerprint, total_time_ms, calls, rows_returned, collected_at FROM observability.db_top_queries WHERE tenant_key IN ('prod', 'test') AND collected_at <= '2026-04-13 00:15:23+02' ORDER BY tenant_key, fingerprint, collected_at DESC ), fallback_start AS ( SELECT DISTINCT ON (tenant_key, fingerprint) tenant_key, fingerprint, total_time_ms, calls, rows_returned, collected_at FROM observability.db_top_queries WHERE tenant_key IN ('prod', 'test') AND collected_at BETWEEN '2026-04-13 00:15:23+02' AND '2026-04-20 00:15:23+02' ORDER BY tenant_key, fingerprint, collected_at ASC ), end_snap AS ( SELECT DISTINCT ON (tenant_key, fingerprint) tenant_key, fingerprint, query, total_time_ms, calls, rows_returned, collected_at FROM observability.db_top_queries WHERE tenant_key IN ('prod', 'test') AND collected_at BETWEEN '2026-04-13 00:15:23+02' AND '2026-04-20 00:15:23+02' ORDER BY tenant_key, fingerprint, collected_at DESC ), deltas AS ( SELECT e.tenant_key, e.fingerprint, e.query, e.collected_at AS last_seen, GREATEST (COALESCE(e.total_time_ms, 0) - COALESCE(s.total_time_ms, fs.total_time_ms, 0), 0) AS total_time_ms, GREATEST (COALESCE(e.calls, 0) - COALESCE(s.calls, fs.calls, 0), 0) AS calls, GREATEST (COALESCE(e.rows_returned, 0) - COALESCE(s.rows_returned, fs.rows_returned, 0), 0) AS rows_returned FROM end_snap e LEFT JOIN start_snap s ON s.tenant_key = e.tenant_key AND s.fingerprint = e.fingerprint LEFT JOIN fallback_start fs ON fs.tenant_key = e.tenant_key AND fs.fingerprint = e.fingerprint AND s.tenant_key IS NULL ) SELECT fingerprint, MAX(query) AS query, MAX(last_seen) AS last_seen, SUM(total_time_ms) AS total_time_ms, SUM(calls) AS calls, SUM(rows_returned) AS rows_returned, CASE WHEN SUM(calls) > 0 THEN SUM(total_time_ms) / SUM(calls) ELSE 0 END AS mean_time_ms, array_agg(DISTINCT tenant_key ORDER BY tenant_key) AS tenant_keys FROM deltas WHERE total_time_ms > 0 OR calls > 0 GROUP BY fingerprint ORDER BY total_time_ms DESC, calls DESC LIMIT '20' OFFSET '0';

Date: 2026-04-20 00:15:36
Duration: 13s53ms
Database: zemm_monitoring
User: labanimal
Log file: /var/log/postgresql/postgresql-2026-04-20_000000.csv

x Hide

WITH normalised AS ( SELECT id, tenant_key, event_type, severity, message, payload, occurred_at, regexp_replace(regexp_replace(message, '\d+', '#', 'g'), '/[^ :]+/', '.../', 'g') AS fingerprint FROM observability.app_events WHERE tenant_key IN ('prod', 'test') AND occurred_at BETWEEN '2026-04-13 00:55:25+02' AND '2026-04-20 00:55:25+02' AND severity IN ('error', 'warning') AND NOT (event_type = 'db_error' AND (lower(COALESCE(message, '')) = 'lock wait: client / clientread' OR ( lower(COALESCE(payload->>'wait_event_type', '')) = 'client' AND lower(COALESCE(payload ->> 'wait_event', '')) = 'clientread')))) SELECT fingerprint, COUNT(*) AS occurrence_count, COUNT(DISTINCT COALESCE(payload ->> 'user', payload ->> 'username', 'unbekannt')) AS affected_users, MAX(severity) AS max_severity, MIN(occurred_at) AS first_seen, MAX(occurred_at) AS last_seen, (array_agg(message ORDER BY occurred_at DESC))[1] AS sample_message, (array_agg(DISTINCT event_type))[1] AS event_type, (array_agg(payload ->> 'file' ORDER BY occurred_at DESC))[1] AS source_file, (array_agg(payload ->> 'line' ORDER BY occurred_at DESC))[1] AS source_line, (array_agg(payload ->> 'request' ORDER BY occurred_at DESC))[1] AS sample_request, (array_agg(id ORDER BY occurred_at DESC))[1] AS latest_event_id FROM normalised GROUP BY fingerprint ORDER BY occurrence_count DESC, last_seen DESC LIMIT '30';

Date: 2026-04-20 00:55:27
Duration: 2s368ms
Database: zemm_monitoring
User: labanimal
Log file: /var/log/postgresql/postgresql-2026-04-20_000000.csv

WITH normalised AS ( SELECT id, tenant_key, event_type, severity, message, payload, occurred_at, regexp_replace(regexp_replace(message, '\d+', '#', 'g'), '/[^ :]+/', '.../', 'g') AS fingerprint FROM observability.app_events WHERE tenant_key IN ('prod', 'test') AND occurred_at BETWEEN '2026-04-13 00:20:25+02' AND '2026-04-20 00:20:25+02' AND severity IN ('error', 'warning') AND NOT (event_type = 'db_error' AND (lower(COALESCE(message, '')) = 'lock wait: client / clientread' OR ( lower(COALESCE(payload->>'wait_event_type', '')) = 'client' AND lower(COALESCE(payload ->> 'wait_event', '')) = 'clientread')))) SELECT fingerprint, COUNT(*) AS occurrence_count, COUNT(DISTINCT COALESCE(payload ->> 'user', payload ->> 'username', 'unbekannt')) AS affected_users, MAX(severity) AS max_severity, MIN(occurred_at) AS first_seen, MAX(occurred_at) AS last_seen, (array_agg(message ORDER BY occurred_at DESC))[1] AS sample_message, (array_agg(DISTINCT event_type))[1] AS event_type, (array_agg(payload ->> 'file' ORDER BY occurred_at DESC))[1] AS source_file, (array_agg(payload ->> 'line' ORDER BY occurred_at DESC))[1] AS source_line, (array_agg(payload ->> 'request' ORDER BY occurred_at DESC))[1] AS sample_request, (array_agg(id ORDER BY occurred_at DESC))[1] AS latest_event_id FROM normalised GROUP BY fingerprint ORDER BY occurrence_count DESC, last_seen DESC LIMIT '30';

Date: 2026-04-20 00:20:27
Duration: 2s321ms
Database: zemm_monitoring
User: labanimal
Log file: /var/log/postgresql/postgresql-2026-04-20_000000.csv

WITH normalised AS ( SELECT id, tenant_key, event_type, severity, message, payload, occurred_at, regexp_replace(regexp_replace(message, '\d+', '#', 'g'), '/[^ :]+/', '.../', 'g') AS fingerprint FROM observability.app_events WHERE tenant_key IN ('prod', 'test') AND occurred_at BETWEEN '2026-04-13 00:35:24+02' AND '2026-04-20 00:35:24+02' AND severity IN ('error', 'warning') AND NOT (event_type = 'db_error' AND (lower(COALESCE(message, '')) = 'lock wait: client / clientread' OR ( lower(COALESCE(payload->>'wait_event_type', '')) = 'client' AND lower(COALESCE(payload ->> 'wait_event', '')) = 'clientread')))) SELECT fingerprint, COUNT(*) AS occurrence_count, COUNT(DISTINCT COALESCE(payload ->> 'user', payload ->> 'username', 'unbekannt')) AS affected_users, MAX(severity) AS max_severity, MIN(occurred_at) AS first_seen, MAX(occurred_at) AS last_seen, (array_agg(message ORDER BY occurred_at DESC))[1] AS sample_message, (array_agg(DISTINCT event_type))[1] AS event_type, (array_agg(payload ->> 'file' ORDER BY occurred_at DESC))[1] AS source_file, (array_agg(payload ->> 'line' ORDER BY occurred_at DESC))[1] AS source_line, (array_agg(payload ->> 'request' ORDER BY occurred_at DESC))[1] AS sample_request, (array_agg(id ORDER BY occurred_at DESC))[1] AS latest_event_id FROM normalised GROUP BY fingerprint ORDER BY occurrence_count DESC, last_seen DESC LIMIT '30';

Date: 2026-04-20 00:35:27
Duration: 2s271ms
Database: zemm_monitoring
User: labanimal
Log file: /var/log/postgresql/postgresql-2026-04-20_000000.csv

x Hide