Search code examples
postgresqlamazon-rdsamazon-aurora

why is sequence value different across different db instances in postgresql?

I am using RDS Aurora serverless for Postgresql 15 hosted by AWS. The cluster has 3 instances, one write and 2 reads. I created a sequence by SEQUENCE test START 10;. And I use select nextval(test) to increase the sequence value.

However, I found the sequence value is different when I run below command between write and read instance:

select * from test;

I wonder why the value is different. If the write instance is down and one of the read instance becomes the write, will it give me duplicate values?

I have verified the Aurora is not using logical replica:

=> show rds.logical_replication;
 rds.logical_replication
-------------------------
 off
(1 row)

AMSEngine=> SHOW wal_level;
 wal_level
-----------
 replica
(1 row)
Solution

  • If sequence value is included into a table then transaction corresponding to tables are logged in WAL along with incremental sequence value,this is the practical use of sequence objects.

    If you just create a sequence object without linking to any table,the value in slave will be ahead of master,this is done to improve the performance by reducing the WAL logs related to sequence objects.WAL record is not generated for every call of nextval() function.

    Also in case of failover, whatever value the slave has will be present after promotion.This will not result in duplication but you will find gaps in sequence values after failover because slave's sequence value is ahead of master's.

    Refer below link:- https://www.postgresql.org/message-id/flat/1296642753.8673.29.camel%40gibralter

    Also refer below article:-

    https://www.cybertec-postgresql.com/en/gaps-in-sequences-postgresql/