music on atproto
plyr.fm
zzstoatzz.io
1-- Database Performance Dashboard
2-- Shows aggregated database query metrics grouped by query pattern
3
4WITH query_classification AS (
5 -- Classify queries by type and extract table names from actual SQL
6 SELECT
7 span_name,
8 attributes->>'db.statement' as query_text,
9 duration,
10 start_timestamp,
11 trace_id,
12 otel_status_code,
13 CASE
14 WHEN span_name LIKE 'SELECT%' THEN 'SELECT'
15 WHEN span_name LIKE 'INSERT%' THEN 'INSERT'
16 WHEN span_name LIKE 'UPDATE%' THEN 'UPDATE'
17 WHEN span_name LIKE 'DELETE%' THEN 'DELETE'
18 WHEN span_name LIKE 'BEGIN%' THEN 'TRANSACTION'
19 WHEN span_name LIKE 'COMMIT%' THEN 'TRANSACTION'
20 WHEN span_name LIKE 'ROLLBACK%' THEN 'TRANSACTION'
21 ELSE 'OTHER'
22 END AS query_type,
23 -- Extract primary table from actual SQL query in attributes
24 CASE
25 -- tracks queries (most common)
26 WHEN attributes->>'db.statement' LIKE '%FROM tracks%' AND attributes->>'db.statement' NOT LIKE '%JOIN%' THEN 'tracks'
27 WHEN attributes->>'db.statement' LIKE '%FROM tracks JOIN%' THEN 'tracks+joins'
28 WHEN attributes->>'db.statement' LIKE '%INTO tracks%' THEN 'tracks'
29 WHEN attributes->>'db.statement' LIKE '%UPDATE tracks%' THEN 'tracks'
30
31 -- track_likes queries
32 WHEN attributes->>'db.statement' LIKE '%FROM track_likes%' THEN 'track_likes'
33 WHEN attributes->>'db.statement' LIKE '%INTO track_likes%' THEN 'track_likes'
34
35 -- user auth/session queries
36 WHEN attributes->>'db.statement' LIKE '%FROM user_sessions%' THEN 'user_sessions'
37 WHEN attributes->>'db.statement' LIKE '%INTO user_sessions%' THEN 'user_sessions'
38 WHEN attributes->>'db.statement' LIKE '%UPDATE user_sessions%' THEN 'user_sessions'
39
40 -- user preferences
41 WHEN attributes->>'db.statement' LIKE '%FROM user_preferences%' THEN 'user_preferences'
42 WHEN attributes->>'db.statement' LIKE '%INTO user_preferences%' THEN 'user_preferences'
43 WHEN attributes->>'db.statement' LIKE '%UPDATE user_preferences%' THEN 'user_preferences'
44
45 -- queue state
46 WHEN attributes->>'db.statement' LIKE '%FROM queue_state%' THEN 'queue_state'
47 WHEN attributes->>'db.statement' LIKE '%INTO queue_state%' THEN 'queue_state'
48 WHEN attributes->>'db.statement' LIKE '%UPDATE queue_state%' THEN 'queue_state'
49
50 -- artists
51 WHEN attributes->>'db.statement' LIKE '%FROM artists%' THEN 'artists'
52 WHEN attributes->>'db.statement' LIKE '%INTO artists%' THEN 'artists'
53 WHEN attributes->>'db.statement' LIKE '%UPDATE artists%' THEN 'artists'
54
55 -- albums
56 WHEN attributes->>'db.statement' LIKE '%FROM albums%' THEN 'albums'
57 WHEN attributes->>'db.statement' LIKE '%INTO albums%' THEN 'albums'
58
59 ELSE 'other/unknown'
60 END AS primary_table
61 FROM records
62 WHERE
63 -- Filter for database operations
64 (span_name = 'SELECT neondb'
65 OR span_name = 'INSERT neondb'
66 OR span_name = 'UPDATE neondb'
67 OR span_name = 'DELETE neondb'
68 OR span_name = 'BEGIN neondb'
69 OR span_name = 'COMMIT neondb'
70 OR span_name = 'ROLLBACK neondb')
71 -- Exclude very fast queries (connection pool pings, etc.)
72 AND duration > 0.001
73),
74aggregated_metrics AS (
75 -- Aggregate by query type and table
76 SELECT
77 query_type,
78 primary_table,
79 COUNT(*) AS total_queries,
80 ROUND(AVG(duration * 1000)::numeric, 2) AS avg_duration_ms,
81 ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration * 1000)::numeric, 2) AS p50_duration_ms,
82 ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration * 1000)::numeric, 2) AS p95_duration_ms,
83 ROUND(PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY duration * 1000)::numeric, 2) AS p99_duration_ms,
84 ROUND(MAX(duration * 1000)::numeric, 2) AS max_duration_ms,
85 COUNT(*) FILTER (WHERE otel_status_code = 'ERROR') AS error_count,
86 ROUND((COUNT(*) FILTER (WHERE otel_status_code = 'ERROR')::numeric / COUNT(*)::numeric * 100), 2) AS error_rate_pct
87 FROM query_classification
88 GROUP BY query_type, primary_table
89)
90SELECT
91 query_type AS "Query Type",
92 primary_table AS "Table",
93 total_queries AS "Count",
94 avg_duration_ms AS "Avg (ms)",
95 p50_duration_ms AS "p50 (ms)",
96 p95_duration_ms AS "p95 (ms)",
97 p99_duration_ms AS "p99 (ms)",
98 max_duration_ms AS "Max (ms)",
99 error_count AS "Errors",
100 COALESCE(error_rate_pct, 0) AS "Error Rate %"
101FROM aggregated_metrics
102WHERE total_queries > 0
103ORDER BY
104 -- Sort by most impactful queries first
105 (total_queries * avg_duration_ms) DESC,
106 error_count DESC
107LIMIT 50;
108
109
110-- Alternative: Top Slowest Individual Queries
111-- Uncomment to see the actual slowest query instances instead of aggregated patterns
112/*
113SELECT
114 ROUND(duration * 1000, 2) AS "Duration (ms)",
115 CASE
116 WHEN span_name = 'SELECT neondb' THEN 'SELECT'
117 WHEN span_name = 'INSERT neondb' THEN 'INSERT'
118 WHEN span_name = 'UPDATE neondb' THEN 'UPDATE'
119 WHEN span_name = 'DELETE neondb' THEN 'DELETE'
120 ELSE 'OTHER'
121 END AS "Type",
122 LEFT(attributes->>'db.statement', 150) AS "Query Preview",
123 start_timestamp AS "Timestamp",
124 trace_id AS "Trace ID",
125 otel_status_code AS "Status"
126FROM records
127WHERE
128 (span_name = 'SELECT neondb'
129 OR span_name = 'INSERT neondb'
130 OR span_name = 'UPDATE neondb'
131 OR span_name = 'DELETE neondb')
132 AND duration > 0.001
133ORDER BY duration DESC
134LIMIT 25;
135*/
136
137
138-- Alternative: Database Operations Timeline (5-minute aggregation)
139-- Uncomment to see query volume and performance over time
140/*
141WITH time_metrics AS (
142 SELECT
143 DATE_TRUNC('minute', start_timestamp) AS minute,
144 CASE
145 WHEN span_name = 'SELECT neondb' THEN 'SELECT'
146 WHEN span_name = 'INSERT neondb' THEN 'INSERT'
147 WHEN span_name = 'UPDATE neondb' THEN 'UPDATE'
148 WHEN span_name = 'DELETE neondb' THEN 'DELETE'
149 ELSE 'OTHER'
150 END AS query_type,
151 COUNT(*) AS query_count,
152 ROUND(AVG(duration * 1000)::numeric, 2) AS avg_duration_ms,
153 COUNT(*) FILTER (WHERE otel_status_code = 'ERROR') AS error_count
154 FROM records
155 WHERE
156 (span_name = 'SELECT neondb'
157 OR span_name = 'INSERT neondb'
158 OR span_name = 'UPDATE neondb'
159 OR span_name = 'DELETE neondb')
160 AND start_timestamp > NOW() - INTERVAL '2 hours'
161 GROUP BY minute, query_type
162)
163SELECT
164 minute AS "Time",
165 query_type AS "Type",
166 query_count AS "Count",
167 avg_duration_ms AS "Avg Duration (ms)",
168 error_count AS "Errors"
169FROM time_metrics
170ORDER BY minute DESC, query_count DESC;
171*/
172
173
174-- Alternative: Connection Pool and Transaction Metrics
175-- Uncomment to analyze transaction patterns and connection behavior
176/*
177WITH transaction_metrics AS (
178 SELECT
179 DATE_TRUNC('minute', start_timestamp) AS minute,
180 COUNT(*) FILTER (WHERE span_name = 'BEGIN neondb') AS begin_count,
181 COUNT(*) FILTER (WHERE span_name = 'COMMIT neondb') AS commit_count,
182 COUNT(*) FILTER (WHERE span_name = 'ROLLBACK neondb') AS rollback_count,
183 ROUND(AVG(duration * 1000) FILTER (WHERE span_name = 'BEGIN neondb')::numeric, 2) AS avg_begin_ms,
184 ROUND(AVG(duration * 1000) FILTER (WHERE span_name = 'COMMIT neondb')::numeric, 2) AS avg_commit_ms
185 FROM records
186 WHERE
187 span_name = 'BEGIN neondb'
188 OR span_name = 'COMMIT neondb'
189 OR span_name = 'ROLLBACK neondb'
190 GROUP BY minute
191)
192SELECT
193 minute AS "Minute",
194 begin_count AS "Begins",
195 commit_count AS "Commits",
196 rollback_count AS "Rollbacks",
197 avg_begin_ms AS "Avg BEGIN (ms)",
198 avg_commit_ms AS "Avg COMMIT (ms)",
199 CASE
200 WHEN begin_count > 0
201 THEN ROUND((rollback_count::numeric / begin_count::numeric * 100), 2)
202 ELSE 0
203 END AS "Rollback Rate %"
204FROM transaction_metrics
205WHERE begin_count > 0 OR commit_count > 0 OR rollback_count > 0
206ORDER BY minute DESC
207LIMIT 60;
208*/