DEV Community: Andrew Atkinson

What's Coming in Ruby on Rails 7.2: Database Features in Active Record

Andrew Atkinson — Wed, 07 Aug 2024 14:00:00 +0000

Ruby on Rails is currently in major version 7.1 and rolling towards Rails 8, the next comprehensive new release.

Before Rails 8, though, there’s a significant version that will help bridge the gap: Ruby on Rails 7.2.

In this post, we’ll dive into several noteworthy changes in Ruby on Rails 7.2, focusing on the support for database changes in Active Record.

You'll come away with hands-on opportunities to work with these features.

Let's get started!

Active Record Database-Related Features in Ruby on Rails 7.2

The Active Record object-relational mapper (ORM) supports three open-source relational database management systems: MySQL, PostgreSQL, and SQLite. Active Record is most commonly thought of for its ORM facilities, mapping between database operations and database types to Ruby methods and types. However, besides the ORM, Active Record has mechanisms to evolve your database schema definition, capturing incremental changes and a representation of the entire schema in Ruby files or SQL files.

Active Record can connect a single Rails app to multiple databases, and those databases can have distinct roles. The databases might have writer or reader roles or even be “shards” that leverage the Horizontal Sharding feature.

Recently, Beta releases of 7.2 have shipped, allowing developers to preview what’s changed.

As we roll towards Rails 8, we must first pass through 7.2. Let’s examine what's in this release.

What’s New with Composite Primary Keys (CPKs)

Rails 7.1 brought us the first release of composite primary keys (CPKs). What are CPKs? First, let’s talk about unique identifiers in databases more generally. Uniquely identifying rows can take the shape of “natural keys” or “surrogate keys”. Since most databases use surrogate keys, let’s focus on those. Surrogate keys are when “id” integer values are used to uniquely identify a row. This is a surrogate in that it’s not directly related to the data row.

In Rails, the surrogate primary key is usually a column called “id” with an integer type, populated by the database. In PostgreSQL, a sequence object usually provides the value.

What does this have to do with indexes? When we define our id column as the primary key column, this creates a primary key constraint on the table, and constraints have a supporting index. Primary key constraints enforce unique values, and the index helps the lookups performed to enforce that rule to run quickly.

CPKs, or multi-column primary keys, are primary keys where multiple columns uniquely identify a table row. This is a generic mechanism, so it’s up to you as to how you describe your CPK. You may choose to incorporate a surrogate value like the id integer, and combine that with a unique identifier for one of your customers, for example, a customer_id.

You may choose a CPK as a natural key composed of two foreign key columns that point to other tables. This structure is most common with join tables.

With that context in mind, let's arrive at what’s changing in Rails 7.2.

Currently, for the CPK feature, we use the query_constraints keyword on models that relate to other models, and supply a list of foreign key columns that refer to the foreign table primary key columns.

In Rails 7.2, this option changes from query_constraints to foreign_keys. Besides the parameter name change, some interesting discussions are underway about plans to repurpose the original query_constraints name by freeing it up.

If you’d like to play around with single-column surrogate keys, and compare them with composite or multi-column primary keys as a type of natural key, take a look at this Bookshop repo.

Here, we’ve got the models listed in the Rails API documentation for CPKs implemented as a single file Rails app. You can modify the code and database schema design and try out different alternatives.

To try out Rails 7.2 changes, edit the Gemfile portion within the bookshop.rb Ruby file. Use a 7.2 beta version by putting this change into the file, then running bundle install from your terminal:

gem 'rails', '~> 7.2.0.beta1'

Development Containers and Databases in Rails 7.2

Ruby on Rails has always been a productivity-centric framework. One challenge that impacts the developer experience and productivity is creating and maintaining a development environment.

Dev containers intend to fix that. They're used to create reproducible development environments that anyone on your team can run.

Besides reproducibility, another benefit is the isolation of environment dependencies. Some operating system dependencies you use can be particularly challenging to install, or multiple versions might coexist. By isolating dependencies into a container, you can avoid those pitfalls.

Dev containers use Docker, but add a “devcontainer.json” file to the mix. This can describe the text editor configuration and be shared on a team that uses the same editor and configuration.

Let’s generate a new app with a dev container within the bookshop repository. Make sure that Rails 7.2 is the version used by the rails executable, installing the gem if needed.

We'll add --database=postgresql which will configure a Postgres instance in Docker, and install the pg gem for the generated Rails app.

gem install rails -v 7.2.0.beta1 # if needed

rails new myapp --database=postgresql --devcontainer

Open "myapp" in VS Code from the interface or by running code myapp when the code executable is available.

Once in VS Code, if not auto-detected, run "Command-shift-P" and choose “Dev Container: Rebuild in Container”. You'll see “Starting Dev Container”, which starts up Docker as needed.

Click “Show Log” to expand the terminal area, showing log progress. Here, you can tell whether containers are being downloaded and which stage they’re in.

The containers can be viewed and administered using Docker Desktop.

The Rails app, Postgres, and all its gem dependencies should now be running inside the container.

Open a terminal in VS Code and run bin/rails server. Navigate to localhost:3000 in your browser. Since there’s a local port mapping from 3000 into the container, you should see the generated Rails 7.2 app welcome page!

If you run into issues, visit the bookshop repo for more information.

Database Transaction Enhancements, Solid Queue, and Active Job

Transactions are one of the fundamental concepts of relational databases. Databases are designed to support high concurrent access to shared resources, like table row data. For example, multiple clients might try to read and write to the same table row at once. The database consistently processes operations by using transactions with an isolated view of the data.

One of the problems in Rails apps comes when working with relational database transactions and then with another data store like Redis (via background processing with Sidekiq or other Active Job backends).

The issue is one of timing: ensuring data is committed to the relational database before any background work starts.

With Solid Queue — a database-backed queue management system — coming in Rails 8, ensuring transactionally consistent data and operational order is even more important. Transactional consistency errors will become more visible when there's a first-party database-backed queue system.

To prepare for that, what's changed in Rails 7.2? Active Job will now defer enqueuing background jobs until after a database transaction has been committed. This small change ensures no background job processing starts until the database transaction is committed.

Another change in 7.2 is that callbacks will be registered on database transactions.

For example, after_commit can be added within a transaction block to ensure that it runs after the transaction is committed.

Here's the example used in the release notes:

Article.transaction do |transaction|
  article.update(published: true)

  transaction.after_commit do
    # Do work after commit, like send a notification email
  end
end

This approach ensures the code in the after_commit block runs after the article is updated.

New Active Support Instrumentation for Transactions

The Beta 3 version of Rails 7.2 was recently released, and it included new Active Support Instrumentation events we can configure for our applications.

One of these new events is called start_transaction.active_record, and it is triggered when database transactions or savepoints within transactions are started.

Check out the blog post You make a good point! — PostgreSQL Savepoints for more information on savepoints.

When database transactions finish, the event transaction.active_record event is emitted.

What can we do with these events? By creating an Active Support Subscriber that inspects these events and their payloads, we can gain a better understanding of database transaction and savepoint activity within Active Record.

Check out the commit code changes and documentation.

Wrapping Up

In this post, we looked at a few database-related features coming in Ruby on Rails 7.2. We covered the basics of CPKs and an important option name that's changing in 7.2. We also examined how we'll be able to run all of our application dependencies (including our databases) in a dev container.

Finally, we covered how database transactions and database-backed background job systems will tackle the challenge of write operations occurring in the expected order.

Besides those items, there's plenty more to read and learn about Rails 7.2. For example, Ruby 3.1 will become the new default minimum version, there will be support for jemalloc, RuboCop rules, a GitHub CI workflow, and support for progressive web apps (PWAs). To learn more, check out the Rails 7.2 release notes.

Thanks for reading!

P.S. If you'd like to read Ruby Magic posts as soon as they get off the press, subscribe to our Ruby Magic newsletter and never miss a single post!

Upcoming Book Launch: High Performance PostgreSQL for Rails

Andrew Atkinson — Sat, 19 Aug 2023 13:23:14 +0000

Hello! 👋 I’m the author of “High Performance PostgreSQL for Rails” being published by Pragmatic Programmers. The Beta launch is coming up in less than 2 weeks!

Subscribe to 👉http://pgrailsbook.com to be among the first to know when it’s live. You’ll also receive exclusive content and a discount code once it’s live.

Subscribers also get access to summaries of 40+ Ruby gems and PostgreSQL extensions mentioned in the book.

Thanks for taking a look! 😊

Ruby For All Podcast: My Guest Experience 🎙️

Andrew Atkinson — Thu, 02 Feb 2023 15:45:51 +0000

This post has moved!

https://andyatkinson.com/blog/2023/01/19/ruby-for-all-podcast-guest-postgresql

How Long Does It Take To Create An Index?

Andrew Atkinson — Mon, 16 Jan 2023 00:20:06 +0000

A recent tweet asked the following question.

Does anybody know how long it should take to create an index of two integer columns with approximately 110 Million records in the DB? #postgres #postgresql #NotAnDBA

Let's put together an experiment. Create a table with 2 columns populated with 110,000,000 million rows. Each column has a random integer value.

Use the command line psql client that’s included with PostgreSQL to create the index.

To see how long the CREATE INDEX statement will take toggle \timing from psql.

CREATE TABLE t (
    a INTEGER,
    b INTEGER
);

-- Populate 110,000,000 million records
INSERT INTO t(a,b)
SELECT
  (RANDOM() * 1000)::INT,
  (RANDOM() * 1000)::INT
FROM GENERATE_SERIES(1, 110000000) seq;

-- Confirm 110,000,000 records
SELECT COUNT(*) FROM t;
   count
-----------
 110000000

Enable timing to collect the duration of running the CREATE INDEX statement.

-- turn timing on
\timing
Timing is on.

-- Create index "t_a_idx" on table "t" on the "a" column
CREATE INDEX t_a_idx ON t (a);

Time: 52348.022 ms (00:52.348)

Answer

In this test, it took about 52 seconds to create the index.

Test Environment Details

Tested on a M1 MacBook Air with 16GB RAM.
The table has no sequences, constraints, or other indexes
Vacuum was running but was cancelled when the CREATE INDEX started according to the postgresql.log.
No other queries were running on the table.

This post is intended to be a demonstration of how to conduct this sort of experiment on your own and some of the factors that contribute to the time needed for index creation.

The best way to answer questions like this is to create a test on your own server instance or using a separate disconnected instance made from a snapshot.

In a production system with live queries, use the CONCURRENTLY keyword when creating indexes. While this lengthens the time to create the index, it avoids blocking concurrent write operations.

Collect timing info

This experiment could be considered a best case scenario for my machine. The data was loaded into a minimal set of pages, with inserts and no updates or deletes. There were no other queries or background processes running.

A best case scenario gives us a starting point, but the best way to answer this question will be to try this out on your system. Good luck!

Upgrading to PostgreSQL 15 on Mac OS

Andrew Atkinson — Wed, 11 Jan 2023 16:24:27 +0000

This post has moved!

https://andyatkinson.com/blog/2022/12/12/upgrading-postgresql-15-mac-os

Dump and Restore PostgreSQL Tables

Andrew Atkinson — Fri, 16 Aug 2019 17:27:48 +0000

Sometimes it's helpful to dump an exact copy of a database table so it can be loaded elsewhere. How can you do that?

In this example, we will dump and restore a table and assume there is no sensitive content in the table being dumped.

pg_dump can be used to dump table content to a file. By using compression the file size can be kept small even for large tables.

Specify the output format with --format (or -F). The c format is the default. Refer to pg_dump Docs for each of the options and values.

Without compression, the file would be 144MB file from a users table with 1042368 rows. With compression it is 19MB. Compression is continually being improved so this might be even better in newer versions of PostgreSQL or by using external compression programs.

In the example below, the database name is rideshare_development.

Run pg_dump as follows.

pg_dump \
    --format=c \
    --host localhost \
    --table users \
    rideshare_development > users_table.dump

$ du -h users_table.dump
19M    users_table.dump

From the .dump file, the table and all the data rows can be populated into a database where neither exists.

Create a database to create the table and populate it. Call the database temp_load. The command below uses the createdb command line program included with PostgreSQL. The next command loads the content of the dump file using pg_restore.

createdb temp_load;

pg_restore --dbname temp_load users_table.dump

-- Confirm all the rows are there
SELECT count(*) FROM users;
  count
---------
 1042368

That's it! Note that this is only one of the ways to dump and restore table rows. Explore the PostgreSQL documentation for more options.