Support for CockroachDB

[sandstrom]

CockroachDB -- a new database, aspiring to be wire-compatible with PostgreSQL -- has an ActiveRecord adapter that works fairly well (https://github.com/cockroachdb/activerecord-cockroachdb-adapter).

However, they are currently lacking support for Advisory Locks and LISTEN/NOTIFY which are two requirements for Good Job.

Just wanted to check, are there any other "special" features in PostgreSQL, that Good Job depends on?

cc @bensheldon

[bensheldon]

Off the top of my head, GoodJob uses these features from Postgres:

• Advisory Locks
• LISTEN/NOTIFY
• CTEs (Common Table Expressions)
• Introspection and management features (e.g. pg_locks, pg_stat_activity, pg_backend_pid(), pg_terminate_backend())
• bigint column-type and md5()

[sandstrom]

Thanks!

CTEs, bigint and md5 should exist.

Don't know about pg_locks, pg_stat_activity, pg_backend_pid(), pg_terminate_backend().

No need to do anything about Cockroach until they have Advisory locks and LISTEN/NOTIFY. Mainly wanted to start a discussion thread, to see what interest there is and to track what's missing from Cockroach currently.

[jonahgeorge]

On this topic, have you ever thought about expanding GoodJob to support pluggable backends?

I've always considered GoodJob to be the Rails analogy to Oban, which recently added support for Sqlite3 and has MySQL on its radar.

For context, there have been a few scenarios where I would have really liked to be able to run GoodJob in a non-Postgres (but still ActiveRecord/Relational DB) environment, accepting that job execution performance would likely suffer due to it requiring a polling approach rather than LISTEN/NOTIFY & advisory locks.

I fully recognize this is a massive undertaking, but just curious to hear whether this is something you'd even consider for outside contribution.

[bensheldon]

👋 Thanks for asking the question. Here's where my head is at today:

I'd like to keep GoodJob Postgres-only. But I'd be overjoyed to help someone build their own Active Job backend ✨

Here's why: 80% of my effort for GoodJob goes into working with Postgres. I think there's still a lot of interesting runway to improve Postgres-compatibility and performance which makes me very reluctant to add another database. And lots of features (like Concurrency Control, Cron, or the Dashboard) that are very much in the category of "huh, I wonder if that's possible?" and the drag of having to implement every expeditionary feature multiple times fills me with dread.

On the bright side, my learnings from GoodJob is this: building a multithreaded Active Job backend is not a difficult problem. Concurrent Ruby has all the primitives, Rails Engines do a lot of heavy lifting. Executing jobs is not the bottleneck; efficiently fetching and safely locking job data from the database is the hard part.

GoodJob's core job execution objects are: Poller, Scheduler and the JobPerformer interface. They're not very complicated until you look behind the JobPerformer at the Active Record model and anything non-trivial is very Postgres-specific. But, if you copy-paste those 3 objects (which are wrappers around Concurrent Ruby) into a new engine and implement the JobPerformer interface... you've got yourself an Active Job backend (albeit no other bells or whistles).

(I'm oversimplifying it a smidge, but not really!)

I suggest making your own backend because I'd really, really like to see more people working with Concurrent Ruby. It's a lovely library. You'll probably teach me something working through the problems yourself. And I'd love to help... just not in GoodJob right now 💖

[jonahgeorge]

Just dumping some notes here:

CockroachDB

• Advisory Locks ❌
  • sql: fill out pg_advisory_lock stubs cockroachdb/cockroach#13546
  • https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/20171024_select_for_update.md
• LISTEN/NOTIFY ❌
  • Fallback to inefficient poller?
  • sql: support NOTIFY, LISTEN, and UNLISTEN commands of postgresql cockroachdb/cockroach#41522
• CTEs (Common Table Expressions) ✅
• Introspection and management features ❌
• bigint column-type and md5() ✅
  • bigint
  • md5

MySQL

• Advisory Locks ✅
  • get_lock
  • release_lock
  • is_free_lock
  • SELECT FOR UPDATE SKIP LOCKED
• LISTEN/NOTIFY ❌
  • Fallback to inefficient poller?
• CTEs (Common Table Expressions) ✅
• Introspection and management features ⚠️
  • performance_schema.metadata_locks
  • show full processlist
  • connection_id()
  • kill
• bigint column-type and md5() ✅
  • md5()
  • bigint

MSSQL

• Advisory Locks ✅
  • sp_getapplock
  • sp_releaseapplock
  • applock_test
  • applock_mode
  • Pessimistic Locking
    • readpast
    • updlock
• LISTEN/NOTIFY ❌
  • Fallback to inefficient poller?
  • Use Service Broker?
• CTEs (Common Table Expressions) ✅
• Introspection and management features ⚠️
  • sys.dm_tran_locks
  • sp_who
  • kill
  • @@spid
• bigint column-type and md5() ✅
  • hashbytes
  • bigint

Sqlite

• Advisory Locks ❌
  • https://www.sqlite.org/lockingv3.html
• LISTEN/NOTIFY ❌
• CTEs (Common Table Expressions) ✅
• Introspection and management features ❌
• bigint column-type and md5() ❌

Misc Links:

• https://github.com/ClosureTree/with_advisory_lock
• Oban Sqlite engine
• https://www.sqlines.com/oracle-to-mysql/skip_locked
• https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE
• ActiveRecord Pessimistic Locking

[brandondrew]

@sandstrom would it work to run all your jobs in Postgres and keep other data in CockroachDB? Rails makes it fairly easy to have multiple databases per app, and if necessary Postgres' could have views that read data from CockroachDB (probably using Foreign Data Wrappers—though I don't remember if FDWs are needed to read from another database that "looks like" another PG db), although I don't know why that would actually ever be necessary.

I'd love to know if you get that worked out, since I'm working on an app that is starting on Postgres but which may switch to CockroachDB if it resolves certain scaling issues that I expect might arise—and I'd prefer to keep GoodJob if possible.

@bensheldon I'd also love to hear your thoughts on whether this sounds feasible

[bensheldon]

Yep, this sounds similar to the setup for PgBouncer, which builds on GoodJob's compatibility with multiple Active Record database connections (and cross-database data is done via Active Job serializers and GlobalID which in my experience are pretty seamless):

https://github.com/bensheldon/good_job#pgbouncer-compatibility

You lose the small niceness of transactional integrity when enqueuing going to multiple databases, but otherwise, I don't think you'd notice the difference 👍🏻

[brandondrew]

Thanks for the confirmation! It's always nice to have one less thing to worry about when looking toward the future 😄

[sandstrom]

Ben already answered, but basically yes, that would work. I would also guess that CockroachDB will eventually support the missing pieces.

As Ben mentions, you'd loose transactional integrity when enqueuing jobs though.

If you haven't already, you can always add your 👍🏻-vote in the two CDB-issues referenced above:

• sql: fill out pg_advisory_lock stubs cockroachdb/cockroach#13546
• sql: support NOTIFY, LISTEN, and UNLISTEN commands of postgresql cockroachdb/cockroach#41522