/aurora-dsql

MySQL to DSQL: AUTO_INCREMENT Migration

Part of MySQL to DSQL DDL Migration. See Common Verify & Swap Pattern for the shared migration end-pattern.

AUTO_INCREMENT Migration

MySQL syntax:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255)
);

DSQL provides three options for replacing MySQL's AUTO_INCREMENT. Choose based on your workload requirements. See Choosing Identifier Types in the scaling guide for detailed guidance.

When choosing integer auto-increment, ALWAYS use GENERATED AS IDENTITY (not SERIAL, which DSQL does not support). UUIDs (Option 1) remain the recommended default.

Option 1: UUID Primary Key (Recommended for Scalability)

UUIDs are the recommended default because they avoid coordination and scale well for distributed writes.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255)
);

DSQL: gen_random_uuid() is built-in; do NOT run CREATE EXTENSION pgcrypto. DSQL ships PostgreSQL 16's core gen_random_uuid(), so the extension is unnecessary AND CREATE EXTENSION is rejected by DSQL (ERROR: unsupported statement: CreateExtension). Other pgcrypto functions (crypt(), digest(), hmac(), etc.) are unavailable — implement those at the application layer.

Option 2: IDENTITY Column (Recommended for Integer Auto-Increment)

Use GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY when compact, human-readable integer IDs are needed.

DSQL: CACHE is mandatory. DSQL has no implicit default and rejects identity columns declared without it: ERROR: identity column is not supported without an explicit cache size. please define CACHE greater than or equal to 65536 or equal to 1. A migration tool replaying a vanilla PostgreSQL dump (where CACHE defaults to 1) will fail at the first IDENTITY column. Always include (CACHE 1) for strict ordering or (CACHE 65536) (or higher) for high-throughput workloads — see scaling-guide.md.

-- GENERATED ALWAYS: DSQL always generates the value; explicit inserts rejected unless OVERRIDING SYSTEM VALUE
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY (CACHE 65536) PRIMARY KEY,
  name VARCHAR(255)
);

-- GENERATED BY DEFAULT: DSQL generates a value unless an explicit value is provided (closer to MySQL AUTO_INCREMENT behavior)
CREATE TABLE users (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 65536) PRIMARY KEY,
  name VARCHAR(255)
);

Choosing a CACHE Size

REQUIRED: Specify CACHE explicitly. Supported values are 1 or >= 65536.

• CACHE >= 65536 — High-frequency inserts, many concurrent sessions, tolerates gaps and ordering effects (e.g., IoT/telemetry, job IDs, order numbers)
• CACHE = 1 — Low allocation rates, identifiers should follow allocation order closely, minimizing gaps matters more than throughput (e.g., account numbers, reference numbers)

Option 3: Explicit SEQUENCE

Use a standalone sequence when multiple tables share a counter or when you need nextval/setval control.

-- Create the sequence (CACHE MUST be 1 or >= 65536)
CREATE SEQUENCE users_id_seq CACHE 65536 START 1;

-- Create table using the sequence
CREATE TABLE users (
  id BIGINT PRIMARY KEY DEFAULT nextval('users_id_seq'),
  name VARCHAR(255)
);

Migrating Existing AUTO_INCREMENT Data

To UUID Primary Key

CREATE TABLE users_new (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id INTEGER,  -- Preserve original AUTO_INCREMENT ID for reference
  name VARCHAR(255)
);

INSERT INTO users_new (id, legacy_id, name)
SELECT gen_random_uuid(), id, name
FROM users;

If other tables reference the old integer ID, update those references to use the new UUID or the legacy_id column.

To IDENTITY Column (Preserving Integer IDs)

-- Use GENERATED BY DEFAULT to allow explicit ID values during migration
CREATE TABLE users_new (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 65536) PRIMARY KEY,
  name VARCHAR(255)
);

-- Migrate with original integer IDs preserved
INSERT INTO users_new (id, name)
SELECT id, name
FROM users;

-- Set the identity sequence to continue after the max existing ID
-- Get the max ID first:
SELECT MAX(id) as max_id FROM users_new;
-- Then reset the sequence (find the sequence name via:
--   SELECT pg_get_serial_sequence('users_new', 'id');):
SELECT setval('users_new_id_seq', (SELECT MAX(id) FROM users_new));

Verify and swap (see Common Pattern)