A Global Sequence for All Tables

On how to keep order in a random world
Wim den Braven
2025-10-17

whoami (whatami?)

I’ve done programming for a few decades. Ruby on Rails for about 15 years. I am

  • a pragmatic programmer (and a big fan of the pragmatic programmer books1)
  • not a computer scientist (an aerospace engineer actually)
  • a one-person Rails shop developing a one-person Rails app

Philosophy

Paraphrasing a quote attributed to Einstein2, today’s motto is “Make things as simple as possible, but no simpler than that”. Having said that, experience tells me that things are rarely simple in real life. One way to reconcile those two ideas is to think about the first one in terms of architecture and the second one in terms of implementation. In the implementation of real life, there is always one more bug, one more failure mode, and one more edge case…

The App

A while back I started on the third iteration of my end-all-be-all Rails app. One of my goals was (still is) to have a multi-site Rails app to kickstart any future Rails project with core functionality so that the focus can be on whatever business logic and functionality is required for the new app. Putting my money where my mouth is, I’m running both my business website and this blog on the very same app.

Motivation

I am a one-person outfit, probably what dhh had in mind when he talked about Rails being the one-person framework3. But as Bram Jetten points out, one has to wear many hats to pull that off4. One has to write and deploy new code, but that whole codebase has to be kept up-to-date as well. Maintainability is key. Debugability (is that a word?) is key. I want to know what’s going on with my app, and therefore I want to know what happened in my app. I want to see what records are created and in what order. Not just over time, but also within one web request to the app.

Using UUID for id

Before writing any migrations, I had to decide on whether to use bigints or UUIDs for the ActiveRecord primary keys. Nicole Tietz-Sokolskaya explains the different types of UUID5, and Dan Foley explains how to use them6. For some pros and cons of using UUIDs, see Claude’s response below.

It turns out that UUID version 4 is quite suitable for my purpose, and it is supported in Postgres via the pgcrypto extension, which is available for the postgres database on the hosting platform I use. The very first migration for this new app thus looks like this

# db/migrate/20221014213708_enable_extension_pgcrypto.rb
class EnableExtensionPgcrypto < ActiveRecord::Migration[7.0]
  def change
    enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto')
  end
end

The downside of UUIDs

A ‘contra’ of using UUIDs for ids, one surprisingly not recognized by Claude, is that it becomes harder to sort in a meaningful way. Sorting by a UUID-based id creates essentially a random order. Wouldn’t it be nice to be able to sort by order of record creation? Sort by created_at, you might think, but that field is not unique. The rails default for datetime fields is “timestamp(6) without time zone”7, so with a precision of 6, storing times with msec accuracy, it is possible to have two records with the same timestamp. This is easy to reproduce in the Rails console of my app with an insert_all:

attrs = 1000.times.map { GlobalRequestState.current_scope }
Testing::MultiTenant.insert_all(attrs)
Testing::MultiTenant.group(:created_at).count.values.max # => 1000
Testing::MultiTenant.group(:sqnr).count.values.max # => 1

where GlobalRequestState holds current state, per request, including site, tenant, and request-id, but not created_at. The model used here, Testing::MultiTenant, is an ActiveRecord model built-in to the app to facilitate experimentation and testing. More on that below.

I would like to be able to know which records are created, in order, and regardless of the model they are in. What I need, is a global sequence.

Adding a global sequence

Postgres makes it easy to add a global sequence8. In a migration:

# db/migrate/20221014223413_create_sequence_system_sequence.rb
class CreateSequenceSystemSequence < ActiveRecord::Migration[7.0]
  def up
    sql = 'CREATE SEQUENCE IF NOT EXISTS system_sequence AS bigint CYCLE'
    execute sql
  end

  def down
    execute('DROP SEQUENCE IF EXISTS system_sequence')
  end
end

And now that we have such sequence, we can add a sequence-number field to a table. Let’s use that Testing::MultiTenant model as an example:

# db/migrate/20250522230001_create_testing_multi_tenants.rb
# CreateTestingMultiTenants creates the testing_multi_tenants table for the Testing::MultiTenant model
class CreateTestingMultiTenants < ActiveRecord::Migration[8.0]
  def change
    create_table :testing_multi_tenants, id: :uuid do |t|
      t.bigint :sqnr, null: false, default: -> { "nextval('system_sequence')" }, index: true
      t.uuid :rqid, index: true
      t.references :sites_site, null: false, foreign_key: true, type: :uuid
      t.references :tenant, null: false, foreign_key: true, type: :uuid
      t.references :workspaces_workspace, null: false, foreign_key: true, type: :uuid
      t.string :type
      t.string :state, null: false, default: 'initial'
      t.string :name
      t.text :description

      t.timestamps
    end
  end
end

Naming

As summarized by Martin Fowler, one of the two (three?) hard things in computer coding is naming things9. And while my main rule-of-thumb is to use descriptive names, it makes sense to make exceptions for attributes that are more of a bookkeeping nature. As long as it is an, ideally unique, greppable string. Hence the name sqnr, rather than sequence_number. (Naturally, my programming experience with FORTRAN 77, which only allowed variable names up to 6 characters long, has nothing to do with this)

Order control

To order by sqnr by default, add this to ApplicationRecord:

  # app/models/application_record.rb
  self.implicit_order_column = :sqnr

and for good measure, add scopes for ordering by sqnr explicitly:

  # app/models/application_record.rb
  scope :sqnr, -> { order(sqnr: :asc) }
  scope :rnqs, -> { order(sqnr: :desc) }

Of course I could have named to second one sqnr_descending, but, you know…

Things are never simple in real life

In real life there is always one more edge case. For some models, we don’t want to use UUIDs as the primary key, and integer (or bigint) might make more sense. So we need some flexibility when generating migrations. One way to stay flexible on this is to add this to config/application.rb:

  # config/application.rb
  config.generators do |generate|
    generate.orm :active_record, primary_key_type: ENV.fetch('PRIMARY_KEY_TYPE', :uuid).to_sym
  end

and use the PRIMARY_KEY_TYPE environment variable to control the primary key type (integer, bigint, or uuid).

And then there are the cases where we have a polymorphic relationship, and while the foreign-key field (e.g. record_id) is defined as a uuid type, how could we link to records that do not have a uuid primary key? How I solved that may be a good topic for another blog post…

I use formtasic10 for form-building. For some reason it didn’t handle UUID inputs by default, so I added app/inputs/uuid_input.rb:

  # app/inputs/uuid_input.rb
  class UuidInput < Formtastic::Inputs::StringInput
  end

What does Claude say?

I use the Claude Code command line interface as my assistant11. Often helpful. Sometimes not. Here are two prompts related to this blog post with Claude's responses.

About the pros and cons of using UUIDs

Prompt: what are the pros and cons of using uuid for ActiveRecord primary keys?

Response: Here are the main pros and cons of using UUIDs for ActiveRecord primary keys:

Pros

  • Global uniqueness - No collisions across databases or distributed systems
  • Security - IDs aren't sequential, preventing enumeration attacks
  • Scalability - Can generate IDs without database coordination in distributed systems
  • Data privacy - Harder to guess record counts or existence
  • Merging databases - No primary key conflicts when combining datasets

Cons

  • Storage overhead - 16 bytes vs 4-8 bytes for integers
  • Performance impact - Larger indexes, more memory usage, slower joins
  • URL aesthetics - Long, complex URLs instead of clean /users/123
  • Debugging difficulty - Harder to remember and reference specific records
  • Database compatibility - Some older databases have limited UUID support
  • Index fragmentation - Random UUIDs can cause B-tree index inefficiencies (though UUIDv7 addresses this)

For Rails apps, UUIDs work well for public-facing resources where you want to hide implementation details, but integers are often better for internal relationships and admin interfaces where performance and simplicity matter more.

About when the next value is created

Prompt: most tables in @db/structure.sql have an sqnr field that uses the 'system_sequence' db sequence. When does the db pull the next sequence of the sequence queue, when a record is created, or when the transaction that that happens in is saved?

Response: PostgreSQL sequences (like system_sequence) pull the next value when the record is created, not when the transaction commits.

Specifically: - nextval() is called during the INSERT statement execution - The sequence value is consumed immediately and won't be rolled back even if the transaction fails - This means sequence gaps can occur if transactions are rolled back

So if you create 1000 Testing::MultiTenant records rapidly (especially in a single transaction), they'll all get sequential sqnr values from system_sequence at the moment each INSERT executes, regardless of when/if the transaction commits.

This is why sequences are "non-transactional" - they're designed to provide unique values across concurrent transactions without blocking, but at the cost of potential gaps in the sequence.

Footnotes

  1. pragprog.com↩︎

  2. quoteinvestigator.com/2011/05/13/einstein-simple↩︎

  3. world.hey.com/dhh/the-one-person-framework-711e6318↩︎

  4. bramjetten.dev/articles/the-one-person-framework-in-practice↩︎

  5. www.ntietz.com/blog/til-uses-for-the-different-uuid-versions↩︎

  6. dan-foley.medium.com/rails-postgres-uuid-2f14ae1f596d↩︎

  7. www.postgresql.org/docs/current/datatype-datetime.html↩︎

  8. www.postgresql.org/docs/current/sql-createsequence.html↩︎

  9. martinfowler.com/bliki/TwoHardThings.html↩︎

  10. github.com/formtastic/formtastic↩︎

  11. docs.claude.com/en/docs/claude-code/overview↩︎