Use monthly ClickHouse partitions for the RudderStack warehouse destination

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