Postgres Goodies in Ruby on Rails 7.1
I just spent last week at Rails World in Amsterdam and had a blast digging back into the Rails and Active Record world. In conversations with developers over the week, I had some notable takeaways from the newest version of Ruby on Rails that I just had to get written up.
A quick summary before we dig in:
async queries: send long-running queries the background while the code runs along, great for pages with multiple long-running queries that can be run in parallel
composite primary keys: native support for using two or more columns as a primary key
common table expression (CTEs): native integration for a subquery for use later in the statement
unlogged tables: native support for disabling Postgres’ WAL logs on a table (mostly for test environments), so that you get better performance on your tests that use databases
value normalization: a native, universal syntax for normalization of values (like downcase of a email column) instead of using
before_validation
Expansion of Async queries
A feature (not a bug IMO) of Ruby is that it has traditionally been used as a blocking (i.e. not-asynchronous) language. While it does have asynchronous capabilities, in the typical use-case people do not have to grok asynchronous workflows to use it effectively.
In the 7.0 release, Active Record added load_async
for loading whole objects. In 7.1, asynchronous queries have been enabled for aggregations and in full SQL queries using async_find_by_sql
.
First, you'll need to define the async_query_executor
in your environment files (config/environments/{development, production}.rb
).
To use a global setting, use something like the following:
config.active_record.async_query_executor = :global_thread_pool
config.active_record.global_executor_concurrency = 5
To use a per-database setting, use something like the following and min_threads
+ max_threads
the database.yml
:
config/environments/{development, production}.rb
config.active_record.async_query_executor = :multi_thread_pool
config/database.yml
development:
adapter: postgresql
pool: 5
max_threads: 5
min_threads: 5
After setting one of those configurations, we can see how the queries work asynchronously:
irb> u = User.async_find_by_sql("SELECT *, pg_sleep(3) FROM users") # this query will sleep for 3 seconds for each record in your database
=> #<ActiveRecord::Promise status=pending>
Then, sometime later you can use the value
syntax to retrieve the value:
# … sometime later …
irb> u.value
=> {returned results}
Once you call the .value
method, if the query has returned, it will return instantly. If the query has not returned, processing is blocked until the query is complete. This is great for dashboards, charts, and reports that generate more complex queries. Send the query to the background, and let it process while you complete other elements of the request.
Composite primary keys
Composite primary keys have been noticeably absent from Ruby on Rails for a while -- unless you chose the CPK gem. Rails 7.1 added two native methods for implementing Primary Keys: database level and application level.
Database Level Composite Primary Keys: When defining the table in the database migration, you can pass an array of column names to the primary_key
attribute. For databases capable of composite primary keys (like Postgres), Active Record will infer from the schema:
create_table :user_accounts, primary_key: [:user_id, :account_id] do |t|
t.belongs_to(:user, foreign_key: true)
t.belongs_to(:account, foreign_key: true)
t.string :role
t.timestamps
end
After running this migration, if you run \d user_accounts
on your Postgres database, you’ll see the following line for the composite key:
"user_accounts_pkey" PRIMARY KEY, btree (user_id, account_id)
Then, when querying with the composite keys, you do the following:
UserAccounts.find([1, 2]) # where user_id = 1 and account_id = 2
In the hypothetical use-case above, we use the composite primary keys for a join table between users and accounts. Typically, in the past I would use an id
column with a unique constraint on the user_id
and account_id
values. Now, with Postgres, we can use the composite primary key for the row
Application Level Composite Primary Keys: Rails documentation calls this a "virtual primary key". And, you’ll want to know that it’s a bit more restrictive than the composite primary keys above because it enforces an explicit foreign key definition on relationships:
class UserAccounts < ActiveRecord::Base
query_constraints :user_id, :account_id
belongs_to :user, foreign_key: :user_id
belongs_to :account, foreign_key: :account_id
end
You have to be explicit on the foreign_key definitions of the relationship, else it tries to find user_id
and account_id
on every related model.
My recommendation is to use Postgres and the native composite primary keys in a database.
Native Support for CTEs
A "CTE" is a "Common Table Expression". A CTE is a type of a nested SQL statement that is defined before the SQL. Below is an example using native-SQL that would find the latest event for each user on an account:
WITH latest_event_per_user AS (
SELECT
user_id,
MAX(event_logs.created_at) AS last_created_at
FROM event_logs
WHERE
event_logs.account_id = 1
GROUP BY 1
)
SELECT
event_logs.user_id,
event_logs.name,
event_logs.created_at
FROM event_logs
INNER JOIN latest_event_per_user ON
event_logs.user_id = latest_event_per_user.user_id AND
event_logs.created_at = latest_event_per_user.last_created_at;
When would you use something like this? Above is a query that returns the latest events for each user on an account. Another common usage of CTEs is when generating the data for charts and reports. Most of the time, generating this data at the SQL level is much faster than generating it using application level logic. Application logic would require some type of N + 1 query, which can be avoided with a more expressive SQL query.
To support CTEs, Active Record added .with()
method for chaining queries. The with()
accepts an Object, which is quite nice when using a large block. Writing the same query from above in Active Record would look like the following:
latest_event_per_user = EventLog.
where(account_id: 1).
group(:user_id).
select(:user_id, "max(event_logs.created_at) AS last_created_at")
el = EventLog.
with(my_cte: latest_event_per_user).
joins("JOIN my_cte ON event_logs.user_id = my_cte.user_id AND event_logs.created_at = my_cte.last_created_at")
My recommendation: if you are SQL-nerd enough to use CTEs, consider using ActiveRecord::Base.connection.execute()
to execute the raw sql. The one time I could see using this Active Record CTE syntax is if you need the chaining capabilities due to conditional query creation.
Support for unlogged tables (test env only!)
Postgres in test environments do not need the persistence that Postgres needs in production. Enter UNLOGGED TABLE
, which does not apply Postgres durability of a table, but improves performance:
# config/environments/test.rb
ActiveSupport.on_load(:active_record_postgresqladapter) do
self.create_unlogged_tables = true
end
ActiveRecord::Base.normalizes
Most modern databases (including Postgres) are case-sensitive by default. And, users have been known to randomly capitalize values. So, it's best to sanitize values. If you've written a Rails application, you've probably written something like the following:
class User < ApplicationRecord
before_validation do
self.email = self.email.strip.downcase
end
end
Now, we have an native way to do this with normalizes
:
class User < ApplicationRecord
normalizes :email, with: -> given_value { given_value.strip.downcase }
end
For those who don't know about the ->
syntax, this is a function with a single argument called given_value
.
Summary
The PostgreSQL, Active Record, and Ruby on Rails communities continue to show investment in features to make this a strong stack for data heavy production applications.
Related Articles
- PostGIS Day 2024 Summary
8 min read
- Crunchy Data Warehouse: Postgres with Iceberg for High Performance Analytics
8 min read
- Loading the World! OpenStreetMap Import In Under 4 Hours
6 min read
- Easy Totals and Subtotals in Postgres with Rollup and Cube
5 min read
- A change to ResultRelInfo - A Near Miss with Postgres 17.1
8 min read