Clickhouse - Default Partition should be monthly (toYYYYMM) rather than daily

[redsquare]

Advised on Clickhouse to have partitions of between 30gb and 150gb per partition, I am sure most people using rudder->click do not have this volume daily therefore the default should be monthly - keeping the amount of parts on disk lower should be the preferred option

rudder-server/warehouse/integrations/clickhouse/clickhouse.go

Line 853 in 9a4b843

partitionByClause = fmt.Sprintf(`PARTITION BY toDate(%s)`, partitionField)

[contributor-support]

Thanks for opening this issue! We'll get back to you shortly. If it is a bug, please make sure to add steps to reproduce the issue.

[ericdodds]

@redsquare we are going to slot this into an upcoming sprint. I'll reach out to you for more info as we get closer to starting the work.

[redsquare]

@ericdodds any update on this :)

[elliotdickison]

This would be super helpful to us as it's not possible to change partitions on a table after creation - fixing the problem after the fact is quite tricky.

[redsquare]

@elliotdickison agree, @ericdodds any update on this please :)

[gitcommitshow]

Not shipped yet. I am following up with the team to prioritise this.

[elliotdickison]

We've come up with an SOP to work around this - we get the CREATE sql for a RudderStack table, modify the sql with the partitioning we want, create the table, copy data over to it, drop the old table, and rename the new table to match the old table's name. We've automated most of this via a script, we just have to remember to run it any time we add a new event and RudderStack creates a new table.

Given that good partitioning depends on use-case I think a config option to set the default partition strategy (hourly, daily, monthly, quarterly, yearly) might be helpful, although if you have to pick a single default I'd guess monthly is better than daily.

[redsquare]

Yes totally, monthly is best in most cases unless your a cloudflare/uber/ebay

…

On Thu, 9 Jan 2025, 17:07 Elliot Dickison, ***@***.***> wrote: We've come up with an SOP to work around this - a script that'll generate a migration to duplicate RudderStack tables with the partitioning we want. We just have to remember to run it any time we add a new event and RudderStack creates a new table. Given that good partitioning depends on use-case I think a config option to set the default partition strategy (hourly, daily, monthly, quarterly, yearly) might be helpful, although if you have to pick a single default I'd guess monthly is better than daily. — Reply to this email directly, view it on GitHub <#5079 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAOSDW4S4S5VZVS6B2HR7T2J2UF5AVCNFSM6AAAAABN6UPGU6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKOBQHAZTMOJTG4> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- iVendi Ltd is registered in England and Wales under company number 07003191. Registered address Llys Eirias, Abergele Road, Colwyn Bay LL29 8BW. VAT registered number 987472949. Authorised and regulated by the Financial Conduct Authority number 679767. This email, including any attachments, is private and confidential and may contain privileged information. It is intended for use by the addressee(s) only. If you have received this message in error, please contact us immediately and delete it from your system; do not read, copy or disclose its contents to anyone or use it for any purpose.  It is the recipient's responsibility to check this email and any attachments for the presence of viruses.  iVendi Ltd accepts no liability for any damage caused by any virus transmitted by this email.  No responsibility is accepted for emails unconnected with our business.