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

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?

CodePudding user response：

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.