That’s great to hear! Hope you can make use of it.
While working on the pg_notify payloads I discovered that the Base.encode64 part here is crucial. With some benchmarking I discovered that Jason encoding is just as fast as :erlang.term_to_binary + Base.encode64.
Wow. I knew that it did the deduping (discovered while trying to work around notifications only broadcasting after a transaction), but I didn’t know that it was O(n^2). Ouch.
That’s true, the notification from a trigger has a very limited payload. Originally it was just the queue name but I expanded it to aid in tracking runtime metrics.
The advisory lock is only used to indicate which jobs are actively executing. Row level locks are handled by FOR UPDATE SKIP LOCKED, so it isn’t an issue if the advisory lock is already taken when the job is retried. Note that the query uses pg_try_advisory_lock_shared to be account for duplicate locks.
It does attempt to release the lock when marking a job complete, retrying it or discarding it. That doesn’t always work though, due to connection ownership. From a comment in the query module:
Dropping a lock uses the same format as taking a lock. Only the session that originally took the lock can drop it, but we can’t guarantee that the connection used to complete a job was the same connection that fetched the job. Therefore, not all locks will be released immediately after a job is finished. That’s ok, as the locks are guaranteed to be released when the connection closes.
Cleanup is best effort, mainly to ensure we free locks and keep advisory locks below the limit.
What are you using to manage job locking at the database level? I ask because PG Advisory locks are extremely fast for this type of workload. There’s a Ruby queue call Que designed around it with some very compelling throughput numbers.
This looks like a really cool library! I really like the idea of retaining the job history. But I do have a question about that. Based on reading this it seems like the jobs table is essentially unbounded. I’m assuming that its just up to operations to remove old jobs or migrate those to a different table, do rollups, etc.? Or is there some other mitigation strategy you have for this?
That’s interesting, I’ve never seen tcn before. It could have worked for the first implementation of triggers/notifications because it wasn’t forced to be JSON. Oban uses four different notification channels to manage various things (insert trigger, update trigger, node/queue gossip and signaling), and they all use JSON for uniformity.
Considering what @benwilson512 posted regarding the pg_notify deduplication it seems like a good thing that the full contents of each row aren’t broadcast.
I’m using FOR UPDATE SKIP LOCKED for row level locking. It is a more recent addition to PG and doesn’t have the drawbacks that are mentioned in the Que documentation, i.e. there isn’t any lock contention. Oban also uses advisory locks, but as a guarantee for “at least once” job execution instead of a real lock mechanism.
As mentioned by @engineeringdept below there is historic job pruning. There are two modes: time based and limit based. As the system runs it clears out jobs beyond the configured time or number of rows. An alternative, that I shied away from due to operational overhead and limited flexibility, was to use partitioning.
For the UI project I’m adding rollups for added historic information and visualization, but that isn’t ready yet.
Seems on topic to me! I’m a big fan of HLL and would love to use it for the roll up table. My primary concern is portability and whether it will be available on most cloud platforms. For instance, to my knowledge it isn’t available on Heroku and they don’t provide any way to install outside extensions.
Other cloud database providers may be more flexible, I haven’t looked into many of them lately.
