1 00:00:00,060 --> 00:00:02,760 Michael: Hello and welcome to PostgresFM, a weekly show about 2 00:00:02,760 --> 00:00:03,940 all things PostgreSQL. 3 00:00:03,940 --> 00:00:05,600 I am Michael, founder of pgMustard. 4 00:00:05,600 --> 00:00:08,041 As usual, I'm joined by my cohost, Nikolay, founder of 5 00:00:08,041 --> 00:00:09,160 Postgres.ai. 6 00:00:09,160 --> 00:00:09,880 Hello Nikolay. 7 00:00:10,240 --> 00:00:10,980 Nikolay: Hi Mike. 8 00:00:11,960 --> 00:00:16,020 Michael: And today we are also joined by a special guest, Andy 9 00:00:16,020 --> 00:00:20,080 Atkinson, Andrew Atkinson, as we were saying earlier, who has 10 00:00:20,080 --> 00:00:23,440 been a software engineer for 15 years at the likes of Groupon, 11 00:00:23,540 --> 00:00:27,680 Microsoft, and Fountain, a user of PostgreSQL for the past 10, 12 00:00:27,900 --> 00:00:31,640 speaker at several conferences, and now a published author with 13 00:00:31,640 --> 00:00:35,040 the brand new book, High Performance PostgreSQL for Rails. 14 00:00:35,500 --> 00:00:37,700 It's a pleasure to have you on, Andy, welcome. 15 00:00:37,740 --> 00:00:40,080 Andrew: It's a pleasure to be here, thanks for having me, guys. 16 00:00:40,080 --> 00:00:40,760 Really fun. 17 00:00:40,760 --> 00:00:43,040 Michael: Nice, well, I'm gonna let you in on a little secret 18 00:00:43,040 --> 00:00:44,940 here that I didn't tell you beforehand. 19 00:00:45,200 --> 00:00:48,960 You are actually our first guest who has been requested by a 20 00:00:48,960 --> 00:00:49,460 listener. 21 00:00:49,700 --> 00:00:52,780 So somebody specifically asked for you to come on, which is awesome. 22 00:00:53,120 --> 00:00:53,620 Andrew: Wow. 23 00:00:53,940 --> 00:00:56,320 Michael: And with the topic Postgres plus Rails. 24 00:00:56,320 --> 00:00:58,280 So that's what we're going to be talking about. 25 00:00:58,280 --> 00:01:01,260 Andrew: I guess asking my mom to do that really worked out. 26 00:01:03,420 --> 00:01:04,740 No, that's great to hear. 27 00:01:05,220 --> 00:01:05,720 Michael: Awesome. 28 00:01:05,800 --> 00:01:06,520 Well, yeah. 29 00:01:07,080 --> 00:01:09,280 Who better at the moment, you know, you've got this stuff fresh 30 00:01:09,280 --> 00:01:10,140 in your mind. 31 00:01:10,320 --> 00:01:13,100 So yeah, in terms of where to start, I thought it'd be really 32 00:01:13,100 --> 00:01:17,360 interesting to hear from your side, how popular a choice is Postgres 33 00:01:17,360 --> 00:01:17,960 for Rails? 34 00:01:17,960 --> 00:01:21,820 Like what's it competing with and yeah, how's that been changing 35 00:01:21,820 --> 00:01:22,540 over time? 36 00:01:22,660 --> 00:01:23,760 Andrew: Yeah, good questions. 37 00:01:24,280 --> 00:01:27,480 Well, the ORM in Ruby on Rails is called Active Record and it 38 00:01:27,480 --> 00:01:31,960 supports Postgres and MySQL/MariaDB, and SQLite. 39 00:01:31,960 --> 00:01:33,460 So whenever you're generating a new Rails app, that's one of the 40 00:01:33,460 --> 00:01:37,720 first decisions you make, I believe by default it's SQLite. 41 00:01:37,720 --> 00:01:40,380 But most folks that have apps in production, I still think it's 42 00:01:40,380 --> 00:01:44,660 usually, they're using Ruby on Rails, they're usually working 43 00:01:44,840 --> 00:01:47,560 with MySQL or Postgres. 44 00:01:47,560 --> 00:01:49,400 In kind of prepping for this, there are a couple of surveys where 45 00:01:49,400 --> 00:01:53,000 folks have been responding to surveys about how they deploy their 46 00:01:53,000 --> 00:01:56,040 apps. 47 00:01:56,040 --> 00:01:56,540 And there was a 2022 survey from Planet Argon, which I had 48 00:01:56,540 --> 00:02:01,680 pulled up here. 49 00:02:01,680 --> 00:02:02,660 And there were about 2,600 responses. 50 00:02:06,180 --> 00:02:11,240 And from 2014 onward, Postgres has been the most popular relational 51 00:02:11,240 --> 00:02:12,780 database with Rails apps. 52 00:02:13,520 --> 00:02:18,320 And I guess what I've seen is kind of this shift, starting in 53 00:02:18,320 --> 00:02:21,940 maybe the early 2010s, where I think in large part, thanks to 54 00:02:21,940 --> 00:02:25,160 Heroku having Postgres support and Heroku being a really popular 55 00:02:25,160 --> 00:02:28,100 choice, easy place, as you guys have talked about it before, 56 00:02:28,140 --> 00:02:31,500 made deploying your Rails app onto the internet much easier, 57 00:02:31,640 --> 00:02:36,140 pretty much a git push command, and took away a lot of operational 58 00:02:36,580 --> 00:02:39,440 toil that a lot of folks might otherwise take on themselves. 59 00:02:40,320 --> 00:02:43,840 And I think a lot of folks may have switched to Postgres just 60 00:02:43,840 --> 00:02:47,060 because it was part of the package there, and maybe they weren't 61 00:02:47,940 --> 00:02:50,100 deeply using relational database features. 62 00:02:50,740 --> 00:02:53,200 And so I think there was a lot that had to do with Heroku. 63 00:02:53,200 --> 00:02:56,900 And then of course, as also, as you guys have talked about, Postgres 64 00:02:56,940 --> 00:02:59,780 gaining a lot of features, a lot of performance over the last 65 00:02:59,780 --> 00:03:00,520 10 years. 66 00:03:00,720 --> 00:03:05,280 So I kind of noticed this general setup where a lot of the big 67 00:03:05,280 --> 00:03:09,140 Ruby on Rails, the companies that famously use Ruby on Rails, 68 00:03:09,140 --> 00:03:15,300 like GitHub, Shopify, Basecamp, that kind of started in the 2000s, 69 00:03:16,020 --> 00:03:19,760 they tended to choose MySQL at the time and they've stuck with 70 00:03:19,760 --> 00:03:20,600 it for the most part. 71 00:03:20,600 --> 00:03:23,840 They maybe are using, you know, clustering solutions and things, 72 00:03:23,840 --> 00:03:28,820 but companies that started more in the 2010s or mid-2010s, I 73 00:03:28,820 --> 00:03:31,020 feel like a lot of times they're, they're running Postgres. 74 00:03:31,320 --> 00:03:36,360 And so I'm certainly, you know, now I'm doing independent consulting 75 00:03:36,740 --> 00:03:39,340 with Rails teams that use Rails and Postgres. 76 00:03:39,340 --> 00:03:41,880 And I'm certainly, you know, I'm, clients are coming to me that 77 00:03:41,880 --> 00:03:42,660 are using Postgres. 78 00:03:42,660 --> 00:03:45,220 So for whatever that's worth, there's definitely companies out 79 00:03:45,220 --> 00:03:46,140 there using it. 80 00:03:46,280 --> 00:03:48,120 Michael: Yeah, I was going to ask Nikolay as well and then I 81 00:03:48,120 --> 00:03:50,980 realized that all of us have a super biased sample but that does 82 00:03:50,980 --> 00:03:52,580 that definitely does make sense. 83 00:03:52,940 --> 00:03:56,000 That data you mentioned that survey is really cool as well. 84 00:03:56,000 --> 00:04:01,020 I saw the question below was about which one would you like 85 00:04:01,020 --> 00:04:02,220 to be using in production? 86 00:04:02,800 --> 00:04:05,460 And it changes just 2 years earlier. 87 00:04:05,460 --> 00:04:08,800 So you can see that there's that desire and people wanted to 88 00:04:08,800 --> 00:04:11,400 switch or wanted to use it in production. 89 00:04:11,440 --> 00:04:14,440 And then it did eventually take over a couple of years later 90 00:04:14,440 --> 00:04:18,380 and hasn't lost first spot since, which is nice. 91 00:04:18,600 --> 00:04:20,240 You mentioned the ORM already. 92 00:04:20,840 --> 00:04:22,200 Should we dive into that? 93 00:04:22,380 --> 00:04:26,680 How are folks generally sending queries across to Postgres from 94 00:04:26,680 --> 00:04:27,180 Rails? 95 00:04:27,660 --> 00:04:28,820 Nikolay: And where is Postgres? 96 00:04:29,300 --> 00:04:33,880 If Ruby on Rails, then Postgres is under Rails or where? 97 00:04:34,820 --> 00:04:36,520 Andrew: Yeah, so with Active Record. 98 00:04:37,660 --> 00:04:38,900 Oh yeah, where is it? 99 00:04:39,280 --> 00:04:39,960 Let's see. 100 00:04:39,960 --> 00:04:40,320 Where is 101 00:04:40,320 --> 00:04:41,320 Nikolay: the place for it? 102 00:04:41,320 --> 00:04:42,260 Michael: On again, isn't it? 103 00:04:42,260 --> 00:04:44,000 Ruby on Rails, on Postgres. 104 00:04:44,380 --> 00:04:44,880 Andrew: Yep. 105 00:04:46,700 --> 00:04:48,580 Nikolay: The basis for Rails, okay. 106 00:04:49,360 --> 00:04:49,860 Modweb. 107 00:04:50,800 --> 00:04:51,300 Andrew: Yeah. 108 00:04:51,960 --> 00:04:57,440 As a Postgres enthusiast, I tend to think a lot about the database 109 00:04:57,440 --> 00:05:02,760 operations and the relational data model and almost like Rails 110 00:05:02,760 --> 00:05:07,120 is kind of wrapping, developers are writing Ruby code that's 111 00:05:07,120 --> 00:05:09,940 doing database interaction and other, other things. 112 00:05:10,080 --> 00:05:12,640 But I almost tend to think about things now more from a database 113 00:05:12,640 --> 00:05:13,440 first perspective. 114 00:05:13,440 --> 00:05:17,320 So it's almost like Postgres with Ruby on Rails or something. 115 00:05:17,320 --> 00:05:21,740 But I think, you know, depending on how you tend to, most developers, 116 00:05:21,740 --> 00:05:23,480 I think though, it's the other way around. 117 00:05:23,480 --> 00:05:26,400 They're mostly, most Rails teams I've worked on, they're writing 118 00:05:26,400 --> 00:05:27,540 Active Record code. 119 00:05:27,740 --> 00:05:30,620 In the early days, I've been around in Ruby on Rails for a long 120 00:05:30,620 --> 00:05:34,080 time, almost 15 years with some divergences. 121 00:05:34,620 --> 00:05:37,060 But in the early days, there actually was a little more competition 122 00:05:37,080 --> 00:05:38,000 around the ORM. 123 00:05:38,160 --> 00:05:42,700 Some of these projects are still around, but Sequel, SEQUEL, and 124 00:05:42,700 --> 00:05:47,480 Datamapper, some other ORMs that were written in Ruby and allowed 125 00:05:47,480 --> 00:05:52,040 developers to create queries and evolve their schema with an 126 00:05:52,040 --> 00:05:53,500 alternative to Active Record. 127 00:05:53,860 --> 00:05:57,260 Different pros and cons, performance benefits, maybe, that kind 128 00:05:57,260 --> 00:05:57,840 of thing. 129 00:05:58,100 --> 00:06:01,160 But Active Record sort of steamrolled everything or consolidated 130 00:06:01,680 --> 00:06:05,820 everything over time, as can happen with open source projects. 131 00:06:06,740 --> 00:06:11,540 So most developers then, in my experience, they're thinking, 132 00:06:12,440 --> 00:06:17,080 they're working more with objects and interactions and algorithms 133 00:06:17,240 --> 00:06:22,880 and they're building background jobs and working with message 134 00:06:22,880 --> 00:06:23,940 queues and things like that. 135 00:06:23,940 --> 00:06:28,160 So they're not necessarily writing a lot of SQL. 136 00:06:28,280 --> 00:06:30,820 And as I was saying, most Rails teams, they're going to write 137 00:06:30,820 --> 00:06:31,600 Active Record. 138 00:06:31,880 --> 00:06:35,980 So Active Record then generates SQL and it can do things for 139 00:06:35,980 --> 00:06:39,320 us as queries are generated, like annotate them and say where 140 00:06:39,320 --> 00:06:41,180 they're coming from in the app, which is nice. 141 00:06:41,520 --> 00:06:45,060 If we look at queries within Postgres and we wanna kind of go 142 00:06:45,060 --> 00:06:47,440 backwards and say, well, where did this come from in the app? 143 00:06:47,440 --> 00:06:50,340 And then another big way Active Record is used of course, is 144 00:06:50,380 --> 00:06:55,680 it is the de facto schema evolution tool as well, which is interesting 145 00:06:55,680 --> 00:06:58,380 because I think Rails developers just kind of take that for granted. 146 00:06:58,380 --> 00:07:02,420 Like, of course I control the schema And of course I could ruin 147 00:07:02,420 --> 00:07:06,200 everything with a bad migration or whatever, you know, like an 148 00:07:06,200 --> 00:07:07,660 incremental schema change. 149 00:07:08,420 --> 00:07:11,060 But that's not always the case when I've worked on other teams. 150 00:07:11,060 --> 00:07:15,060 Like I've worked at, when I was at Groupon, actually, the main 151 00:07:15,060 --> 00:07:19,700 applications I was working on were Java and there were DBAs and 152 00:07:19,700 --> 00:07:20,740 we used Postgres. 153 00:07:22,120 --> 00:07:26,260 But sometimes due to the scale there, I mean, usually developers, 154 00:07:26,280 --> 00:07:28,740 unless you're working on a small microservice maybe that you 155 00:07:28,740 --> 00:07:31,820 might have ownership of, if you're working on any of the core 156 00:07:31,960 --> 00:07:35,080 services there because of the scale of the operation or how the 157 00:07:35,080 --> 00:07:39,060 company was set up, usually there were DBAs that would do riskier 158 00:07:39,060 --> 00:07:41,400 database changes and that sort of thing. 159 00:07:42,380 --> 00:07:45,300 So you might actually just kind of provide them an example of 160 00:07:45,300 --> 00:07:47,440 the change that you want, and then it might just be done and 161 00:07:47,440 --> 00:07:50,260 they let you know, maybe via a ticketing system or something. 162 00:07:50,820 --> 00:07:54,120 So I think, you know, it's interesting, like if you come to, 163 00:07:55,160 --> 00:07:58,460 you know, your background might be where as a Rails developer, 164 00:07:58,460 --> 00:08:02,220 you may at a large company, you may not do as much of the direct 165 00:08:02,220 --> 00:08:05,980 schema control, but certainly for a lot of small to medium companies 166 00:08:05,980 --> 00:08:07,600 that is what developers do as well. 167 00:08:07,600 --> 00:08:11,820 So they do need to be informed about good schema design, of course 168 00:08:11,820 --> 00:08:15,240 creating indexes, constraints, all those sorts of things. 169 00:08:15,240 --> 00:08:18,360 And that's where it kind of, that's where then the ORM can start 170 00:08:18,360 --> 00:08:21,260 to be limited in its scope, you know, and you got to kind of 171 00:08:21,260 --> 00:08:24,640 go beyond and learn like, okay, well, what are the capabilities 172 00:08:24,660 --> 00:08:26,660 I have at my disposal within Postgres? 173 00:08:28,620 --> 00:08:32,220 Nikolay: And at some point you need to switch from schema rb 174 00:08:32,220 --> 00:08:33,900 to structure SQL, right? 175 00:08:34,120 --> 00:08:35,700 Andrew: Yeah, Michael asked about that. 176 00:08:35,860 --> 00:08:38,000 Nikolay: I saw several companies switching. 177 00:08:39,800 --> 00:08:43,260 Why do we have schema rb by default at all? 178 00:08:43,260 --> 00:08:46,080 Because everyone is switching at some point while growing, right? 179 00:08:46,080 --> 00:08:49,540 Andrew: Yeah, well, and I kind of agree with you, but I didn't 180 00:08:49,540 --> 00:08:50,720 always feel that way. 181 00:08:50,820 --> 00:08:53,980 It's actually an interesting little like microcosm of the whole 182 00:08:54,000 --> 00:08:57,320 spectrum, I think, of being more of an application developer, 183 00:08:57,380 --> 00:09:00,920 working within your programming language, which is Ruby in this 184 00:09:00,920 --> 00:09:04,400 case, and kind of thinking of the relational database as just 185 00:09:04,400 --> 00:09:06,760 a thing that you don't really directly work with, but it's just 186 00:09:06,760 --> 00:09:09,100 there to like store your data and access your data. 187 00:09:09,180 --> 00:09:12,040 And then on the other extreme, kind of like I was saying, like 188 00:09:12,040 --> 00:09:15,520 you're thinking of completely in the database operations, you're 189 00:09:15,520 --> 00:09:19,340 thinking about the schema, the queries, the indexes, you're running 190 00:09:19,340 --> 00:09:20,460 query plans in your head. 191 00:09:20,460 --> 00:09:23,300 You're thinking about, you know, how do we, we have this high 192 00:09:23,300 --> 00:09:23,940 growth table. 193 00:09:23,940 --> 00:09:24,940 Should we use partitioning? 194 00:09:25,080 --> 00:09:26,120 Should we split it out? 195 00:09:26,120 --> 00:09:28,220 Like that's the opposite end of the spectrum. 196 00:09:28,840 --> 00:09:32,640 And yeah, I think so the, to briefly, for anyone that's not familiar 197 00:09:32,640 --> 00:09:37,400 with the schema rb and the structure file by default in Ruby 198 00:09:37,400 --> 00:09:38,100 on Rails. 199 00:09:38,360 --> 00:09:41,240 As you make changes, let's say you add a table or you add an 200 00:09:41,240 --> 00:09:42,540 index, et cetera. 201 00:09:42,740 --> 00:09:45,460 You generate what Rails calls a migration, and that would be 202 00:09:45,460 --> 00:09:46,780 that incremental change. 203 00:09:46,980 --> 00:09:48,620 So here's the new table definition. 204 00:09:48,680 --> 00:09:49,940 Here's the index definition. 205 00:09:50,940 --> 00:09:55,940 And it's expressed in Ruby, but of course, it generates SQL statements 206 00:09:56,000 --> 00:09:58,520 to run and they run against your local database. 207 00:09:58,580 --> 00:10:02,180 And then what happens is your local database is then dumped, 208 00:10:02,520 --> 00:10:06,680 its schema definition or its database definition is then dumped 209 00:10:06,680 --> 00:10:07,280 into a file. 210 00:10:07,280 --> 00:10:09,040 And by default, that's a Ruby file. 211 00:10:09,220 --> 00:10:12,080 So it kind of like translates it back to Ruby and represents 212 00:10:12,080 --> 00:10:12,880 it as Ruby. 213 00:10:14,340 --> 00:10:16,560 And it kind of insulates you from the SQL. 214 00:10:16,560 --> 00:10:19,140 But really in Postgres, what's happening is it's just running, 215 00:10:19,540 --> 00:10:23,220 well, I should say, if you move to the SQL form, what it's doing 216 00:10:23,220 --> 00:10:24,360 is it's running pg_dump. 217 00:10:24,720 --> 00:10:28,440 And it's basically just taking the raw pg_dump output and putting 218 00:10:28,440 --> 00:10:32,440 that into a SQL form of the file with a little bit of extra stuff 219 00:10:32,440 --> 00:10:35,800 at the end, which are those migrations that you're creating. 220 00:10:35,800 --> 00:10:36,940 Each of them have a version. 221 00:10:36,940 --> 00:10:40,120 So it dumps those versions as insert statements into the end. 222 00:10:40,260 --> 00:10:42,980 But otherwise, it's basically just the pg_dump output. 223 00:10:43,580 --> 00:10:46,560 So yeah, what happens a lot of times for teams is they start 224 00:10:46,560 --> 00:10:50,140 out with the Ruby schema file, they start to use things that 225 00:10:50,320 --> 00:10:51,820 are beyond what it covers. 226 00:10:53,000 --> 00:10:59,600 And so they might use like a materialized view or like triggers, 227 00:11:00,260 --> 00:11:00,760 triggers, 228 00:11:01,220 --> 00:11:01,980 Nikolay: maybe, right? 229 00:11:01,980 --> 00:11:03,420 Andrew: Yeah. So stuff like that. 230 00:11:04,020 --> 00:11:06,820 And, and then what can happen too, is the open source community 231 00:11:06,820 --> 00:11:10,460 can, can spring in and can say, "Oh, we can fix this, we can, 232 00:11:10,600 --> 00:11:13,020 we can actually extend the Ruby form of the file. 233 00:11:13,020 --> 00:11:16,020 So you can keep using it, but you gotta also run this Ruby gem 234 00:11:16,100 --> 00:11:16,680 with your app. 235 00:11:16,680 --> 00:11:19,340 And now we can express triggers in the Ruby file. 236 00:11:19,640 --> 00:11:23,420 But I kind of tend to just encourage, okay, at that point just 237 00:11:23,420 --> 00:11:26,260 switch away or maybe even just start that way. 238 00:11:26,300 --> 00:11:28,780 But just switch to the SQL file because it's going to give you 239 00:11:28,780 --> 00:11:30,160 the highest fidelity information. 240 00:11:30,160 --> 00:11:32,940 It's essentially what pg_dump is. 241 00:11:34,020 --> 00:11:38,660 Nikolay: And if some database guy is performing code review, 242 00:11:38,940 --> 00:11:42,940 it's much easier to see the changes in Structured SQL than in 243 00:11:43,360 --> 00:11:46,400 this language you don't fully understand. 244 00:11:47,280 --> 00:11:52,360 Basically, if you do it in the project with SchemaRB, you end 245 00:11:52,360 --> 00:11:56,260 up asking, provide me a full log or a full dump of everything. 246 00:11:56,400 --> 00:11:56,900 Andrew: Right. 247 00:11:57,340 --> 00:12:00,880 Not to get too philosophical, but as we're on a team building 248 00:12:00,880 --> 00:12:04,300 an application, the code is kind of our method of communicating 249 00:12:05,220 --> 00:12:08,620 with the computer and with the team members as well, you know, 250 00:12:08,620 --> 00:12:10,580 as we express the domain concept. 251 00:12:10,760 --> 00:12:13,680 Or in this case, as we're expressing the database design. 252 00:12:14,180 --> 00:12:18,220 So if your team has people that are more, you know, database 253 00:12:18,240 --> 00:12:23,200 only people then giving them a Ruby file is, I mean, they, they 254 00:12:23,200 --> 00:12:26,120 would have to learn it and it doesn't actually have all of the 255 00:12:26,120 --> 00:12:27,380 information in it often. 256 00:12:28,040 --> 00:12:30,220 So yeah, it's a little, it's a little weird. 257 00:12:30,820 --> 00:12:34,120 However, as I was writing the book, I was thinking about a lot 258 00:12:34,120 --> 00:12:36,040 of these kinds of things too because I have been on a lot of 259 00:12:36,040 --> 00:12:36,760 different teams. 260 00:12:37,200 --> 00:12:40,640 And I was kind of trying to not be too preachy about one thing 261 00:12:40,640 --> 00:12:42,860 or the other, just kind of saying like, well, if your team is 262 00:12:42,860 --> 00:12:46,760 mostly Rails developers, you know, just know that there are limitations 263 00:12:46,860 --> 00:12:50,080 to the Ruby schema dump, that there may be some information that 264 00:12:50,080 --> 00:12:51,400 you're not getting in here. 265 00:12:51,500 --> 00:12:53,800 However, you know, and then I sort of made the pitch for the 266 00:12:53,800 --> 00:12:54,980 structure file maybe. 267 00:12:55,440 --> 00:12:56,320 But yeah, that's a good point. 268 00:12:56,320 --> 00:12:58,680 Like if you're working with a DBA, you know, you can hand them 269 00:12:58,680 --> 00:13:01,320 a dump file and they'd have a clear understanding of what's in 270 00:13:01,320 --> 00:13:01,940 the database. 271 00:13:03,400 --> 00:13:07,280 Nikolay: Yeah, I'm curious if things would be changed with LLM 272 00:13:07,280 --> 00:13:09,900 and so on, which can easily translate or something. 273 00:13:09,900 --> 00:13:10,920 But it's a different story. 274 00:13:10,920 --> 00:13:12,320 Andrew: Yeah, that's an interesting idea. 275 00:13:12,880 --> 00:13:17,020 Nikolay: Do you have an understanding why do we need to keep 276 00:13:17,240 --> 00:13:18,600 two tracks of changes? 277 00:13:18,600 --> 00:13:22,460 So first is each migration is kind of different, and if we keep 278 00:13:22,460 --> 00:13:26,600 all of them, we always can build from zero, from ground, we can 279 00:13:26,600 --> 00:13:28,760 build our final schema, right? 280 00:13:28,820 --> 00:13:29,720 Just in steps. 281 00:13:30,020 --> 00:13:33,840 But also we keep this structure SQL, or schema RB, doesn't matter, 282 00:13:33,840 --> 00:13:35,880 which is a snapshot of the latest version. 283 00:13:36,980 --> 00:13:38,260 Why do we need both? 284 00:13:38,560 --> 00:13:41,460 I was very curious, I was trying to understand this all the time. 285 00:13:41,660 --> 00:13:42,840 Why do we need both? 286 00:13:43,460 --> 00:13:48,220 Andrew: Yeah, well some people even say, so as your incremental 287 00:13:48,260 --> 00:13:51,900 changes contribute to the single file representation, schema 288 00:13:51,900 --> 00:13:55,080 RB, and like if we're talking about the Ruby one, some people say 289 00:13:55,080 --> 00:14:00,040 that like the schema RB, it is basically the same as the incremental 290 00:14:00,060 --> 00:14:00,460 changes. 291 00:14:00,460 --> 00:14:01,980 It's just everything at once. 292 00:14:02,180 --> 00:14:06,180 It's kind of this intermediary Ruby representation of your database's 293 00:14:06,300 --> 00:14:06,800 structure. 294 00:14:07,740 --> 00:14:10,700 And you don't actually need both, really. 295 00:14:11,680 --> 00:14:16,160 The incremental changes do serve as the log, which you'd otherwise 296 00:14:16,160 --> 00:14:16,500 have to... 297 00:14:16,500 --> 00:14:17,780 You could get through Git. 298 00:14:17,780 --> 00:14:20,880 Like if you looked at, you know, if you pulled changes as a developer 299 00:14:20,880 --> 00:14:24,140 on a team and you notice that the schema would be changed, but 300 00:14:24,140 --> 00:14:27,480 there wasn't the incremental file that represented the change, 301 00:14:27,920 --> 00:14:30,660 you could look into the Git history and try to discern what happened, 302 00:14:30,660 --> 00:14:32,820 but I guess the file kind of makes it easy. 303 00:14:33,340 --> 00:14:36,060 And also when Ruby on Rails was started, we didn't really have 304 00:14:36,060 --> 00:14:38,640 Git, so we were stuck with like subversion or something else. 305 00:14:38,640 --> 00:14:44,100 So, you know, I think Git made doing that kind of version to 306 00:14:44,100 --> 00:14:47,080 version investigation easier in my experience anyways, like being 307 00:14:47,080 --> 00:14:49,240 a, I think a better version control tool. 308 00:14:49,300 --> 00:14:51,020 But yeah, I mean, technically you don't need it. 309 00:14:51,020 --> 00:14:54,520 And so what some teams do after a while is they just throw away 310 00:14:54,520 --> 00:14:55,820 the incremental ones. 311 00:14:56,320 --> 00:14:59,540 And so Ruby on Rails also allows you to just load the schema. 312 00:14:59,540 --> 00:15:02,560 If you're, if you're setting up a brand new machine, or a new 313 00:15:02,560 --> 00:15:06,100 developer on the team, you can just load directly from that single 314 00:15:06,100 --> 00:15:07,120 file, the schema.rb. 315 00:15:07,120 --> 00:15:09,100 So that would be like a structure load command. 316 00:15:10,120 --> 00:15:12,940 And that would work the same regardless of which type. 317 00:15:13,080 --> 00:15:15,040 So Michael, you were asking about like a beef. 318 00:15:15,040 --> 00:15:17,120 I think there's, it's like not a real beef. 319 00:15:17,120 --> 00:15:20,720 It's like a, it's like a faux beef that programmers invented, 320 00:15:20,720 --> 00:15:21,140 I think. 321 00:15:21,140 --> 00:15:24,160 But it's kind of like, do you want to preserve the beauty or 322 00:15:24,160 --> 00:15:27,720 elegance of the Ruby code, or do you want to just like, you know, 323 00:15:27,720 --> 00:15:30,520 throw it away for the ugliness of an SQL file, maybe what some 324 00:15:30,520 --> 00:15:34,400 Ruby programmers might say, Or what I might say is like, I actually 325 00:15:34,400 --> 00:15:36,280 think the SQL file is pretty. 326 00:15:36,280 --> 00:15:37,040 It's elegant. 327 00:15:37,080 --> 00:15:39,720 You know, it's got, like I said, it's like high fidelity. 328 00:15:39,720 --> 00:15:43,100 It's all of the information, you know, and you can even customize 329 00:15:43,180 --> 00:15:43,580 it. 330 00:15:43,580 --> 00:15:47,960 Active Record allows you to pass flags through to pg_dump. 331 00:15:48,100 --> 00:15:50,760 So if you need even more information or you want to change the 332 00:15:50,760 --> 00:15:54,620 output, actually I did think of, there is one reason maybe the 333 00:15:54,620 --> 00:15:58,980 beef emerged is if you have two developers running slightly different 334 00:15:58,980 --> 00:16:01,960 versions of Postgres, so maybe the same minor major version, 335 00:16:01,960 --> 00:16:03,820 but slightly different minor versions. 336 00:16:04,340 --> 00:16:08,640 Postgres changes the pg_dump format over time, different, like 337 00:16:08,640 --> 00:16:12,340 it could be the ordering or I think it's usually the ordering. 338 00:16:12,600 --> 00:16:16,360 And what can happen is you get these annoying diffs as a developer 339 00:16:16,560 --> 00:16:19,540 where two developers are essentially it's noise. 340 00:16:19,540 --> 00:16:24,100 It's not a real meaningful change, but it's like, Oh, the triggers 341 00:16:24,100 --> 00:16:27,100 we added now they're on top of the constraints. 342 00:16:27,340 --> 00:16:29,620 And before they were after the constraints or something like 343 00:16:29,620 --> 00:16:29,700 that. 344 00:16:29,700 --> 00:16:32,640 I'm just making that up, but I have a solution for that though 345 00:16:32,640 --> 00:16:33,140 too. 346 00:16:33,340 --> 00:16:36,200 Lucas Fiddle created, this is several years old, but it's a Ruby 347 00:16:36,200 --> 00:16:39,940 gem that basically does some post processing on that dump process 348 00:16:40,260 --> 00:16:43,020 and does like an explicit ordering of all the content. 349 00:16:43,320 --> 00:16:46,200 So that if you have a team that uses that tool, then you should 350 00:16:46,200 --> 00:16:48,500 have consistent ordering amongst each other. 351 00:16:48,900 --> 00:16:50,520 Michael: And this was for the .sql? 352 00:16:50,900 --> 00:16:52,360 Andrew: Yep, that's for the SQL version. 353 00:16:52,360 --> 00:16:52,800 Michael: Makes sense. 354 00:16:52,800 --> 00:16:53,300 Yeah. 355 00:16:54,640 --> 00:16:55,140 Cool. 356 00:16:55,580 --> 00:16:59,120 I could see Nikolay exploding as you were talking about how beautiful 357 00:16:59,120 --> 00:17:00,180 the Ruby was. 358 00:17:00,480 --> 00:17:03,420 And so for our podcast listeners, I felt like that couldn't go 359 00:17:03,420 --> 00:17:03,920 unsaid. 360 00:17:04,640 --> 00:17:07,540 Nikolay: There are 2 of the ugliest languages in the world, JavaScript 361 00:17:07,660 --> 00:17:08,400 and SQL. 362 00:17:09,320 --> 00:17:12,540 They also happen to be the most popular ones. 363 00:17:14,340 --> 00:17:15,900 Andrew: That is interesting, isn't it? 364 00:17:17,700 --> 00:17:18,900 Or even C, right? 365 00:17:18,900 --> 00:17:20,340 Like C is still, you know. 366 00:17:20,860 --> 00:17:25,320 Nikolay: Another thing is that Ruby was created or else in 1995, 367 00:17:28,360 --> 00:17:32,520 same as Java, JavaScript, and PostgreSQL 95 and what else, right? 368 00:17:32,520 --> 00:17:36,640 So many things same year, just a random fact. 369 00:17:37,100 --> 00:17:37,460 Yeah. 370 00:17:37,460 --> 00:17:37,960 True. 371 00:17:39,260 --> 00:17:39,280 Michael: Yeah. 372 00:17:39,280 --> 00:17:44,240 So you mentioned way back about some limitations of the ORM. 373 00:17:44,240 --> 00:17:47,860 I think it's worth talking about like ORMs have a bad reputation 374 00:17:47,900 --> 00:17:50,280 if you talk to database folk. 375 00:17:50,600 --> 00:17:53,900 Is it worth talking about some of the like more common issues 376 00:17:53,940 --> 00:17:58,020 there or like ways around those issues or limitations when you 377 00:17:58,020 --> 00:17:59,940 need to break out that kind of thing? 378 00:18:00,340 --> 00:18:01,140 Andrew: Sure yeah. 379 00:18:01,240 --> 00:18:05,200 Well that was actually a pretty big premise I wanted to cover 380 00:18:05,220 --> 00:18:09,640 in the book is to show people Show readers like there are these 381 00:18:09,640 --> 00:18:12,260 other things in PostgreSQL that you may not be aware of if you've 382 00:18:12,260 --> 00:18:17,340 limited your kind of research area to just what's supported 383 00:18:17,340 --> 00:18:18,340 in ActiveRecord. 384 00:18:18,340 --> 00:18:18,820 Michael: Mm-hmm. 385 00:18:18,820 --> 00:18:19,200 I 386 00:18:19,200 --> 00:18:21,640 Andrew: mean one that comes to mind right away is table partitioning. 387 00:18:21,740 --> 00:18:26,420 I mean, there's not really any support in ActiveRecord for table 388 00:18:26,420 --> 00:18:26,920 partitioning. 389 00:18:27,340 --> 00:18:29,700 It doesn't mean you can't do it with a Rails app, but you might 390 00:18:29,700 --> 00:18:32,860 run into a couple of small issues, especially prior to recently, 391 00:18:32,920 --> 00:18:35,920 composite primary keys became supported in ActiveRecord. 392 00:18:36,660 --> 00:18:40,680 But I was performing a table partitioning project on a Rails, older 393 00:18:40,680 --> 00:18:45,320 Rails project about a year ago, and there were some issues with 394 00:18:45,320 --> 00:18:48,080 assumptions code would make about primary keys, for example, 395 00:18:48,080 --> 00:18:51,900 just like there's only one column that is the primary key definition. 396 00:18:52,440 --> 00:18:55,640 But yeah, so it's worth noting that, you know, if you have a 397 00:18:55,640 --> 00:18:59,200 high growth table that you wanna look at table partitioning for, 398 00:18:59,540 --> 00:19:03,240 you would be likely doing that a bit on your own with writing 399 00:19:03,240 --> 00:19:06,600 SQL commands and or kind of maybe researching like a Ruby gem 400 00:19:06,600 --> 00:19:09,720 that you would add into your Rails project that has done some 401 00:19:09,720 --> 00:19:15,260 of that work for you around creating the table structure or making 402 00:19:15,260 --> 00:19:18,480 sure your queries have the partition key column or things like 403 00:19:18,480 --> 00:19:18,980 that. 404 00:19:19,340 --> 00:19:21,480 Yeah, I mean, definitely ActiveRecord. 405 00:19:21,880 --> 00:19:24,960 I think ORMs generally try to bring some of what the database 406 00:19:24,960 --> 00:19:26,100 does into the application. 407 00:19:26,320 --> 00:19:30,660 I think that's fair to say, like for example, triggers in Postgres 408 00:19:31,000 --> 00:19:34,600 that have, you know, that can trigger different trigger types that fire 409 00:19:34,600 --> 00:19:36,840 at different times and that have different scopes. 410 00:19:37,660 --> 00:19:40,740 There's a whole set of things called Active Record lifecycle 411 00:19:40,800 --> 00:19:44,380 callbacks that are similar in their purpose where, you know, 412 00:19:44,380 --> 00:19:49,440 you might want to persist an object, which would be taking an 413 00:19:49,440 --> 00:19:52,120 in-memory Ruby object, you know, turning it into an insert or 414 00:19:52,120 --> 00:19:53,860 update statement, basically. 415 00:19:53,860 --> 00:19:56,780 But you could intercept that kind of before that event happens 416 00:19:56,780 --> 00:20:00,220 and do something within the application code with an Active Record 417 00:20:00,900 --> 00:20:01,400 callback. 418 00:20:01,640 --> 00:20:05,300 So you could have like a before save or a before commit. 419 00:20:05,740 --> 00:20:10,400 And some developers might not then, they might try to design 420 00:20:10,400 --> 00:20:15,420 things within that scope where maybe a trigger could be a solution 421 00:20:15,720 --> 00:20:19,180 where like if two applications were sharing the same database, 422 00:20:19,240 --> 00:20:22,420 which is not a great idea sometimes, but maybe you'd want to 423 00:20:22,420 --> 00:20:24,800 put the trigger in instead of having it at the application level 424 00:20:24,800 --> 00:20:27,140 so you don't have to duplicate code or that kind of thing. 425 00:20:27,500 --> 00:20:29,440 Nikolay: What about transaction control in this case? 426 00:20:29,440 --> 00:20:33,700 Is it inside one transaction and Rails controls this or can you 427 00:20:33,700 --> 00:20:37,040 do like trigger outside of transaction so it's not guaranteed 428 00:20:37,040 --> 00:20:38,660 that it will be consistent? 429 00:20:39,520 --> 00:20:40,780 Andrew: Yeah, yeah. 430 00:20:41,140 --> 00:20:44,180 Active Record supports a transaction concept that maps pretty 431 00:20:44,180 --> 00:20:47,820 much straight up to Postgres, you know, begin, commit or rollback 432 00:20:47,840 --> 00:20:48,340 transaction. 433 00:20:48,940 --> 00:20:53,900 And actually recently learned you can pass in, you can do transaction 434 00:20:53,980 --> 00:20:54,400 control. 435 00:20:54,400 --> 00:20:58,100 Like if you want to change the transaction. 436 00:21:01,840 --> 00:21:02,980 Yeah, the isolation level. 437 00:21:02,980 --> 00:21:03,780 Yes, thanks. 438 00:21:04,340 --> 00:21:06,960 If you want to change the isolation level, I actually just recently 439 00:21:07,120 --> 00:21:11,040 realized ActiveRecord lets you, supports that, you can pass it 440 00:21:11,040 --> 00:21:11,820 in as an option. 441 00:21:11,820 --> 00:21:16,440 And then I kind of verified it myself and made sure that the 442 00:21:16,440 --> 00:21:19,260 SQL statements were generating those things. 443 00:21:19,280 --> 00:21:22,660 But yeah, I guess if you do want to have some kind of nested 444 00:21:22,660 --> 00:21:27,660 transactions or if you want a little more control, then I think 445 00:21:27,660 --> 00:21:30,280 part of what, you know, you certainly might want to take that 446 00:21:30,280 --> 00:21:30,960 on yourself. 447 00:21:31,340 --> 00:21:34,740 And, But I wanted to actually then tie that into, I think, part 448 00:21:34,740 --> 00:21:40,040 of why ActiveRecord has been successful is it doesn't, at a certain 449 00:21:40,040 --> 00:21:42,540 point, you might just say, well, I just want to write SQL, but 450 00:21:42,540 --> 00:21:45,560 I still want maybe some ActiveRecord objects to work with. 451 00:21:45,720 --> 00:21:49,540 And ActiveRecord doesn't prevent you from just writing SQL within 452 00:21:50,020 --> 00:21:51,180 your ActiveRecord code. 453 00:21:51,180 --> 00:21:56,140 And so that can be beneficial if you want to just say, well, 454 00:21:56,140 --> 00:21:59,140 I just want to write my own SQL statement here for a query, or 455 00:21:59,140 --> 00:22:02,420 I want to even just use it as kind of an interface to run some 456 00:22:02,420 --> 00:22:06,540 commands, like maybe, you know, opening a transaction, although 457 00:22:06,540 --> 00:22:07,860 that is supported directly. 458 00:22:08,320 --> 00:22:11,680 But you can write SQL commands within ActiveRecord as a string 459 00:22:11,680 --> 00:22:15,360 that then get invoked or get sent through the ActiveRecord connection 460 00:22:15,360 --> 00:22:16,360 pool, etc. 461 00:22:17,220 --> 00:22:20,280 And then what you can do is you can leverage then taking a result 462 00:22:20,280 --> 00:22:24,060 set and taking advantage of mapping all of those database types 463 00:22:24,060 --> 00:22:26,780 into your Ruby object types and have an object to work with. 464 00:22:26,820 --> 00:22:30,580 Or you can even use primitive types like having a simple lists 465 00:22:30,580 --> 00:22:32,460 and strings and that sort of thing. 466 00:22:33,080 --> 00:22:37,220 So it's kind of like, it allows you to, has these nice helpers 467 00:22:37,280 --> 00:22:41,980 to do things like, you know, perform joins and limit fields and 468 00:22:41,980 --> 00:22:42,660 things like that. 469 00:22:42,660 --> 00:22:44,860 But it also doesn't prevent you from just saying like, I wanna, 470 00:22:44,860 --> 00:22:45,900 I'm gonna take over here. 471 00:22:45,900 --> 00:22:47,040 I'm just gonna kind of write my 472 00:22:47,040 --> 00:22:48,660 Nikolay: own SQL within this. 473 00:22:48,820 --> 00:22:51,060 Huge recursive CTE I want to write. 474 00:22:51,180 --> 00:22:52,360 Andrew: Yeah, you could do that. 475 00:22:52,360 --> 00:22:53,080 Yeah, there's. 476 00:22:54,380 --> 00:22:57,240 Michael: What proportion of the time do you tend to see, for 477 00:22:57,240 --> 00:23:01,260 yourself and for others, do you find that you're, Yeah, you're 478 00:23:01,260 --> 00:23:02,820 using one versus the other. 479 00:23:03,460 --> 00:23:05,740 Andrew: Yeah, it's interesting because like 10 years ago, I did 480 00:23:05,740 --> 00:23:09,100 see more of, I'd say, writing plain SQL within ActiveRecord. 481 00:23:09,520 --> 00:23:11,980 And I think it was because a lot of folks that were using Ruby 482 00:23:11,980 --> 00:23:15,840 on Rails then, they had more that were more senior engineers, 483 00:23:15,920 --> 00:23:19,100 they had experienced, or they had experience with working with 484 00:23:19,100 --> 00:23:22,580 SQL and other databases, and it's just how they worked, you know? 485 00:23:22,900 --> 00:23:25,680 And ActiveRecord was also more limited in its capabilities. 486 00:23:26,200 --> 00:23:28,860 And then I think there's been this blend maybe over time, or 487 00:23:28,860 --> 00:23:32,460 this interesting parallel tracks of things happening. 488 00:23:32,540 --> 00:23:36,220 One could be ActiveRecord has gained more, it's continually adding 489 00:23:36,220 --> 00:23:37,220 more and more helpers. 490 00:23:37,640 --> 00:23:41,120 The documentation refers to it as helpers, but like, you know, 491 00:23:41,120 --> 00:23:44,260 as I was mentioning before, recently common table expressions 492 00:23:44,260 --> 00:23:48,740 or CTEs gained support, which I would have thought maybe they would 493 00:23:48,740 --> 00:23:53,240 have been around for a lot earlier, but ActiveRecord has a first-class 494 00:23:53,320 --> 00:23:57,820 method, a helper method for CTEs now, and then composite primary 495 00:23:57,820 --> 00:23:58,760 keys I mentioned. 496 00:23:59,380 --> 00:24:01,960 So ActiveRecord has gained more support even when you move beyond 497 00:24:01,960 --> 00:24:02,560 one database. 498 00:24:02,560 --> 00:24:04,900 If you want to work with multiple databases, you can configure 499 00:24:04,900 --> 00:24:05,940 that in your application. 500 00:24:06,680 --> 00:24:11,780 There's even the ability to take advantage of automatically sending 501 00:24:11,960 --> 00:24:16,100 read-only queries identified by the HTTP verb for your web app 502 00:24:16,320 --> 00:24:17,380 to a read replica. 503 00:24:17,440 --> 00:24:20,700 If you have that configured, Active Record lets you set up a, 504 00:24:20,860 --> 00:24:24,100 what they call a writer and a reader role, and then they even 505 00:24:24,100 --> 00:24:27,800 added that to the sharding capabilities too, where if you have, 506 00:24:27,800 --> 00:24:30,560 if you take advantage of horizontal sharding in Active Record, 507 00:24:30,860 --> 00:24:34,040 you can do some automatic shard distribution. 508 00:24:35,080 --> 00:24:37,900 And so I think because there are more capabilities that ActiveRecord 509 00:24:38,000 --> 00:24:43,660 supports, there also, I tend to see less writing of SQL, but 510 00:24:43,660 --> 00:24:46,220 again, I think it kind of comes back to the team's composition 511 00:24:46,260 --> 00:24:46,420 too. 512 00:24:46,420 --> 00:24:51,060 If the team's very familiar with SQL and also myself as I've 513 00:24:51,060 --> 00:24:52,940 gotten more experienced with SQL. 514 00:24:53,420 --> 00:24:55,960 I think my patience for like, okay, is this supported in ActiveRecord? 515 00:24:55,960 --> 00:24:56,280 record? 516 00:24:56,280 --> 00:24:58,780 Like, okay, fine, I'll do it in Active Record because that will 517 00:24:58,780 --> 00:25:00,800 work well for the rest of the team or whatever. 518 00:25:01,020 --> 00:25:04,040 But if I can't find it pretty quickly, I'll just write it as 519 00:25:04,040 --> 00:25:04,900 an SQL statement. 520 00:25:04,900 --> 00:25:06,360 It's no big deal to me. 521 00:25:07,200 --> 00:25:07,860 Michael: Makes sense. 522 00:25:08,680 --> 00:25:12,100 Nikolay: I worked with Rails projects a lot, and one of them is 523 00:25:12,100 --> 00:25:12,600 GitLab. 524 00:25:13,100 --> 00:25:17,360 Shameless plug-in of ads for their migration helpers. 525 00:25:18,380 --> 00:25:22,160 .rb is great, and documentation is open and great. 526 00:25:24,160 --> 00:25:28,880 But someone in the Rails community should finally rename disable_ddl_transaction!, disabled DDL transaction, because 527 00:25:29,060 --> 00:25:34,540 people, just because of this weird, I don't want to say word 528 00:25:34,540 --> 00:25:40,640 or literal, a lot of people tend to say we are going to deploy 529 00:25:40,640 --> 00:25:46,640 this without transactions, in non-transactional mode. 530 00:25:46,640 --> 00:25:50,420 Postgres cannot work without transactions. 531 00:25:50,500 --> 00:25:52,280 So it's just becoming like a single transaction, each statement 532 00:25:52,900 --> 00:25:56,000 becomes a single transaction. 533 00:25:56,000 --> 00:25:57,080 Michael: It's not transactional, right? 534 00:25:58,470 --> 00:26:04,440 And I don't like this part of Rails at all, for many, many years, 535 00:26:04,440 --> 00:26:05,700 and it should be renamed. 536 00:26:06,660 --> 00:26:10,580 But at the same point, you know, levels of understanding. 537 00:26:11,260 --> 00:26:14,960 I also understand at some point, like, I understood, creating this 538 00:26:14,960 --> 00:26:18,620 concurrently, and you need disable_ddl_transaction to run create index 539 00:26:18,620 --> 00:26:19,120 concurrently. 540 00:26:19,200 --> 00:26:20,640 It's not transactional, right? 541 00:26:21,100 --> 00:26:22,900 Because it can leave leftovers. 542 00:26:23,840 --> 00:26:25,640 So things are difficult. 543 00:26:25,640 --> 00:26:31,480 But I see people like Ruby developers, they say we will execute 544 00:26:31,480 --> 00:26:32,520 this without transactions. 545 00:26:33,520 --> 00:26:36,220 It just breaks my ear, Postgres ear. 546 00:26:36,220 --> 00:26:37,460 It's not right. 547 00:26:37,900 --> 00:26:42,140 Is it possible to change at all, to create pull requests or something? 548 00:26:42,800 --> 00:26:43,940 Andrew: Oh, you definitely could. 549 00:26:44,060 --> 00:26:45,060 I've actually seen... 550 00:26:45,060 --> 00:26:47,900 I mean, yeah, we could after this call, we could create a pool 551 00:26:47,900 --> 00:26:50,560 request to Rails and try to get that approved, but it'd be an 552 00:26:50,560 --> 00:26:51,840 uphill battle, I think. 553 00:26:52,540 --> 00:26:53,900 Nikolay: It's probably a huge battle. 554 00:26:54,140 --> 00:26:58,940 But I already have 1 battle won, and I remember the 37signals 555 00:26:59,100 --> 00:27:01,420 team, or Basecamp, or who is that? 556 00:27:01,420 --> 00:27:01,920 Yeah. 557 00:27:02,580 --> 00:27:07,780 They added support of the logic when we have replicas, asynchronous 558 00:27:07,840 --> 00:27:11,600 replicas, and a write happened in a session. 559 00:27:11,920 --> 00:27:15,060 So you need to stick to the primary for some period of time, 560 00:27:15,060 --> 00:27:20,660 because otherwise if a replica is lagging, you won't see your 561 00:27:20,660 --> 00:27:22,860 own write immediately after. 562 00:27:23,300 --> 00:27:24,780 So they implemented this. 563 00:27:25,160 --> 00:27:28,080 Before that, many companies, and I also implemented this logic 564 00:27:28,080 --> 00:27:32,700 in several languages, in PHP and Java, in my past. 565 00:27:32,860 --> 00:27:38,380 But I see this path finally Rails is going through, and it's 566 00:27:38,380 --> 00:27:42,920 obvious to me that they implemented this lag as a constant written 567 00:27:42,920 --> 00:27:43,580 in code. 568 00:27:43,740 --> 00:27:44,840 It should be configurable. 569 00:27:45,480 --> 00:27:49,340 So there was a big battle, and DHH supported my proposal. 570 00:27:49,740 --> 00:27:53,100 I was happy to see, like, let's make this configurable. 571 00:27:53,900 --> 00:27:56,340 So maybe we can win 1 more battle, right? 572 00:27:56,680 --> 00:27:57,120 Maybe. 573 00:27:57,120 --> 00:27:57,620 Maybe. 574 00:27:58,660 --> 00:28:01,580 It's not a question, sorry, it's just my story with Rails. 575 00:28:01,720 --> 00:28:04,460 Andrew: Yeah, well, it is actually a good, I like that the design 576 00:28:04,540 --> 00:28:09,860 of, so this replication lag, there's like a resolver class concept 577 00:28:09,860 --> 00:28:12,620 and the documentation talks about, actually I wanna get back 578 00:28:12,620 --> 00:28:16,100 to your question too, Michael, about are there any other benefits 579 00:28:16,100 --> 00:28:17,240 we didn't really cover? 580 00:28:18,340 --> 00:28:21,760 I like how the documentation says, hey, we purposefully have 581 00:28:21,760 --> 00:28:23,540 this simple resolver class. 582 00:28:24,280 --> 00:28:28,620 And if you have greater needs, then they've built in an extension 583 00:28:28,620 --> 00:28:28,860 point. 584 00:28:28,860 --> 00:28:31,220 They've said like, okay, just create your own resolver class, 585 00:28:31,220 --> 00:28:33,840 implement this method, and then you can do whatever you want. 586 00:28:34,540 --> 00:28:38,680 And if I remember correctly, that's how the automatic replica 587 00:28:38,680 --> 00:28:41,780 switching and the shard switching works, is they both have kind 588 00:28:41,780 --> 00:28:46,200 of an official extension point so that you do kind of get some 589 00:28:46,200 --> 00:28:47,060 default behavior. 590 00:28:47,440 --> 00:28:50,500 They took a guess at what a reasonable replica lag would be, 591 00:28:50,500 --> 00:28:51,020 I guess. 592 00:28:51,020 --> 00:28:53,360 And then, but they said like, if you want to do something else 593 00:28:53,360 --> 00:28:56,980 or omit some certain, you don't want to make this type automatic 594 00:28:56,980 --> 00:28:59,540 or you do want to make this type automatic, etc., you could 595 00:28:59,540 --> 00:29:02,020 do that in your own implementation class. 596 00:29:02,500 --> 00:29:04,920 Just so we could cover this quickly too, Michael, you were asking 597 00:29:04,920 --> 00:29:08,900 before about other benefits, like if you do stay kind of within 598 00:29:09,280 --> 00:29:10,220 the Rails world. 599 00:29:10,320 --> 00:29:13,980 I think like, because I wanted to mention that a lot of these, 600 00:29:13,980 --> 00:29:16,760 what I would call enhancements for like higher scale operations 601 00:29:16,880 --> 00:29:22,600 are coming from developers at GitHub and Shopify and companies 602 00:29:22,600 --> 00:29:27,660 that do have, you know, internet scale operations, and they're 603 00:29:27,660 --> 00:29:30,360 still using Ruby on Rails with their relational database. 604 00:29:30,660 --> 00:29:32,740 And they're building a lot of things into ActiveRecord. 605 00:29:33,560 --> 00:29:37,180 And then I think those are, you know, great gifts that we receive 606 00:29:37,200 --> 00:29:40,560 as users then of the framework, even if we don't have that scale, 607 00:29:40,560 --> 00:29:43,500 we have some pathways that we could grow into that if we need. 608 00:29:43,660 --> 00:29:46,560 And we also get them the benefit of the ORM there where it's 609 00:29:46,560 --> 00:29:50,140 like, well, multiple database adapters are supported by the framework. 610 00:29:50,460 --> 00:29:54,800 So you need to make sure that this works in both MySQL, even 611 00:29:54,800 --> 00:29:57,680 though they don’t use Postgres at GitHub, from my understanding. 612 00:29:58,580 --> 00:30:02,440 The Active Record capabilities support multiple relational databases. 613 00:30:03,360 --> 00:30:06,400 So we kind of get that, even though they're not using Postgres 614 00:30:06,660 --> 00:30:07,620 within the framework. 615 00:30:08,940 --> 00:30:09,960 Michael: Yeah, that's really cool. 616 00:30:09,960 --> 00:30:13,520 And it used to be like, I'm old enough to remember when people 617 00:30:13,520 --> 00:30:16,400 used to ask does Rails scale and think, you know, like that, 618 00:30:16,400 --> 00:30:17,300 those old questions. 619 00:30:17,300 --> 00:30:19,640 And it just feels like we have so many good examples now that 620 00:30:19,640 --> 00:30:24,520 doesn't seem to come up as often anymore, which is nice and refreshing. 621 00:30:24,800 --> 00:30:27,480 I did actually almost want to ask from the other perspective 622 00:30:27,600 --> 00:30:33,340 quickly, though, what, like, as a Postgres community, what can 623 00:30:33,340 --> 00:30:38,340 we keep doing or what can we do better to make sure Postgres 624 00:30:38,400 --> 00:30:41,820 stays as the number 1 choice for Rails or how can we make 625 00:30:41,820 --> 00:30:46,140 things easier for developers at both large and small organizations? 626 00:30:46,920 --> 00:30:48,380 Andrew: Yeah, that's a good question. 627 00:30:49,540 --> 00:30:53,760 I do think that there's a little bit of a recent popularity with 628 00:30:53,760 --> 00:30:55,140 SQLite within Rails. 629 00:30:55,380 --> 00:30:58,220 It's gained some features that I think make it more scalable. 630 00:30:59,080 --> 00:31:03,000 And then also, depending on your deployment setup, it may offer 631 00:31:03,000 --> 00:31:05,040 you a simpler deployment configuration. 632 00:31:05,840 --> 00:31:09,000 And then on the MySQL side, I think that there's, you know, there's 633 00:31:09,000 --> 00:31:10,680 certainly I hear a lot about PlanetScale. 634 00:31:10,900 --> 00:31:14,720 They're, they've been doing great with, they're the only, I actually 635 00:31:14,720 --> 00:31:17,320 tweeted this a while ago, but like they somehow made foreign 636 00:31:17,320 --> 00:31:18,340 key constraints look cool. 637 00:31:18,340 --> 00:31:20,340 I don't know if you saw that video or not. 638 00:31:21,260 --> 00:31:23,940 They have this well-produced video where they're like, boom, 639 00:31:23,940 --> 00:31:25,440 we added foreign key constraints. 640 00:31:25,440 --> 00:31:27,720 And I was like, it was actually kind of cool. 641 00:31:27,720 --> 00:31:35,580 And I mean, I think that databases are, it's hard to make, to 642 00:31:35,580 --> 00:31:38,540 generate a lot of enthusiasm maybe around it. 643 00:31:38,600 --> 00:31:42,040 But I would say that like, you know, some of the companies offering 644 00:31:42,040 --> 00:31:45,560 Postgres as a service these days, there’s a number of new startup 645 00:31:45,560 --> 00:31:46,060 companies. 646 00:31:46,640 --> 00:31:51,660 They're kind of either advertising more like we can help you 647 00:31:51,660 --> 00:31:54,360 with multi-tenancy or we can help you with full-text search or 648 00:31:54,360 --> 00:31:58,340 we can help you with these other sorts of like capabilities or, 649 00:31:59,060 --> 00:32:01,920 You know, usages that Postgres supports. 650 00:32:01,920 --> 00:32:05,300 It just might be a lot that you need to kind of build yourself. 651 00:32:06,260 --> 00:32:09,400 And so I do think Postgres has like the raw ingredients for a 652 00:32:09,400 --> 00:32:12,400 lot of stuff, but I do think that if you're not going to use 653 00:32:12,400 --> 00:32:15,300 a managed service and you want to take this on, you got to do 654 00:32:15,300 --> 00:32:19,340 a lot of work to build skills and, you know, like if you want 655 00:32:19,340 --> 00:32:23,300 to build in your own full-text search at a good scale with good 656 00:32:23,300 --> 00:32:27,500 performance, Postgres has a lot of built-in capabilities and 657 00:32:27,500 --> 00:32:28,200 is extensible. 658 00:32:28,320 --> 00:32:29,280 You can add extensions. 659 00:32:30,040 --> 00:32:33,160 But I think like, you know, just continuing to maybe think about 660 00:32:33,160 --> 00:32:37,700 the developer experience, I guess, like at least for Postgres 661 00:32:37,700 --> 00:32:41,000 users that are web applications, anywhere the Postgres community 662 00:32:41,000 --> 00:32:45,860 can contribute, you know, guides and tutorials or if companies 663 00:32:45,860 --> 00:32:48,740 that use it successfully with some of these use cases, if they 664 00:32:48,740 --> 00:32:52,120 can publish on their engineering blogs, like I always love to 665 00:32:52,120 --> 00:32:52,860 read those. 666 00:32:53,680 --> 00:32:56,420 Like DoorDash is a company I know that is a Rails and Postgres 667 00:32:56,420 --> 00:32:59,400 company and has a lot of great engineering blog posts. 668 00:33:00,060 --> 00:33:04,120 And I think those are ways that show pathways to leverage some 669 00:33:04,120 --> 00:33:04,980 of these capabilities. 670 00:33:05,660 --> 00:33:08,300 Yeah, and then I guess I've been thinking about, I think the 671 00:33:08,300 --> 00:33:11,900 open source solution still around end to end query observability 672 00:33:12,100 --> 00:33:16,020 is still like, it can sometimes be limited in my experience where 673 00:33:16,020 --> 00:33:19,380 I'll like, you know, we, we know of tools like pg_stat_statements, 674 00:33:19,700 --> 00:33:23,080 but then we want to collect samples and it can be a little bit 675 00:33:23,080 --> 00:33:23,580 difficult. 676 00:33:24,160 --> 00:33:27,620 You kind of go back and forth between relying on logs using system 677 00:33:27,620 --> 00:33:31,300 catalogs, but Postgres keeps investing in that there's new catalogs 678 00:33:31,300 --> 00:33:32,180 and there's new things. 679 00:33:32,180 --> 00:33:35,760 And so I think like anytime, any of those sorts of things that 680 00:33:35,760 --> 00:33:40,280 make it easier to see what my query workload is like, where are 681 00:33:40,280 --> 00:33:43,900 the costs coming from, especially if they're connected to things 682 00:33:43,900 --> 00:33:45,300 I can take action on. 683 00:33:45,300 --> 00:33:49,120 Like, Nikolay, you mentioned recently how pg_stat_statements shows 684 00:33:49,120 --> 00:33:50,420 the number of rows returned. 685 00:33:50,740 --> 00:33:52,860 You could go through and look at queries where you could say, 686 00:33:52,860 --> 00:33:55,360 well, we should really probably add limits to these queries. 687 00:33:55,840 --> 00:33:57,540 Things like that can make really meaningful... 688 00:33:58,940 --> 00:34:01,400 You can draw a real clear line between, like, here's information 689 00:34:01,400 --> 00:34:04,120 that's being provided to you within Postgres that helps you out 690 00:34:04,120 --> 00:34:07,380 on as you're building and scaling and operating your system. 691 00:34:07,820 --> 00:34:09,680 Nikolay: But this approach is reactive. 692 00:34:10,460 --> 00:34:14,400 If we see pg_stat_statements on production, it's already happening. 693 00:34:15,060 --> 00:34:19,260 It would be great to try to guess based on Rails code. 694 00:34:21,040 --> 00:34:25,900 It's not about migrations already, just regular some code, Rb 695 00:34:25,900 --> 00:34:30,600 file, which serves some page or API endpoint. 696 00:34:31,560 --> 00:34:36,240 And we can just look at it, we can say, oh, the limit is missing 697 00:34:36,240 --> 00:34:37,160 here, right? 698 00:34:37,440 --> 00:34:37,940 Yeah. 699 00:34:38,400 --> 00:34:41,660 People at GitHub, for example, they build a lot of additional 700 00:34:42,100 --> 00:34:44,680 things in CI which help them. 701 00:34:45,060 --> 00:34:45,760 Andrew: Yeah, that's right. 702 00:34:45,760 --> 00:34:49,520 Nikolay: I think something can be found in public. 703 00:34:49,840 --> 00:34:55,160 So basically, for example, a select plus 1 thing, which is very 704 00:34:55,160 --> 00:34:59,840 common for ORM, it can be automatically detected before you deploy. 705 00:35:01,080 --> 00:35:04,740 And even if you test it on a very small database, not on a full-size 706 00:35:04,820 --> 00:35:11,060 database, or probably workload generated is not yet in production, 707 00:35:11,280 --> 00:35:14,560 but we already can see something is dangerous here. 708 00:35:15,060 --> 00:35:18,020 I'm trying to say this is probably not Postgres's job. 709 00:35:18,340 --> 00:35:20,040 Andrew: Yeah, it's probably not Postgres's job. 710 00:35:20,280 --> 00:35:21,100 I agree with you. 711 00:35:21,100 --> 00:35:23,980 I was kind of thinking there's the Ruby open source community, 712 00:35:23,980 --> 00:35:25,640 there are some great command-line tools. 713 00:35:25,640 --> 00:35:29,920 I'll shout out rails-pg-extras is one. 714 00:35:30,460 --> 00:35:34,340 And a common interception point where you might want to take 715 00:35:34,340 --> 00:35:36,720 a more of a proactive approach would be when you're creating 716 00:35:36,720 --> 00:35:37,320 a migration. 717 00:35:37,540 --> 00:35:40,280 Like, oh, do you realize now you've created an inconsistency 718 00:35:40,580 --> 00:35:42,280 between your schema and your application? 719 00:35:42,440 --> 00:35:46,220 Like maybe you were checking over here it was a string type and 720 00:35:46,220 --> 00:35:48,980 over here you've created an integer type, that kind of thing. 721 00:35:49,200 --> 00:35:52,800 Or that's not the most exciting example, but that command-line 722 00:35:52,800 --> 00:35:55,760 time, like when the developer is working, is kind of one touch 723 00:35:55,760 --> 00:35:56,180 point. 724 00:35:56,180 --> 00:36:00,000 Or like you said on CI, you shipped off some code to a system 725 00:36:00,060 --> 00:36:02,560 running tests and it starts to detect things and gives the developer 726 00:36:02,560 --> 00:36:05,260 feedback before it goes to production, that's always a good thing. 727 00:36:05,740 --> 00:36:06,240 Nikolay: Yeah. 728 00:36:06,380 --> 00:36:09,640 Well, I'm trying to reflect on my experience with Rails. 729 00:36:10,160 --> 00:36:13,880 I can show you another one from 2017 I found. 730 00:36:14,680 --> 00:36:16,740 So Shopify is on MySQL, right? 731 00:36:16,840 --> 00:36:17,340 Yep. 732 00:36:17,940 --> 00:36:18,960 You mentioned this. 733 00:36:19,080 --> 00:36:20,340 So let me describe it. 734 00:36:20,340 --> 00:36:24,340 There's a gem or library, I don't know, called Delayed Job. 735 00:36:24,960 --> 00:36:27,660 Delayed Job, just a single one. 736 00:36:28,780 --> 00:36:31,560 And in 2017, I saw the issue. 737 00:36:31,560 --> 00:36:36,800 I had a client in 2017 which was on Rails and on RDS already. 738 00:36:37,120 --> 00:36:40,520 It was maybe around the first time I had a good production experience 739 00:36:40,520 --> 00:36:46,080 with RDS, which I actually found liking because it provided some 740 00:36:46,080 --> 00:36:49,340 good automation for cloning, right? 741 00:36:49,940 --> 00:36:50,780 For experiments. 742 00:36:51,020 --> 00:36:52,920 Experiments matter a lot. 743 00:36:52,920 --> 00:36:54,820 So I found this problem. 744 00:36:55,160 --> 00:36:58,600 It was not performing well, delayed jobs, under scale. 745 00:36:58,660 --> 00:37:04,900 So you have a lot of jobs, a lot of entries, also like high insert 746 00:37:04,900 --> 00:37:08,140 rate. You need to have a high processing rate. 747 00:37:08,140 --> 00:37:13,580 It was super easy for me to find what was the problem and I also 748 00:37:13,580 --> 00:37:20,160 found this issue. It was discussed on GitHub since 2013, saying 749 00:37:21,040 --> 00:37:24,400 with half a million jobs it gets really slow. 750 00:37:25,380 --> 00:37:28,040 Half a million is not a huge number at all for Postgres, it's 751 00:37:28,040 --> 00:37:28,940 a small number. 752 00:37:29,540 --> 00:37:33,120 So I popped up saying it's super easy, like you just create an additional 753 00:37:33,120 --> 00:37:37,060 index, this 1, and you have already selected for an update, just 754 00:37:37,060 --> 00:37:38,620 add 2 words, keep locked. 755 00:37:39,440 --> 00:37:45,560 And index plus this, you have a massive boost for your library. 756 00:37:45,660 --> 00:37:49,180 You don't need to migrate to Sidekiq or Kafka or something. 757 00:37:49,240 --> 00:37:49,940 That's it. 758 00:37:50,380 --> 00:37:53,240 You don't need to learn PGQ from Skype. 759 00:37:53,840 --> 00:37:55,820 And they didn't do it. 760 00:37:55,840 --> 00:37:59,060 Now I understand, because it's from Shopify, and they're from 761 00:37:59,060 --> 00:38:00,020 MySQL world. 762 00:38:00,020 --> 00:38:04,320 They started discussing some Postgres versions, and I see that 763 00:38:04,500 --> 00:38:10,020 I got a bunch of likes, and I keep having email notifications, 764 00:38:10,240 --> 00:38:14,440 people thanking me because this saved their life, because this 765 00:38:14,440 --> 00:38:14,940 improved. 766 00:38:15,660 --> 00:38:21,300 But it looks like not only we talk about the connection of Ruby and 767 00:38:21,300 --> 00:38:25,320 Postgres worlds and the problem of how to connect better, but 768 00:38:25,320 --> 00:38:31,360 also this idea of ORM, let's be abstract, let's keep agnostic 769 00:38:32,420 --> 00:38:33,280 from the database. 770 00:38:34,220 --> 00:38:36,300 This makes life worse actually. 771 00:38:36,500 --> 00:38:39,040 We cannot use what Postgres can offer. 772 00:38:39,520 --> 00:38:40,520 What do you think about this? 773 00:38:40,520 --> 00:38:44,240 So again, not a question, a reflection of my experience, but maybe 774 00:38:44,240 --> 00:38:45,980 it's a good discussion I think. 775 00:38:47,520 --> 00:38:50,950 Michael: Well, I don't think you'd be able to use Postgres if 776 00:38:50,950 --> 00:38:54,300 there was only, if Rails only supported one relational database 777 00:38:54,720 --> 00:38:57,980 it would not be Postgres I don't think, based on who runs the 778 00:38:57,980 --> 00:38:58,920 Rails project. 779 00:38:59,100 --> 00:39:02,500 Andrew: Yeah it certainly wasn't the most popular in 2005 when 780 00:39:02,500 --> 00:39:06,180 Ruby on Rails was kind of really getting out there for the first 781 00:39:06,180 --> 00:39:07,540 time, which now is a long time ago. 782 00:39:07,540 --> 00:39:10,380 And yeah, I mean, so it does provide you that indirection point 783 00:39:10,380 --> 00:39:13,900 where you can say, well, we have a generic adapter and then we 784 00:39:13,900 --> 00:39:15,560 have the MySQL adapter. 785 00:39:15,860 --> 00:39:18,240 And yeah, and maybe it doesn't support skip locked. 786 00:39:18,240 --> 00:39:22,120 So maybe we can't do skip locked at all because it's not supported 787 00:39:22,120 --> 00:39:25,300 across all three of our databases, which is a trade-off. 788 00:39:26,740 --> 00:39:28,000 Nikolay: You can write an if. 789 00:39:28,860 --> 00:39:31,060 If it's Postgres, add two words. 790 00:39:31,060 --> 00:39:33,840 Andrew: Yeah, and actually that's, there's been, I should have 791 00:39:33,840 --> 00:39:37,380 had a couple of examples ready to go, but there's actually a 792 00:39:37,380 --> 00:39:39,980 couple of things that are actually only supported in Postgres 793 00:39:40,680 --> 00:39:43,480 that ActiveRecord supports, which is pretty exciting because 794 00:39:43,480 --> 00:39:46,220 it's like a little bit of a philosophical switch to me where 795 00:39:46,220 --> 00:39:48,840 you say like, okay, well we have these really useful capabilities, 796 00:39:49,840 --> 00:39:52,120 but we're just going to support them in Postgres only. 797 00:39:52,120 --> 00:39:53,820 You know, sorry, MySQL users. 798 00:39:54,060 --> 00:39:57,420 And that way then you get the best of both of those categories. 799 00:39:57,440 --> 00:39:59,800 But of course, like, then we aren't really getting into this, 800 00:39:59,800 --> 00:40:02,600 but there's a whole discussion about SQL standards and stuff 801 00:40:02,600 --> 00:40:03,280 like that. 802 00:40:03,280 --> 00:40:06,020 So I know like, for example, the returning clause is not supported 803 00:40:06,020 --> 00:40:08,760 in, my understanding is not supported in MySQL. 804 00:40:09,520 --> 00:40:12,800 Nikolay: I just checked the docs for MySQL, like latest version, 805 00:40:12,800 --> 00:40:15,580 and they already started to support skip locked, so. 806 00:40:15,580 --> 00:40:18,120 Okay, yeah. 807 00:40:18,580 --> 00:40:21,440 Andrew: Well, and it could be that a framework that chooses to 808 00:40:21,440 --> 00:40:23,980 offer conditional support, as long as it's, of course, like, 809 00:40:23,980 --> 00:40:26,760 you know, well tested and supported, then that could actually 810 00:40:26,760 --> 00:40:30,600 apply pressure to another open source database to add support 811 00:40:30,600 --> 00:40:32,160 for a SQL standard command, right? 812 00:40:32,160 --> 00:40:35,880 It's like if this gains support, probably not one framework like 813 00:40:35,880 --> 00:40:41,520 Rails, but if everything Rails and Laravel and Prisma and all 814 00:40:41,520 --> 00:40:46,220 the language communities are all clamoring for additional support. 815 00:40:47,020 --> 00:40:49,840 Nikolay: You mentioned SQL standard, but the problem here is 816 00:40:49,840 --> 00:40:52,760 that SQL standard doesn't care about performance at all. 817 00:40:53,000 --> 00:40:56,300 Nothing about indexes and SKIP LOCKED is not there. 818 00:40:56,960 --> 00:40:58,600 They don't care about performance. 819 00:40:59,440 --> 00:41:01,280 And here we talk about performance, right? 820 00:41:01,280 --> 00:41:03,620 So it's not easy sometimes. 821 00:41:04,740 --> 00:41:08,260 There should be some addition to standard talking about performance. 822 00:41:09,340 --> 00:41:11,820 But I don't see how it can happen. 823 00:41:12,380 --> 00:41:12,880 Andrew: Yeah. 824 00:41:13,480 --> 00:41:16,520 I think it's great that you've provided that feedback, Nikolay, 825 00:41:16,520 --> 00:41:17,440 into the community. 826 00:41:17,720 --> 00:41:21,820 I think there is, to the earlier question too, I think like Postgres 827 00:41:21,820 --> 00:41:25,840 is going to, you know, has its core set of objectives with each 828 00:41:25,840 --> 00:41:28,340 release, you know, where the development effort investment is 829 00:41:28,340 --> 00:41:30,480 going and that sort of thing, which is going to be a completely 830 00:41:30,480 --> 00:41:33,160 different track than Ruby on Rails. 831 00:41:33,160 --> 00:41:36,300 But like if we have some overlap, you know, the folks that tend 832 00:41:36,300 --> 00:41:40,920 to maintain the Postgres adapter code are Postgres fans in the 833 00:41:40,920 --> 00:41:42,020 Ruby on Rails community. 834 00:41:42,040 --> 00:41:43,880 They happen to also write Ruby. 835 00:41:44,180 --> 00:41:47,380 And it's great to have those kind of overlapping folks. 836 00:41:47,900 --> 00:41:51,180 There's kind of this discussion around, I know we don't have, 837 00:41:51,180 --> 00:41:53,860 we're out of time to get into this, but there's an example that 838 00:41:53,860 --> 00:41:59,040 comes to mind too of an in-clause SQL query that has a large 839 00:41:59,040 --> 00:41:59,940 list of values. 840 00:42:00,160 --> 00:42:05,520 And then some folks notice that from Ruby on Rails and on Postgres. 841 00:42:05,540 --> 00:42:08,660 And that was one where I felt like, I think Nikolay, we might have 842 00:42:08,940 --> 00:42:11,400 both tweeted about this at some point, but like there was kind 843 00:42:11,400 --> 00:42:15,040 of this crossing the streams for me where I saw people, Postgres 844 00:42:15,040 --> 00:42:17,560 people I follow and Ruby on Rails people, were like, you know, 845 00:42:17,560 --> 00:42:21,320 we need to do something about this and improve query performance 846 00:42:21,420 --> 00:42:22,780 for these types of queries. 847 00:42:23,640 --> 00:42:24,140 Nikolay: Right. 848 00:42:24,440 --> 00:42:27,380 Also, .plug, or how is it called? 849 00:42:27,380 --> 00:42:28,140 Plug, right? 850 00:42:28,140 --> 00:42:31,120 This is like when you retrieve everything, then you do something 851 00:42:31,120 --> 00:42:33,180 like Postgres can do it much more efficiently. 852 00:42:35,920 --> 00:42:39,520 Don't do work instead of database on application side, right? 853 00:42:39,720 --> 00:42:46,880 But I guess it's a small thing, any application language can 854 00:42:46,880 --> 00:42:50,960 be used for this not efficient approach. 855 00:42:51,060 --> 00:42:53,540 What do you think about the future of Ruby in general? 856 00:42:54,240 --> 00:42:57,100 Andrew: I think it's achieved that kind of cockroach status where 857 00:42:57,100 --> 00:42:58,250 it just never goes away now. 858 00:42:58,250 --> 00:42:58,680 I don't know. 859 00:42:58,680 --> 00:43:02,980 There just seems to be enough, not cockroach DB, but the actual 860 00:43:02,980 --> 00:43:03,480 insect. 861 00:43:05,660 --> 00:43:08,040 There's such a large amount of companies that there's just a 862 00:43:08,040 --> 00:43:11,540 lot of work to maintain the applications if they're successful 863 00:43:11,820 --> 00:43:13,280 that use Ruby on Rails. 864 00:43:13,380 --> 00:43:16,160 Of course, the growth is not what it was in the early days. 865 00:43:16,320 --> 00:43:18,960 But there's a huge community of Rails developers and there are 866 00:43:18,960 --> 00:43:20,200 new people entering it. 867 00:43:20,200 --> 00:43:23,440 So if someone enters it because maybe it's like their bootcamp 868 00:43:23,440 --> 00:43:26,720 program or they joined a company that's using Rails, despite 869 00:43:26,720 --> 00:43:29,740 being 20 years old as a framework for Rails anyways. 870 00:43:30,040 --> 00:43:32,440 First of all, I think Ruby is a great language outside of Rails, 871 00:43:32,440 --> 00:43:37,260 but despite being a 20 year old framework, like there's a very 872 00:43:37,260 --> 00:43:38,300 steady development clip. 873 00:43:38,300 --> 00:43:41,060 There's new features being added that are exciting. 874 00:43:41,120 --> 00:43:44,200 I just think they tend to be more front end oriented. 875 00:43:44,640 --> 00:43:47,920 Like Ruby on Rails these days is trying to capture more attention 876 00:43:47,920 --> 00:43:51,860 from what might have been JavaScript only apps before and offer 877 00:43:51,860 --> 00:43:56,400 a lot of capabilities on as you're building your web application 878 00:43:56,480 --> 00:44:00,040 screens, like giving you a lot of interactivity and low latency, 879 00:44:00,660 --> 00:44:04,480 but doing it within Rails and kind of getting back to some more 880 00:44:04,480 --> 00:44:07,660 of the full stack kind of application building capabilities. 881 00:44:08,400 --> 00:44:11,380 But there are things happening too on every release related to 882 00:44:11,380 --> 00:44:14,940 Postgres and I certainly track those and it still feels active, 883 00:44:15,060 --> 00:44:17,260 just not where everyone's flocking to. 884 00:44:17,640 --> 00:44:20,180 Michael: I thought the active might be a Rails joke. 885 00:44:20,380 --> 00:44:21,840 Andrew: Oh yeah, it could be, yeah. 886 00:44:22,000 --> 00:44:22,500 Michael: Yeah. 887 00:44:23,220 --> 00:44:26,260 On a serious note though, and thanks so much for all your time, 888 00:44:26,600 --> 00:44:28,760 where can folks go to learn more? 889 00:44:29,540 --> 00:44:34,020 Andrew: Yeah, If anyone's interested in this topic area and they'd 890 00:44:34,020 --> 00:44:37,060 like to explore the book that I wrote, it's at pragprog.com. 891 00:44:38,200 --> 00:44:40,160 That's the publishers Pragmatic Programmers. 892 00:44:40,760 --> 00:44:44,840 And I do a fair amount of blogging on Postgres and Rails topics. 893 00:44:45,100 --> 00:44:46,320 My blog is andyatkinson.com. 894 00:44:47,900 --> 00:44:50,500 And I'm also on most of the social media apps. 895 00:44:51,140 --> 00:44:56,020 This year I'm going to be at Sin City Ruby and PG Day Chicago, 896 00:44:56,040 --> 00:44:58,520 as well, so probably more for Postgres people. 897 00:44:58,520 --> 00:45:01,400 If anyone's at PG Day Chicago, I hope to make it to some more 898 00:45:01,400 --> 00:45:04,840 Postgres events, but I love the in-person events too and being 899 00:45:04,840 --> 00:45:08,060 Able to meet more community members and learn from other people. 900 00:45:08,560 --> 00:45:11,920 Nikolay: And the book is called High Performance PostgreSQL for 901 00:45:11,920 --> 00:45:12,900 Rails, right? 902 00:45:12,900 --> 00:45:14,320 Andrew: Yep, that's right. 903 00:45:15,020 --> 00:45:17,920 Michael: Awesome work, awesome publisher as well. And thanks 904 00:45:17,920 --> 00:45:18,420 again. 905 00:45:19,060 --> 00:45:19,650 Andrew: Thanks for having me. 906 00:45:19,650 --> 00:45:20,660 A lot of fun, guys. 907 00:45:20,660 --> 00:45:21,280 Thank you.