How do I prevent my sidekiq workers from overwhelming its database connection limit?

Question

Right now my DigitalOcean managed database backend connection limit is 22. Here are my sidekiq.yml and database.yml configurations:

# config/sidekiq.yml

development:
  :concurrency: 18
production:
  :concurrency: 18

.

# config/database.yml

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 18

I have a bunch or workers that interact with the database whenever I kick off a scheduled task. Since we're planning on allowing this scheduled task to kick off multiple times at the same time, we're running into a lot of connection database pool errors. So now I'm trying to figure out the best way to optimize this process or find another service that may be better for us.

For testing purposes, I created a sidekiq worker that looks like this:

class MySampleWorker
    include Sidekiq::Worker
    sidekiq_options queue: Rails.env.to_sym
    
    def perform
        User.first
    end
end

If I call this sidekiq worker 20 times simultaneously, everything runs just smoothly. But if I call it 50 times simultaneously, then I get about 3-5 failed workers that ends up being retried.

My question is -- how would I scale something like this? In my case, I'm going to have to call the same workers multiple times, more and more as the demand grows, and that's obviously going to result in several failed workers. In some cases, these workers may take 5-10 minutes each - they're essentially running health check commands on remote systems and waiting on outputs in order to complete the worker.

Scaling in this manner seems like it would be disastrous. Rather than the workers failing, is there any way to just simply have them queued up and run when there's available space, as opposed to failing? If I understand the way this works, shouldn't database.yml be limiting the connection at 18 and, thus, it should never actually be trying to access the postgresql database beyond 18 connections at a time while the postgresql database has a connection limit of 22? I would think I should expect to see activerecord database connection timeout errors before I see any issues with postgresql database connection issues.

Here's the error I receive when one of the workers fail now:

2020-06-18T15:05:16.536Z pid=1152049 tid=gofhou0qp WARN: PG::ConnectionBad: FATAL:  remaining connection slots are reserved for non-replication superuser connections

2020-06-18T15:05:16.536Z pid=1152049 tid=gofhou0qp WARN: /usr/local/rvm/gems/ruby-2.5.8/gems/pg-1.2.3/lib/pg.rb:58:in `initialize'
/usr/local/rvm/gems/ruby-2.5.8/gems/pg-1.2.3/lib/pg.rb:58:in `new'
/usr/local/rvm/gems/ruby-2.5.8/gems/pg-1.2.3/lib/pg.rb:58:in `connect'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/connection_adapters/postgresql_adapter.rb:692:in `connect'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/connection_adapters/postgresql_adapter.rb:223:in `initialize'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/connection_adapters/postgresql_adapter.rb:48:in `new'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/connection_adapters/postgresql_adapter.rb:48:in `postgresql_connection'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:830:in `new_connection'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:874:in `checkout_new_connection'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:853:in `try_to_checkout_new_connection'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:814:in `acquire_connection'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:538:in `checkout'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:382:in `connection'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:1033:in `retrieve_connection'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/connection_handling.rb:118:in `retrieve_connection'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/connection_handling.rb:90:in `connection'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/relation/delegation.rb:76:in `connection'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/relation/query_methods.rb:934:in `build_arel'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/relation/query_methods.rb:900:in `arel'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/relation.rb:560:in `block in exec_queries'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/relation.rb:584:in `skip_query_cache_if_necessary'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/relation.rb:547:in `exec_queries'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/relation.rb:422:in `load'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/relation.rb:200:in `records'
/usr/local/rvm/gems/ruby-2.5.8/gems/bullet-6.1.0/lib/bullet/active_record52.rb:46:in `records'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/relation.rb:195:in `to_ary'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/relation/finder_methods.rb:532:in `find_nth_with_limit'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/relation/finder_methods.rb:517:in `find_nth'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/relation/finder_methods.rb:125:in `first'
/usr/local/rvm/gems/ruby-2.5.8/gems/activerecord-5.2.4/lib/active_record/querying.rb:5:in `first'
/var/www/test-dev/app/workers/my_sample_worker.rb:12:in `perform'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:196:in `execute_job'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:164:in `block (2 levels) in process'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/middleware/chain.rb:133:in `invoke'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:163:in `block in process'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:136:in `block (6 levels) in dispatch'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/job_retry.rb:111:in `local'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:135:in `block (5 levels) in dispatch'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/rails.rb:43:in `block in call'
/usr/local/rvm/gems/ruby-2.5.8/gems/activesupport-5.2.4/lib/active_support/execution_wrapper.rb:87:in `wrap'
/usr/local/rvm/gems/ruby-2.5.8/gems/activesupport-5.2.4/lib/active_support/reloader.rb:73:in `block in wrap'
/usr/local/rvm/gems/ruby-2.5.8/gems/activesupport-5.2.4/lib/active_support/execution_wrapper.rb:87:in `wrap'
/usr/local/rvm/gems/ruby-2.5.8/gems/activesupport-5.2.4/lib/active_support/reloader.rb:72:in `wrap'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/rails.rb:42:in `call'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:131:in `block (4 levels) in dispatch'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:257:in `stats'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:126:in `block (3 levels) in dispatch'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/job_logger.rb:13:in `call'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:125:in `block (2 levels) in dispatch'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/job_retry.rb:78:in `global'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:124:in `block in dispatch'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/logger.rb:10:in `with'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/job_logger.rb:33:in `prepare'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:123:in `dispatch'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:162:in `process'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:78:in `process_one'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/processor.rb:68:in `run'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/util.rb:15:in `watchdog'
/usr/local/rvm/gems/ruby-2.5.8/gems/sidekiq-6.0.7/lib/sidekiq/util.rb:24:in `block in safe_thread'

Here's my Puma config:

threads_count = ENV.fetch("RAILS_MAX_THREADS") { 5 }
threads threads_count, threads_count

port        ENV.fetch("PORT") { 3000 }

environment ENV.fetch("RAILS_ENV") { "development" }

Answer 1

As you scale up, you should just get a queue of jobs for workers to work on - if your concurrency is correctly set. Not failed jobs - your expectation there is correct.

However, the expectation that setting the database pool to 18 is maybe wrong. And, certainly only applies within each application server (do you have multiple?)

But this will depend on how you have Puma configured, post that config.

And see https://devcenter.heroku.com/articles/concurrency-and-database-connections for a good description of how the pool should be shared between workers and multiple application servers. And, https://devcenter.heroku.com/articles/deploying-rails-applications-with-the-puma-web-server for best practise on setting worker and thread size.