Rails 7.1 limits the maximum length of auto-generated index names

An index is a specialized database structure that facilitates efficient data retrieval operations. It acts as a pointer to the data stored in a table, enabling faster access to specific records without scanning the entire table each time a query is executed. Indexes are particularly beneficial for large tables, where the performance improvement can be significant.

Using database migrations, Rails helps developers create, update tables and add or remove indexes to the tables. But Rails auto-generated index names can run into the name is a too long error. Let's explore the issue in detail.

Before Rails 7.1

Let's say you have a Rails application with a PostgreSQL database, where you added an Employee model using migration as shown below:

class CreateEmployees < ActiveRecord::Migration[7.0]
  def change
    create_table :employees do |t|
      t.string :first_name
      t.string :middle_name
      t.string :last_name
      t.integer :department_id
      t.string :designation
      t.date :joining_date
      t.integer :salary
    end

    add_index :employees, [:department_id, :designation, :joining_date]
  end
end

As no index name is passed to the add_index command, Rails generates the index name as index_employees_on_department_id_and_designation_and_joining_date. The index name length is 65, bigger than the PostgreSQL limit of 63. When you run the migration, it fails with below error:

ArgumentError: Index name 'index_employees_on_department_id_and_designation_and_joining_date' on table 'employees' is too long; the limit is 63 characters.

To fix the issue in the Rails version before 7.1, you must pass a custom index name to the migration.

class CreateEmployees < ActiveRecord::Migration[7.0]
  def change
    create_table :employees do |t|
      t.string :first_name
      t.string :middle_name
      t.string :last_name
      t.integer :department_id
      t.string :designation
      t.date :joining_date
      t.integer :salary
    end

    add_index :employees, [:department_id, :designation, :joining_date], name: "index_employees_on_department_id_designation_and_joining_date"
  end
end

The database migration passes without raising any errors.

In Rails 7.1

Rails 7.1 limits the maximum length of auto-generated index names. The length is set to 62 to ensure it works for the default configurations of SQLite, MySQL and PostgreSQL.

• MySQL: 64
• Postgres: 63
• Sqlite: 62

If the index name length is shorter than or equal to 62, Rails does not alter it. If the length exceeds 62, the index name is modified. You need to add the migration and not worry about the index name anymore. Rails will handle the index name length.

class CreateEmployees < ActiveRecord::Migration[7.0]
  def change
    create_table :employees do |t|
      t.string :first_name
      t.string :middle_name
      t.string :last_name
      t.integer :department_id
      t.string :designation
      t.date :joining_date
      t.integer :salary
    end

    add_index :employees, [:department_id, :designation, :joining_date]
  end
end

The index name index_employees_on_department_id_designation_and_joining_date will be modified by Rails to idx_on_department_id_designation_joining_date_18832e3a17. The 18832e3a17 are the first 10 letters of SHA256 hexdigest of [:department_id, :designation, :joining_date].

column_names = [:department_id, :designation, :joining_date]

hashed_identifier = OpenSSL::Digest::SHA256.hexdigest(column_names).first(10)

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