How does it work
Astrato appends metadata to the end of each query, subsequently allowing this to be viewed in Postgres. Postgres already contains data about which databaseuser has accessed each table, warehouse etc.
In this article we will cover how to store this data in a new database, using only one block of code.
The data that Astrato appends is shown below.
--
{
"objectId":"f4ed7820-9884-45ba-b078-86fc260d5511",
"sheetId":"3c2fa835-05ce-47f8-9489-3b99a76adc0b",
"workbookId":"d8ccb7b8-970e-477f-a093-6e862a431e21"
}
To supplement this, you can export the lookup associating workbooks, sheets and users, from the administration section https://app.astrato.io/administration/exports.
Currently, this is a manual upload for security purposes, since user data contains PII. The tables can be directly uploaded to supabase/Postgres. These tables/exports from Astrato are required to determine the names of users, workbooks and groups.
Accessing High Level Overview
This query analyses Postgres query history and extracts Astrato metadata embedded in SQL comments. It combines database performance metrics with contextual information about who ran the query and from which workbook, sheet, and object.
This provides a small dataset to
ℹ️ pg_stat_statements provides aggregated database query statistics, not user activity logs. It cannot track logins, sessions, dashboard opens, or UI interactions. Results depend on SQL comments for user attribution, may be reset on restart, and represent grouped query patterns rather than individual events.
-- 1) enable (requires restart if not already)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 2) read history-like rollups (not per-execution rows, but persisted stats)
WITH parsed AS (
SELECT
queryid,
calls,
total_exec_time,
mean_exec_time,
min_exec_time,
max_exec_time,
rows,
stats_since,
regexp_replace(query, '^.*--\s*', '')::jsonb AS meta_json
FROM pg_stat_statements
WHERE query LIKE '%-- {%'
)
SELECT
queryid,
calls,
total_exec_time,
mean_exec_time,
min_exec_time,
max_exec_time,
rows,
stats_since,
meta_json ->> 'type' AS type,
meta_json ->> 'userId' AS user_id,
meta_json ->> 'roleType' AS role_type,
meta_json ->> 'objectId' AS object_id,
meta_json ->> 'sheetId' AS sheet_id,
meta_json ->> 'workbookId' AS workbook_id,
meta_json ->> 'workbookState' AS workbook_state,
meta_json -> 'measures' AS measures
FROM parsed
ORDER BY total_exec_time DESC;
Accessing query-level logs
This approach creates a persistent query history layer on top of Postgres in Supabase. It captures executed SQL statements and performance metrics, and stores them in a dedicated table that can be queried like any other dataset.
Unlike pg_stat_statements, which only exposes aggregated statistics, this solution provides a time-based record of query activity that can be analysed alongside Astrato metadata. This makes it possible to connect database workload directly to users, workbooks, sheets, and business activity.
The result is a lightweight, Postgres-native query history, designed for analytics, governance, and performance insight.
Use cases
Dashboard performance analysis
Identify which workbooks or visuals generate the slowest queries.User and workload profiling
Understand which users or roles drive the highest query volume.Feature and KPI usage tracking
Analyse which measures, dimensions, or KPIs are queried most often.Capacity and scaling decisions
Use historical query patterns to inform database sizing and optimisation.Governance and audit reporting
Provide evidence of analytics usage across teams and tenants.Root cause analysis
Trace performance incidents back to specific queries, sheets, or users.Product analytics (infrastructure lens)
Complement application telemetry with database-level behaviour.
Instead of analysing raw SQL in isolation, teams can interpret database activity in business terms.
-- Supabase (managed Postgres): enable logging of EVERY statement + its duration
-- Logs are viewable in Supabase Dashboard → Logs Explorer → Postgres logs
-- Note: Supabase does not let you manage logging_collector / log files directly; you control what gets emitted. :contentReference[oaicite:0]{index=0}
-- 1) Log every statement (SQL text)
alter role "postgres" set "log_statement" to 'all';
-- 2) Log duration for every completed statement (0ms threshold)
alter role "postgres" set "log_min_duration_statement" to '0';
-- 3) (Optional) Reduce non-query noise in logs
alter role "postgres" set "log_min_messages" to 'warning';
-- 4) Check effective values
show "log_statement";
show "log_min_duration_statement";
show "log_min_messages";
-----------------------------------
-- Step 1 — Create a query log table
create schema if not exists observability;
create table if not exists observability.postgres_query_logs (
id bigint generated always as identity primary key,
logged_at timestamptz default now(),
duration_ms numeric,
query_text text
);
-- Step 2 — Capture queries automatically (inside Postgres)
create extension if not exists pg_stat_statements;
-- Step 3 — Snapshot queries into your table
insert into observability.postgres_query_logs (duration_ms, query_text)
select
mean_exec_time as duration_ms,
query as query_text
from pg_stat_statements;
-- Step 4 — Query your “logs” | EXAMPLES
select *
from observability.postgres_query_logs
where query_text like '%-- {%'
order by logged_at desc;
--Slowest queries:
select *
from observability.postgres_query_logs
WHERE query_text LIKE '%-- {%'
order by duration_ms desc
limit 50;
--Most frequent queries:
select query_text, count(*)
from observability.postgres_query_logs
WHERE query_text LIKE '%-- {%'
group by query_text
order by count(*) desc;