WEBVTT 1 00:00:04.559 --> 00:00:08.919 Hey, folks, welcome back to another episode of Javis JavaScript Chamber. 2 00:00:09.000 --> 00:00:15.679 I am let me try that again. I am so sorry, 3 00:00:17.399 --> 00:00:22.280 I am so I don't know anyway, Hey folks, welcome 4 00:00:22.320 --> 00:00:26.359 back to another episode of the Ruby Rogues podcast. This week, 5 00:00:26.399 --> 00:00:28.679 on our panel, we have a Yoshinatya. 6 00:00:29.280 --> 00:00:29.559 Hello. 7 00:00:29.600 --> 00:00:34.560 Hello, I'm Charles Maxwood from top Endevs Ruby Geniuses dot Com. 8 00:00:34.719 --> 00:00:38.439 We have a special guest this week that is Steven Margheim. Stephen, 9 00:00:39.000 --> 00:00:39.799 welcome back. 10 00:00:41.039 --> 00:00:42.560 Yes, thank you for having me again. 11 00:00:43.799 --> 00:00:46.600 Yeah, we had you on I need to go look 12 00:00:46.679 --> 00:00:50.000 exactly when, but we had John. We talked about sequel 13 00:00:50.039 --> 00:00:51.880 Light last time, and I think it was mostly a 14 00:00:52.240 --> 00:00:54.200 can you really do all this stuff in sequel Light? 15 00:00:54.240 --> 00:01:00.079 And the answer was pretty much yeah. So yeah. Is 16 00:01:00.079 --> 00:01:02.039 there anything else that you want to bring up as 17 00:01:02.039 --> 00:01:06.359 far as an introduction before we dive in and start 18 00:01:06.400 --> 00:01:09.120 talking about more even more sequel Light awesomeness. 19 00:01:11.280 --> 00:01:15.680 No, not much. I'm happy to be back here, just 20 00:01:15.879 --> 00:01:20.640 braving the cold dark Berlin Winters. So happy to have 21 00:01:21.280 --> 00:01:25.400 the opportunity to chat with some other Ruby and Rails 22 00:01:26.040 --> 00:01:30.640 enthusiasts and hopefully maybe by the end of this talk 23 00:01:30.920 --> 00:01:36.079 convert you into Sequelight enthusiasts as well. Let's see awesome. 24 00:01:36.680 --> 00:01:43.120 So yeah, let me get the link into the comments 25 00:01:43.200 --> 00:01:47.079 for the videos. But yeah, we talked quite a bit 26 00:01:47.079 --> 00:01:51.400 about sequel Light last time. Are there any new developments 27 00:01:51.400 --> 00:01:53.840 as things go along with sequel Light or. 28 00:01:55.560 --> 00:02:00.280 Yeah, so there's there's been a one major development. So 29 00:02:01.040 --> 00:02:05.280 if I were to summarize the conversation last time, it 30 00:02:05.319 --> 00:02:09.680 would be can we really do this with SQLite in 31 00:02:09.840 --> 00:02:12.759 a web application built with Rails, And the answer is yes, 32 00:02:13.280 --> 00:02:18.120 but you need to finesse it. And the big change 33 00:02:18.360 --> 00:02:24.199 which is really exciting is that the release of Rails 34 00:02:24.280 --> 00:02:31.400 eight marks the first time that Rails and as far 35 00:02:31.439 --> 00:02:35.919 as I'm aware, any major web application framework has provided 36 00:02:36.680 --> 00:02:43.280 a fully production ready experience for a server driven web 37 00:02:43.319 --> 00:02:48.520 application backed entirely by seqlight. You can drive your cash 38 00:02:48.520 --> 00:02:53.960 through sqlight, your background jobs through sqlit, your web socket 39 00:02:54.280 --> 00:02:59.319 messages obviously your primary data, and you don't have to 40 00:02:59.639 --> 00:03:03.879 fiddle with any configuration. You don't have to fut's about 41 00:03:04.319 --> 00:03:11.280 with any setup or installation. You get a no compromises, 42 00:03:11.680 --> 00:03:17.560 high performance but incredibly lean, mean and stable application. When 43 00:03:17.639 --> 00:03:20.120 you run Rails new like you literally can run Rails 44 00:03:20.159 --> 00:03:23.400 new and come all deploy and you won't have any 45 00:03:23.400 --> 00:03:25.719 business logic, so it might not be very useful. But 46 00:03:26.120 --> 00:03:29.599 the bones of that application are as sturdy and as 47 00:03:29.639 --> 00:03:33.560 simple as they have ever been in the two decade 48 00:03:33.639 --> 00:03:34.400 history of RAILS. 49 00:03:35.960 --> 00:03:38.439 That's awesome. I have to admit that a lot of 50 00:03:38.439 --> 00:03:42.080 the stuff that's in Rails eight that just kind of 51 00:03:42.120 --> 00:03:48.479 comes baked in. I am so excited about. On Friday, 52 00:03:49.960 --> 00:03:53.759 I was how do I put it? I did some 53 00:03:53.879 --> 00:03:58.520 therapeutic coding and anger, and I thought, Okay, I'm just 54 00:03:58.560 --> 00:04:01.439 going to pull together this apple. I mean, admittedly I 55 00:04:01.479 --> 00:04:07.120 use postcris QOL, but I literally, yeah, I did the 56 00:04:07.159 --> 00:04:11.319 exact steps you were talking about. Other than that, and yeah, 57 00:04:11.319 --> 00:04:17.639 I had to deploy an accessory on Camal for the database, 58 00:04:18.839 --> 00:04:21.160 but then I only had one other accessory to deploy, 59 00:04:21.199 --> 00:04:22.959 and that was my Rails app. I mean, that was it, 60 00:04:23.639 --> 00:04:27.519 and it's it's so refreshing to be able to do that. 61 00:04:28.040 --> 00:04:29.680 And then I was looking at search and I was thinking, 62 00:04:29.759 --> 00:04:31.319 so I have to do elastic search, and it turns 63 00:04:31.360 --> 00:04:33.240 out that I am figuring out how to do the 64 00:04:33.319 --> 00:04:37.199 vector search in postgress. So even that's awesome. And I 65 00:04:37.199 --> 00:04:40.439 think there's a vector extension for sqlight isn't there. So 66 00:04:40.680 --> 00:04:43.879 there is I mean, it's it's I mean, this is 67 00:04:43.920 --> 00:04:46.959 the beautiful thing, right and so if you want to 68 00:04:47.000 --> 00:04:50.120 deploy it and make it run and all of that stuff, 69 00:04:50.160 --> 00:04:53.319 you definitely can. I have a question just to throw 70 00:04:53.360 --> 00:04:54.920 that out there. You said you can just do the 71 00:04:55.000 --> 00:04:59.879 camal deploy. Do you set up an accessory for sql 72 00:04:59.879 --> 00:05:02.079 I or do you just store it on a volume 73 00:05:02.720 --> 00:05:05.680 so that when you kill and restart your dock or 74 00:05:05.680 --> 00:05:07.560 it just connects to the same sequel light file. 75 00:05:08.639 --> 00:05:09.319 The latter. 76 00:05:11.639 --> 00:05:19.199 That's awesome, so then you only need one container, Yes, exactly. 77 00:05:18.319 --> 00:05:22.680 That's the cleost thought about sequel lighter thing is it's 78 00:05:22.720 --> 00:05:25.800 literally just a file. You're literally reading a file. I 79 00:05:25.839 --> 00:05:26.160 love that. 80 00:05:27.279 --> 00:05:27.519 Yep. 81 00:05:29.480 --> 00:05:33.279 Just a quick question about like the changes from RAEL 82 00:05:33.480 --> 00:05:37.000 seven to eight which kind of made a sequel light 83 00:05:37.000 --> 00:05:39.199 production ready. One thing that I know is something called 84 00:05:39.199 --> 00:05:42.000 a busy handler that was I think baked into the 85 00:05:42.040 --> 00:05:44.519 connection now and there's a couple other bits above that 86 00:05:44.519 --> 00:05:46.560 I remember reading a long blog push from music. Could 87 00:05:46.560 --> 00:05:50.040 you just summarize what are the changes from sys RAEL 88 00:05:50.120 --> 00:05:53.360 seven two to eight that now make sequel light production 89 00:05:53.439 --> 00:05:54.839 ready That wasn't the case earlier. 90 00:05:55.519 --> 00:06:02.680 Yeah, so there are two foundational changes that take rails 91 00:06:02.800 --> 00:06:06.839 from being You can sort of use it, but you 92 00:06:06.839 --> 00:06:09.279 need to work around some pain points to it works 93 00:06:09.279 --> 00:06:12.800 out of the box. So the first one concerns how 94 00:06:14.120 --> 00:06:20.920 sequalites transaction management system works in the context of a 95 00:06:21.079 --> 00:06:25.480 multi threaded web application. So I'm going to try to 96 00:06:25.519 --> 00:06:31.040 do this as succinctly as possible. But the default way 97 00:06:31.480 --> 00:06:37.959 that seqalite works with transactions, it calls working with deferred transactions. 98 00:06:38.000 --> 00:06:42.360 So to give one piece of context, seqalite only allows 99 00:06:42.560 --> 00:06:47.000 one writer to be doing a right operation at a time, 100 00:06:47.040 --> 00:06:50.279 so it has linear rights. Now, you can open up 101 00:06:50.360 --> 00:06:54.600 multiple connections to the database, and you can have connections 102 00:06:54.680 --> 00:06:58.319 reading in parallel, like that's totally fine. You can have 103 00:06:58.360 --> 00:07:00.959 five connections and they're all doing red and they can 104 00:07:01.079 --> 00:07:03.759 just do that at the same time. But if you 105 00:07:03.800 --> 00:07:06.279 have five connections open and they all try to write, 106 00:07:06.879 --> 00:07:09.240 have to line up and go one at a time. 107 00:07:10.800 --> 00:07:19.680 And when you use a transaction, the default behavior so 108 00:07:19.720 --> 00:07:25.040 if you just write begin transaction, some seql commands and 109 00:07:25.079 --> 00:07:31.560 then commit seql light doesn't attempt to acquire the right lock. 110 00:07:31.680 --> 00:07:35.079 This is the way in which it figures out which 111 00:07:35.120 --> 00:07:37.160 connection is able to write, like there's a lock, and 112 00:07:37.560 --> 00:07:40.600 each connection has to pass it around, so it doesn't 113 00:07:40.600 --> 00:07:43.480 attempt to acquire the right lock until it sees a 114 00:07:43.920 --> 00:07:47.959 right operation inside of your transaction. So if you do 115 00:07:48.240 --> 00:07:53.959 begin transactions select star, select star, update set from select, 116 00:07:54.920 --> 00:07:58.199 at that third operation, it will say, okay, let me 117 00:07:58.279 --> 00:08:03.480 try to acquire the right lock. And the problem that 118 00:08:03.519 --> 00:08:06.199 you hit and this is where anybody who tried to 119 00:08:06.319 --> 00:08:10.879 use seqalite in a Rail's application before Rails eight saw 120 00:08:10.959 --> 00:08:14.879 this error in their logs database is locked. You know, 121 00:08:14.959 --> 00:08:18.519 you got that error all the time, and that error 122 00:08:19.120 --> 00:08:25.560 was happening so often because when you begin the transaction, 123 00:08:26.399 --> 00:08:30.000 that is when seqalite takes a snapshot of your database 124 00:08:30.279 --> 00:08:34.120 to use for that transaction right to keep the isolation guaranteed. 125 00:08:35.480 --> 00:08:39.159 And so when you have a right operation inside of 126 00:08:39.200 --> 00:08:44.320 that transaction in order to ensure that isolation guarantee right 127 00:08:44.399 --> 00:08:49.240 for our acidic guarantees of this relational database, Sequalite does 128 00:08:49.279 --> 00:08:56.960 not allow that attempt to acquire the right lock to retry. 129 00:08:57.600 --> 00:09:00.000 So you get one shot. If the right block is 130 00:09:00.120 --> 00:09:04.639 being held by another connection, you immediately error. And so 131 00:09:04.919 --> 00:09:08.320 when you have a multi threaded web and you have 132 00:09:08.440 --> 00:09:12.919 many rights, there's no retry mechanism. It's like you either 133 00:09:13.320 --> 00:09:16.200 are able it's the lock is free and you can 134 00:09:16.240 --> 00:09:20.080 grab it, or it isn't and you error. And so 135 00:09:20.360 --> 00:09:24.679 the first change was to ensure that when RAILS generates 136 00:09:24.799 --> 00:09:31.480 transactions forceqlight, it explicitly makes them what seqlight calls immediate transactions. 137 00:09:31.480 --> 00:09:34.519 And all that that means is that you attempt to 138 00:09:34.639 --> 00:09:38.080 acquire the right lock at the begin transaction statement, not 139 00:09:38.200 --> 00:09:41.279 at the update statement. And the begin transaction statement is 140 00:09:41.320 --> 00:09:45.000 when you capture the snap shot, and so if the 141 00:09:45.080 --> 00:09:49.840 lock is being held, then we retry the begin transaction. 142 00:09:50.320 --> 00:09:52.679 So we retry taking a snap shot, and it's not 143 00:09:52.840 --> 00:09:55.639 until we can acquire the right lock for that particular 144 00:09:55.679 --> 00:09:58.840 connection that we begin the transaction. And then we have 145 00:09:58.879 --> 00:10:01.480 the right lock for the entire of the transaction, everything 146 00:10:01.519 --> 00:10:04.799 will be fine and we pass it along. So that 147 00:10:05.360 --> 00:10:09.279 immediately solved the major problem of just like getting a 148 00:10:09.320 --> 00:10:14.879 ton of databases locked aerrors by allowing our connection pool 149 00:10:15.200 --> 00:10:21.799 to deal with transactions. The second problem crept up thereafter, 150 00:10:22.440 --> 00:10:27.240 which is okay, now when you are retrying, so retries 151 00:10:27.360 --> 00:10:31.039 are happening at the sequlite level, and they're happening often 152 00:10:31.159 --> 00:10:34.080 in our multi threaded rails application with decent right tripe 153 00:10:34.240 --> 00:10:39.399 right through. But how does seqalite retry stuff? And it 154 00:10:39.519 --> 00:10:44.879 uses a very simple mechanism. Connection A has the right 155 00:10:44.919 --> 00:10:49.159 lock connection B attempts to acquire it can't seql. It says, okay, 156 00:10:49.480 --> 00:10:53.919 just sleep for one millisecond and then try again. So 157 00:10:53.960 --> 00:10:56.879 it sleeps one millisecond, tries again. It's still being held 158 00:10:57.039 --> 00:10:59.960 by A. So B is told sleep for two millisec 159 00:11:00.279 --> 00:11:02.919 and try again. And it has a kind of polynomial 160 00:11:03.120 --> 00:11:03.639 back off. 161 00:11:04.480 --> 00:11:08.960 It sounds like my kid's dad, Yeah, Dad, Dad. 162 00:11:09.840 --> 00:11:11.720 Are we there yet? Are we there yet? Are we 163 00:11:11.799 --> 00:11:12.279 there yet? 164 00:11:12.360 --> 00:11:14.840 And then they sit there and listen for a second. No, 165 00:11:14.919 --> 00:11:18.440 he's in there, dad, Right, that's the backup exactly. 166 00:11:20.159 --> 00:11:23.320 And this is simple and it works incredibly well, right, 167 00:11:23.399 --> 00:11:29.759 Like you can serialize concurrent operations like without the application 168 00:11:29.879 --> 00:11:32.559 needing to do any custom logic. That's great, right. The 169 00:11:32.639 --> 00:11:37.320 problem that's when I eventually go what yeah, exactly. The 170 00:11:37.399 --> 00:11:43.639 problem came from how Ruby and sequelite work together. Because 171 00:11:44.000 --> 00:11:47.120 the magic and the beauty of sequlite is that it 172 00:11:47.240 --> 00:11:51.080 is an embedded database. It's not a client server database, 173 00:11:51.120 --> 00:11:56.200 and this means that it runs inside of your application process. 174 00:11:56.240 --> 00:11:59.919 You don't spin up a Sequalite process that's running next 175 00:12:00.080 --> 00:12:05.080 to your Rails server process. It is code that's executed 176 00:12:05.120 --> 00:12:08.840 inside of that process. And what that meant for Rails 177 00:12:09.240 --> 00:12:14.440 or Ruby more generally is that, by default the way 178 00:12:14.559 --> 00:12:21.480 that Ruby is integrating with Sequalite, the Ruby interpreter did 179 00:12:21.519 --> 00:12:27.840 not see sequel lighte connection sleeping as io that it 180 00:12:27.879 --> 00:12:33.080 can release the GVL on. So the GVL gets retained 181 00:12:33.159 --> 00:12:36.960 as Seqlite retries. And so what that meant was if 182 00:12:37.000 --> 00:12:41.320 you spin up ten Puma workers and one Puma worker 183 00:12:41.399 --> 00:12:44.759 acquires the right lock and it's doing a little bit 184 00:12:44.840 --> 00:12:50.120 of longer running work, another Puma worker could and should 185 00:12:50.200 --> 00:12:54.519 theoretically be able to do Ruby work while Sequalite is 186 00:12:55.240 --> 00:12:59.399 doing Seqlite work, but they weren't able to coordinate correctly. 187 00:12:59.559 --> 00:13:04.559 So this is the busy handler thing. So what we did, I'm. 188 00:13:04.320 --> 00:13:06.240 Just going to back up for a second because you 189 00:13:06.279 --> 00:13:09.240 mentioned the GVL, yeah, which is something that we've talked 190 00:13:09.240 --> 00:13:11.120 about at length on this show before, but not for 191 00:13:11.200 --> 00:13:17.360 quite a while. So the gvl's the global vmlock and 192 00:13:17.399 --> 00:13:21.200 it says essentially, hey, I've got this thread that's trying 193 00:13:21.240 --> 00:13:25.399 to do a thing, and what Ruby does is when 194 00:13:25.440 --> 00:13:29.039 it's doing IO. Right, So if it's sending off to 195 00:13:29.080 --> 00:13:32.039 the network and waiting for a response or talking to 196 00:13:32.080 --> 00:13:34.840 the database and waiting for a response, what it does 197 00:13:34.919 --> 00:13:37.879 is it releases the GVL and lets something else, another 198 00:13:37.960 --> 00:13:42.320 thread pick things up and work. And so what Steven's 199 00:13:42.320 --> 00:13:49.080 talking about is essentially Ruby didn't detect this as IO 200 00:13:49.840 --> 00:13:52.320 is what he said, which meant that it wouldn't release 201 00:13:52.360 --> 00:13:54.960 the GVL. In other words, it's going to sit there 202 00:13:55.000 --> 00:14:00.440 and it's just go, I'm still waiting instead of you know, 203 00:14:00.919 --> 00:14:04.080 letting another thread pick up work and go do work. 204 00:14:04.120 --> 00:14:06.600 Because if it's not doing iowork that it has to 205 00:14:06.639 --> 00:14:08.159 wait for, then it can do other things, right. It 206 00:14:08.159 --> 00:14:12.159 can calculate stuff or you know, sort your raise or whatever, 207 00:14:12.240 --> 00:14:15.480 right because that all happens in the VM. And so yeah, 208 00:14:15.480 --> 00:14:19.200 so what Stephen's talking about is you would have things 209 00:14:19.279 --> 00:14:21.519 essentially just hang there until they got an answer. 210 00:14:22.080 --> 00:14:27.200 Yeah, and this is particularly important for typical rails applications 211 00:14:27.240 --> 00:14:30.279 because your typical rails application is using Puma. Puma's going 212 00:14:30.320 --> 00:14:34.399 to spin up a number of worker threads. Let's just 213 00:14:34.399 --> 00:14:38.080 say it's ten. And the way that it is expected 214 00:14:38.120 --> 00:14:42.799 to work is that you are increasing your throughput by 215 00:14:42.840 --> 00:14:48.600 having high saturated concurrent work. So one Ruby thread can 216 00:14:49.519 --> 00:14:52.759 take an incoming HTTP request and do Ruby work to 217 00:14:53.000 --> 00:14:54.840 like parse the stream like get a sense, let me 218 00:14:54.879 --> 00:14:58.360 get the rails request object, and then it makes a 219 00:14:58.399 --> 00:15:01.320 call to the database. It's like okay, now that worker 220 00:15:01.440 --> 00:15:03.120 is going to wait for the database. So the next 221 00:15:03.159 --> 00:15:07.200 worker over can start and pick up the next request, 222 00:15:07.360 --> 00:15:09.840 and so your CPU can be doing work constantly. We 223 00:15:09.879 --> 00:15:12.639 can really saturate our resources, take advantage of all of 224 00:15:12.679 --> 00:15:18.639 our cores, and do high through put work by really 225 00:15:18.720 --> 00:15:23.600 leaning into concurrency. That's like essential to how Puma is performant. 226 00:15:24.039 --> 00:15:28.279 And you could imagine that if talking to your database 227 00:15:29.559 --> 00:15:32.840 did not give the opportunity to your other Puma workers 228 00:15:32.879 --> 00:15:36.360 to do the other parts of the web request processing, 229 00:15:36.840 --> 00:15:41.159 you have ten workers, but effectively you have one, Like 230 00:15:41.320 --> 00:15:46.399 you have removed the advantages of the concurrency. And so 231 00:15:47.039 --> 00:15:51.120 what we needed to do was instead of using sqlightes 232 00:15:51.159 --> 00:15:57.320 built in mechanism for retrying, we wrote they expose a 233 00:15:57.399 --> 00:16:02.399 lower level API to write your own and then that 234 00:16:02.480 --> 00:16:07.320 allows us to write our own in Ruby. And so 235 00:16:07.559 --> 00:16:11.759 instead of calling the OS level sleep, we're calling Ruby sleep, 236 00:16:11.799 --> 00:16:16.600 and Ruby sleep does properly communicate to the interpreter, Hey, 237 00:16:16.879 --> 00:16:19.639 I am just sleeping. That is IO, you can release 238 00:16:19.679 --> 00:16:24.279 the GVL. And the third related thing, just to finish 239 00:16:24.360 --> 00:16:27.000 off the triad is when we were doing that rewrite 240 00:16:27.000 --> 00:16:30.639 and writing it a Ruby, I was doing benchmarking and 241 00:16:30.919 --> 00:16:37.679 I saw that sequelights implementation had a sort of subtle 242 00:16:37.840 --> 00:16:42.639 nasty side effect that I call punishing older queries or 243 00:16:43.200 --> 00:16:46.600 older connections, where because it was a polynomial back off 244 00:16:47.720 --> 00:16:50.120 what that and it's just like telling you don't ask 245 00:16:50.200 --> 00:16:53.080 me again for another five seconds. If you had high 246 00:16:53.600 --> 00:17:01.879 right throughput, once a connection needed to retry two or 247 00:17:01.919 --> 00:17:04.519 three times, so now it's waiting five milliseconds and then 248 00:17:04.559 --> 00:17:10.200 ten milliseconds. New connections have a much higher likelihood of 249 00:17:10.240 --> 00:17:14.240 acquiring the lock because they will retry more immediately and 250 00:17:14.279 --> 00:17:16.559 then one millisecond than too, so they get to try 251 00:17:16.599 --> 00:17:21.400 four times before my older connection even checks in again. 252 00:17:21.480 --> 00:17:24.079 And so if I have a steady stream of new connections, 253 00:17:24.400 --> 00:17:27.400 that old connection will go from the fourth generation to 254 00:17:27.440 --> 00:17:29.640 the fifth to the sixth, and it'll you'll always have 255 00:17:29.720 --> 00:17:33.319 one which goes until it errs out. And so you're 256 00:17:33.440 --> 00:17:37.200 like P ninety nine will have like these big spikes 257 00:17:37.240 --> 00:17:40.680 because you'll have like one connection that just never acquires. 258 00:17:40.680 --> 00:17:44.799 So we also rewrote the logic to just have every 259 00:17:44.880 --> 00:17:49.359 connection every time check in every millisecond, so there's no 260 00:17:49.519 --> 00:17:54.039 penalty for being quote unquote older having more retries as 261 00:17:54.079 --> 00:17:57.720 compared to younger having fewer retries. And this flattened out 262 00:17:57.880 --> 00:18:01.839 the latency that we saw well at like P ninety 263 00:18:01.880 --> 00:18:05.480 nine and P ninety five. So those three fixes are 264 00:18:05.559 --> 00:18:11.480 the major changes in the Rail's eight release that make 265 00:18:12.519 --> 00:18:14.880 rails production ready out of the box. You're not going 266 00:18:14.920 --> 00:18:17.160 to get these database lock airs, You're not going to 267 00:18:17.240 --> 00:18:20.480 see these latency spikes in your P ninety five or 268 00:18:20.480 --> 00:18:23.960 your P ninety nine statistics. You're going to get a 269 00:18:24.039 --> 00:18:29.599 high performance, stable experience by running rails new. 270 00:18:31.319 --> 00:18:35.799 Awesome. So I have another question. I seem to remember 271 00:18:36.160 --> 00:18:39.000 seeing somewhere that you were fairly involved in a lot 272 00:18:39.000 --> 00:18:45.960 of this. So when sequel light exposed the lower level thing, 273 00:18:46.079 --> 00:18:51.359 what was that for rails, Like, did you or David 274 00:18:51.480 --> 00:18:54.640 or somebody else go and actually talk to him and say, hey, 275 00:18:54.680 --> 00:18:56.839 we have this problem, can you let us do this 276 00:18:56.960 --> 00:18:59.559 other thing? Or was that something was already there? 277 00:19:00.119 --> 00:19:03.759 That was something that was already there. This is one 278 00:19:03.759 --> 00:19:09.519 of the things I have come to really appreciate about Syqalite. 279 00:19:10.200 --> 00:19:13.400 You know, it's thirty years old at this point, and 280 00:19:13.440 --> 00:19:20.240 it's used everywhere, and so it really is today like 281 00:19:20.759 --> 00:19:26.319 very feature rich and very powerful. The big difference that 282 00:19:26.400 --> 00:19:29.599 trips people up, because it's quite uncommon in our modern 283 00:19:29.599 --> 00:19:36.480 software culture, is that Sequelite cares far more about backwards 284 00:19:36.519 --> 00:19:44.119 compatibility than it does about enabling newer, better defaults and features. 285 00:19:44.839 --> 00:19:47.720 And so what that means is that if you don't 286 00:19:47.720 --> 00:19:51.039 do anything, if you don't do any configuration or any tuning, 287 00:19:52.319 --> 00:19:57.960 when you are using Seqlite today, you are effectively using 288 00:19:58.000 --> 00:20:01.680 Sequlite as it existed in two thousand four, which is 289 00:20:01.720 --> 00:20:07.759 when version three was released. And so these myths and 290 00:20:07.839 --> 00:20:10.559 sort of fears that have grown up in the web 291 00:20:10.599 --> 00:20:13.640 development community like seqlite is not a production of any database, 292 00:20:13.799 --> 00:20:19.039 have a root in reality, because in two thousand and six, 293 00:20:19.240 --> 00:20:22.960 seqlit was not a good database for web applications. It 294 00:20:23.079 --> 00:20:27.920 was squarely built for embedded devices and that context. Over 295 00:20:27.920 --> 00:20:30.880 the last two decades, a lot of features and a 296 00:20:31.000 --> 00:20:34.440 lot of work has been done to make it a 297 00:20:34.599 --> 00:20:40.160 far more flexible and powerful database engine, but literally none 298 00:20:40.200 --> 00:20:42.720 of those features are enabled by default, you have to 299 00:20:42.880 --> 00:20:49.920 manually configure seqlite to be properly run in a web application. 300 00:20:50.039 --> 00:20:52.440 This is the other big thing that rails does. We 301 00:20:52.559 --> 00:20:56.680 started doing this in rail step two. But it has 302 00:20:56.920 --> 00:21:02.000 a set of six or eight configurations that it applies 303 00:21:02.079 --> 00:21:05.119 to every connection you make so that it ensures that 304 00:21:05.119 --> 00:21:11.839 seqalite is running in a web application friendly mode. So 305 00:21:12.079 --> 00:21:14.759 this is just one of many features that seqalite has 306 00:21:14.799 --> 00:21:19.279 had sort of added over the last two decades that 307 00:21:20.319 --> 00:21:24.400 you can take advantage of to make seqallite run really 308 00:21:24.400 --> 00:21:27.480 well in the context of a multi threaded web application. 309 00:21:28.440 --> 00:21:32.000 But you have to do the work to figure out 310 00:21:32.079 --> 00:21:35.200 how you need seqlite to behave for your context. 311 00:21:37.880 --> 00:21:38.680 It's interesting that. 312 00:21:38.640 --> 00:21:42.519 You summarize the connection parameters. Is that going down a 313 00:21:42.559 --> 00:21:45.440 depravat hold the configuration stuff. 314 00:21:45.920 --> 00:21:51.519 I can summarize the key points. So the first most 315 00:21:51.519 --> 00:21:58.240 important one is that the original implementation of sequalite's journal mode, 316 00:21:58.279 --> 00:22:03.079 so the way in which it deals with stashing data 317 00:22:03.119 --> 00:22:10.240 to keep durability guarantees, was limited in that it required 318 00:22:10.519 --> 00:22:15.240 any operation to be linear, so reads and writes both 319 00:22:15.319 --> 00:22:17.359 contended with each other, so you could only do one 320 00:22:17.440 --> 00:22:20.599 thing at a time, so you could open multiple connections, 321 00:22:20.640 --> 00:22:23.480 but it's kind of useless. You were single threaded quote 322 00:22:23.519 --> 00:22:27.079 unquote at the point of view ofc QUAD. Over the years, 323 00:22:27.079 --> 00:22:32.559 they added a new journaling mode, right aheadlogging, which is 324 00:22:32.599 --> 00:22:36.599 the same sort of algorithm that postgress uses, which allows 325 00:22:36.640 --> 00:22:40.319 for concurrent reads. So that's like major change number one. 326 00:22:40.359 --> 00:22:43.640 You need to be in right a headlogging mode. When 327 00:22:44.480 --> 00:22:48.759 you use rite a headlogging mode, you have a much 328 00:22:48.880 --> 00:22:53.839 naturally safer environment, and so if you don't care about 329 00:22:53.880 --> 00:22:59.160 like the absolute strictest durability constraints, you can flush to 330 00:22:59.279 --> 00:23:06.799 disk often and get much faster performance. So the other default, 331 00:23:06.839 --> 00:23:10.240 like by default sequelite after every right flushes to disk, 332 00:23:10.359 --> 00:23:14.200 so you're doing the file system calls on every right, 333 00:23:14.240 --> 00:23:18.160 which adds a lot of overhead. So RAILS also defaults 334 00:23:18.200 --> 00:23:23.359 to flushing to disk less often, which is encouraged when 335 00:23:23.359 --> 00:23:25.519 you're using right ahead logging, so you get better performance. 336 00:23:26.359 --> 00:23:31.720 It also expands the default size of the in memory 337 00:23:33.000 --> 00:23:36.359 portion sort of space that sqlight uses. This is another 338 00:23:36.880 --> 00:23:42.079 misconception people have right like, every database is working with 339 00:23:42.240 --> 00:23:45.319 data on disc, like that's how we get durability for 340 00:23:45.359 --> 00:23:50.920 the ACID guarantee. But no database exclusively uses data on disc. 341 00:23:51.079 --> 00:23:54.759 Every database generates a pool of memory. It's like, here's 342 00:23:54.759 --> 00:23:57.599 a memory space I'm going to use, and they keep 343 00:23:57.960 --> 00:24:01.079 all of the hot data in that pool of memory 344 00:24:01.119 --> 00:24:03.599 such that they can just read and write directly in memory. 345 00:24:04.480 --> 00:24:07.559 Sequalite does the same, and you can control how big 346 00:24:07.640 --> 00:24:11.359 that space is. And it's default from you know, ten 347 00:24:11.480 --> 00:24:15.759 years ago is paltry compared to the memory that modern 348 00:24:15.839 --> 00:24:20.480 machines have, so we bumped that up. So for a 349 00:24:20.519 --> 00:24:23.680 lot of databases, you can actually like run your entire 350 00:24:23.759 --> 00:24:26.720 database effectively in memory like you have. There's literally no 351 00:24:26.799 --> 00:24:30.359 way any database can beat the speed of Sequelite in 352 00:24:30.400 --> 00:24:36.079 that context. That's actually what the guys Waprus found. They 353 00:24:36.160 --> 00:24:39.400 poorted their client from using reddis to Sequelite. He messaged 354 00:24:39.400 --> 00:24:40.680 me at one point He's like, Hey, I think I'm 355 00:24:40.680 --> 00:24:44.240 doing something wrong because I ran these benchmarks and it's 356 00:24:44.279 --> 00:24:47.160 actually faster with Sequelite and we're switched to Sequlite because 357 00:24:47.160 --> 00:24:52.039 we need like richer query language and we just knew 358 00:24:52.039 --> 00:24:55.119 we were going to pay that with slower speeds, but 359 00:24:55.160 --> 00:24:57.559 somehow it's faster. What am I doing wrong in my benchmarks? 360 00:24:57.960 --> 00:24:59.640 And we dug into it, it's like, no, your whole 361 00:24:59.720 --> 00:25:06.440 data fits in memory, so Seqallite is a more expressive 362 00:25:06.480 --> 00:25:09.839 and be faster than reddish in this case. And then 363 00:25:11.559 --> 00:25:15.839 we do a few other like small little bits and bobs. 364 00:25:15.839 --> 00:25:18.079 I have a whole blog post on my on my 365 00:25:18.119 --> 00:25:21.920 blog that breaks down all of the different settings with 366 00:25:22.000 --> 00:25:27.200 links to the documentation and explanations. Just the last one 367 00:25:27.200 --> 00:25:30.319 out I'll flag here Rails has been doing this forever, 368 00:25:30.359 --> 00:25:35.079 though you have to manually. By default, Sequalite does not 369 00:25:35.279 --> 00:25:39.319 enforce foreign keys, so like do you. Originally when it 370 00:25:39.359 --> 00:25:43.079 was built, poor and key constraints were just like you know, 371 00:25:43.279 --> 00:25:46.480 just sugar. The engine didn't do anything with them. They 372 00:25:46.519 --> 00:25:49.720 added that feature later for backwards compatibility. It's not added 373 00:25:49.720 --> 00:25:52.440 by defaults, so you have to explicitly say sequel Ie, 374 00:25:52.440 --> 00:25:53.920 please enforce my foreign keys. 375 00:25:56.799 --> 00:25:58.880 Interesting with the title of the blog post that you're 376 00:25:59.000 --> 00:25:59.599 referred to. 377 00:26:01.440 --> 00:26:05.039 Let me look up the exact title. So my blog 378 00:26:05.119 --> 00:26:07.200 is at fractalmind dot get habio and I have a 379 00:26:07.240 --> 00:26:10.000 little search up at the top. So I'm going to 380 00:26:10.039 --> 00:26:17.640 search for pragmas which is the name. I think it's 381 00:26:17.680 --> 00:26:20.480 called fine Fine tuning your database. 382 00:26:22.000 --> 00:26:24.960 Cool, I can see it now. I shall dive into 383 00:26:24.960 --> 00:26:25.400 that later. 384 00:26:29.680 --> 00:26:30.599 Yeah, I need to. 385 00:26:31.839 --> 00:26:32.240 I want to. 386 00:26:32.359 --> 00:26:33.839 I just want to capture it too so that we 387 00:26:33.839 --> 00:26:35.119 can put it into the show notes. 388 00:26:35.160 --> 00:26:42.880 And so let's move on to something a bit more real, 389 00:26:42.920 --> 00:26:47.079 specifics of a solid trifecta, which I am quite excited 390 00:26:47.079 --> 00:26:51.240 about because it's like more stuff big into reels rather 391 00:26:51.319 --> 00:26:57.079 than reaching for third party dependencies. It was obviously kind 392 00:26:57.079 --> 00:27:02.559 of conceptualized with sequel in mind, which is awesome, but 393 00:27:02.680 --> 00:27:05.759 there's still quite a lot of postgress out there in 394 00:27:05.799 --> 00:27:09.920 the world. So my question is, how, like, when you 395 00:27:10.119 --> 00:27:13.519 think about web applications, how differently do you have to 396 00:27:13.559 --> 00:27:17.119 think when you're using sequel light and if you're using 397 00:27:17.440 --> 00:27:22.200 the solid trifector with postgress, are there any things you 398 00:27:22.279 --> 00:27:24.799 really need to be aware of? Because instantly things like 399 00:27:24.880 --> 00:27:28.240 solid cable where you're kind of pulling for messages, it's 400 00:27:28.240 --> 00:27:31.160 making my spidery sense tingle that this is less efficient 401 00:27:31.200 --> 00:27:34.880