Non-Paged and Reverse CQL Reads

Introduction
Welcome back to How to Write Better Apps Section! Today we’re see how to avoid Non-Paged and Reverse CQL Reads.

In today’s agenda, we’re gonna add a new feature in our project and also going through the two gauges mentioned above.

And in the end, we’re gonna take a look in a real Scylla Monitoring Dashboard with the fixes mentioned here, ok?

Project Overview
Let’s take a look at our achievements so far: our twitter app is running smoothly, the driver configuration is on a good shape and everything is going well.

However, now your boss just ask you to implement a new feature like “Recap”, that brings the first tweets you saw from a specific user… Seems ok to implement, right? He even gave your the query that you need to implement it.

You deploy it, then you out for the weekend and in the morning your boss calls you saying that the whole project just started to slow down…

Now everything is on fire, but you know the reason. Right? If you don’t know, I’ll show you the way.

First Gauge: Reverse CQL Reads
Let’s take a look on the first gauge here:

We can rapidly identify that the Reverse CQL gauges is hitting 50%. This happend because you’re doing a scan in the partition looking for something and ignoring completely the way that the table works.

Originally, the Timeline is clustering everything by the created_at field in descending order.

So… If we DO IT in ascending order, we’re going to go through the whole partition and then start reading the data.

Depending on the size of the partition, you may not want to do that. So, how we solve it? Denormalize your data and create an Materialized View for it.

The rule of thumb to use the ALLOW FILTERING is if you RARELY will use a query with it. In this case, this will be available for the users so probably will be pretty often.

Second Gauge: Non-Paged CQL Reads
Now the last gauge that we need to work on: the Non Paged CQL Reads.

Under the query that we implemented given by your boss, you totally forgot something: the LIMITATION of the retrieved data.

Besides the query being REVERSED (which is pretty bad not gonna lie), we’re getting all the data from the partition which makes things even worse.

You can also have the same behavior by adding this on the PreparedStatement query builder.

And to avoid it is just making sure to take a look on the Scylla Monitoring after a new deploy and if something is wrong, go through the code and clean it.

Solving in the real world
Back to the graph dashboard, we can see that the gauges are at 50%, as expected based on the code that we just implemented. We are running two select queries, and one of them has the limitation, but the other one, the new query that we just created, does not.

We are using reverse SQL reads and allowing filtering. Now, we are going to stop this and see the new implementation, which involves setting the page size for both queries and using a materialized view. This should resolve any issues.

When we refresh, all of these gauges will start to drop immediately. A few seconds later, specifically 10 seconds later, all the gauges are at 0%, indicating that our application is healthy in terms of CQL performance.

Okay, I hope you enjoyed this lesson. See you next time!