What the new Rails 7.1 brings for Postgres users

Today, we're going to talk about the new Ruby on Rails 7.1 release and what it brings for Postgres users. This is 5mins of Postgres episode 87.

Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel.


Rails 7.1 and Postgres

Usually, we don't speak about language specific or framework specific changes on 5mins of Postgres because you might be using Go, you might be using Java, you might be using Python, or you might be using Ruby, but not everybody uses the same framework. I do like to make an exception here because I personally use Rails and I do think that the new Rails 7.1 release brings a good number of changes that will be helpful for a lot of people.

And so if you're not using Rails, feel free to skip this episode. If you are using Rails, I think there's some good nuggets here.

We'll start with this blog post by Christopher Winslett on the Crunchy Data blog. Christopher talks about a few of the changes that have come in the recent Rails 7.1 release, which was just released a couple weeks ago.

Improvements to async queries in Rails 7.1

Christopher starts by focusing on async queries. Async queries have been added in Rails 7.0, the previous release, and were significantly improved in this release.

If you're familiar with the Postgres side of this, we often talk about pipelining. Pipelining is a function of the client where the client essentially is able to send the next query without waiting for the result of the former query.

What Rails does here is not the same as pipelining though. This is essentially a thread pool. So you're saying, I want to run five threads that are able to execute queries. And then when you call an explicit async function in the Ruby API, it issues a query asynchronously, letting the rest of the code continue, and then only when you access the query result, it waits on that thread to succeed and returns the result.


This is already in previous Rails releases, but what was added now was a way to just run custom SQL with that same async code using the find_by_sql function. This is quite useful because when I use Rails, oftentimes I find myself wanting to actually run my own SQL instead of relying on the Rails ActiveRecord ORM.

How Rails handles ActiveRecord load_async

If you are interested in the async handling in Rails, Pawel Urbanek wrote a good blog post about this a while ago in the Rails 7.0 release, where he talks about how to use this async functionality for running queries in Rails.

Composite primary keys in Rails 7.1

The other thing that I think is quite exciting, which I've heard from many people using Rails over the years, is that Rails did not have support for using composite primary keys. A composite primary key is one where you have more than one column as your primary key.

Oftentimes in Postgres that makes sense, because if you have a join table between two different tables, you might not want to add an "id" column to the join table. Instead you could just have the ids of the respective tables be the primary key for that join table. And so with Rails 7.1 you can actually finally use composite primary keys officially in Rails. You no longer need to rely on third party libraries to support that.

Now you can create your tables and your migrations, and Rails will know what to do when you pass it multiple primary keys when you're finding records. Quite useful feature that I'll see myself using as well.

Compiling Common Table Expressions in Postgres with ActiveRecord

For those of you who don't want to write your own queries, there is a way to compile CTEs, common table expressions in Postgres, with Rails ActiveRecord. Now, this is something that I personally am not going to use. When I'm writing a CTE, I don't think an ORM will be able to represent it well, but sometimes there are use cases where you want to do query building with the ActiveRecord primitives. This lets you stay in that world without having to write your own SQL formatter.

Faster test suites with UNLOGGED tables

There's also a nice improvement on making your test suite faster by using UNLOGGED tables. This is something that's generally applicable and that you could use with other frameworks as well. In your test suite, usually you don't need the Postgres WAL functionality. What this does is that Rails will create your tables as UNLOGGED tables, which means that the tables do not create WAL records, which means that it is much faster to create the tables, update them, and delete them again. Which you might do a lot when you're running your test suite. That should be a nice performance improvement.

Download Free eBook: How To Get 3x Faster Postgres

SQLCommenter in Rails 7.1

I noticed one other thing in Rails 7.1 that wasn't pointed out here yet.

This is very specific, but I think it will be helpful for those of you who use Rails in production and who want to make sure to understand where your queries are coming from when they hit the database side. Rails 7 introduced a feature called query logs, where they bundled what was previously in a gem called "marginalia" into the core functionality of Rails.

You can use query logs to say: I want to add comments to my queries that attach certain application side information. And this helps you when you're looking at the database side of the house. Imagine you're using pganalyze, for example, you're looking at a query in pganalyze, we use these query tags to highlight to you where in your application a query is coming from.

This is very useful, and you can enable this quite easily now in Rails, by just turning on this query_log_tags_enabled flag. That's great to have.

Now, what Rails 7.1 introduced was using SQLCommenter. SQLCommenter is a project that standardizes how query tags are structured.

SQLCommenter syntax as opposed to marginalia syntax

Previously Rails was using the older "marginalia" syntax, but now they switched to the official, more standard syntax of SQLCommenter. For comparison, previously you might've seen a query tag like this, where it's using the old style syntax. Now you have the newer style syntax, which essentially uses URL encoding for the values. This is very useful if you have more complex tags that might contain Unicode characters and things like that. They would also be supported now with this SQLCommenter syntax being used by default in Rails 7.1.

This is a best practice that I would recommend everybody adopt. Be it Rails, or other frameworks. Make sure your queries are annotated with the origin, where in the application they're coming from, so that you can understand better which part of the application is slow based on a slow query performance.

All in all, I think Rails 7.1 is quite exciting for those of us who use Rails. I think as a Postgres user, it's great to see interest in making Postgres work well, and I look forward to using it myself.

This was episode 87 of 5mins of Postgres! Subscribe to our YouTube channel, sign up for our newsletter and follow us on LinkedIn and Twitter to get updates about new episodes!

What we have discussed in this episode of 5mins of Postgres

Enjoy blog posts like this?

Get them once a month to your inbox