Timeouts for long-running SQL queries

While the main goal always is to prevent long-running queries in the first place, automatic timeouts can serve as a safety net to terminate problematic queries automatically if a set time limit is exceeded. This prevents single queries from taking up all of your database’s resources and reduces the need for manual intervention that might destabilize or even crash the application.

As Rails does not set a timeout on database statements by default, the following query will run for an entire day:

CopyActiveRecord::Base.connection.execute("SELECT pg_sleep(86400)")

The good news is that with statement_timeout for PostgreSQL and max_execution_time for MySQL (5.7.8 or higher) both databases offer a variable to limit the execution time of queries. While they serve similar purposes they do have some differences in behavior and implementation.

PostgreSQL

• Timeout Type: statement_timeout
• Scope: Applies to all types of SQL statements (SELECT, INSERT, UPDATE, DELETE).
• Units: Can be specified in various time units (milliseconds, seconds, minutes).
• Behavior: If any statement exceeds the specified timeout, PostgreSQL raises an error indicating the statement was canceled due to the timeout (ERROR: canceling statement due to statement timeout (PG::QueryCanceled)). If multiple SQL statements appear in a single simple-query message, the timeout is applied to each statement separately.

Set the timeout globally for all environments by adjusting your config/database.yml:

Copydefault: &default
  adapter: postgresql
  # ...
  variables:
    statement_timeout: 10s # or ms, min, etc

Test that it works:

CopyActiveRecord::Base.connection.execute("show statement_timeout;").map { |row| row }
=> [{"statement_timeout"=>"10s"}]

begin
  ActiveRecord::Base.connection.execute("SELECT pg_sleep(15)")
rescue ActiveRecord::QueryCanceled => e
  Rails.logger.error("Query was canceled: #{e.message}")
end

Adjust or disable the timeout for a single transaction:

CopyActiveRecord::Base.transaction do
  # Increase statement timeout to 20 seconds
  ActiveRecord::Base.connection.execute("SET LOCAL statement_timeout = '20s'")
  ActiveRecord::Base.connection.execute("SELECT pg_sleep(15)") # no timeout
  
  # Disable statement timeout
  ActiveRecord::Base.connection.execute("SET LOCAL statement_timeout = '0'")
  ActiveRecord::Base.connection.execute("SELECT pg_sleep(20)") # no timeout
end

ActiveRecord::Base.connection.execute("SELECT pg_sleep(15)") # timeout after 10 seconds (global setting)

You may also want to encapsulate the timeout logic in a module:

Copymodule DatabaseTimeout
  module_function

  # Usage: DatabaseTimeout.timeout(10000) { some_long_running_query }
  def timeout(milliseconds)
    ActiveRecord::Base.transaction do
      ActiveRecord::Base.connection.execute("SET LOCAL statement_timeout = '#{milliseconds.to_i}'")
      yield
    end
  end
end

Alternatively you can set the statement globally for your PostgreSQL cluster Show archive.org snapshot or for a specific database user Show archive.org snapshot .

MySQL (5.7.8 or higher)

• Timeout Type: max_execution_time
• Scope: Applies only to SELECT statements; other types of queries do not have a dedicated timeout.
• Units: Specified in milliseconds.
• Behavior: If a SELECT statement exceeds the specified timeout, MySQL raises an error indicating the statement was canceled due to the timeout (Query execution was interrupted, maximum statement execution time exceeded (Mysql2::Error))

You can set the timeout globally for all environments by adjusting your config/database.yml:

Copydefault: &default
  adapter: mysql2
  # ...
  variables:
    max_execution_time: 10000 # ms

Test that it works:

Copybegin
  ActiveRecord::Base.connection.execute("SELECT 1 WHERE sleep(15)")
rescue ActiveRecord::StatementTimeout => e
  Rails.logger.error("Query was canceled: #{e.message}")
end

Adjust (disable is not possible) the timeout for a single transaction using an optimizer hint Show archive.org snapshot :

Copy# Increase statement timeout to 20 seconds
ActiveRecord::Base.connection.execute("SELECT /*+ MAX_EXECUTION_TIME(20000) */ 1 WHERE sleep(15)") # no timeout

ActiveRecord::Base.connection.execute("SELECT 1 WHERE sleep(15)") # timeout after 10 seconds (global setting)

Further resources

There is also an extensive guide to Ruby Timeouts in general on Github: The Ultimate Guide to Ruby Timeouts! Show archive.org snapshot