Clickhouse Logs Queries

Querying Supabase logs (ClickHouse)

Supabase logs live in a single ClickHouse logs table, served by the

logs.all.otel analytics endpoint. Every log line from every part of the stack

is one row in this table, tagged by a source column. This replaces the older

BigQuery model, where each service had its own table and fields were reached

through cross join unnest(metadata).

Two kinds of work use this skill, and they share the same SQL model:

• Writing or reviewing a logs query (in the Logs Explorer or anywhere a raw

ClickHouse logs query is needed). Start here in this file.

• Wiring a logs query in the Studio codebase (branded analytics SQL, the

endpoint picker, the OTEL query builders). Read

references/codebase-integration.md.

If you are converting an existing BigQuery logs query, read

references/bigquery-migration.md for the full

translation table.

The logs table

Each row has a small set of real columns. Everything specific to a service lives

in log_attributes.

| Column | Type | Notes |

| ---------------- | --------------------- | ----------------------------------------------------- |

| id | String | Unique log identifier. |

| timestamp | DateTime64 (UTC) | When the log was produced. Order/compare it directly. |

| event_message | String | The raw log line. |

| severity_text | String | Log level, when the source sets one. |

| source | String | The service the log came from. Always filter on this. |

| log_attributes | Map(String, String) | Structured per-source fields, keyed by a dotted path. |

timestamp is formatted like 2026-06-22T09:34:06.215000 (ISO 8601, microsecond

precision, no trailing Z). In the Logs Explorer the selected time range is

applied for you, so you rarely need to write a timestamp filter by hand.

A minimal, well-formed query. Lead with a comment naming the query, filter by

source, and always limit:

-- recent edge requests
select timestamp, event_message
from logs
where source = 'edge_logs'
order by timestamp desc
limit 100;

Sources

source selects the service. The common ones:

• edge_logs — API gateway requests and responses
• postgres_logs — database statements and errors (also where pg_cron logs live)
• auth_logs — authentication and authorization activity
• function_edge_logs — edge function requests and responses
• function_logs — console output from inside edge functions
• storage_logs — object upload and retrieval activity
• realtime_logs — Realtime client connections
• postgrest_logs, supavisor_logs, pgbouncer_logs — mostly id, timestamp, event_message

The Logs Explorer Field Reference drawer lists every source and the fields it

actually sets. When in doubt about a key, discover it from real data rather than

guessing (see below).

Reading fields from log_attributes

log_attributes maps a string key to a string value. Read a field with bracket

access. There are no unnesting joins:

select
  log_attributes['request.method'] as method,
  log_attributes['request.path'] as path,
  log_attributes['response.status_code'] as status
from logs
where source = 'edge_logs'

The key keeps the dotted path that BigQuery expressed through nested structs, with

the metadata root dropped: BigQuery metadata.request.method becomes

log_attributes['request.method']. Keep the full prefix — request.cf.country

is log_attributes['request.cf.country'], not log_attributes['cf.country'].

Common keys by source:

• edge_logs: request.method, request.path, request.search, response.status_code, identifier
• postgres_logs: parsed.error_severity, parsed.detail, parsed.hint, parsed.query, identifier
• auth_logs: level, status, path, msg, error
• function_edge_logs: response.status_code, request.method, request.pathname, function_id, execution_id, execution_time_ms
• function_logs: event_type, function_id, execution_id, level

Numeric fields are strings

Map values are always strings. To compare or aggregate a numeric field, wrap it in

toInt32OrZero, which returns 0 for missing or non-numeric values so it never

errors on partial data:

select count() as server_errors
from logs
where source = 'edge_logs'
  and toInt32OrZero(log_attributes['response.status_code']) between 500 and 599

Discover the keys a source sets

Read mapKeys from recent rows rather than guessing key names:

select arrayJoin(mapKeys(log_attributes)) as key, count() as n
from logs
where source = 'postgres_logs'
group by key
order by n desc
limit 100;

arrayJoin(mapKeys(...)) flattens the map keys into one row per key so you can

rank them by frequency. (The Studio codebase does exactly this for the Field

Reference drawer and to feed real keys to the AI rewrite.)

ClickHouse vs BigQuery functions

These are the substitutions that trip people up most:

| Need | BigQuery | ClickHouse |

| ------------------ | ----------------------------- | -------------------------------------------- |

| Count rows | count(*) | count() |

| Regex match | regexp_contains(x, 'p') | match(x, 'p') |

| Substring match | x like '%p%' | x ilike '%p%' (case-insensitive) or like |

| Numeric coercion | cast(x as int64) | toInt32OrZero(x) |

| Read the timestamp | cast(timestamp as datetime) | timestamp (use the column directly) |

| Map keys | n/a (used unnest) | mapKeys(log_attributes) |

The logs.all.otel analytics endpoint (and the Logs Explorer on top of it)

rejects count(*) and select * — use count() and list the columns you need.

(Raw ClickHouse supports both; this is a constraint of the logs query surface.)

Best practices

These keep queries correct and cheap. Log tables are large; an unbounded scan

reads far more data than you need.

• Start every query with an identifying comment (e.g. -- errors since last deploy). It labels the query in logs and review, and makes each of several queries in a file easy to tell apart.
• Always include a LIMIT. Even for aggregates while you iterate.
• Always query from logs where source = '...'. There is no per-service table (no edge_logs, postgres_logs, etc. table) — there is one logs table, and source scopes it to a service. Filtering by source is required, not just an optimization.
• Keep the time range tight. A smaller window returns results faster.
• Filter on the real columns (source, timestamp) before reaching into

log_attributes.

• Order by timestamp desc to see the most recent logs first.
• Use count(), not count(*) or select *.

Worked examples

Requests by status code:

select
  toInt32OrZero(log_attributes['response.status_code']) as status,
  count() as count
from logs
where source = 'edge_logs'
group by status
order by count desc
limit 50

Auth errors:

select timestamp, event_message, log_attributes['msg'] as message
from logs
where source = 'auth_logs'
  and log_attributes['level'] in ('error', 'fatal')
order by timestamp desc
limit 100

Search the raw message:

select timestamp, event_message
from logs
where source = 'postgres_logs'
  and event_message ilike '%deadlock%'
order by timestamp desc
limit 100

Postgres errors grouped by severity (the canonical unnest-to-map conversion):

select log_attributes['parsed.error_severity'] as severity, count() as count
from logs
where source = 'postgres_logs'
  and log_attributes['parsed.error_severity'] in ('ERROR', 'FATAL', 'PANIC')
group by severity
order by count desc
limit 100

When the user pastes a BigQuery query

Convert it rather than running it as-is. The mechanical steps (drop the

per-service table for from logs where source = ..., remove every

cross join unnest(...), rewrite unnest-alias columns as log_attributes['...']

lookups, swap the functions above) are spelled out with a full before/after in

references/bigquery-migration.md. The Logs

Explorer also has a built-in Rewrite to ClickHouse action that does this with

AI; point users to it for one-off conversions in the dashboard.