Detecting issues

Detection is the step between accessing project data and troubleshooting a specific problem. Use the sources in Observe the data to produce a count, rate, trend, or named finding. Do not try to prove the root cause yet.

This guide provides starting checks for Health, Security, Performance, and Usage. The log examples use ClickHouse SQL in the Logs Explorer or MCP query_logs. The database examples use Postgres SQL in the SQL Editor or MCP execute_sql.

Use a time range that represents normal traffic, then compare it with the same period after a deployment or configuration change. When a check returns a spike, error code, SQLSTATE, object name, or advisor finding, take that evidence to Diagnosing.

Health#

Health checks answer whether a service is available and behaving within its normal error and resource envelope.

Measure API server-error rate#

Count requests and 5xx responses by hour. A rate is more useful than a raw error count when traffic changes.

1
select
2
  toStartOfHour(timestamp) as hour,
3
  count() as requests,
4
  countIf(toInt32OrZero(log_attributes['response.status_code']) >= 500) as server_errors,
5
  round(
6
    100.0 * countIf(toInt32OrZero(log_attributes['response.status_code']) >= 500) /
7
      nullIf(count(), 0),
8
    2
9
  ) as server_error_percent
10
from logs
11
where source = 'edge_logs'
12
group by hour
13
order by hour desc
14
limit 24;

Find failing API paths#

Use the rate check to find an affected window, then identify the paths and status codes producing the errors.

1
select
2
  log_attributes['request.path'] as path,
3
  toInt32OrZero(log_attributes['response.status_code']) as status,
4
  count() as errors
5
from logs
6
where source = 'edge_logs'
7
  and toInt32OrZero(log_attributes['response.status_code']) >= 500
8
group by path, status
9
order by errors desc
10
limit 20;

Check Postgres connection pressure#

Compare active and waiting connections with the configured limit. A high percentage is a signal to inspect pooler settings, long-running transactions, and traffic before changing the limit.

1
select
2
  count(*) as current_connections,
3
  count(*) filter (where state = 'active') as active_connections,
4
  count(*) filter (where wait_event_type is not null) as waiting_connections,
5
  current_setting('max_connections')::int as max_connections,
6
  round(
7
    100.0 * count(*) / nullif(current_setting('max_connections')::int, 0),
8
    2
9
  ) as connection_percent
10
from pg_stat_activity;

You can read API response errors and service availability in Reports, or use the Metrics API for CPU and connection series. Once you have a failing path, status, or saturated resource, continue in Diagnosing.

Security#

Security checks look for access-control findings and changes in authentication or authorization failures. Treat them as review signals, not proof of an attack.

Measure authorization failures#

Count 401 and 403 responses by hour and status. Compare the rate with a known-good window so normal unauthenticated traffic does not become an alert by itself.

1
select
2
  toStartOfHour(timestamp) as hour,
3
  toInt32OrZero(log_attributes['response.status_code']) as status,
4
  count() as failures
5
from logs
6
where source = 'edge_logs'
7
  and toInt32OrZero(log_attributes['response.status_code']) in (401, 403)
8
group by hour, status
9
order by hour desc, status
10
limit 48;

Find affected paths and methods#

After detecting a spike, group failures by route and method. This separates a broken client flow from failures spread across the API.

1
select
2
  log_attributes['request.method'] as method,
3
  log_attributes['request.path'] as path,
4
  toInt32OrZero(log_attributes['response.status_code']) as status,
5
  count() as failures
6
from logs
7
where source = 'edge_logs'
8
  and toInt32OrZero(log_attributes['response.status_code']) in (401, 403)
9
group by method, path, status
10
order by failures desc
11
limit 20;

Find public-schema tables without RLS#

This database query is a focused inventory check. Confirm each result against the project's intended access model; a result is not evidence that data was exposed.

1
select
2
  n.nspname as schema_name,
3
  c.relname as table_name
4
from
5
  pg_class as c
6
  join pg_namespace as n on n.oid = c.relnamespace
7
where n.nspname = 'public' and c.relkind in ('r', 'p') and not c.relrowsecurity
8
order by table_name;

Run Security Advisor from Studio, MCP get_advisors, the CLI, or the Management API for the full catalog of deterministic checks. Take a lint name, table, policy, path, or status pattern to Diagnosing before changing policies, grants, or keys.

Performance#

Performance checks identify expensive work, contention, and cache misses. They narrow the investigation to a query, relation, session, or resource.

Find long-running sessions#

Look for sessions that have been active or idle in a transaction for more than 30 seconds.

1
select
2
  pid,
3
  usename as role,
4
  state,
5
  now() - query_start as duration,
6
  wait_event_type,
7
  wait_event,
8
  left(query, 120) as query
9
from pg_stat_activity
10
where datname = current_database()
11
  and pid != pg_backend_pid()
12
  and state in ('active', 'idle in transaction')
13
  and now() - query_start > interval '30 seconds'
14
order by duration desc
15
limit 20;

Find blocked sessions#

Use pg_blocking_pids to name the blocked and blocking processes. Do not cancel either process until you understand the transaction and its impact.

1
select
2
  blocked.pid as blocked_pid,
3
  blocked.usename as blocked_role,
4
  blocker.pid as blocking_pid,
5
  blocker.usename as blocking_role,
6
  now() - blocked.query_start as blocked_for,
7
  left(blocked.query, 120) as blocked_query,
8
  left(blocker.query, 120) as blocking_query
9
from pg_stat_activity as blocked
10
cross join lateral unnest(pg_blocking_pids(blocked.pid)) as blocking_pid
11
join pg_stat_activity as blocker on blocker.pid = blocking_pid
12
order by blocked_for desc;

Find expensive query patterns#

pg_stat_statements aggregates normalized queries over time. Rank by total execution time, then inspect mean time and calls before deciding whether a frequent query is inefficient.

1
select
2
  calls,
3
  round(total_exec_time::numeric, 2) as total_time_ms,
4
  round(mean_exec_time::numeric, 2) as mean_time_ms,
5
  rows,
6
  left(query, 160) as query
7
from pg_stat_statements
8
order by total_exec_time desc
9
limit 20;

Measure shared-buffer hit rate#

A ratio below 99% means more than 1% of observed block accesses missed shared_buffers. Postgres cannot tell whether a miss was served by the operating system cache or physical disk.

1
select
2
  'index hit rate' as name,
3
  round(100.0 * sum(idx_blks_hit) / nullif(sum(idx_blks_hit) + sum(idx_blks_read), 0), 2) as ratio
4
from pg_statio_user_indexes
5
union all
6
select
7
  'table hit rate' as name,
8
  round(
9
    100.0 * sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0),
10
    2
11
  ) as ratio
12
from pg_statio_user_tables;

Pull Performance Advisor findings and compare the same window with Reports or the Metrics API. The full command and SQL catalog is in Inspect the database.

Usage#

Usage checks identify growth in traffic, data, and connections before it becomes a capacity problem. They do not calculate billing totals.

Trend API requests#

Count requests by hour to establish a baseline and spot step changes.

1
select
2
  toStartOfHour(timestamp) as hour,
3
  count() as requests
4
from logs
5
where source = 'edge_logs'
6
group by hour
7
order by hour desc
8
limit 168;

Find high-volume API paths#

Group by method and path to identify which workload accounts for the growth.

1
select
2
  log_attributes['request.method'] as method,
3
  log_attributes['request.path'] as path,
4
  count() as requests
5
from logs
6
where source = 'edge_logs'
7
group by method, path
8
order by requests desc
9
limit 20;

Find the largest relations#

Measure tables and their indexes together. Save the result on a regular cadence to establish a growth trend.

1
select
2
  schemaname,
3
  relname as table_name,
4
  pg_total_relation_size(relid) as total_bytes,
5
  pg_size_pretty(pg_total_relation_size(relid)) as total_size
6
from pg_catalog.pg_statio_user_tables
7
order by total_bytes desc
8
limit 20;

Count connections by role and state#

Connection growth can reveal a new workload or a client that is not pooling correctly.

1
select
2
  usename as role,
3
  state,
4
  count(*) as connections
5
from pg_stat_activity
6
where datname = current_database()
7
group by role, state
8
order by connections desc;

Reports show request, disk, and database-size trends without SQL. The Management API usage endpoint returns request counts for authorized scripts. Use supabase inspect db table-sizes and bloat to run related database checks from the CLI.

Turn a detection into a diagnosis#

A detection result should name an affected time window and at least one concrete anchor: a path, status, SQLSTATE, request ID, query, relation, PID, policy, or advisor lint. Take that evidence to Diagnosing, identify the cause, apply the smallest relevant solution, and rerun the same detection check to verify the result.

After a check is useful and repeatable, hire an agent to run it on a schedule.