Events just got faster: migrating to ClickHouse

TL;DR

• What's new? Talo's Events service now uses ClickHouse for better performance and scalability.
• What's changed? We've migrated the Events service to ClickHouse to provide faster access to your event data and improve the performance and scalability of the service.

Talo + ClickHouse = 🚄

Sadly, there's no emoji that can accurately convey the speed and scalability improvements we've made to the Events service. We've updated the service to use ClickHouse, a column-oriented database management system that's designed for real-time analytics.

This migration will provide better performance and scalability for the Events service, ensuring that it can handle the increasing volume of events generated by your games and providing you with quick access to your players' event data.

Taking a step back: what are events?

Events are an easy and declarative way of measuring player behaviour in your game. You can use events to track player actions, such as logging in, completing a level, or purchasing an item. By tracking these events, you can gain insights into how players are interacting with your game and use this data to improve your game's design and player experience.

Talo lets you aggregate events, filter them and drill down into specific events on an individual player level. Events are incredibly powerful for debugging too - you can follow a player's journey through their events to understand potential issues they might be facing.

You can learn more about Talo's Events service here.

Why did we migrate to ClickHouse?

ClickHouse is optimised for running analytical queries on large datasets, making it perfect for the Events service which needs to handle a high volume of events generated by your games. It also provides lightning fast access to your event data, allowing you to quickly query and analyse your players' behaviour.

Previously, we noticed large dips in performance when fetching large sets of events (for example when the event date range was upwards of a year). This impacted both the homepage of the dashboard where we show a summary of events, the Events overview where you can visualise events and the player events page where you can drill down into specific player journeys.

We want Talo to be as scalable as possible and ClickHouse is a key part of that vision. The migration to ClickHouse will ensure that Talo can handle events from games of any size - whether you're building a small indie game or a large-scale multiplayer project.

How did we do it?

To use ClickHouse, we had to drop the relational way of storing events and move to a column-oriented database. This means that we store data in columns rather than rows, which allows for faster read and write operations.

We migrated our existing events table into two tables: one for events and one for event_props (the metadata associated with each event). This separation allows us to store the event data in a more efficient way and makes it easier to query and analyse the data. We don't always need to fetch the props so separating them out allows us to be more efficient with our queries.

We also made sure the new tables were atomic meaning that they didn't rely on external data that exists in the operational database. This ensures that the Events service is resilient to operational database changes and can continue to function independently.

After updating our other services (like the player and dashboard headlines services) to use the new ClickHouse driver, we were ready to start migrating data to the new ClickHouse database.

To do this, we ran SQL queries to pull out the existing data into the new format as CSVs (one for events and one for event_props) and inserted that directly into the ClickHouse database using the CSVFormat. Whether you have JSON to insert, a CSV file or even an SQL query, ClickHouse is incredibly flexible when it comes to inserting data.

Talo is completely open-source, so you can check out the migration PR here if you're interested.

Leaderboard and resilience updates

We've also been hard at work making improvements to our Godot plugin and Unity package. We recently introduced leaderboard entry caches to reduce the number of requests made to the Talo API when fetching leaderboard entries. This should make it easier to build leaderboards and improve the performance and responsiveness of leaderboards in your game.

You can check out the docs for the Godot plugin entry cache here and the Unity package entry cache here.

Our focus is currently on making Talo as resilient as possible. To do this, we're looking into adding more local caching functionality for handling network loss/drop-out issues. For example, if Talo is down for maintenance in the future, or a player's network temporarily drops out, we want to ensure that your game can continue to function as expected and that any changes made are reflected in Talo after a connection is re-established.

Join the community

Looking to use Talo? You can find the Talo community on Discord. Come for a chat, ask questions or share your feedback with us. We'd love to hear from you!