XO Ruby Atlanta 2025, From Expensive Queries to Smart Caching: A DBA’s Guide for Rails by Javier Zon

What is discussed, and when

1. 0:00 – 5:00: ClickFunnels, Jay Thomas, New Relic
2. 10:00 – 15:00: ClickFunnels
3. 15:00 – 20:00: New Relic
4. 20:00 – 24:22: Bullet Train

Notable quotes from "XO Ruby Atlanta 2025, From Expensive Queries to Smart Caching: A DBA’s Guide for Rails by Javier Zon"

• Right? Like why am I here? if you need to find, I'm the head of platform of Clickfunnels. I heard that somebody was with us and they left us that she has come back. We have a…

  about ClickFunnels · at 0:46

• Want to find me, I left Facebook and Instagram long time ago, but I'm still with Jay Thomas. and you can find me on LinkedIn. and this is me winning a Georgia championship…

  about Jay Thomas · at 2:16

• How I see on my phone because I was literally getting ready to tondo and when I see this on New Relic, it's oh crap, this is this will be an issue, right? So, Oops, there we…

  about New Relic · at 4:53

• Yes and you can see what is the query in this case select teams we use bullet train who use bullet train around that there we go so we use bullet train is our president on click funnels and we tend to help a lot on application.

  about Bullet Train · at 22:20

Who and what is mentioned in this episode?

People

• Jay Thomas — Person · 1 mention · first at 2:20

Companies & orgs

• ClickFunnels — Company · 3 mentions · first at 0:50
• ReadySet — Company · 3 mentions
• New Relic — Company · 2 mentions · first at 4:57
• Scalb — Company · 2 mentions
• Bullet Train — Company · 1 mention · first at 22:24
• Azure — Company · 1 mention

Products & tech

• ProxySQL — Product · 3 mentions

Concepts & topics

• MySQL — Concept · 6 mentions

Full transcript with timestamps

0:20 Hey guys. Um, first of all, I need to say like this is my first uh speaking in English. if I switch into Spanish or Portuguese, it's like I'm start. Yeah. Um, and another thing is like this is the first time that I speaking not to my SQL DBAs. So, if you heard that something is like you're going too deep, just raise your hand and just let me know. Um, before going further, uh, who who am I, right? Like why why am I here? Uh, if you need to find, I'm the head of platform of Clickfunnels. I heard that somebody was was with us and they left us that she has come back. We have a really nice platform. Um, if you don't know, we basically help entrepreneurs to get into online. Uh, basically we have 600 of thousand of domains, custom domains that they run on our platform.

1:06 Uh, at some point in time, we were like the fifth platform running all these funnels or uh what you can probably see as a landing pages. Um, I'm founder of Scalb. Uh, this is my logo uh just in case. Uh basically what we do is helping uh companies with my SQL on their stack to not spend a leg on my SQL. Uh we focus on AWS but uh tends to be uh into GCP or Azure as well. Um and as well I'm Taekwondo black belt. Uh sometimes when you cannot break something on the code you just break a leg or just break a wall. So that helps a ton. Uh and besides that uh my day is kind of like 34 hours a day. So uh try to fit in into some of the hobby outside laptops and problems. I'm on the upside as well on on Clickfunnels and it's like you know like not feature wise but it's always u you know like incidents related. So, um, focus on something else rather than, um, all it. Um, and yes, if you can if you want to find me, I left Facebook and

2:18 Instagram long time ago, but I'm still with Jay Thomas. Um, and you can find me on LinkedIn. Um, and this is me winning a Georgia championship uh, right in Dallas. So, just just in case if someone wants to try uh, taekwondo, I'm around there. Um so before talking about the problems I want to know you guys like I honestly I think I know a few people here but who runs rub and rails in production. Okay good. Uh who runs my SQL with the ruby rails? Good. Uh the others are running possess or SQL light with rails 8. Okay cool. Awesome. So um what I will talk about it's applies to I'm coming from the MySQL world but applies as well to possess. uh I'm speaking about tooling that you can put between rails and your database. I will speak about tono about my SQL but just try to think about uh it's a dolphin or an elephant is still an animal so uh you can put somebody in front of it. Um one of the problem that I face uh as on a

3:21 scaleb and as click finance as well we solved that long time ago when I get in but um one of the issues that we see is active record connection pooling um every time that you need to scale I don't know who's running Amazon Heroku uh anything that runs over there every time that you have a really nice spike on your application which everybody wants to um that's open a ton of connection to the database um basically The way that we define is I don't I don't have anything here but the the way that we define I remember Alex showing you just have your data uh database yl file that you just put connection pool five 10 20 uh which it's nice when you have one dynino on one pod on kubernetes but the moment that you need to scale up uh puma puma dev uh puma processes or psychic or whatever that goes pretty fast pretty pretty easy um and basically what I want to show you This is August 4. I know that you won't

4:19 be able to see it. This is August 4, 5:00 p.m. Eastern time, my time. I was literally planning to go to Tekwondo as normal every single day, and this spike happened. Um, you don't need to know what happened, but as you can see, normally we are run around 7,000 connections to the database. Uh, at some point in time at 5:00 p.m., this is back to 38,000. Um, I don't know who can scale that up that fast. If you know how to do it, just let me know. Um, uh, this is what you see. This is how how I see on my phone because I was literally getting ready to tondo and when I see this on New Relic, it's like, oh crap, this is this will be an issue, right? So, um, Oops, there we go. Um, I just zoom in. You can see we have a pretty rough time from 5 to 6 p.m. Um, who can just think what happened during that time?

5:19 Alex is not here. Yes. Um no this uh sadly this could happen every time uh at any moment. So we are not always thinking uh who else

5:36 that could be good. Um our yes go well end of the day in in Eastern time but Pacific could be anywhere right. So uh what happening here is like someone sent an email uh a viral email that everybody wants to use it. Uh so it's like we cannot blame our customers. They were doing their business. They hit 5 million emails going out. Uh they were viral. People was buying the stuff. They made like $8 million in 55 minutes. Uh this is the time that they were doing $50 million, right? Or whatever. It was like seven figures. Um so that could happen. that's we we need to support them, right? Um keep that in mind. I will explain you uh how we solve those stuff. Um this is another problem that I found and and it's basically object reation uh mapping magic uh on the on on my side of the of the of the world on the database side on CIS admin world. Um talking about OMS for you guys are amazing. We hate it. Um if you want to

6:41 put it into a pressure a database uh engineer or or a CIS admin talk about like how our MS are amazing and we we don't understand it. Um this is this is copy from one of our platforms from our customers. Um I don't know how scopes works and even worse I don't know how unscoped works. So um this is just something for you guys like ego is loving it's like oh I can read that that's easy that's just a probably a report from purchases right for us as a DBA this is way more better like I hate to see just concatenating stuff dots dot dot dot just this is like oh I know this is four tables we are joining hopefully we have an index over there they are all primary keys hopefully is something similar um but you remember this is something like uh you don't generate eight right this is active record so doing some magic behind him uh and getting this into the database so to understand what's going on as well

7:42 that's a big problem that we try to solve um let's talk about another problem uh who loves callbacks thank you eager um specifically for cash invalidation um our I don't know remember if I left this but oh I think I think I took it because was like too big um but something that someone said over there and probably you already heard u um stuff in computer science naming things and caching validation is like the worst case scenario that you need to work. Um so that's another problem that we try to solve. I was I thought that I have my grape over there, but it was like 260ish lines on a pretty big repo uh just for cash. Every time that you do something after after commit touch this thing just to break the cache uh which is which is pretty hard. So I will try to introduce some of the solutions that scalb applies to the customers. Uh this is not the only solution. Uh this is the solution that I know. If you know if you want to

8:45 know more just Five me later. Um, active record connection pooling, you need to be a smart connection pooling. Um, basically this is just a term that I came up. Um, but active record is nice until you need to scale up. So, let's try to keep that in mind. um option relation and mashics uh from the DBI side or from the observability side try to be more visual into queries and then caching validations try to be as smart as smart as you can to keep the cache the more more fresh as you can right um talking about that is like oh yeah sure easy um I don't think that you can do that on rails today so what I'm introducing is a few toolings outside the Rails board that you can start using today. Um, one of those is proxy SQL for connection pooling. Uh, what proxy anybody running proxy SQL in pro or staging or anything that's good. Um, you can use it. It's open source is there.

9:48 Uh, came from a guy that basically was um I think was a GitHub engineer and suffering a ton of stuff with uh with the databases. So, he come back on this one. What uh proxy SQL does is it allows you to connect as many connections that you want from your Rails app. Works for anything, right? Like PHP, uh Go anything. But on Rails specifically, uh you can connect as many times that you want, but because it's a reverse proxy, it's it's a transparent proxy, not a real proxy. Um could hold all the front end connections and decide how many connection goes to the back end. Um on the other side because holds the connection and knows how to re how to reply you can connect to my SQL you can connect to posgress on new versions on proxy SQL you can connect to basically uh a few others uh we are using for example for click house so you can even you don't even need to know like the developer itself doesn't need to know if

10:45 that's going to posess or my SQL or is routing to to click house in this case um the other oh this is something like this is the connections that my s uh proxy SQL in this case uh this was took it like yesterday I don't know if you can see but remember the graph at the beginning was around 7,000 this is a normal numbers for us this is 7,000 or 400 and on the other side I don't know I cannot do math uh pretty fast but if you see the connection use and the connection free is how many really connections to my SQL back end is doing uh so imagine Even all those eight almost 8,000 connections are being used for seven eight um that's how much CPU and memory you are saving on the back end right on on the back end when I say back end not rails back end it's on the database back end um on the other side uh proxy SQL against uh the other mashall uh again don't be mad I love active records but sometimes

11:50 it's hard to troubleshoot stuff. Um, proxy SQL sits in the middle of your application and your database. So, could act as a man in the middle. Uh, in fact is my best friend to do some stuff uh pretty hard. I will show you uh some of those stuff. Uh, besides that records um stats for every single query. So, they have a table is my SQL accessible. So you can do select star from this stats table and basically you can see new queries coming up, new feature uh starting up and you can see like okay well this new feature we released it today we have new queries that they need more indexes uh we need to change those queries etc. And uh the power of query, right? Um I know at least on the companies that I work, they hate when I do this. Um how many of you saw like you do select inside of selects inside of selects? Uh for the database that hates that's that's bad. Uh we tend to ask them hey can you do includes and could you do

12:52 joins and sometimes they cannot do it because they need to move on. So with the power of query writes I can just go to proxy SQL and rewrite them. Um it's bad don't do that but uh I don't have that that that photo to say like I'm just saying don't do on production directly but this is you can see the numbers of query rules. Um this took yesterday [snorts] I took it. Uh you can see how many billions of hits has some ruling um um under NDA for everybody. This is uh Clickfunnels production uh on the last few days. Uh we have a ton of access to the database. Um but proxy SQL handles this is a four course EC2 instance um HA with autoscaling group and everything but we have three proxy SQLs. This is how one handles in the last few hours a few days. Um this is a query that's probably what I was saying right like you have like select in select um if you do this I will hate you from now on forever uh don't do that uh uh your your

14:00 ops team your DBA team I will love you if you come with something else but because sometimes you cannot do it uh proxy SQL what allows you is like pointing to that digest which is this query um do a pattern who loves reax Oh, nice. Good. Um, you just, it's literally Google reax. You just, um, define what you want and then you just apply it on the second one. So, this query went from uh, 10 or 15 seconds into milliseconds just because we are using the right approach of accessing the database. Um, using the same the same pattern, you can do uh, basically whatever you want. Of course, with all the power, right? Comes with responsibility. So um you can basically do select one on all the queries and then you will end up having result as one which is bad. So be aware. Um another tip for uh people here in in rub rails uh SQL commener is nice. You just uh I don't need to explain how to install a gem but you just install a

15:05 gem. You just uh initialize this. uh they have many more options but basically on the other side we see this the ops team the observability team the DBA team can see and then can go back and blame to whoever right uh we love to put blame on developers uh but you know like order controllers yes well where who is doing select star from orders where's the workloads where is the filter where is why are you getting all the all the columns so this could give us way more advanced troubleshooting without involving you or your developers just for something that we are seeing on the database. Uh and it's pretty neat.

15:44 We we we have this we don't have this in in production uh but we have this on on staging and releases and all the other uh environments and helps a ton. Uh and another solution which uh it's called ready sets. Who knows about ready sets? Yes, good. That's amazing. Um I invent this num this name and they they love it. Uh to me it's a smart caching. Uh who deals with caching on the app? Good. Who use radius for caching those stuff? Um or memcache or whatever. Um the the difference like this is another player, right? This is another player for for caching. The different on them is they can pre-calculate results. So basically they sit between your app and your database as well. um they consume via um change data capture. So if you're on my SQL you read binary logs or if you're on on postress you you read walls. Uh basically every time that you write on the database ready set sees that and applies into their hash mapping. Um

16:48 imagine that now you are basically are doing kind of like a no SQL with all your big queries just mapping value and key value. Uh so that's why it's like pretty fast. It's pretty fast at the nancond response time. Um people I I have some some graph for you guys but I don't want to spoiler but people thinks that the application breaks when you enable this kind of stuff like disappear like where is my time where is New Relic showing my SQL disappear from from their um from the graph which is which is pretty nice. Um, and I already said that, but the good thing is they keep seeing all the rights that you do on the database. So you don't need to worry about when invalidate the cache. Like if you touch a user that touch a user that's means that translates into a right into the database. That database will generate a binary log will be binary log or a wall. But at some point raise get this. Um we have an alert on

17:47 on lagging on ready set just in case because you can at some point uh when you work on primary and readers you can have some lag like 20 25 millconds but if your application supports that uh it's amazing and instead on top of that they have a failover mechanism. So so if you ask for a cache to raise it and they don't have the data they know how to go back and get the information set it that and and retrieve it. Um, so let uh oops click click not moving over. Hold on. Okay, there we go. Um, this is what smart caching with ready sets looks. Um you can see I didn't I just cut the queries just to to show everybody but you can see uh probably I don't know if you see the time but you can see when we apply some changes on ready sets uh this was Alex left but as you can see as well I don't know if you are familiar with um performance insight on on Amazon this is the kind of the same graph that Alex was

18:57 showing today uh 32 cores on this machine um we almost near and 25 cores using being used. It's fine. You don't need to worry. Uh the issue is when when you have these spikes on people sending emails and and buying. But you can see like the the worst case scenario for our queries. We will literally put into cache. Um no call change on the on the application side. Um we keep ready set and proxy SQL configuration into our GitHubs. But it's basically no application changes. um on that process I I think hopefully you are thinking about now I don't need to pay for 32 cores anymore um and and in fact we we don't pay anymore for 32 cores um this is another case uh but this is on the other side on the SQL handle side um I don't know how postres work but on my SQL if you hammer a table like literally punch punch punch on writes and reads and write and reads um the table get hot right so um basically what you can do is

19:57 offload that that um if you you can split writes and reads for example from primary and and and readers but as well you can put ready sets uh the good thing on on using ready sets in the middle uh and again I'm I'm not sponsoring by them so you can just go and check with them but um the the good thing that you can do is like the the cost of ready sets is way cheaper than Aurora or my SQL running so um on this case I don't remember which query it was but I think was some related to users and uh um login activity and and usage of the API. Uh we basically off offload the right to the primary as as it should and then all the readers went to to to raise it and you can see as well now we went from all those 32 cores that we were having now bottom down to almost two.

20:49 Keep thinking on the cost as well, right? Of that. Um this is another case. Uh you can see it's pretty easy to see when we apply stuff like around 10 p.m. um uh sorry 8:00 p.m. uh Eastern time. Uh it's when I'm almost done with the day and then I can do some changes. So this is another case. Uh this is all uh enabling queries into resets. Uh this is raisets. Uh there we go. Um this is the amount. Yeah. Ready. Ah. Um this is the amount of queries per second that we hit on a single instance on ready set. Uh you can see the spike on 30,000 requests per second. Um this is just one query could be mostly mostly all of this is uh related to uh knowing who you are. Uh uh privileges uh kind of like what device that in the middle of like or can can we have all those stuff in the middle. So it's literally knowing who you are and and telling you what you should do. Uh most of the time on this one as well

21:54 could be psychic running some some jobs. So uh we scale up to almost 3,000 pots running psychics. So uh we we have this this amount of requests per second. Um and this is I don't know if you can see but this is how much or how fast they reply. Um I don't know if you can see but it's microsconds. Um it's it's pretty damn good. Um yes and you can see what is the query in this case select teams uh we use bullet bullet train who use bullet train around that uh there we go um so we use bullet train is our president on click funnels um and and we tend to help a lot on on application that runs bullet train uh but this is this is amazing [clears throat] like if you even try to do this on my SQL with one single request uh on one single record probably it's not even replying that fast uh and because Basically, my SQL needs to parse your your your SQL specifically, find that data into memory or whatever. Um, Razet doesn't do that.

22:58 That's why it's it's not magic, right? Like it's just doing key value stores and and it's pretty fast. Um, just uh talking about me a little bit because I talk about too much on on Razet. Uh, I create a demo. I'm not doing a demo here, but I create a demo. It's pretty basic. It's a Docker compos with proxy SQL. uh my SQL and um an array set instance. You can just double check um it's a public repo and I think has like three commits. So if you want to uh put more information there, create issues, I will try to help you on there.

23:34 Um I love to share this kind of stuff because I know that people doesn't doesn't like too much databases. So if you have questions, create issues there. I tried to even um vibe coding how to use GitHub actions just to test this stuff and was pretty nice. So it's over there as well. Um and I think before questions oh this is me if you want to check uh I love diving as well as eager. Um so if you want to dive here in Georgia probably it's hard but uh let us know as well and yep questions if you're afraid to ask you can just find me on LinkedIn and you can ask.

24:10 Thank you.

Questions about this episode

What is discussed in "XO Ruby Atlanta 2025, From Expensive Queries to Smart Caching: A DBA’s Guide for Rails by Javier Zon"?

In order: 0:00 ClickFunnels, Jay Thomas, New Relic; 10:00 ClickFunnels; 15:00 New Relic; 20:00 Bullet Train.

What is mentioned in this Confreaks episode?

9 people, works and ideas are mentioned, including MySQL, ClickFunnels, ProxySQL, ReadySet, New Relic, Scalb. Each one on this page links to its own page with every episode that mentions it.

How long is "XO Ruby Atlanta 2025, From Expensive Queries to Smart Caching: A DBA’s Guide for Rails by Javier Zon"?

The episode runs 24 min; the transcript is about 4,197 words in 26 timestamped sections.

More from Confreaks

• All analyzed episodes
• Books mentioned on Confreaks
• People mentioned on Confreaks
• Topics mentioned on Confreaks