One of the new features released in PostgreSQL 18 is native support for UUIDv7. These UUIDs are time-ordered allowing for the randomness of UUIDs without the fracturing of btree indexes as well as the ability to more accurately sort rows by date using the primary key. While Ruby on Rails supports UUIDs as primary keys, it's only UUIDv4. That said, you don't need to upgrade your database or wait for a patch in Rails to take advantage of this new feature today.

To accomplish this, we'll perform the following steps:

1. Create a custom database function to generate a UUIDv7 value with the same precision that PostgreSQL 18 uses (millisecond precision + sub-millisecond timestamp + random).
2. Update existing default values on existing primary keys to use the new function instead of generating UUIDv4 values.
3. See how to apply the custom function to new tables going forward.

In order to do this, one prerequisite is you either need a gem that supports custom functions in schema.rb or set config.active_record.schema_format = :sql in your application.rb to generate a structure.sql file.

There are a few gems out there that help manage custom database functions, but for this exercise, we'll create the function directly in a migration.

class CreateCustomUuidv7Function < ActiveRecord::Migration[8.1]
  def up
    execute <<~SQL
      CREATE FUNCTION generate_uuidv7(timestamptz DEFAULT clock_timestamp()) RETURNS uuid
      AS $$
      SELECT ENCODE(
        SUBSTRING(int8send(FLOOR(t_ms)::int8) FROM 3) ||
        int2send((7<<12)::int2 | ((t_ms-floor(t_ms))*4096)::int2) ||
        SUBSTRING(uuid_send(gen_random_uuid()) FROM 9 FOR 8)
        , 'hex')::uuid
        FROM (SELECT extract(epoch FROM $1)*1000 AS t_ms) s
      $$ LANGUAGE sql volatile parallel safe;
    SQL
  end

  def down
    execute "DROP FUNCTION IF EXISTS generate_uuidv7(timestamptz);"
  end
end

The above is mostly lifted directly from https://github.com/fboulnois/pg_uuidv7 which is an user-created extension for PostgreSQL. Naming the function generate_uuidv7 allows it to live side-by-side and not conflict with the PostgreSQL 18 uuidv7() function when/if the database is ever upgraded.

After migrating, you can see the function working in a psql console:

mydb=# SELECT generate_uuidv7();
           generate_uuidv7            
--------------------------------------
 019a68e7-ee62-7702-ad64-d0c724ea72f8
(1 row)

Next, this migration will convert existing UUID primary keys in all tables to use the new function to generate UUIDv7 primary keys going forward:

class ConvertIdColumnsToUseCustomUuidv7Function < ActiveRecord::Migration[8.1]
  def up
    ActiveRecord::Base.connection.tables.each do |table|
      pk = ActiveRecord::Base.connection.primary_key(table)
      next unless pk

      column = ActiveRecord::Base.connection.columns(table).find { |col| col.name == pk }
      next unless column.sql_type_metadata.type == :uuid

      execute <<-SQL.squish
        ALTER TABLE #{table}
        ALTER COLUMN #{pk}
        SET DEFAULT generate_uuidv7();
      SQL
    end
  end

  def down
    ActiveRecord::Base.connection.tables.each do |table|
      pk = ActiveRecord::Base.connection.primary_key(table)
      next unless pk

      column = ActiveRecord::Base.connection.columns(table).find { |col| col.name == pk }
      next unless column.sql_type_metadata.type == :uuid

      execute <<-SQL.squish
        ALTER TABLE #{table}
        ALTER COLUMN #{pk}
        SET DEFAULT gen_random_uuid();
      SQL
    end
  end
end

For future tables, you can use the default: option in create_table to properly tell ActiveRecord to use the new function to auto generate the primary key.

class CreatePosts < ActiveRecord::Migration[8.1]
  def change
    create_table :posts, id: :uuid, default: -> { "generate_uuidv7()" } do |t|
      t.string :title
      t.string :body

      t.timestamps
    end
  end
end

Finally, when/if the database is upgraded to PostgeSQL 18 or later, if you wish you can convert the existing tables to use the native uuidv7() function and drop the custom function.

class ConvertIdColumnsToUseNativeUuidv7Function < ActiveRecord::Migration[8.1]
  def change
    ActiveRecord::Base.connection.tables.each do |table|
      pk = ActiveRecord::Base.connection.primary_key(table)
      next unless pk

      column = ActiveRecord::Base.connection.columns(table).find { |col| col.name == pk }
      next unless column.sql_type_metadata.type == :uuid

      execute <<-SQL.squish
        ALTER TABLE #{table}
        ALTER COLUMN #{pk}
        SET DEFAULT uuidv7();
      SQL
    end

    execute "DROP FUNCTION IF EXISTS generate_uuidv7(timestamptz);"
  end
end

References:

• PostgreSQL 18 uuid functions
• 3rd party UUIDv7 PostgreSQL extension