Understanding an outage: concurrency control and vacuuming in PostgreSQL

My memory is a bit hazy on this, so forgive the details but...

Postgres uses something called a multi-xact store to track row locks- iirc, an entry in this store uniquely identifies an entity and all the actors that have locked it: `(id, [pid])`

It happened that our ETL pipeline was routinely hitting issues with a transaction wraparound vacuum, despite the Postgres database the ETL instance was recovered from having fairly aggressive vacuum targets.

Investigation showed it wasn't the standard xid wraparound we were hitting, it was wraparound on the multi-xid counter.

It took me a solid weekend of debugging to figure this out, as multi-xact is (was?) very poorly documented, but reading the code helped me understand that a multi-xact entry is immutable. This means whenever you get a new pid wanting to add itself to the lockers, Postgres would make a copy of the previous entry (and all the users that were holding the lock) and add the new actor to the list.

That means your entries would go something like this:

    1
    1, 2
    1, 2, 3
    1, 3, 3, 4
    ...

And as Postgres appended this to the multi-xact log, which is what incremented the multi-xid counter, the counter would progress at a rate proportional to the sum of the series, which is quadratic.

This wouldn't have been a problem if our codebase hadn't accidentally introduced a pathological pattern where it's sub-transaction would repeatedly lock against a row it had previously locked against, which in Postgres 9.4 would be identified as a different transaction, rather than the same process and locking entity.

It meant a tight loop of code like this:

    ActiveRecord::Base.transaction do
      model.lock!
      # Assume many, possibly 500-1000 resources
      model.resources.each do |child|
        ActiveRecord::Base.transaction(requires_new: true) do
          child.lock!
          # do something
        end
      end
    end

Would add (no of resources)^2 to the multi-xid counter. Each day a cron would fire which iterated through ~50k of the parent model, then paginate through the resources, of which there could be ~5000 children.

When you graphed it, you could see the multi-xid counter blast off every time the cron ran. It meant we'd hit the non-optional wraparound threshold almost daily, which was the source of our ETL woes.

I was super happy when I figured this out, and was looking forward to contribute a fix to Postgres that made the locking no-op if the sub-transaction was already in the lock list. When I checked out master, I realised I could no longer reproduce the bug.

It was then I learned a painful lesson about always running the latest version of your database, as this had been fixed in 9.6.