When are Predicate Locks Freed

Dave Halter <[email protected]>
Newsgroups gmane.comp.db.postgresql.general
Message-ID <CAA=HWYgJRB8yjDy3_HVXHJCdZCfYYt2bbVfEBdV0ANpOBfHkhA@mail.gmail.com>
Hello PostgreSQL Community

I have worked quite a bit with serializable transactions. I'm trying
to understand when predicate locks are freed. I read the whole
README-SSI [1], but I'm still not sure when a predicate lock gets
dropped.

What I learned from that README is that predicate locks are not freed
at the moment where a transaction passes, but only at a later stage.
This makes sense, because some concurrent transactions might be
invalidated by the predicate locks that are not needed anymore for a
transaction that was just committed.

What I don't understand is: We have only short transaction (<1s), but a

    select count(*) from pg_locks where mode = 'SIReadLock';

would vary by a lot. It typically went up all the way to 300k. At this
point it would drop to almost zero (always <10k) and rise again. This
cycle pretty consistently happened every three minutes. (I think it
looked a bit more like 2 minutes 50 seconds, but I wasn't measuring it
exactly). Is there a "garbage collection" happening every few minutes?
Since we scaled up our service by quite a bit, we increased
`max_pred_locks_per_transaction`. Now we are at 1.0m to 1.3m
predicate locks with the same ~3 minute cleanup period.

Does anybody have any insights in why this might be happening? I would
also be very interested in good explanations about when predicate
locks are actually freed. I tried reading the C code, but couldn't
really figure out where that was happening.

A very happy PostgreSQL user.
Thanks for your continued efforts!
~ Dave

[1]: https://github.com/postgres/postgres/blob/db2687d1f3787aa8113b3dbb358153feee30c64c/src/backend/storage/lmgr/README-SSI
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.