主键选型：Sequences与Identity Columns孰优？适配DB2与PostgreSQL

Great question—since you're building a DB2 database with an eye toward future PostgreSQL migration, picking between sequences and identity columns requires balancing both platforms' behaviors, plus your team's needs. Let's break down the pros and cons of each, with cross-compatibility front and center:

Sequences: Pros & Cons #

Sequences are standalone database objects that generate incremental values, which you can tie to table columns.

Pros #

• Maximum flexibility: You can manually fetch values with NEXTVAL(), configure cache sizes, step increments, start values, and even share a single sequence across multiple tables (though this is rarely recommended for primary keys). Syntax is nearly identical across DB2 and PostgreSQL:

  -- DB2 & PostgreSQL compatible sequence creation
  CREATE SEQUENCE seq_user_ids START WITH 1 INCREMENT BY 1 CACHE 20;
  

• Transparent monitoring: You can easily check the current value of a sequence with CURRVAL('seq_user_ids') on both platforms, making debugging and auditing simpler.
• Explicit control: If you need to manually insert a specific primary key value (e.g., data migrations), you can do so without modifying table definitions—just skip calling NEXTVAL() for that row.

Cons #

• Extra maintenance overhead: Each sequence is a separate object that needs to be created, tracked, and maintained alongside your tables. This adds a small but consistent layer of work compared to identity columns.
• Higher risk of human error: If developers forget to use NEXTVAL() and manually insert values, you'll end up with primary key conflicts. This risk grows when migrating between teams or platforms with different habits.
• Verbose inserts: You have to explicitly call the sequence in your INSERT statements, which adds extra code:

  INSERT INTO users (id, name) VALUES (NEXTVAL('seq_user_ids'), 'Alice');
  

Identity Columns (Generated Always as Identity/Serial): Pros & Cons #

Identity columns are column-level definitions that automatically generate primary key values, using an implicit sequence under the hood.

Pros #

• Clean, concise syntax: Define the identity directly in the table creation statement, no separate sequence setup needed. The GENERATED ALWAYS AS IDENTITY syntax is supported by both DB2 and PostgreSQL 10+ (PostgreSQL's older SERIAL is a sequence wrapper but lacks cross-platform parity):

  -- DB2 & PostgreSQL compatible identity column
  CREATE TABLE users (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(50) NOT NULL
  );
  

• Built-in safety: With GENERATED ALWAYS, you can't manually insert values into the identity column (without overriding the setting), eliminating accidental primary key conflicts.
• Semantic clarity: Anyone looking at the table schema immediately understands that the id column is an auto-generated primary key—no need to hunt for linked sequences.

Cons #

• Less flexibility: By default, each identity column is tied to a single table. Sharing generated values across tables requires manually linking the implicit sequence, which is more cumbersome than using an explicit sequence.
• Hidden object complexity: The underlying sequence is named differently on each platform (DB2 uses SYSIBM.SYSEQ_xxxx; PostgreSQL uses {table}_{column}_seq), making it harder to check current values without first locating the sequence name.
• Legacy PostgreSQL caveat: If your target PostgreSQL environment is pre-10, GENERATED ALWAYS AS IDENTITY isn't supported—you'd have to use SERIAL, which has no direct DB2 equivalent and adds migration work.

Final Recommendation for Your Use Case #

Given your need for DB2 → PostgreSQL compatibility and a 20+ table database:

• Prioritize GENERATED ALWAYS AS IDENTITY: It's clean, secure, and has near-perfect cross-platform syntax for modern PostgreSQL versions. It reduces maintenance overhead and makes your schema easier to understand for all team members.
• Use explicit sequences only for special cases: If you need custom sequence behavior (like cross-table value sharing, non-standard increments, or heavy caching), go with sequences—but stick to syntax that works on both platforms (avoid DB2-only parameters like NO CYCLE if PostgreSQL handles it differently).
• Avoid SERIAL entirely: It's a PostgreSQL-specific syntax sugar that doesn't translate to DB2, so it'll create unnecessary work during migration.

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