Hi there!
Welcome to our September newsletter! We're excited to share our latest updates, insights, and ClickHouse community highlights.
This month, we're diving into some powerful ClickHouse features, introducing new security measures, and a ton of improvements to the platform.
Query of the month: defensive date parsing
Parsing dates is a tricky business. Dates often come in various formats from different apps and even from user input. It's like the wild west out there.
The ClickHouse function parseDateTimeBestEffort does the trick. It will parse any date in almost any format that you throw at it.
Here are some examples:
-- ISO 8601 format:
SELECT parseDateTimeBestEffortOrNull('2024-08-12T17:36:00Z') AS parsed_date;
-- RFC 1123 format:
SELECT parseDateTimeBestEffortOrNull('Sat, 18 Aug 2024 07:22:16 GMT') AS parsed_date;
-- Unix timestamp:
SELECT parseDateTimeBestEffortOrNull('1284101485') AS parsed_date;Essentially, you can throw anything at it, and if it is parseable as a date in any way, shape, or form, parseDateTimeBestEffort will get the job done.
You can learn more in our recent blog post: Parsing dates in ClickHouse.
What’s New
This month, the emphasis was again on improving Propel. We've made amazing improvements to the platform at all levels.
Here are the highlights of what’s new 👇
- Multi-factor authentication
- Coming soon: ClickHouse Interface
- 25 improvements
- 7 bug fixes
🔐 MFA
We've introduced Multi-Factor Authentication (MFA) to enhance the security of your Propel account. This feature adds an extra layer of protection beyond your password, significantly reducing the risk of unauthorized access. We strongly recommend enabling MFA for all users to keep your valuable data safe and secure.
👀 Coming soon: ClickHouse Interface
With the ClickHouse interface, Propel will be like any other ClickHouse, just completely serverless and instantly auto-scalable.
Want to kick the tires? Reply to this email.
From Propel
ClickHouse performance optimizations explained
In this post, we explain the key strategies for optimizing ClickHouse performance to ensure your data applications deliver fast and reliable analytics. ClickHouse's speed is rooted in its columnar storage, which efficiently compresses data and speeds up queries; its full-fledged DBMS features, which offer advanced query optimization; and its distributed architecture for horizontal scaling, enabling parallel query execution and high availability.
To optimize performance, consider the following strategies: design your database schema with appropriate data types and compression codecs; optimize queries using primary keys, data sorting, materialized views, and projections; and tune queries for efficient joins and filtering.
For self-managed deployments, implement sharding and replication to enhance scalability and availability. Leverage tiered storage with cache, SSD, and object storage layers to balance performance and cost-effectiveness.
Read the full post.
Other posts worth reading:
From the community
Alert Evaluations: Incremental Merges in ClickHouse
By Zane Mayberry @ Highlight
In this post, the team at Highlight.io explains how they optimized their alert evaluation system using ClickHouse’s powerful -State and -Merge Aggregate function combinators. They faced significant performance challenges when calculating complex aggregates over large time windows—like triggering alerts when the 99th percentile of network request durations exceeded one second in the past hour. Recomputing these metrics every minute was both slow and resource-intensive.
By leveraging ClickHouse’s ability to compute and store intermediate aggregate states with functions like quantileState and then merging them using quantileMerge, they efficiently calculated incremental results without repeatedly scanning the entire dataset. This approach not only achieved a 10x speedup in performance but also drastically reduced memory usage from 7.6 GB to 82 MB. Their experience highlights the effectiveness of ClickHouse’s advanced aggregation functions in handling real-time analytics over large datasets.
Read the full post.
Other posts worth mentioning:
- How we built our Internal Data Warehouse at ClickHouse: A year later
- I spent 5 hours learning how ClickHouse built their internal data warehouse.
How to get started with Propel for free?
Start building today with a generous free tier that gets you 10GB of storage and 50,000 queries per month 👩🏽💻 🏗️ 🚀!
Claim your $15 of free monthly usage forever.
Thank you for reading!
Nico Acosta
Co-founder & CEO
Propel
If you find these product updates useful, please forward them to a colleague who can subscribe here. Alternatively, you can subscribe to our podcast, Data Chaos, on Spotify or Apple Podcasts.
