"Allows efficient queries based on position. i.e. WHERE SequenceNum > @LastSeenSeqNum"
A good test is to have many parallel threads inserting events and have another thread periodically issuing this query and checking that what it retrieves doesn't contain gaps.
Postgres assigns the serial number at the start of each transaction that inserts the event. If the transactions commit out of order (which postgres is happy to do!) then the query can return gaps which are later filled in. The net effect is that checking against the LastSeen value means you can miss updates as they became visible to your query out of order. Search for "how to create a gapless sequence in postgres".
"Allows efficient queries based on position. i.e. WHERE SequenceNum > @LastSeenSeqNum"
A good test is to have many parallel threads inserting events and have another thread periodically issuing this query and checking that what it retrieves doesn't contain gaps.
Postgres assigns the serial number at the start of each transaction that inserts the event. If the transactions commit out of order (which postgres is happy to do!) then the query can return gaps which are later filled in. The net effect is that checking against the LastSeen value means you can miss updates as they became visible to your query out of order. Search for "how to create a gapless sequence in postgres".
Thanks for pointing this out. I missed this the first time around, but this was pointed in my subsequent article.
I discovered the same gapless sequence article and posted an updated solution in the comments, which can properly handle concurrent writers.