Why is id as SERIAL discontinuous values after failover in RDS Aurora PostgreSQL?

Question

I'm testing failover using RDS Aurora PostgreSQL.

First, create RDS Aurora PostgreSQL and access the writer cluster to create users table.

$ CREATE TABLE users ( 
id SERIAL PRIMARY KEY NOT NULL, 
name varchar(10) NOT NULL,
createAt TIMESTAMP DEFAULT Now() );

And I added one row and checked the table.

$ INSERT INTO users(name) VALUES ('test');

$ SELECT * FROM users;
+----+--------+----------------------------+
| id | name   | createdAt                  |
+----+--------+----------------------------+
|  1 | test   | 2022-02-02 23:09:57.047981 |
+----+--------+----------------------------+

After failover of RDS Aurora Cluster, I added another row and checked the table.

$ INSERT INTO users(name) VALUES ('temp');

$ SELECT * FROM users;
+-----+--------+----------------------------+
|  id | name   | createdAt                  |
+-----+--------+----------------------------+
|   1 | test   | 2022-02-01 11:09:57.047981 |
|  32 | temp   | 2022-02-01 11:25:57.047981 |
+-----+--------+----------------------------+

After failover, the id value that should be 2 became 32.

Why is this happening?

Is there any way to solve this problem?

afaik, if you were to create a serial in a transaction and then rollback. And then create a new serial, that rolled back serial is skipped. As if it were taken and then discarded. I guess it has to do with the way serials they are generated. — The Fool, Feb 02 '22 at 14:30
Consider using [identity](https://stackoverflow.com/a/55300741/4003419) instead of [serial](https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_serial). — LukStorms, Feb 02 '22 at 14:34
@nari120 Don't worry about it. The only important thing is that IDs need to be **unique**. It doesn't matter if they are strictly sequential or serial. — The Impaler, Feb 02 '22 at 14:44

score 1 · Accepted Answer · answered Feb 02 '22 at 15:30

That is to be expected. Index modifications are not WAL logged whenever nextval is called, because that could become a performance bottleneck. Rather, a WAL record is written every 32 calls. That means that the sequence can skip some values after a crash or failover to the standby.

You may want to read my ruminations about gaps in sequences.

Why is id as SERIAL discontinuous values after failover in RDS Aurora PostgreSQL?

1 Answers1