Hey there! I get that your current id_2 method is dragging down performance because it's loading the entire User table just to find the index of a single record. Let's walk through some better, database-focused solutions to fix this, depending on your needs for real-time accuracy vs. raw speed.

Option 1: Use Database Window Functions (No Extra Storage Needed) #

Instead of loading all users into Ruby to calculate the sequence, let the database handle the sorting and numbering directly with a window function. This avoids full-table memory loads and leverages the database's optimized sorting capabilities.

Instance Method for a Single User #

If you need the sequence number for one user at a time:

class User < ApplicationRecord
  def id_2
    # Use ROW_NUMBER() to calculate the sequence based on created_at
    self.class.select("ROW_NUMBER() OVER (ORDER BY created_at) AS row_num")
              .where(id: self.id)
              .pluck(:row_num)
              .first
  end
end

Batch Query for Multiple Users #

If you often need sequence numbers for a list of users, pull them all in one query:

class User < ApplicationRecord
  def self.with_sequence_numbers
    select("users.*, ROW_NUMBER() OVER (ORDER BY created_at) AS id_2")
  end
end

# Usage: User.with_sequence_numbers.where(active: true).each { |u| puts u.id_2 }

Pros: No extra database columns to maintain, always reflects the current order (even if users are deleted).
Cons: Runs the window function on each query, so it's less ideal for high-frequency calls on very large datasets.

Option 2: Add a Stored Sequence Column (Fastest Queries) #

If you need blazingly fast access to the sequence number (e.g., for frequent display or API responses), add a dedicated column to store the number and keep it updated automatically.

Step 1: Add the Column #

First, generate a migration to add the column:

rails generate migration AddSequenceNumberToUsers sequence_number:integer:index
rails db:migrate

Option 2a: Database Trigger (Best for Concurrency) #

Use a database trigger to handle sequence number assignment automatically—this avoids race conditions in high-concurrency environments and keeps logic outside your Rails code.

For PostgreSQL, run these SQL commands (you can execute them via a migration or directly in your database console):

-- Create a function to set the sequence number
CREATE OR REPLACE FUNCTION set_user_sequence_number()
RETURNS TRIGGER AS $$
BEGIN
  -- Use COALESCE to handle the first user (where MAX is NULL)
  NEW.sequence_number = (SELECT COALESCE(MAX(sequence_number), 0) + 1 FROM users);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach the trigger to run before inserting a user
CREATE TRIGGER trigger_set_user_sequence_number
BEFORE INSERT ON users
FOR EACH ROW EXECUTE FUNCTION set_user_sequence_number();

Initialize Existing Data

Don't forget to backfill the sequence number for existing users:

UPDATE users u
SET sequence_number = sub.row_num
FROM (
  SELECT id, ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
  FROM users
) sub
WHERE u.id = sub.id;

Pros: Zero Rails code changes needed after setup, handles concurrency seamlessly, and queries are just a simple column lookup (user.sequence_number).
Cons: If users are deleted, the sequence will have gaps. If you need strictly continuous numbers even after deletions, stick with the window function approach.

Option 2b: Rails Callbacks (Simpler for Small Apps) #

If you prefer keeping logic in Rails (and don't have high concurrency), use a before_create callback with a table lock to avoid duplicate numbers:

class User < ApplicationRecord
  before_create :set_sequence_number

  private

  def set_sequence_number
    # Lock the table temporarily to prevent race conditions
    self.sequence_number = User.lock.maximum(:sequence_number).to_i + 1
  end
end

Initialize Existing Data

Run this in a Rails console to backfill old users:

User.order(:created_at).each_with_index do |user, index|
  user.update!(sequence_number: index + 1)
end

Pros: No raw SQL needed, easy to understand and modify.
Cons: Table locks can slow down concurrent user creation, and you have to manage the logic in your Rails code.

Quick Decision Guide #

• Use window functions if you need continuous numbers (even after deletions) and don't mind slightly slower queries.
• Use trigger-backed stored column if you prioritize query speed and can tolerate sequence gaps when users are deleted.
• Use Rails callbacks only for small apps with low concurrency where simplicity matters more than peak performance.

内容的提问来源于stack exchange，提问作者Simon