advisory locks sound interesting, but this really sounds like a problem that the author is forcing on themselves. i get the benefit of having your database persist the immutable ledger, but it seems like you would have a much more scalable solution if you maintained both the materialization and the ledger in the database. then you could condition the transaction on the materialized view state and update the view/append at the same time.

if you didn't want to do this, i would also assume you could do this within a transaction w/o the lock using a serializable transaction. though no idea how indexes interplay with postgres and serializibility.