Michael and Nikolay are joined by Andrew Atkinson, author of High Performance PostgreSQL for Rails, to discuss how Rails and Postgres work together - where the limits are, how people use the ORM, things that are improving, and some things we can do as a Postgres community to make it even better. Here are some links to things they mentioned: Planet Argon survey https://rails-hosting.com/2022/#databases Active Record https://guides.rubyonrails.org/active_record_basics.html PostgreSQL specific usage of Active Record https://guides.rubyonrails.org/active_record_postgresql.html Multiple Databases with Active Record https://guides.rubyonrails.org/active_record_multiple_databases.html schema.rb vs structure.sql https://blog.appsignal.com/2020/01/15/the-pros-and-cons-of-using-structure-sql-in-your-ruby-on-rails-application.html activerecord-clean-db-structure (Ruby gem by Lukas Fittl) https://github.com/lfittl/activerecord-clean-db-structure GitLab’s migration_helpers.rb https://gitlab.com/gitlab-org/gitlab/-/blob/master/lib/gitlab/database/migration_helpers.rb SQLite https://www.sqlite.org PlanetScale’s foreign key support announcement video https://twitter.com/PlanetScale/status/1732070818958500083 DoorDash Engineering Blog https://doordash.engineering/blog rails-pg-extras https://github.com/pawurb/rails-pg-extras Benoit Tigeot testing Peter Geoghegan improvement for large IN lists https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d High Performance PostgreSQL for Rails (Andy’s book, 35% discount code “postgres.fm”) https://pragprog.com/titles/aapsql/high-performance-postgresql-for-rails Andy’s blog and website https://andyatkinson.com ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With special thanks to: Jessie Draws for the elephant artwork
Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.
Transcript
From the show. Tap a time to play from there.
Michael: Hello and welcome to PostgresFM, a weekly show about all things PostgreSQL. I am Michael, founder of pgMustard. As usual, I'm joined by my cohost, Nikolay, founder of Postgres.ai. Hello Nikolay. Nikolay: Hi Mike. Michael: And today we are also joined by a special guest, Andy Atkinson, Andrew Atkinson, as we were saying earlier, who has been a software engineer for 15 years at the likes of Groupon, Microsoft, and Fountain, a user of PostgreSQL for the past 10, speaker at several conferences, and now a published author with
the brand new book, High Performance PostgreSQL for Rails. It's a pleasure to have you on, Andy, welcome. Andrew: It's a pleasure to be here, thanks for having me, guys. Really fun. Michael: Nice, well, I'm gonna let you in on a little secret here that I didn't tell you beforehand. You are actually our first guest who has been requested by a listener. So somebody specifically asked for you to come on, which is awesome. Andrew: Wow. Michael: And with the topic Postgres plus Rails. So that's what we're going to be talking about. Andrew: I guess asking my mom to do that really worked out.
No, that's great to hear. Michael: Awesome. Well, yeah. Who better at the moment, you know, you've got this stuff fresh in your mind. So yeah, in terms of where to start, I thought it'd be really interesting to hear from your side, how popular a choice is Postgres for Rails? Like what's it competing with and yeah, how's that been changing over time? Andrew: Yeah, good questions. Well, the ORM in Ruby on Rails is called Active Record and it supports Postgres and MySQL/MariaDB, and SQLite. So whenever you're generating a new Rails app, that's one of the
first decisions you make, I believe by default it's SQLite. But most folks that have apps in production, I still think it's usually, they're using Ruby on Rails, they're usually working with MySQL or Postgres. In kind of prepping for this, there are a couple of surveys where folks have been responding to surveys about how they deploy their apps. And there was a 2022 survey from Planet Argon, which I had pulled up here. And there were about 2,600 responses.
And from 2014 onward, Postgres has been the most popular relational database with Rails apps. And I guess what I've seen is kind of this shift, starting in maybe the early 2010s, where I think in large part, thanks to Heroku having Postgres support and Heroku being a really popular choice, easy place, as you guys have talked about it before, made deploying your Rails app onto the internet much easier, pretty much a git push command, and took away a lot of operational
toil that a lot of folks might otherwise take on themselves. And I think a lot of folks may have switched to Postgres just because it was part of the package there, and maybe they weren't deeply using relational database features. And so I think there was a lot that had to do with Heroku. And then of course, as also, as you guys have talked about, Postgres gaining a lot of features, a lot of performance over the last 10 years. So I kind of noticed this general setup where a lot of the big Ruby on Rails, the companies that famously use Ruby on Rails,
like GitHub, Shopify, Basecamp, that kind of started in the 2000s, they tended to choose MySQL at the time and they've stuck with it for the most part. They maybe are using, you know, clustering solutions and things, but companies that started more in the 2010s or mid-2010s, I feel like a lot of times they're, they're running Postgres. And so I'm certainly, you know, now I'm doing independent consulting with Rails teams that use Rails and Postgres.
And I'm certainly, you know, I'm, clients are coming to me that are using Postgres. So for whatever that's worth, there's definitely companies out there using it. Michael: Yeah, I was going to ask Nikolay as well and then I realized that all of us have a super biased sample but that does that definitely does make sense. That data you mentioned that survey is really cool as well. I saw the question below was about which one would you like to be using in production? And it changes just 2 years earlier. So you can see that there's that desire and people wanted to switch or wanted to use it in production.
And then it did eventually take over a couple of years later and hasn't lost first spot since, which is nice. You mentioned the ORM already. Should we dive into that? How are folks generally sending queries across to Postgres from Rails? Nikolay: And where is Postgres? If Ruby on Rails, then Postgres is under Rails or where? Andrew: Yeah, so with Active Record. Oh yeah, where is it? Let's see. Where is Nikolay: the place for it? Michael: On again, isn't it?
Ruby on Rails, on Postgres. Andrew: Yep. Nikolay: The basis for Rails, okay. Modweb. Andrew: Yeah. As a Postgres enthusiast, I tend to think a lot about the database operations and the relational data model and almost like Rails is kind of wrapping, developers are writing Ruby code that's doing database interaction and other, other things. But I almost tend to think about things now more from a database
first perspective. So it's almost like Postgres with Ruby on Rails or something. But I think, you know, depending on how you tend to, most developers, I think though, it's the other way around. They're mostly, most Rails teams I've worked on, they're writing Active Record code. In the early days, I've been around in Ruby on Rails for a long time, almost 15 years with some divergences. But in the early days, there actually was a little more competition around the ORM. Some of these projects are still around, but Sequel, SEQUEL, and
Datamapper, some other ORMs that were written in Ruby and allowed developers to create queries and evolve their schema with an alternative to Active Record. Different pros and cons, performance benefits, maybe, that kind of thing. But Active Record sort of steamrolled everything or consolidated everything over time, as can happen with open source projects. So most developers then, in my experience, they're thinking, they're working more with objects and interactions and algorithms
and they're building background jobs and working with message queues and things like that. So they're not necessarily writing a lot of SQL. And as I was saying, most Rails teams, they're going to write Active Record. So Active Record then generates SQL and it can do things for us as queries are generated, like annotate them and say where they're coming from in the app, which is nice. If we look at queries within Postgres and we wanna kind of go backwards and say, well, where did this come from in the app?
And then another big way Active Record is used of course, is it is the de facto schema evolution tool as well, which is interesting because I think Rails developers just kind of take that for granted. Like, of course I control the schema And of course I could ruin everything with a bad migration or whatever, you know, like an incremental schema change. But that's not always the case when I've worked on other teams. Like I've worked at, when I was at Groupon, actually, the main applications I was working on were Java and there were DBAs and
we used Postgres. But sometimes due to the scale there, I mean, usually developers, unless you're working on a small microservice maybe that you might have ownership of, if you're working on any of the core services there because of the scale of the operation or how the company was set up, usually there were DBAs that would do riskier database changes and that sort of thing. So you might actually just kind of provide them an example of the change that you want, and then it might just be done and they let you know, maybe via a ticketing system or something.
So I think, you know, it's interesting, like if you come to, you know, your background might be where as a Rails developer, you may at a large company, you may not do as much of the direct schema control, but certainly for a lot of small to medium companies that is what developers do as well. So they do need to be informed about good schema design, of course creating indexes, constraints, all those sorts of things. And that's where it kind of, that's where then the ORM can start to be limited in its scope, you know, and you got to kind of
go beyond and learn like, okay, well, what are the capabilities I have at my disposal within Postgres? Nikolay: And at some point you need to switch from schema rb to structure SQL, right? Andrew: Yeah, Michael asked about that. Nikolay: I saw several companies switching. Why do we have schema rb by default at all? Because everyone is switching at some point while growing, right? Andrew: Yeah, well, and I kind of agree with you, but I didn't always feel that way. It's actually an interesting little like microcosm of the whole
spectrum, I think, of being more of an application developer, working within your programming language, which is Ruby in this case, and kind of thinking of the relational database as just a thing that you don't really directly work with, but it's just there to like store your data and access your data. And then on the other extreme, kind of like I was saying, like you're thinking of completely in the database operations, you're thinking about the schema, the queries, the indexes, you're running query plans in your head. You're thinking about, you know, how do we, we have this high growth table. Should we use partitioning?
Should we split it out? Like that's the opposite end of the spectrum. And yeah, I think so the, to briefly, for anyone that's not familiar with the schema rb and the structure file by default in Ruby on Rails. As you make changes, let's say you add a table or you add an index, et cetera. You generate what Rails calls a migration, and that would be that incremental change. So here's the new table definition. Here's the index definition. And it's expressed in Ruby, but of course, it generates SQL statements
to run and they run against your local database. And then what happens is your local database is then dumped, its schema definition or its database definition is then dumped into a file. And by default, that's a Ruby file. So it kind of like translates it back to Ruby and represents it as Ruby. And it kind of insulates you from the SQL. But really in Postgres, what's happening is it's just running, well, I should say, if you move to the SQL form, what it's doing is it's running pg_dump. And it's basically just taking the raw pg_dump output and putting
that into a SQL form of the file with a little bit of extra stuff at the end, which are those migrations that you're creating. Each of them have a version. So it dumps those versions as insert statements into the end. But otherwise, it's basically just the pg_dump output. So yeah, what happens a lot of times for teams is they start out with the Ruby schema file, they start to use things that are beyond what it covers. And so they might use like a materialized view or like triggers,
triggers, Nikolay: maybe, right? Andrew: Yeah. So stuff like that. And, and then what can happen too, is the open source community can, can spring in and can say, "Oh, we can fix this, we can, we can actually extend the Ruby form of the file. So you can keep using it, but you gotta also run this Ruby gem with your app. And now we can express triggers in the Ruby file. But I kind of tend to just encourage, okay, at that point just switch away or maybe even just start that way. But just switch to the SQL file because it's going to give you the highest fidelity information. It's essentially what pg_dump is.
Nikolay: And if some database guy is performing code review, it's much easier to see the changes in Structured SQL than in this language you don't fully understand. Basically, if you do it in the project with SchemaRB, you end up asking, provide me a full log or a full dump of everything. Andrew: Right. Not to get too philosophical, but as we're on a team building an application, the code is kind of our method of communicating
with the computer and with the team members as well, you know, as we express the domain concept. Or in this case, as we're expressing the database design. So if your team has people that are more, you know, database only people then giving them a Ruby file is, I mean, they, they would have to learn it and it doesn't actually have all of the information in it often. So yeah, it's a little, it's a little weird. However, as I was writing the book, I was thinking about a lot of these kinds of things too because I have been on a lot of
different teams. And I was kind of trying to not be too preachy about one thing or the other, just kind of saying like, well, if your team is mostly Rails developers, you know, just know that there are limitations to the Ruby schema dump, that there may be some information that you're not getting in here. However, you know, and then I sort of made the pitch for the structure file maybe. But yeah, that's a good point. Like if you're working with a DBA, you know, you can hand them a dump file and they'd have a clear understanding of what's in the database. Nikolay: Yeah, I'm curious if things would be changed with LLM
and so on, which can easily translate or something. But it's a different story. Andrew: Yeah, that's an interesting idea. Nikolay: Do you have an understanding why do we need to keep two tracks of changes? So first is each migration is kind of different, and if we keep all of them, we always can build from zero, from ground, we can build our final schema, right? Just in steps. But also we keep this structure SQL, or schema RB, doesn't matter, which is a snapshot of the latest version. Why do we need both?
I was very curious, I was trying to understand this all the time. Why do we need both? Andrew: Yeah, well some people even say, so as your incremental changes contribute to the single file representation, schema RB, and like if we're talking about the Ruby one, some people say that like the schema RB, it is basically the same as the incremental changes. It's just everything at once. It's kind of this intermediary Ruby representation of your database's structure. And you don't actually need both, really.
The incremental changes do serve as the log, which you'd otherwise have to... You could get through Git. Like if you looked at, you know, if you pulled changes as a developer on a team and you notice that the schema would be changed, but there wasn't the incremental file that represented the change, you could look into the Git history and try to discern what happened, but I guess the file kind of makes it easy. And also when Ruby on Rails was started, we didn't really have Git, so we were stuck with like subversion or something else. So, you know, I think Git made doing that kind of version to
version investigation easier in my experience anyways, like being a, I think a better version control tool. But yeah, I mean, technically you don't need it. And so what some teams do after a while is they just throw away the incremental ones. And so Ruby on Rails also allows you to just load the schema. If you're, if you're setting up a brand new machine, or a new developer on the team, you can just load directly from that single file, the schema.rb. So that would be like a structure load command. And that would work the same regardless of which type. So Michael, you were asking about like a beef.
I think there's, it's like not a real beef. It's like a, it's like a faux beef that programmers invented, I think. But it's kind of like, do you want to preserve the beauty or elegance of the Ruby code, or do you want to just like, you know, throw it away for the ugliness of an SQL file, maybe what some Ruby programmers might say, Or what I might say is like, I actually think the SQL file is pretty. It's elegant. You know, it's got, like I said, it's like high fidelity. It's all of the information, you know, and you can even customize it. Active Record allows you to pass flags through to pg_dump.
So if you need even more information or you want to change the output, actually I did think of, there is one reason maybe the beef emerged is if you have two developers running slightly different versions of Postgres, so maybe the same minor major version, but slightly different minor versions. Postgres changes the pg_dump format over time, different, like it could be the ordering or I think it's usually the ordering. And what can happen is you get these annoying diffs as a developer where two developers are essentially it's noise.
It's not a real meaningful change, but it's like, Oh, the triggers we added now they're on top of the constraints. And before they were after the constraints or something like that. I'm just making that up, but I have a solution for that though too. Lucas Fiddle created, this is several years old, but it's a Ruby gem that basically does some post processing on that dump process and does like an explicit ordering of all the content. So that if you have a team that uses that tool, then you should have consistent ordering amongst each other. Michael: And this was for the .sql?
Andrew: Yep, that's for the SQL version. Michael: Makes sense. Yeah. Cool. I could see Nikolay exploding as you were talking about how beautiful the Ruby was. And so for our podcast listeners, I felt like that couldn't go unsaid. Nikolay: There are 2 of the ugliest languages in the world, JavaScript and SQL. They also happen to be the most popular ones. Andrew: That is interesting, isn't it? Or even C, right? Like C is still, you know. Nikolay: Another thing is that Ruby was created or else in 1995,
same as Java, JavaScript, and PostgreSQL 95 and what else, right? So many things same year, just a random fact. Yeah. True. Michael: Yeah. So you mentioned way back about some limitations of the ORM. I think it's worth talking about like ORMs have a bad reputation if you talk to database folk. Is it worth talking about some of the like more common issues there or like ways around those issues or limitations when you need to break out that kind of thing?
Andrew: Sure yeah. Well that was actually a pretty big premise I wanted to cover in the book is to show people Show readers like there are these other things in PostgreSQL that you may not be aware of if you've limited your kind of research area to just what's supported in ActiveRecord. Michael: Mm-hmm. I Andrew: mean one that comes to mind right away is table partitioning. I mean, there's not really any support in ActiveRecord for table partitioning. It doesn't mean you can't do it with a Rails app, but you might run into a couple of small issues, especially prior to recently,
composite primary keys became supported in ActiveRecord. But I was performing a table partitioning project on a Rails, older Rails project about a year ago, and there were some issues with assumptions code would make about primary keys, for example, just like there's only one column that is the primary key definition. But yeah, so it's worth noting that, you know, if you have a high growth table that you wanna look at table partitioning for, you would be likely doing that a bit on your own with writing
SQL commands and or kind of maybe researching like a Ruby gem that you would add into your Rails project that has done some of that work for you around creating the table structure or making sure your queries have the partition key column or things like that. Yeah, I mean, definitely ActiveRecord. I think ORMs generally try to bring some of what the database does into the application. I think that's fair to say, like for example, triggers in Postgres that have, you know, that can trigger different trigger types that fire
at different times and that have different scopes. There's a whole set of things called Active Record lifecycle callbacks that are similar in their purpose where, you know, you might want to persist an object, which would be taking an in-memory Ruby object, you know, turning it into an insert or update statement, basically. But you could intercept that kind of before that event happens and do something within the application code with an Active Record callback. So you could have like a before save or a before commit.
And some developers might not then, they might try to design things within that scope where maybe a trigger could be a solution where like if two applications were sharing the same database, which is not a great idea sometimes, but maybe you'd want to put the trigger in instead of having it at the application level so you don't have to duplicate code or that kind of thing. Nikolay: What about transaction control in this case? Is it inside one transaction and Rails controls this or can you do like trigger outside of transaction so it's not guaranteed
that it will be consistent? Andrew: Yeah, yeah. Active Record supports a transaction concept that maps pretty much straight up to Postgres, you know, begin, commit or rollback transaction. And actually recently learned you can pass in, you can do transaction control. Like if you want to change the transaction. Yeah, the isolation level. Yes, thanks. If you want to change the isolation level, I actually just recently
realized ActiveRecord lets you, supports that, you can pass it in as an option. And then I kind of verified it myself and made sure that the SQL statements were generating those things. But yeah, I guess if you do want to have some kind of nested transactions or if you want a little more control, then I think part of what, you know, you certainly might want to take that on yourself. And, But I wanted to actually then tie that into, I think, part of why ActiveRecord has been successful is it doesn't, at a certain
point, you might just say, well, I just want to write SQL, but I still want maybe some ActiveRecord objects to work with. And ActiveRecord doesn't prevent you from just writing SQL within your ActiveRecord code. And so that can be beneficial if you want to just say, well, I just want to write my own SQL statement here for a query, or I want to even just use it as kind of an interface to run some commands, like maybe, you know, opening a transaction, although that is supported directly. But you can write SQL commands within ActiveRecord as a string
that then get invoked or get sent through the ActiveRecord connection pool, etc. And then what you can do is you can leverage then taking a result set and taking advantage of mapping all of those database types into your Ruby object types and have an object to work with. Or you can even use primitive types like having a simple lists and strings and that sort of thing. So it's kind of like, it allows you to, has these nice helpers to do things like, you know, perform joins and limit fields and
things like that. But it also doesn't prevent you from just saying like, I wanna, I'm gonna take over here. I'm just gonna kind of write my Nikolay: own SQL within this. Huge recursive CTE I want to write. Andrew: Yeah, you could do that. Yeah, there's. Michael: What proportion of the time do you tend to see, for yourself and for others, do you find that you're, Yeah, you're using one versus the other. Andrew: Yeah, it's interesting because like 10 years ago, I did see more of, I'd say, writing plain SQL within ActiveRecord. And I think it was because a lot of folks that were using Ruby
on Rails then, they had more that were more senior engineers, they had experienced, or they had experience with working with SQL and other databases, and it's just how they worked, you know? And ActiveRecord was also more limited in its capabilities. And then I think there's been this blend maybe over time, or this interesting parallel tracks of things happening. One could be ActiveRecord has gained more, it's continually adding more and more helpers. The documentation refers to it as helpers, but like, you know, as I was mentioning before, recently common table expressions
or CTEs gained support, which I would have thought maybe they would have been around for a lot earlier, but ActiveRecord has a first-class method, a helper method for CTEs now, and then composite primary keys I mentioned. So ActiveRecord has gained more support even when you move beyond one database. If you want to work with multiple databases, you can configure that in your application. There's even the ability to take advantage of automatically sending read-only queries identified by the HTTP verb for your web app
to a read replica. If you have that configured, Active Record lets you set up a, what they call a writer and a reader role, and then they even added that to the sharding capabilities too, where if you have, if you take advantage of horizontal sharding in Active Record, you can do some automatic shard distribution. And so I think because there are more capabilities that ActiveRecord supports, there also, I tend to see less writing of SQL, but again, I think it kind of comes back to the team's composition too.
If the team's very familiar with SQL and also myself as I've gotten more experienced with SQL. I think my patience for like, okay, is this supported in ActiveRecord? record? Like, okay, fine, I'll do it in Active Record because that will work well for the rest of the team or whatever. But if I can't find it pretty quickly, I'll just write it as an SQL statement. It's no big deal to me. Michael: Makes sense. Nikolay: I worked with Rails projects a lot, and one of them is GitLab. Shameless plug-in of ads for their migration helpers.
.rb is great, and documentation is open and great. But someone in the Rails community should finally rename disable_ddl_transaction!, disabled DDL transaction, because people, just because of this weird, I don't want to say word or literal, a lot of people tend to say we are going to deploy this without transactions, in non-transactional mode. Postgres cannot work without transactions.
So it's just becoming like a single transaction, each statement becomes a single transaction. Michael: It's not transactional, right? And I don't like this part of Rails at all, for many, many years, and it should be renamed. But at the same point, you know, levels of understanding. I also understand at some point, like, I understood, creating this concurrently, and you need disable_ddl_transaction to run create index concurrently. It's not transactional, right?
Because it can leave leftovers. So things are difficult. But I see people like Ruby developers, they say we will execute this without transactions. It just breaks my ear, Postgres ear. It's not right. Is it possible to change at all, to create pull requests or something? Andrew: Oh, you definitely could. I've actually seen... I mean, yeah, we could after this call, we could create a pool request to Rails and try to get that approved, but it'd be an uphill battle, I think.
Nikolay: It's probably a huge battle. But I already have 1 battle won, and I remember the 37signals team, or Basecamp, or who is that? Yeah. They added support of the logic when we have replicas, asynchronous replicas, and a write happened in a session. So you need to stick to the primary for some period of time, because otherwise if a replica is lagging, you won't see your own write immediately after.
So they implemented this. Before that, many companies, and I also implemented this logic in several languages, in PHP and Java, in my past. But I see this path finally Rails is going through, and it's obvious to me that they implemented this lag as a constant written in code. It should be configurable. So there was a big battle, and DHH supported my proposal. I was happy to see, like, let's make this configurable.
So maybe we can win 1 more battle, right? Maybe. Maybe. It's not a question, sorry, it's just my story with Rails. Andrew: Yeah, well, it is actually a good, I like that the design of, so this replication lag, there's like a resolver class concept and the documentation talks about, actually I wanna get back to your question too, Michael, about are there any other benefits we didn't really cover? I like how the documentation says, hey, we purposefully have this simple resolver class.
And if you have greater needs, then they've built in an extension point. They've said like, okay, just create your own resolver class, implement this method, and then you can do whatever you want. And if I remember correctly, that's how the automatic replica switching and the shard switching works, is they both have kind of an official extension point so that you do kind of get some default behavior. They took a guess at what a reasonable replica lag would be, I guess. And then, but they said like, if you want to do something else or omit some certain, you don't want to make this type automatic
or you do want to make this type automatic, etc., you could do that in your own implementation class. Just so we could cover this quickly too, Michael, you were asking before about other benefits, like if you do stay kind of within the Rails world. I think like, because I wanted to mention that a lot of these, what I would call enhancements for like higher scale operations are coming from developers at GitHub and Shopify and companies that do have, you know, internet scale operations, and they're
still using Ruby on Rails with their relational database. And they're building a lot of things into ActiveRecord. And then I think those are, you know, great gifts that we receive as users then of the framework, even if we don't have that scale, we have some pathways that we could grow into that if we need. And we also get them the benefit of the ORM there where it's like, well, multiple database adapters are supported by the framework. So you need to make sure that this works in both MySQL, even though they don’t use Postgres at GitHub, from my understanding.
The Active Record capabilities support multiple relational databases. So we kind of get that, even though they're not using Postgres within the framework. Michael: Yeah, that's really cool. And it used to be like, I'm old enough to remember when people used to ask does Rails scale and think, you know, like that, those old questions. And it just feels like we have so many good examples now that doesn't seem to come up as often anymore, which is nice and refreshing. I did actually almost want to ask from the other perspective quickly, though, what, like, as a Postgres community, what can
we keep doing or what can we do better to make sure Postgres stays as the number 1 choice for Rails or how can we make things easier for developers at both large and small organizations? Andrew: Yeah, that's a good question. I do think that there's a little bit of a recent popularity with SQLite within Rails. It's gained some features that I think make it more scalable. And then also, depending on your deployment setup, it may offer you a simpler deployment configuration.
And then on the MySQL side, I think that there's, you know, there's certainly I hear a lot about PlanetScale. They're, they've been doing great with, they're the only, I actually tweeted this a while ago, but like they somehow made foreign key constraints look cool. I don't know if you saw that video or not. They have this well-produced video where they're like, boom, we added foreign key constraints. And I was like, it was actually kind of cool. And I mean, I think that databases are, it's hard to make, to generate a lot of enthusiasm maybe around it.
But I would say that like, you know, some of the companies offering Postgres as a service these days, there’s a number of new startup companies. They're kind of either advertising more like we can help you with multi-tenancy or we can help you with full-text search or we can help you with these other sorts of like capabilities or, You know, usages that Postgres supports. It just might be a lot that you need to kind of build yourself. And so I do think Postgres has like the raw ingredients for a
lot of stuff, but I do think that if you're not going to use a managed service and you want to take this on, you got to do a lot of work to build skills and, you know, like if you want to build in your own full-text search at a good scale with good performance, Postgres has a lot of built-in capabilities and is extensible. You can add extensions. But I think like, you know, just continuing to maybe think about the developer experience, I guess, like at least for Postgres users that are web applications, anywhere the Postgres community
can contribute, you know, guides and tutorials or if companies that use it successfully with some of these use cases, if they can publish on their engineering blogs, like I always love to read those. Like DoorDash is a company I know that is a Rails and Postgres company and has a lot of great engineering blog posts. And I think those are ways that show pathways to leverage some of these capabilities. Yeah, and then I guess I've been thinking about, I think the open source solution still around end to end query observability
is still like, it can sometimes be limited in my experience where I'll like, you know, we, we know of tools like pg_stat_statements, but then we want to collect samples and it can be a little bit difficult. You kind of go back and forth between relying on logs using system catalogs, but Postgres keeps investing in that there's new catalogs and there's new things. And so I think like anytime, any of those sorts of things that make it easier to see what my query workload is like, where are the costs coming from, especially if they're connected to things
I can take action on. Like, Nikolay, you mentioned recently how pg_stat_statements shows the number of rows returned. You could go through and look at queries where you could say, well, we should really probably add limits to these queries. Things like that can make really meaningful... You can draw a real clear line between, like, here's information that's being provided to you within Postgres that helps you out on as you're building and scaling and operating your system. Nikolay: But this approach is reactive. If we see pg_stat_statements on production, it's already happening.
It would be great to try to guess based on Rails code. It's not about migrations already, just regular some code, Rb file, which serves some page or API endpoint. And we can just look at it, we can say, oh, the limit is missing here, right? Yeah. People at GitHub, for example, they build a lot of additional things in CI which help them.
Andrew: Yeah, that's right. Nikolay: I think something can be found in public. So basically, for example, a select plus 1 thing, which is very common for ORM, it can be automatically detected before you deploy. And even if you test it on a very small database, not on a full-size database, or probably workload generated is not yet in production, but we already can see something is dangerous here.
I'm trying to say this is probably not Postgres's job. Andrew: Yeah, it's probably not Postgres's job. I agree with you. I was kind of thinking there's the Ruby open source community, there are some great command-line tools. I'll shout out rails-pg-extras is one. And a common interception point where you might want to take a more of a proactive approach would be when you're creating a migration. Like, oh, do you realize now you've created an inconsistency between your schema and your application? Like maybe you were checking over here it was a string type and
over here you've created an integer type, that kind of thing. Or that's not the most exciting example, but that command-line time, like when the developer is working, is kind of one touch point. Or like you said on CI, you shipped off some code to a system running tests and it starts to detect things and gives the developer feedback before it goes to production, that's always a good thing. Nikolay: Yeah. Well, I'm trying to reflect on my experience with Rails. I can show you another one from 2017 I found. So Shopify is on MySQL, right?
Yep. You mentioned this. So let me describe it. There's a gem or library, I don't know, called Delayed Job. Delayed Job, just a single one. And in 2017, I saw the issue. I had a client in 2017 which was on Rails and on RDS already. It was maybe around the first time I had a good production experience with RDS, which I actually found liking because it provided some good automation for cloning, right?
For experiments. Experiments matter a lot. So I found this problem. It was not performing well, delayed jobs, under scale. So you have a lot of jobs, a lot of entries, also like high insert rate. You need to have a high processing rate. It was super easy for me to find what was the problem and I also found this issue. It was discussed on GitHub since 2013, saying
with half a million jobs it gets really slow. Half a million is not a huge number at all for Postgres, it's a small number. So I popped up saying it's super easy, like you just create an additional index, this 1, and you have already selected for an update, just add 2 words, keep locked. And index plus this, you have a massive boost for your library. You don't need to migrate to Sidekiq or Kafka or something. That's it. You don't need to learn PGQ from Skype.
And they didn't do it. Now I understand, because it's from Shopify, and they're from MySQL world. They started discussing some Postgres versions, and I see that I got a bunch of likes, and I keep having email notifications, people thanking me because this saved their life, because this improved. But it looks like not only we talk about the connection of Ruby and Postgres worlds and the problem of how to connect better, but
also this idea of ORM, let's be abstract, let's keep agnostic from the database. This makes life worse actually. We cannot use what Postgres can offer. What do you think about this? So again, not a question, a reflection of my experience, but maybe it's a good discussion I think. Michael: Well, I don't think you'd be able to use Postgres if there was only, if Rails only supported one relational database it would not be Postgres I don't think, based on who runs the
Rails project. Andrew: Yeah it certainly wasn't the most popular in 2005 when Ruby on Rails was kind of really getting out there for the first time, which now is a long time ago. And yeah, I mean, so it does provide you that indirection point where you can say, well, we have a generic adapter and then we have the MySQL adapter. And yeah, and maybe it doesn't support skip locked. So maybe we can't do skip locked at all because it's not supported across all three of our databases, which is a trade-off. Nikolay: You can write an if.
If it's Postgres, add two words. Andrew: Yeah, and actually that's, there's been, I should have had a couple of examples ready to go, but there's actually a couple of things that are actually only supported in Postgres that ActiveRecord supports, which is pretty exciting because it's like a little bit of a philosophical switch to me where you say like, okay, well we have these really useful capabilities, but we're just going to support them in Postgres only. You know, sorry, MySQL users. And that way then you get the best of both of those categories. But of course, like, then we aren't really getting into this,
but there's a whole discussion about SQL standards and stuff like that. So I know like, for example, the returning clause is not supported in, my understanding is not supported in MySQL. Nikolay: I just checked the docs for MySQL, like latest version, and they already started to support skip locked, so. Okay, yeah. Andrew: Well, and it could be that a framework that chooses to offer conditional support, as long as it's, of course, like, you know, well tested and supported, then that could actually apply pressure to another open source database to add support
for a SQL standard command, right? It's like if this gains support, probably not one framework like Rails, but if everything Rails and Laravel and Prisma and all the language communities are all clamoring for additional support. Nikolay: You mentioned SQL standard, but the problem here is that SQL standard doesn't care about performance at all. Nothing about indexes and SKIP LOCKED is not there. They don't care about performance. And here we talk about performance, right?
So it's not easy sometimes. There should be some addition to standard talking about performance. But I don't see how it can happen. Andrew: Yeah. I think it's great that you've provided that feedback, Nikolay, into the community. I think there is, to the earlier question too, I think like Postgres is going to, you know, has its core set of objectives with each release, you know, where the development effort investment is going and that sort of thing, which is going to be a completely different track than Ruby on Rails.
But like if we have some overlap, you know, the folks that tend to maintain the Postgres adapter code are Postgres fans in the Ruby on Rails community. They happen to also write Ruby. And it's great to have those kind of overlapping folks. There's kind of this discussion around, I know we don't have, we're out of time to get into this, but there's an example that comes to mind too of an in-clause SQL query that has a large list of values. And then some folks notice that from Ruby on Rails and on Postgres.
And that was one where I felt like, I think Nikolay, we might have both tweeted about this at some point, but like there was kind of this crossing the streams for me where I saw people, Postgres people I follow and Ruby on Rails people, were like, you know, we need to do something about this and improve query performance for these types of queries. Nikolay: Right. Also, .plug, or how is it called? Plug, right? This is like when you retrieve everything, then you do something like Postgres can do it much more efficiently.
Don't do work instead of database on application side, right? But I guess it's a small thing, any application language can be used for this not efficient approach. What do you think about the future of Ruby in general? Andrew: I think it's achieved that kind of cockroach status where it just never goes away now. I don't know. There just seems to be enough, not cockroach DB, but the actual insect. There's such a large amount of companies that there's just a
lot of work to maintain the applications if they're successful that use Ruby on Rails. Of course, the growth is not what it was in the early days. But there's a huge community of Rails developers and there are new people entering it. So if someone enters it because maybe it's like their bootcamp program or they joined a company that's using Rails, despite being 20 years old as a framework for Rails anyways. First of all, I think Ruby is a great language outside of Rails, but despite being a 20 year old framework, like there's a very steady development clip.
There's new features being added that are exciting. I just think they tend to be more front end oriented. Like Ruby on Rails these days is trying to capture more attention from what might have been JavaScript only apps before and offer a lot of capabilities on as you're building your web application screens, like giving you a lot of interactivity and low latency, but doing it within Rails and kind of getting back to some more of the full stack kind of application building capabilities.
But there are things happening too on every release related to Postgres and I certainly track those and it still feels active, just not where everyone's flocking to. Michael: I thought the active might be a Rails joke. Andrew: Oh yeah, it could be, yeah. Michael: Yeah. On a serious note though, and thanks so much for all your time, where can folks go to learn more? Andrew: Yeah, If anyone's interested in this topic area and they'd like to explore the book that I wrote, it's at pragprog.com. That's the publishers Pragmatic Programmers.
And I do a fair amount of blogging on Postgres and Rails topics. My blog is andyatkinson.com. And I'm also on most of the social media apps. This year I'm going to be at Sin City Ruby and PG Day Chicago, as well, so probably more for Postgres people. If anyone's at PG Day Chicago, I hope to make it to some more Postgres events, but I love the in-person events too and being Able to meet more community members and learn from other people. Nikolay: And the book is called High Performance PostgreSQL for
Rails, right? Andrew: Yep, that's right. Michael: Awesome work, awesome publisher as well. And thanks again. Andrew: Thanks for having me. A lot of fun, guys. Thank you.
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...
Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...