Hi Dillon, thanks for taking the time to talk with us today. Here are a few diagnostic queries that will help us understand the scale of your deployment. They should only take a few seconds to run. Feel free to send the results to me in a private message if you鈥檇 prefer.
-- Phoenix PostgreSQL Diagnostic Queries
-- Replace 'public' with your schema name if different (most deployments use 'public').
-- 1. Table sizes (heap vs indexes vs TOAST)
SELECT
c.relname AS table_name,
pg_size_pretty(pg_relation_size(c.oid)) AS heap_size,
pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size,
pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast_heap_size,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
AND c.relname IN ('spans', 'traces', 'span_annotations')
ORDER BY pg_total_relation_size(c.oid) DESC;
-- 2. Row counts and vacuum stats
SELECT
relname AS table_name,
n_live_tup AS row_count,
n_dead_tup AS dead_tuples,
last_autovacuum
FROM pg_stat_user_tables
WHERE schemaname = 'public'
AND relname IN ('spans', 'traces', 'span_annotations', 'span_costs', 'trace_annotations')
ORDER BY n_live_tup DESC;
-- 3. PostgreSQL settings
SELECT name, setting, unit FROM pg_settings
WHERE name IN (
'work_mem', 'shared_buffers', 'effective_cache_size',
'max_connections', 'max_parallel_workers_per_gather'
);
-- 4. Sampled span stats (~1% of pages)
SET search_path TO public;
SELECT
COUNT(*) AS sampled_rows,
MIN(start_time)::date AS earliest,
MAX(start_time)::date AS latest,
pg_size_pretty(AVG(pg_column_size(attributes))::bigint) AS avg_attributes_size,
pg_size_pretty(AVG(pg_column_size(events))::bigint) AS avg_events_size
FROM spans TABLESAMPLE SYSTEM (1);
