Aurora DSQL supports IDENTITY / SEQUENCE - I also verified methods for migrating existing tables

On February 13, 2026, Amazon Aurora DSQL announced support for IDENTITY columns and SEQUENCE objects.

https://aws.amazon.com/jp/about-aws/whats-new/2026/02/amazon-aurora-dsql-adds-identity-columns-sequence/

This update enables automatic integer ID generation on the database side. As I was just implementing an alternative solution in a system under development, I'll introduce the actual use case along with it.

Update Overview

The following features are now available in Aurora DSQL:

• GENERATED AS IDENTITY (IDENTITY columns)
• CREATE SEQUENCE / nextval() (Sequence objects)

You can use the standard SQL syntax compatible with PostgreSQL as is. Available in all regions where Aurora DSQL is offered.

Use Case: ID Numbering Problem in AI Tag Management Table

Table Structure

I'm developing a blog article management system where LLM automatically generates tags for each article. Since multiple tags are linked to a single article (1:N), the tag table needed an independent primary key (surrogate key).

Why Numbering Was a Problem

Aurora DSQL did not previously support SERIAL or SEQUENCE.

The method of "numbering with SELECT MAX(id) + 1" is an anti-pattern. ID duplications can occur during concurrent execution, and the scan cost of MAX(id) increases with record growth. Inconsistencies can also occur depending on the transaction isolation level.

Previous Solution: ID Management with DynamoDB

We had set up a numbering management table in DynamoDB and issued IDs exclusively using Atomic Counter (ADD operation with UpdateItem).

We chose DynamoDB because Atomic Counter updates are atomic and locks are reliable, allowing safe numbering without loading DSQL.

However, this increased dependency on external services and made it complex to ensure transactional consistency between numbering and INSERT operations. Also, to handle exclusive control for numbering, we limited tag assignment processing to one concurrent execution, preventing parallel processing. This created throughput challenges for linking tags to large numbers of articles, particularly when batch-tagging thousands of past articles.

Before (DynamoDB numbering method):

After (IDENTITY method):

With numbering completed on the DB side, the numbering table becomes unnecessary. Parallel execution becomes possible, allowing throughput improvement with Step Functions Map processing and removing concurrent execution limits.

Practical Testing: Trying IDENTITY Columns

Basic Operation

I created a test table to verify automatic numbering, explicit ID specification, and bulk INSERT.

As a DSQL-specific constraint, explicitly specifying CACHE size is mandatory. Omitting it results in an error (requiring CACHE >= 65536 or = 1). This is likely designed to batch sequence values on each compute node in a distributed architecture to avoid global lock contention.

Before (previously): ID numbering managed by the application

CREATE TABLE post_tag (
    id BIGINT NOT NULL PRIMARY KEY,
    post_id VARCHAR NOT NULL,
    tag_id VARCHAR NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- INSERT with application-generated id
INSERT INTO post_tag (id, post_id, tag_id)
VALUES (12345, 'post-001', 'aws');

After (IDENTITY): ID automatically numbered by the database

CREATE TABLE post_tag (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 65536) PRIMARY KEY,
    post_id VARCHAR NOT NULL,
    tag_id VARCHAR NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Just omit id for automatic numbering
INSERT INTO post_tag (post_id, tag_id)
VALUES ('post-001', 'aws')
RETURNING id;

※ tag_id corresponds to keys in a separate tag master table

When defining an IDENTITY column, you need to specify the numbering mode and CACHE size. I verified each option.

GENERATED BY DEFAULT vs GENERATED ALWAYS

• GENERATED BY DEFAULT — Automatic numbering, but you can explicitly specify IDs. Useful when migrating existing data
• GENERATED ALWAYS — Always numbered by the DB. Specifying an ID results in an error
• ALTER TABLE ... SET GENERATED ALWAYS / BY DEFAULT allows switching on existing tables

CACHE Performance Comparison

I compared CACHE 1 and CACHE 65536 with 1000 bulk INSERTs. The comparison results are as follows:

Note that with CACHE 65536, each node independently caches different ID ranges, so IDs are unique but their magnitude may not match their creation order. Records inserted earlier may have larger IDs, and large gaps may occur. If strict sorting by creation order is needed, use created_at in conjunction.

CACHE 1 was slow because it persists sequence values one by one.
For use cases like surrogate keys where performance is prioritized and strict sequential numbering isn't required, CACHE 65536 is recommended. When performing bulk INSERTs, this cache setting significantly impacts processing time, so careful consideration based on requirements is necessary.

Verification of Adding to Existing Tables

Can ALTER TABLE Be Used?

I first tried the simplest method.

ALTER TABLE post_tag
    ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (CACHE 65536);

The result was not supported in DSQL.

ERROR: unsupported ALTER TABLE ALTER COLUMN ... ADD IDENTITY statement

While possible in standard PostgreSQL, current DSQL doesn't support this ALTER TABLE syntax.

New Table Creation → Data Migration → Rename Method

Since ALTER TABLE doesn't work, I verified a method of creating a new table and migrating the data.

-- 1. Create new table with IDENTITY
CREATE TABLE post_tag_new (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 65536) PRIMARY KEY,
    post_id VARCHAR NOT NULL,
    tag_id VARCHAR NOT NULL,
    created_at TIMESTAMP
);

-- 2. Migrate existing data while preserving IDs
INSERT INTO post_tag_new (id, post_id, tag_id, created_at)
SELECT id, post_id, tag_id, created_at
FROM post_tag;

-- 3. Set starting value
ALTER TABLE post_tag_new ALTER COLUMN id RESTART WITH <MAX(id) + 1>;

-- 4. Swap tables
ALTER TABLE post_tag RENAME TO post_tag_old;
ALTER TABLE post_tag_new RENAME TO post_tag;

Verification Results

Results from testing with 1000 production data records:

With GENERATED BY DEFAULT, we could INSERT with explicitly specified existing IDs, and after migration, auto-numbering began when id was omitted. Table switching was completed instantly with RENAME.

Summary

Although ALTER TABLE couldn't add IDENTITY to existing columns, I confirmed migration is possible using the new table + rename method. Application-side changes only require omitting the id column from INSERT statements and removing numbering logic. The DynamoDB numbering table and related logic can be completely eliminated, and operational constraints prohibiting concurrent execution are resolved.

The performance difference in CACHE settings is 108x, highlighting the importance of choosing appropriate values for your use case.

This is a long-awaited update for those who had to set up external numbering mechanisms. For the article tag management system I introduced as an example, we're planning to begin migration soon.

Reference Links

• Official announcement
• Aurora DSQL documentation - Sequences and identity columns