.png&w=640&q=75){kind=small}
Overview
ClickHouse maintains various system log tables that track internal operations, queries, metrics, and errors. Over time, these tables can accumulate significant amounts of data and consume disk space. This guide explains how to identify large log tables and safely clean them up by dropping old partitions.
Data Deletion Notice
The operations described in this guide permanently delete data. While dropping system log partitions is generally safe and does not affect your application data, please be aware:
- These operations are irreversible. Once a partition is dropped, the data cannot be recovered.
- Review carefully before executing. Always verify you are targeting the correct table and partition.
- You are responsible for any accidental data loss. Ensure you understand the implications before proceeding.
It is recommended to perform these operations during a maintenance window and to have appropriate backups if the log data is critical for your use case.
Step 1: List All System Log Tables and Their Sizes
First, identify which log tables exist and how much disk space they consume. This query joins system.tables with system.parts to calculate the total size of each log table.
SELECT
t.database,
t.name AS table,
sum(p.bytes_on_disk) AS bytes,
formatReadableSize(sum(p.bytes_on_disk)) AS size_readable
FROM system.tables AS t
LEFT JOIN system.parts AS p
ON t.database = p.database
AND t.name = p.table
AND p.active
WHERE t.database = 'system'
AND t.name LIKE '%_log'
GROUP BY t.database, t.name
ORDER BY bytes DESC NULLS LAST;Example Output:
┌─database─┬─table───────────────────┬─────bytes─┬─size_readable─┐
│ system │ asynchronous_metric_log │ 113226730 │ 107.98 MiB │
│ system │ trace_log │ 67686213 │ 64.55 MiB │
│ system │ metric_log │ 39356548 │ 37.53 MiB │
│ system │ processors_profile_log │ 13186698 │ 12.58 MiB │
│ system │ latency_log │ 11642604 │ 11.10 MiB │
│ system │ query_log │ 11125406 │ 10.61 MiB │
│ system │ part_log │ 33019 │ 32.25 KiB │
│ system │ query_views_log │ 30340 │ 29.63 KiB │
│ system │ text_log │ 4292 │ 4.19 KiB │
│ system │ error_log │ 3133 │ 3.06 KiB │
│ system │ asynchronous_insert_log │ 2690 │ 2.63 KiB |
└──────────┴─────────────────────────┴───────────┴───────────────┘Step 2: Inspect Partitions for a Specific Table
Once you've identified a table to clean, examine its partitions. Usually, the largest table is a good starting point. System log tables are typically partitioned by month (format: YYYYMM). This query shows the partition breakdown with row counts and sizes.
Replace 'metric_log' with the name of the table you want to inspect.
SELECT
database,
table,
partition,
sum(rows) AS rows,
sum(bytes_on_disk) AS bytes,
formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE database = 'system'
AND table = 'metric_log' -- Replace with your target table
GROUP BY database, table, partition
ORDER BY partition ASC;Example Output
┌─database─┬─table──────┬─partition─┬───rows─┬────bytes─┬─size──────┐
│ system │ metric_log │ 202511 │ 20046 │ 3778557 │ 3.60 MiB │
│ system │ metric_log │ 202512 │ 117031 │ 35430658 │ 33.79 MiB │
└──────────┴────────────┴───────────┴────────┴──────────┴───────────┘Note: The partition column shows the partition key. For monthly partitions, 202511 represents November 2025, 202512 represents December 2025, etc.
Step 3: Drop Old Partitions
To free up disk space, drop partitions you no longer need. Typically, you would keep recent partitions and drop older ones.
ALTER TABLE system.<table_name> DROP PARTITION '<partition_id>';Example: Drop the November 2025 partition from the metric_log table:
ALTER TABLE system.metric_log DROP PARTITION '202511';Optional: Verify Cleanup
After dropping partitions, re-run the queries from Steps 1 and 2 to confirm the data has been removed and disk space has been reclaimed.
Best Practices
1. Start with the largest tables — Focus on tables consuming the most disk space first.
2. Keep recent data — Retain at least the current month's partition for debugging purposes.
3. Schedule regular maintenance — Periodically review and clean log tables to prevent disk space issues.