Aurora DSQL Now Supports IDENTITY / SEQUENCE - Also Testing How to Migrate Existing Tables

On 2026/2/13, support for IDENTITY columns and SEQUENCE objects in Amazon Aurora DSQL was announced.

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

This update enables automatic integer ID numbering on the database side. I happened to be building an alternative implementation in a system under development, so I'll introduce it together with an actual use case.

Update Overview

The following are now available in Aurora DSQL.

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

You can use the standard PostgreSQL-compatible SQL syntax as is. It is available in all regions where Aurora DSQL is offered.

Use Case: The Numbering Problem in an AI Tag Management Table

Table Structure

I am developing a mechanism in a blog post management system where an LLM automatically generates tags for each post. Since multiple tags are linked to one post (1:N), the tag table needed an independent primary key (surrogate key).

Why Numbering Is a Problem

Until now, Aurora DSQL did not support SERIAL or SEQUENCE.

Numbering with SELECT MAX(id) + 1 is an anti-pattern. Concurrent execution causes duplicate IDs, and the scan cost of MAX(id) also grows as the number of records increases. Depending on the transaction isolation level, inconsistencies can also occur.

Previous Workaround: Numbering Management with DynamoDB

I prepared a numbering management table in DynamoDB and issued IDs exclusively using an Atomic Counter (ADD in UpdateItem).

I chose DynamoDB because updates via the Atomic Counter are atomic and the locking is reliable, so IDs could be issued safely without putting load on DSQL.

However, it increased dependency on an external service and made it complicated to guarantee transaction consistency between numbering and INSERT. In addition, because of the exclusive control for numbering, I had limited the concurrency of the tagging process to 1, which made parallel processing impossible and left a throughput problem for linking tags to a large number of posts. In particular, for processing on the scale of tens of thousands of items, such as bulk tagging of past posts, this caused it to take a long time.

Before (DynamoDB numbering method):

After (IDENTITY method):

Since numbering is completed on the database side, the numbering table becomes unnecessary. Parallel execution becomes possible, throughput can be improved with Map processing in Step Functions, and the concurrency limit can also be removed.

Hands-on Verification: Trying IDENTITY Columns

Basic Behavior

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

As a DSQL-specific constraint, explicitly specifying the CACHE size is mandatory. Omitting it results in an error (it requires CACHE >= 65536 or = 1). This is thought to be a design that, in a distributed architecture, batches sequence values on each compute node to avoid global lock contention.

Before (previously): id numbering is managed on the application side

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
);

-- Specify the id numbered on the application side and INSERT
INSERT INTO post_tag (id, post_id, tag_id)
VALUES (12345, 'post-001', 'aws');

After (IDENTITY): id is automatically numbered on the database side

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 the key of a separate tag master table

When defining an IDENTITY column, you need to specify the numbering mode and the CACHE size. I checked each of them.

GENERATED BY DEFAULT vs GENERATED ALWAYS

• GENERATED BY DEFAULT — Auto-numbered, but you can also explicitly specify an ID. Useful when migrating existing data
• GENERATED ALWAYS — Always numbered by the database. Specifying an ID causes an error
• You can also switch an existing table with ALTER TABLE ... SET GENERATED ALWAYS / BY DEFAULT

CACHE Performance Comparison

I compared CACHE 1 and CACHE 65536 with a bulk INSERT of 1000 rows. The results are as follows.

Note that with CACHE 65536, each node independently caches a different range of IDs, so IDs are unique, but the generation order and the magnitude of the values do not match. A record INSERTed earlier may have a larger ID (an inversion), and large gaps may occur. If you need to sort in strict creation order, use created_at as well.

CACHE 1 was slow because it persists the sequence value for every single row.
For uses such as surrogate keys where performance is a priority and strict sequentiality is not required, using CACHE 65536 is recommended. When performing large bulk INSERTs, this cache setting greatly affects processing time, so careful consideration according to your requirements is needed.

Verification of Retrofitting to an Existing Table

Can ALTER TABLE Be Used?

First, I tried the simplest method.

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

The result was that it is not supported in DSQL.

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

This is possible in standard PostgreSQL, but DSQL does not currently support this ALTER TABLE syntax.

New Table Creation → Data Migration → Rename Method

Since ALTER TABLE cannot be used, I verified an approach of creating a new table and moving the data.

-- 1. 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. 既存データを ID 保持で移植
INSERT INTO post_tag_new (id, post_id, tag_id, created_at)
SELECT id, post_id, tag_id, created_at
FROM post_tag;

-- 3. 開始値を設定
ALTER TABLE post_tag_new ALTER COLUMN id RESTART WITH <MAX(id) + 1>;

-- 4. テーブル入れ替え
ALTER TABLE post_tag RENAME TO post_tag_old;
ALTER TABLE post_tag_new RENAME TO post_tag;

Verification Results

These are the results of sampling and verifying 1000 rows of production data.

Because it is GENERATED BY DEFAULT, I could INSERT with existing IDs explicitly specified, and after migration it switched to automatic numbering by omitting id. Swapping the tables was completed instantly with RENAME.

I tried the verification within Aurora DSQL's hard limit of 3000 rows or fewer per transaction, but please note that for a large-scale migration, you need an implementation that specifies ID ranges on the application side or similar, and commits in units of 3,000 rows or fewer.

Maximum number of table and index rows that can be mutated in a transaction block

3,000 rows per transaction.

https://docs.aws.amazon.com/aurora-dsql/latest/userguide/CHAP_quotas.html

Summary

Retrofitting with ALTER TABLE was not possible, but I confirmed that migration is possible with the new table + rename method. The changes on the application side are simply omitting the id column from INSERT statements and removing the numbering logic. The DynamoDB numbering table and related logic can be completely retired, and the operational constraint of prohibiting concurrent execution is also eliminated.

The performance difference from the CACHE setting is large at 108x, so choosing an appropriate value according to the use case is important.

For those who had prepared an external mechanism for numbering, this may be a long-awaited update. For the tag management system of the posts I introduced as an example, I also would like to start working toward migration.

Reference Links

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