ClickHouse inactive parts accumulating in observations table — 112 GiB buildup in production despite non-business hours

[Vinothkumars1994]

Describe your question

Description

Environment:

Langfuse: self-hosted (Docker)
ClickHouse: running in Docker container
Usage: production environment with active chat/LLM workload

Langfuse Version: 3.143.0

Problem 1:

We are observing a large accumulation of inactive parts in our ClickHouse default.observations and default.traces tables in production.

Running the following query:

SELECT
database,
table,
formatReadableSize(sum(bytes_on_disk)) AS inactive_size,
count() AS inactive_parts
FROM system.parts
WHERE active = 0
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC;

default observations 112.55 GiB 191

Root Cause (our analysis):

High chat volume causes frequent small inserts into observations and traces. ClickHouse background merge threads cannot keep up with the insert rate, causing inactive parts to pile up even during non-business hours.

Questions:

1. Please provide the best solution to clean up the inactive parts in the observations and traces tables.
2. Also, please provide the steps to prevent this merge pile-up from happening again in the future.

---

Problem 2:

Our ClickHouse system database log tables are consuming over 100 GiB of disk space and appear to be growing unbounded with no TTL or retention policy configured.

SELECT
table,
formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE database = 'system' AND active = 1
GROUP BY table
ORDER BY sum(bytes_on_disk) DESC;

text_log 59.20 GiB
opentelemetry_span_log 20.48 GiB
query_log 15.69 GiB
trace_log 2.17 GiB
part_log 1.50 GiB

Question:

Please provide detailed steps to perform log rotation, or any other recommended solution, to manage the disk space consumed by ClickHouse system log tables in a self-hosted Langfuse production deployment.

Langfuse Cloud or Self-Hosted?

Self-Hosted

If Self-Hosted

3.143.0

If Langfuse Cloud

No response

SDK and integration versions

No response

Pre-Submission Checklist

• I have checked for existing issues/discussions and consulted Langfuse AI.

[inkeep-ai-assistant[bot]]

Problem 1: Inactive Parts Accumulation

The 112 GiB of inactive parts you're seeing in observations is normal ClickHouse behavior—inactive parts are remnants from merges that haven't been garbage-collected yet. ClickHouse eventually removes them automatically. (1)

Cleanup options:

• You can force cleanup with OPTIMIZE TABLE observations FINAL, but this requires scratch space roughly equal to the partition size and can be resource-intensive (2)
• For ongoing management, consider configuring a data retention policy to automatically delete old traces, observations, and scores (3), (1)

Prevention:

• Ensure your ClickHouse instance has adequate resources—minimum recommended is 2 CPU, 8 GiB Memory, with at least 16 GiB for larger deployments (1)
• If you're on ClickHouse 25.7+, you can reduce mutation pressure by setting CLICKHOUSE_LIGHTWEIGHT_DELETE_MODE to lightweight_update and CLICKHOUSE_USE_LIGHTWEIGHT_UPDATE=true (1)

---

Problem 2: System Log Tables Consuming 100+ GiB

This is a common issue. ClickHouse system log tables (trace_log, text_log, opentelemetry_span_log, etc.) have no TTL by default and can dominate disk usage. Langfuse does not read from these tables, so you can safely reduce them. (1), (4)

Option 1 — Disable unused system log tables (recommended):

Mount a config file into /etc/clickhouse-server/config.d/ with:

<clickhouse>
  <trace_log remove="1"/>
  <text_log remove="1"/>
  <opentelemetry_span_log remove="1"/>
  <asynchronous_metric_log remove="1"/>
  <metric_log remove="1"/>
  <latency_log remove="1"/>
</clickhouse>

(1)

Keep query_log, part_log, and error_log enabled—they're useful for debugging and remain small. (1)

Option 2 — Apply aggressive TTLs:

If you want to keep the tables for debugging, attach short TTLs and disable the query profiler:

<clickhouse>
  <profiles>
    <default>
      <query_profiler_real_time_period_ns>0</query_profiler_real_time_period_ns>
      <query_profiler_cpu_time_period_ns>0</query_profiler_cpu_time_period_ns>
    </default>
  </profiles>
  <trace_log>
    <engine>ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_time) TTL event_date + INTERVAL 7 DAY</engine>
  </trace_log>
  <opentelemetry_span_log>
    <engine>ENGINE = MergeTree PARTITION BY toYYYYMM(finish_date) ORDER BY (finish_date, finish_time_us) TTL finish_date + INTERVAL 7 DAY</engine>
  </opentelemetry_span_log>
  <query_log>
    <engine>ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_time) TTL event_date + INTERVAL 30 DAY</engine>
  </query_log>
</clickhouse>

(1)

Important: TTL directives only apply when the system table is first created. On an existing install, after deploying the config you also need to retrofit the tables:

SET max_table_size_to_drop = 0;
TRUNCATE TABLE system.trace_log;

ALTER TABLE system.trace_log MODIFY TTL event_date + INTERVAL 7 DAY;

(1)

Repeat for each table you want to cap.

📚 Sources:

1. Scaling
2. Self-hosted ClickHouse disk fills up — lightweight-deleted rows never reclaimed
3. ClickHouse
4. How do I reduce ClickHouse disk size on self-hosted Langfuse?

---

Have another question? Just tag @inkeep.

[Vinothkumars1994]

Hi Team , We need some urgent help regarding the problem one for Inactive parts (Problem 1: Inactive Parts Accumulation)
Highly anticipating for some feasible solution since the size is keep on reaching its limits on regular basis.
Thank you so much for the understanding!!

[Ashmita152]

Hi @Vinothkumars1994 Can you provide me output of following queries, this will help to determine whether the 112 GiB is recent/transient or genuinely stuck.

SELECT
    database,
    table,
    count() AS inactive_parts,
    formatReadableSize(sum(bytes_on_disk)) AS inactive_size,
    min(remove_time) AS oldest_remove_time,
    max(remove_time) AS newest_remove_time,
    max(dateDiff('second', remove_time, now())) AS oldest_age_seconds,
    countIf(refcount > 2) AS parts_still_in_use,
    groupArrayDistinct(removal_state) AS removal_states
FROM system.parts
WHERE active = 0
  AND database = 'default'
  AND table IN ('observations', 'traces')
GROUP BY database, table;

To check if the issue is active small parts.

SELECT
    table,
    partition_id,
    count() AS active_parts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active
  AND database = 'default'
  AND table IN ('observations', 'traces')
GROUP BY table, partition_id
ORDER BY active_parts DESC;

[Protemir]

@Vinothkumars1994 these are two separate problems, and fixing either one doesn't touch your Langfuse data.

1. 112 GiB of inactive parts in observations

Inactive parts are the old copies left behind after a merge or a mutation, so they come from work that did run. If merges were falling behind, you'd see many active parts instead; @Ashmita152's second query shows that. ClickHouse deletes an inactive part about 8 minutes later (old_parts_lifetime = 480 s), but only once no query or merge still uses it. If @Ashmita152's first query shows an oldest_remove_time hours ago and parts_still_in_use above 0, something is holding them. Look for long queries and pending mutations:

SELECT query_id, round(elapsed) AS sec, left(query, 100) AS query
FROM system.processes ORDER BY elapsed DESC LIMIT 5;

SELECT database, table, command, create_time, parts_to_do, latest_fail_reason
FROM system.mutations WHERE NOT is_done;

Stop a stuck query with KILL QUERY WHERE query_id = '...'. Once nothing references the parts, ClickHouse removes them by itself (a ClickHouse restart clears them too). Langfuse's deletes run as mutations in ClickHouse, and each mutation writes a new version of every part it touches, so a long mutation queue keeps producing fresh inactive parts.

A detail about the number itself: a mutation hard-links the column files it doesn't change, so the old and the new part share them on disk, and system.parts counts them in both. Part of the 112 GiB may be the same bytes. du -sh on the ClickHouse data directory, compared with the total of the active parts, shows what the inactive ones really cost.

While the disk is tight I'd skip OPTIMIZE TABLE observations FINAL: it rewrites whole partitions, needs free space for a full new copy, and leaves the old parts inactive until they're cleaned up.

2. About 100 GiB of ClickHouse's own logs

These are ClickHouse's diagnostics, not your traces. Langfuse does read system.query_log for some of its own queries (#13123), so empty it, but don't switch it off.

text_log is 59.20 GiB, over ClickHouse's 50 GB drop limit (max_table_size_to_drop), so a plain TRUNCATE fails with Code 359. On ClickHouse 23.12+ you can lift the limit for one statement:

TRUNCATE TABLE system.text_log SETTINGS max_table_size_to_drop = 0;
TRUNCATE TABLE system.opentelemetry_span_log;
TRUNCATE TABLE system.query_log;

On older ClickHouse, create a one-time flag first, then run the plain TRUNCATE:

docker compose exec clickhouse sh -c 'touch /var/lib/clickhouse/flags/force_drop_table && chmod 666 /var/lib/clickhouse/flags/force_drop_table'

The first DROP or TRUNCATE that needs the flag uses it up.

To stop it coming back, mount a file like this into /etc/clickhouse-server/config.d/ and restart ClickHouse (docker compose up -d clickhouse; a config reload isn't enough):

<clickhouse>
    <query_log><ttl>event_date + INTERVAL 7 DAY DELETE</ttl></query_log>
    <text_log><ttl>event_date + INTERVAL 7 DAY DELETE</ttl></text_log>
    <trace_log><ttl>event_date + INTERVAL 7 DAY DELETE</ttl></trace_log>
    <part_log><ttl>event_date + INTERVAL 7 DAY DELETE</ttl></part_log>
    <opentelemetry_span_log>
        <engine>ENGINE = MergeTree PARTITION BY toYYYYMM(finish_date) ORDER BY (finish_date, finish_time_us) TTL finish_date + INTERVAL 7 DAY DELETE</engine>
    </opentelemetry_span_log>
</clickhouse>

Two traps:

• opentelemetry_span_log is defined with <engine> in the default config, so a separate <ttl> for it stops the server from starting (Cannot add TTL to opentelemetry_span_log via config.xml ClickHouse/ClickHouse#88366). That's why its TTL sits inside <engine> above. If you don't need it, <opentelemetry_span_log remove="1"/> works too, but remove doesn't delete the existing table, so DROP it yourself.
• On restart, ClickHouse renames each changed log to text_log_0, query_log_0 and so on, with all the old rows and no TTL, and creates a new table with the TTL. So the ALTER TABLE ... MODIFY TTL step from the bot's answer isn't needed, and truncating first keeps the copies tiny. Then drop them; this query writes the statements for you:

SELECT 'DROP TABLE system.' || name || ' SETTINGS max_table_size_to_drop = 0;'
FROM system.tables
WHERE database = 'system' AND match(name, '_log_[0-9]+$');

One more thing, since you're on Langfuse 3.143.0: if you ever move ClickHouse to 26.8 or newer, update Langfuse first (#16858).

The same steps with the full file for every log, how to mount it and how to check the result are in section 4 of a guide I maintain (my project): https://github.com/Protemir/diskvet/blob/main/docs/guide.md#4-how-do-i-set-a-ttl-on-clickhouse-system-logs