Hello Phoenix Support - Disk space keeps increasing when i use the postgres DB for observability even after having the 'Maximum retention trace period' in settings to be 20 days. Am i missing something, wat should i do to clean up the disk space.
💡 Hint: Mention RunLLM in the thread for followups.
are you using the latest version of Phoenix?
you can also sort the spans by date to see wether the old spans are deleted
i have checked the DB and it doesn't have data prior to 20 days
am using helm chart deployment.
ok. if the old data is deleted, it could just be that new data is very large, or postgres hasn’t vacuumed the dead tuples yet. you can check how much dead tuples there are and when the auto vacuum took place via the following query.
SELECT
n.nspname AS schema,
c.relname AS table_name,
pg_size_pretty(pg_relation_size(c.oid)) AS main_table_size,
pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast_heap_size,
pg_size_pretty(pg_indexes_size(c.reltoastrelid)) AS toast_index_size,
pg_size_pretty(
pg_relation_size(c.reltoastrelid) + pg_indexes_size(c.reltoastrelid)
) AS toast_total_size,
ROUND(
(pg_relation_size(c.reltoastrelid) + pg_indexes_size(c.reltoastrelid))::numeric
/ NULLIF(pg_total_relation_size(c.oid), 0) * 100,
2
) AS toast_pct_of_total,
ct.relname AS toast_table_name,
COALESCE(st.n_live_tup, 0) AS live_toast_chunks,
COALESCE(st.n_dead_tup, 0) AS dead_toast_chunks,
COALESCE(
ROUND(st.n_dead_tup::numeric / NULLIF(st.n_live_tup + st.n_dead_tup, 0) * 100, 2),
0
) AS toast_dead_pct,
st.last_autovacuum AS toast_last_vacuum
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_class ct ON ct.oid = c.reltoastrelid
LEFT JOIN pg_stat_all_tables st ON st.relid = ct.oid
WHERE c.relkind = 'r'
AND c.reltoastrelid > 0
AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
AND pg_total_relation_size(c.oid) > 10000000
ORDER BY pg_relation_size(c.reltoastrelid) + pg_indexes_size(c.reltoastrelid) DESC
LIMIT 20;