Question #15436
Updated by Kévin Mèche over 5 years ago
Hello, After doing database maintenance operations (as per written in FAQ (1)), I explored a bit the database, to see which tables are taking the most space. (For information, my RudderMachine has ~60GB of storage, and manages 113 nodes exactly.) storage) <pre><code class="shell"> rudder=# SELECT pg_size_pretty( pg_database_size('rudder') ); pg_size_pretty ---------------- 40 GB </code></pre> So I checked for the heavyweight tables, and the winners are : <pre><code class="shell"> rudder=# SELECT relname AS "Table", pg_size_pretty(pg_total_relation_size(relid)) AS "Size", pg_size_pretty(pg_relation_size(relid)) AS "Relation size", pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS "External Size" FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 5; Table | Size | Relation size | External Size -------------------------+---------+---------------+--------------- nodes_info | 16 GB | 4392 kB | 16 GB nodes | 14 GB | 3405 MB | 10 GB archivedruddersysevents | 3237 MB | 2706 MB | 530 MB ruddersysevents | 2994 MB | 1429 MB | 1565 MB archivednodecompliance | 842 MB | 699 MB | 143 MB </code></pre> So, I do have 2 tables using half of the DB weight. I did a bit of exploration (and thanks to ticket #9518), and found out that some nodes have a long history stored in db, even for deleted reports or old configurations checks. For the table `nodes`, it seems I can remove safely historical entries from nodes with a request like <code class="sql">DELETE FROM nodes WHERE endtime < (now() - interval '7 days')</code>, but I fear it might break something, so I won't do. <pre><code class="shell"> rudder=# select count(*) from nodes where endtime < (now() - interval '7 days'); count ---------- 29083989 </code></pre> So, my question is: - is the request written above (or a cleaner one) viable ? - is there a possibility to clean the `nodes_info` table of what seems to be out of date values ? Warm regards, --- 1. https://faq.rudder.io/knowledge-bases/2/articles/14-database-is-using-too-much-space