Why Indexing Matters for Enums #

Enums are stored as integers in the database, and since they are frequently used in queries, indexing the enum column can significantly improve query performance.

When you query records using enums, Rails translates symbols into integers behind the scenes:

User.where(status: :active) # Translates to: SELECT * FROM users WHERE status = 0;

Without an index, searching for status: :active requires scanning the entire users table. With an index, lookups are much faster.

If the status column is not indexed, the database must perform a full table scan, checking every row one by one.

Database Query Without an Index (Slow Performance) #

EXPLAIN ANALYZE SELECT * FROM users WHERE status = 0;

Output:

Seq Scan on users  (cost=0.00..123456.78 rows=50000 width=123)

This indicates a sequential scan, meaning the database checks every row instead of using an index. This is inefficient for large datasets.

Adding an Index to Enum Columns #

To optimize queries, we should add an index to the enum column using a database migration.

Step 1: Add an Index in a Rails Migrationruby #

class AddIndexToUsersStatus < ActiveRecord::Migration[7.0]
  def change
    add_index :users, :status
  end
end

Now, the status column is indexed, allowing faster lookups.

Step 2: Run the Migration #

rails db:migrate

Query Performance After Indexing #

After indexing, let’s re-run the database query.

Database Query with an Index (Optimized Performance) #

EXPLAIN ANALYZE SELECT * FROM users WHERE status = 0;

Output:

Index Scan using index_users_on_status  (cost=0.42..500.75 rows=500 width=123)

Now, the database uses an Index Scan, which is significantly faster than a sequential scan.

Performance Improvement Breakdown #

By adding an index, we reduce query execution time from 120ms to just 5ms, making the system 25x faster! 🚀