Hi there!

At Propel, we're on a mission to be the easiest way to run ClickHouse at any scale.

Today, one thing is clear: ClickHouse is taking off. Developers love it for its speed and the control it offers in making performance/cost tradeoffs with minimal complexity.

Looking at GitHub stars—a somewhat vanity metric but still indicative of developer interest—we can see ClickHouse's growth. It's outpacing other analytical databases, surpassing popular DBs like MongoDB, and even catching up with ubiquitous technologies like Apache Spark.

What’s New

We have some awesome updates this month.

Here are the highlights of what’s new 👇

• Geospatial functions
• Coming soon: ClickHouse Interface
• 5 bug fixes
• 4 improvements

🌍 Geospatial functions are now available

We've expanded our ClickHouse compatibility to include geospatial functions. This powerful addition allows you to perform complex spatial analysis and queries on your geographical data. Key features include:

• Point, LineString, and Polygon data type support
• Distance calculations between geographic points
• Spatial indexing for optimized performance
• Geofencing and point-in-polygon queries

These functions open up new possibilities for location-based analytics and insights. Whether you're working with GPS data, mapping applications, or location-based services, our geospatial functions provide the tools you need to extract valuable information from your spatial data.

Learn more about Geo functions

👀 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.

Query of the month: filtering with array values

Storing data in JSON arrays is super useful when you have user-provided values (like tags) or when you want to keep things simple and avoid a denormalization transformation.

The challenge arises when you need to filter your query by a value in the array. In the example below, imagine you have a JSON column toppings with values ["guacamole", "jalapenos", "cheese", "onions"] and you need to retrieve all rows that contain "jalapenos" in the toppings.

This query does the trick:

SELECT *
FROM taco_orders
WHERE has(JSONExtractArrayRaw(assumeNotNull(toppings)), '"jalapenos"')

In this query:

• JSONExtractArrayRaw extracts an array of JSON values from the toppings column.
• assumeNotNull ensures that the toppings column is treated as non-nullable.
• The has function checks if the array contains the specified value, which in this case is a double-quoted ID.

From Propel

• Building a real-time dashboard with Redpanda, Propel’s Serverless ClickHouse, and Next.js (Redpanda blog post)
• Essential string functions in ClickHouse (Blog post)
• Joins in ClickHouse: A developer's guide (Blog post)
• How to choose a primary key in ClickHouse (Blog post)
• Installing and running ClickHouse with Docker (Blog post)
• ClickHouse WHERE clause with array values (Blog post)

From the community

• Using ClickHouse to scale an events engine - Amazing to see how Lago built their billing platform on ClickHouse
• ClickHouse over ElasticSearch for Observability - ClickHouse is becoming the choice for Observability workloads as well.
• Learning from Rockset - Interesting post by Altinity on why to bet on an open source database like ClickHouse.
• ClickHouse function availability across versions - quick and dirty web site visualizing the ClickHouse function availability across versions.
• Cost effective logging platform with ClickHouse for petabyte scale - They handle 50TB of data per day 😳. The cost-effectiveness of ClickHouse is crazy.

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 $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.

‍