Support PostgreSQL identity columns as the primary key generation strategy

rubyonrails-core
, ,
1

Rails currently creates PostgreSQL bigint primary keys using bigserial:

id bigserial primary key

I would like to propose first-class support for PostgreSQL identity columns as an alternative primary-key generation strategy:

id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

Motivation

PostgreSQL has supported identity columns since PostgreSQL 10.

The PostgreSQL documentation describes serial / bigserial as a PostgreSQL-specific way to create an autoincrementing column, and points to the SQL-standard identity-column feature as the alternative:

bigserial is not a true PostgreSQL type. It is shorthand that creates a bigint column, a sequence, and a DEFAULT nextval(...).

For modern PostgreSQL schemas, the equivalent identity form expresses the generation behavior directly:

id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

Rails already understands PostgreSQL identity columns at runtime and during schema introspection, but Rails migrations still default to:

NATIVE_DATABASE_TYPES = {
  primary_key: "bigserial primary key",
  # ...
}

See the current PostgreSQL adapter implementation:

The adapter also already reports support for identity columns via supports_identity_columns?.

So the missing piece is primarily on the DDL / migration-generation side: applications should be able to ask the PostgreSQL adapter to create normal Rails bigint primary keys using IDENTITY without resorting to raw SQL.

Proposed API

I think this should be configurable at the PostgreSQL adapter level, similarly to the existing datetime_type configuration.

Rails currently allows applications to do:

ActiveSupport.on_load(:active_record_postgresqladapter) do
  self.datetime_type = :timestamptz
end

A similar API could be introduced for primary-key generation:

ActiveSupport.on_load(:active_record_postgresqladapter) do
  self.primary_key_generation = :identity
end

The default could remain:

:serial

for backwards compatibility.

With the default Rails behavior:

create_table :users do |t|
  t.string :name
end

Rails would continue to generate:

CREATE TABLE "users" (
  "id" bigserial primary key,
  "name" character varying
);

With:

ActiveSupport.on_load(:active_record_postgresqladapter) do
  self.primary_key_generation = :identity
end

the same idiomatic migration would instead generate:

CREATE TABLE "users" (
  "id" bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "name" character varying
);

This keeps the Rails migration API unchanged while allowing the PostgreSQL adapter to choose the PostgreSQL representation.

Why primary_key_generation rather than primary_key_type

I initially considered an API such as:

self.primary_key_type = :identity

but serial versus identity is not really a difference in the underlying data type.

Both ultimately use a bigint column. What differs is the mechanism used to generate values.

For that reason, something like:

self.primary_key_generation = :serial
self.primary_key_generation = :identity

seems more precise.

It also avoids confusion with existing Rails concepts such as primary_key_type: :uuid, where the actual logical column type changes.

Why GENERATED BY DEFAULT

For Rails’ normal primary-key behavior, I think:

GENERATED BY DEFAULT AS IDENTITY

is a better fit than:

GENERATED ALWAYS AS IDENTITY

PostgreSQL documents that BY DEFAULT allows an explicitly supplied value to take precedence, while ALWAYS rejects an explicit value unless the statement uses OVERRIDING SYSTEM VALUE.

Rails applications and tooling can legitimately provide explicit IDs in cases such as fixtures, imports, data migrations, replication workflows, or restoring existing data.

BY DEFAULT therefore seems closer to the behavior Rails applications already expect from bigserial.

See:

Per-table override

It may also be useful to support an explicit per-table override.

For example:

create_table :users, id: :identity do |t|
  # ...
end

or another API that fits Active Record’s existing type system.

The application-level adapter setting would define the normal default, while individual migrations could override it when necessary.

Conceptually:

PostgreSQL adapter default
    :serial

Application configuration
    primary_key_generation = :identity

Per-table migration override
    id: :serial / id: :identity

The exact migration syntax is less important to me than having the adapter-level configuration, because applications that choose identity columns generally want that behavior consistently rather than repeating an option on every create_table.

Backwards compatibility

I see two possible approaches.

The conservative option is to keep bigserial as the Rails default indefinitely and make identity opt-in:

ActiveSupport.on_load(:active_record_postgresqladapter) do
  self.primary_key_generation = :identity
end

Another option would be to make IDENTITY the default for a future migration version, while preserving bigserial behavior for older migration versions through Active Record’s migration compatibility layer.

I think the first step can simply be first-class, configurable support. Whether identity should later become the default can be considered separately.

Existing Rails support and related work

There has already been work in Active Record to correctly handle PostgreSQL identity columns.

Identity values not being returned

rails/rails#49502 — ActiveRecord 7.1.0 does not return and assign primary keys that are generated identity columns

This issue demonstrated Active Record being used with both:

GENERATED BY DEFAULT AS IDENTITY

and:

GENERATED ALWAYS AS IDENTITY

Runtime support fix

rails/rails#49504 — Fix auto populating IDENTITY columns for PostgreSQL

This PR fixed Active Record’s handling of identity-generated primary keys and was merged.

Earlier discussion involving PostgreSQL identity

rails/rails#33194 — Migrations with auto_increment fail on default postgresql database

This older issue explicitly discussed PostgreSQL identity columns as the PostgreSQL equivalent for automatically generated IDs.

GENERATED ALWAYS / override behavior

rails/rails#36940 — PostgreSQL IDENTITY and OVERRIDE clause for Arel/AR INSERT

This issue discussed the interaction between GENERATED ALWAYS AS IDENTITY and explicit ID insertion.

Identity columns and composite primary keys

rails/rails#51821 — Activerecord: Insert fails on composed_primary_key model with id as not null Identity column

This is another example of Active Record already needing to understand identity-column semantics at runtime.

These issues and fixes show that identity columns are already a supported PostgreSQL schema concept in Active Record. The proposal here is to make creation of those columns first-class in the normal Rails migration path as well.

Questions

I would especially appreciate feedback on these points:

  1. Does an adapter-level setting such as PostgreSQLAdapter.primary_key_generation fit Active Record’s configuration model?
  2. Are :serial and :identity reasonable values for that setting?
  3. Should GENERATED BY DEFAULT AS IDENTITY be the identity behavior Rails generates by default?
  4. Should there also be a per-table migration override?
  5. Should identity remain opt-in, or would it make sense to consider it as the default for a future migration compatibility version?

My main goal is not to change Rails’ logical default primary-key type away from bigint.

The goal is to allow PostgreSQL applications to use the modern SQL-standard identity mechanism for that bigint primary key through idiomatic Rails migrations:

bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

instead of requiring raw SQL or continuing to rely on the PostgreSQL-specific bigserial shorthand.