What does log_cnt mean in the postgres sequence?
Mar 3
I was trying to figure out why the sequence number in my table is jumping exponentially, while that could be an issue in my API which I still have to figure out, I came across this query
to find the current value of the sequence
SELECT * from some_sequence
It gives me a column named log_cnt. The value of this column keeps jumping around 30. I am not sure what does this number means and if at all this can affect the next number which is going to come in the sequence.
Can someone help me on this?
postgresql1 answer
Accepted answer · original discussion
Mar 3
Changes in the state of a sequence must be written to transaction log (WAL). This could potentially lead to a lot of WAL records, which would harm performance.
As an optimization, PostgreSQL does not log the current sequence counter, but a value that is 32 greater. See this comment in src/backend/commands/sequence.c:
/*
* We don't want to log each fetching of a value from a sequence,
* so we pre-log a few fetches in advance. In the event of
* crash we can lose (skip over) as many values as we pre-logged.
*/
#define SEQ_LOG_VALS 32
That means that up to 32 sequence values could get lost during a crash (recovery will set the sequence to the logged position), which is no problem.
log_cnt shows how many fetches remain before a new WAL record has to be written.
After the first call to nextval after a checkpoint, log_cnt will be 32. It will decrease with every call to nextval, and once it reaches 0, it is set to 32 again, and a WAL record is written.
3 question comments
Use comments to ask for clarification. Post a solution as an answer.
Mar 18