Rails 7.1 adds the ability to defer the unique constraints in PostgreSQL

By default, PostgreSQL constraints are checked after each row is inserted or updated. This means that if you run multiple queries in a transaction, and one of those queries violates a constraint, the constraint will be enforced, and the transaction will fail.

Let's say you have a TODO application built in Rails. You can drag and drop items to reorder them. The application has a List model with an order attribute. There is a unique constraint on the order column. You have the following entries in the List model based on your TODOs:

first_item = List.create!(order: 1)
second_item = List.create!(order: 2)

If you want to change the order of the above todo's, you might implement the code as below:

List.transaction do
  first_item.update!(order: 2)
  second_item.update!(order: 1)
end

The above transaction will fail with a UNIQUE key error on executing the first update! statement.

-- ERROR: duplicate key value violates unique constraint "lists_order_key"
-- DETAIL: Key (order)=(2) already exists.

Before Rails 7.1

PostgreSQL supports deferring constraint checks until the end of a transaction. This means the database will not check the constraints until the transaction is committed.

Prior to Rails 7.1, there was no built-in support for deferrable constraints in Rails. This meant that you had to write raw SQL queries to implement deferrable constraints.

class AddUniqueIndexOnList < ActiveRecord::Migration[6.0]
  def up
    execute <<-SQL
      alter table lists
        add constraint unique_list_order unique (position)
        DEFERRABLE IMMEDIATE;
    SQL
  end

  def down
    execute <<-SQL
      alter table lists
        drop constraint if exists unique_list_order;
    SQL
  end
end

The approach works fine, but it is more error-prone to write raw SQL queries. This is because you need to be mindful of the syntax, which is less readable than Rails methods.

In Rails 7.1

Rails 7.1 adds the ability to defer the unique constraints in PostgreSQL. You can use the add_unique_key command and pass the deferrable option to do this. The deferrable option can take two values: immediate and deferred.

1. deferrable: :immediate

class AddUniqueIndexOnList < ActiveRecord::Migration[7.1]
  add_unique_key :lists, [:order], deferrable: :immediate, name: "unique_list_order"
end

Setting the deferrable option to :immediate does not change the behaviour of the above example. The constraint will still be checked immediately after the first update query. However, you can manually defer the check using the SET CONSTRAINTS ALL DEFERRED command within the transaction. This will cause the unique constraints to be checked after the transaction has been committed.

List.transaction do
  ActiveRecord::Base.connection.execute('SET CONSTRAINTS ALL DEFERRED')

  first_item.update!(order: 2)
  second_item.update!(order: 1)

  # NOTE: Clear `SET CONSTRAINTS` statement at the end of transaction.
end

You can also defer specific constraints using the SET CONSTRAINTS statement with a list of constraint names. For example:

List.transaction do
  ActiveRecord::Base.connection.execute('SET CONSTRAINTS unique_list_order DEFERRED')

  first_item.update!(order: 2)
  second_item.update!(order: 1)

  # NOTE: Clear `SET CONSTRAINTS` statement at the end of transaction.
end

2. deferrable: :deferred

The deferrable: :deferred option tells PostgreSQL to defer the check of the unique constraint on the order column of the lists table until the end of the transaction.

class AddUniqueIndexOnList < ActiveRecord::Migration[7.1]
  add_unique_key :lists, [:order], deferrable: :deferred
end

For the TODO application, deferrable is the best way to implement the drag-and-drop flow. deferrable feature can be used when bulk importing large data or importing data files of SQL statements that are not in the expected order.

In some cases, it may be beneficial to defer unique constraints in order to improve the performance of queries that do not need to enforce the constraints. For example, if you are querying a table for all rows with a certain value, you may not need to enforce the unique constraint on that column. By deferring the constraint, the database can avoid checking it for each row in the result set, which can improve the performance of the query.

NOTE: PostgreSQL allows developers to create unique constraints on top of the unique index that cannot be deferred. Even if the developer creates a deferrable unique constraint, the existing unique index does not allow it to violate uniqueness within the transaction. If you want to change the existing unique index to deferrable, you need to execute remove_index before creating deferrable unique constraints.

To know more about this feature, please refer to this PR.