Use monthly ClickHouse partitions for the RudderStack warehouse destination

TL;DR

Issue rudderlabs/rudder-server#5079 (closed, 10 comments): ClickHouse guidance says partitions should be 30-150GB each, and most RudderStack users do not hit that volume daily, so the warehouse destination defaulting to daily partitions created too many small parts on disk. Worse, you cannot change a ClickHouse table partition after creation.

When to use

You are seeing this: Users shared an SOP: grab the CREATE sql, rewrite the partitioning, copy data over, drop and rename. Use this skill when you run into "Use monthly ClickHouse partitions for the RudderStack warehouse destination".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

The problem

Issue rudderlabs/rudder-server#5079 (closed, 10 comments): ClickHouse guidance says partitions should be 30-150GB each, and most RudderStack users do not hit that volume daily, so the warehouse destination defaulting to daily partitions created too many small parts on disk. Worse, you cannot change a ClickHouse table partition after creation. Users shared an SOP: grab the CREATE sql, rewrite the partitioning, copy data over, drop and rename. The maintainers eventually shipped the real fix: month partitioning is now supported and can be configured during destination setup (PR 6931).

The fix

If you are sending RudderStack warehouse data to ClickHouse, avoid the old daily-partition default: ClickHouse partitions should be 30-150GB each, and daily partitions for most event volumes just pile up small parts on disk. Since you cannot change a table's partition after creation, getting this right at setup matters. Monthly partitioning is now supported and configurable during destination setup (landed in PR 6931). If you already have tables stuck on daily partitioning, the workaround users settled on is: take the CREATE sql for the RudderStack table, rewrite the partitioning, create the new table, copy the data over, drop the old table, and rename the new one to match. Automate it, because you have to repeat it every time a new event creates a new table.

Notes

Thread: https://github.com/rudderlabs/rudder-server/issues/5079. Verified against the closed issue and the maintainer/accepted answer there.

In https://github.com/rudderlabs/rudder-server/issues/5079, the reporter asked: Issue rudderlabs/rudder-server#5079 (closed, 10 comments): ClickHouse guidance says partitions should be 30-150GB each, and most RudderStack users do not hit that volume daily, so the warehouse destination defaulting to daily partitions created too many small parts on disk. Worse, you cannot change a ClickHouse table partition after creation. Users shared an SOP: grab the CREATE sql, rewrite the partitioning, copy data over, drop and rename. The maintainers eventually shipped the real fix: month partitioning is now supported and can be configured during destination setup (PR 6931). The verified answer: If you are sending RudderStack warehouse data to ClickHouse, avoid the old daily-partition default: ClickHouse partitions should be 30-150GB each, and daily partitions for most event volumes just pile up small parts on disk. Since you cannot change a table's partition after creation, getting this right at setup matters. Monthly partitioning is now supported and configurable during destination setup (landed in PR 6931). If you already have tables stuck on daily partitioning, the workaround users settled on is: take the CREATE sql for the RudderStack table, rewrite the partitioning, create the new table, copy the data over, drop the old table, and rename the new one to match. Source thread: https://github.com/rudderlabs/rudder-server/issues/5079