sorentwo

sorentwo OP

Oban Core Team

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.

4
Post #11
sorentwo

sorentwo OP

Oban Core Team

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.

1
Post #12
hauleth

hauleth

Also it is worth mentioning that you do not need to define your own function for trigger, as there is PostgreSQL extension in default distribution that does exactly that, however that one do not use JSON as message output but custom format.

2
Post #13
brightball

brightball

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.

2
Post #14
keathley

keathley

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?

3
Post #15
engineeringdept

engineeringdept

This might answer your question: Oban — Oban v2.23.0

4
Post #16
sorentwo

sorentwo OP

Oban Core Team

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.

5
Post #17
axelson

axelson

Scenic Core Team

At the risk of being slightly off topic, if I was implementing the roll up tables I would look into HyperLogLog: Efficient rollup tables with HyperLogLog in Postgres - Citus Data

I haven’t been able to try them out yet but it seems like it is a great fit for your use case.

1
Post #18
sorentwo

sorentwo OP

Oban Core Team

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.

1
Post #19
brightball

brightball

If it doesn’t change the code much, could be something you could list as a suggestion if available.

0
Post #20