Office Hours #14: ClickHouse® Partitioning Deep Dive скачать в хорошем качестве

Office Hours #14: ClickHouse® Partitioning Deep Dive

Have questions about ClickHouse®? Get a free consultation with Altinity. No pressure, no sales talk. Just answers to your questions. Book a free help session: https://hubs.la/Q02qr1RZ0 ______________________________________________________ CHAPTERS: 2:00 What is the purpose of PARTITION BY in ClickHouse? 3:02 How do parts, merges, and background threads affect ingestion performance? 9:26 [question in the chat] What about timeseries with several entities? 12:15 [question in the chat]: Is there a better way to handle data with partitions, say have a monthly partitioned data for cold data (say 2 year or older) and then a daily partitioned for recent data. 16:52 [question in the chat]: What happens if you don’t partition at all? Any downside except constant merge until parts grew to 150gb? 20:28 [question in the chat]: I have a report that needs to be rerun many times per scenario (think of a simulation run and it needs to be recalculated once the user changes input) and regenerated frequently. Usually, only a handful of scenarios are queried at a time for analysis. Would you say that partitioning by something like scenario_id is appropriate, since it would make it very easy to replace/drop the report and not keep every iteration? Inserts would only target one scenario at a time. What would the partition limit be in that case? 21:51[question in the chat]: I have a table (241M rows) with an uncompressed size of 8.96 GiB and with no partitions (the cluster has 2 shards and 2 replicas) and it takes around 10 min to delete one day of data. Is this acceptable? 27:21 How does partition size affect merges and query performance? 30:20 How does partitioning interact with TTLs and data retention? 32:45 [question in the chat]: A partition should not aggregate more data then the TTL policy? 35:25 When does over-partitioning become a problem? 37:35 [follow-up question from previous question in the chat]: If I move from PARTITION BY to ORDER BY, do I need to handle it via mutations instead? 40:10 [question in the chat]: What would symptoms be for select statements with too many partitions? Slower queries? More cpu/memory utilization etc? 41:31 [question in the chat]: what would happen if one inserts data with a timestamp that does not meet the TTL criteria anymore? 44:10 How do you safely change partitioning for an existing table? ==================== RESOURCES ==================== Altinity Knowledge Base for ClickHouse®: https://kb.altinity.com/ Altinity Documentation for ClickHouse®: https://docs.altinity.com Altinity Stable Builds for ClickHouse®: https://altinity.com/altinity-stable/ Training for ClickHouse®: https://altinity.com/clickhouse-train... Altinity vs other ClickHouse® vendors: https://altinity.com/altinity-cloud-v... ==================== COMMUNITY ==================== 🧑🏽‍💻 Join the Community: https://www.altinity.com/slack 🙋🏽 Get Support on Slack: https://www.altinity.com/slack ⭐️ Star on GitHub: https://github.com/Altinity/ 🌐 Connect on LI: / 10955938 🌐 Follow on 𝕏: / altinitydb 🌐 Say Hi on Reddit: / clickhouse #clickhouse #developer #devops #opensource #officehours #databasemanagement