Set a Statement Timeout for PostgreSQL Queries in Rails

29 Jun 2022

Long running queries can tie up database connections, eventually bogging down your app and causing it to come to a grinding halt. Thankfully you can leverage the PostgreSQL statement_timeout to cause long running queries to raise an error, breaking the connection with the database.

Database.yml

The config/database.yml file can be configured to enable or disable the length of time you want to allow queries to run before they are forced to cancel and timeout. This is achieved by adding a variables.statement_timeout element. Below it has been added to the default section so that all database environments are configured with a statement_timeout of ten seconds.

docker: &default
  adapter: <%= ENV.fetch('DB_ADAPTER') { 'postgresql' } %>
  encoding: unicode
  pool: <%= ENV.fetch('RAILS_MAX_THREADS') { 5 } %>
  host: <%= ENV.fetch('DB_HOST') { 'localhost' } %>
  port: <%= ENV.fetch('DB_PORT') { 5432 }  %>
  username: <%= ENV.fetch('DB_USER') { nil }  %>
  password: <%= ENV.fetch('DB_PASSWORD') { nil }  %>
  variables:
    statement_timeout: 10000

It's not immediately apparent that the unit here is milliseconds so I'd recommend either adding a comment or you can set the value as a string that is a little more descriptive.

  variables:
    statement_timeout: '10s'

Now it's pretty obvious that our statement_timeout is ten seconds.

Verify the statement_timeout has been set

By dropping into a rails console we can verify the timeout has been set to ten seconds by running the following:

ActiveRecord::Base.connection.execute('show statement_timeout').first
#=> {"statement_timeout"=>"10s"}

Check it works

Now we know the statement_timeout has been set we can verify that it works by trying to run a query that takes longer that 10 seconds to execute by invoking pg_sleep

ActiveRecord::Base.connection.execute('select pg_sleep(12)')
#=> ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR:  canceling statement due to statement timeout

What if I need longer running queries?

There may be scenarios where you need to have a long running query, for example if you're bulk updating some records or performing an intensive action in a background task. Thankfully you can manually update the statement_timeout:

begin
  ActiveRecord::Base.connection.execute('set statement_timeout = 20000')
  # Do some long running queries
ensure
  # Always restore the statement_timeout to its initial value
  ActiveRecord::Base.connection.execute('set statement_timeout = 10000')
end