New index name generation in Rails 7.1

For as long as I can remember Rails has had this little quirk where it will auto generate index names that are too long.

A couple weeks ago Andrew Culver tweeted out his wish for this to be improved.

I know I run into this frequently! And felt motivated to figure it out.

New format in 7.1

I proposed a solution with a new length limit plus fallback format. After a lot of great discussion and help from the Rails core team, we got it merged!

How it works

MySQL, Postgres and SQLite all have different max index lengths. MySQL is 64 characters, Postgres is 63 bytes and SQLite is 62 characters.

To make this work for all of the natively supported database adapters, I set the limit to 62 bytes. I explored making the limit dynamic based on each databases self reported limit. The problem with that is that Rails migrations need to be deterministic. Having an externally defined value that could change (even if unlikely) could result in migrations running differently on different peoples machines.

New fallback format

Now whenever an index is longer than 62 bytes, Rails will fallback to a "short format".

Before:

index_testings_on_foo_and_bar_and_first_name_and_last_name_and_administrator

After:

idx_on_foo_bar_first_name_last_name_administrator_5939248142

The short format includes a hash at the end which is generated from the original long version. This ensures that the index name is unique database wide. Avoiding potential collisions. This is important because some databases (such as Postgres) require indexes to be unique database wide. We didn't want to be trading one problem for another.

Custom adapters

The 62 byte limit is setup to support the database adapters built into Rails. If custom adapters would like the change the limit, they can override the max_index_name_size method to adjust it for their specific needs.

The bounty

Once this was merged in, we donated the $500 to Hack Club. They do incredible work giving students having a place to code together. Something I certainly wish I had when in high school. Thanks to Andrew Culver for the idea and the bounty!