Postgres Sequence jumping by 32

Question

We use sequences to maintain the number of orders at a particular business unit. Last few we days, we have noticed that there have been has strange jumps ranging from 1 to 32 in the sequence numbers, multiple times a day. The sequence which we have configured has a cache_value of 1. Why is this happening and how can we resolve this? I couldn't find much documentation regarding the same.

Answer 1

PostgreSQL sequences can jump by 32 when you promote a secondary server to primary, and sometimes when PostgreSQL crashes hard and has to recover.

I have only seen it skip 32, and I haven't been able to change it by changing the sequence's cache value.

https://www.postgresql.org/message-id/1296642753.8673.29.camel%40gibralter, and the response https://www.postgresql.org/message-id/20357.1296659633%40sss.pgh.pa.us, indicate that this is expected behavior in a crash condition. I'm unsure if this only occurs if you're using Streaming Replication or not.

I thought it was a safety feature, in case the primary had committed records that had not been replicated to the secondary at the time of promotion. Then a human would be able to manually dump and copy the records from the old-primary to the new-primary before wiping the old-primary.

This could indicate that your primary is crashing and recovering several times per day, which is not a good state to be in.