Optimize PostgreSQL connection setup of session variables by using libpq options parameter

rubyonrails-core
1

Problem

PostgreSQL adapter’s configure_connection executes multiple SET commands after establishing each connection. Each SET command requires a database roundtrip (~3-5ms). For applications with high connection churn, this overhead becomes significant. Source

Current behavior (2+ roundtrips per connection):

  1. SET standard_conforming_strings = on
  2. SET intervalstyle = iso_8601
  3. SET TIMEZONE (from reconfigure_connection_timezone if not provided in variables)

At scale, this can be expensive arguably?

Proposed Solution

I wonder if it’s acceptable to use libpq’s options connection parameter to set session variables during the TCP handshake instead of after connection establishment. This eliminates all roundtrips.

libpq already supports this via the options parameter (e.g., -c intervalstyle=iso_8601). Rails even uses this pattern in dbconsole but not in the adapter itself.

Implementation

Move Rails-required variables and user-configured variables: from configure_connection to the connection string:

def new_connection
  # Build options string
  options_parts = []
  
  # Rails-required session variables
  options_parts << "-c standard_conforming_strings=on"
  options_parts << "-c intervalstyle=iso_8601"
  
  # User variables from database.yml
  variables = @config.fetch(:variables, {}).stringify_keys
  variables.each do |name, value|
    next if value == ":default" || value == :default || value.nil?
    # Use same escaping as dbconsole (line 86)
    escaped_value = value.to_s.gsub(/[ \\]/, '\\\\\0')
    options_parts << "-c #{name}=#{escaped_value}"
  end
  
  conn_params[:options] = options_parts.join(" ") unless options_parts.empty?
  PG.connect(conn_params)
end

Remove the corresponding SET commands from configure_connection.

Benefits

  • Zero extra roundtrips for session variable configuration
  • Fully backwards compatible - existing database.yml configs work unchanged
  • Existing pattern - Rails uses this for dbconsole
  • Minimal code change - reuses existing escaping logic

Edge Cases

  • client_min_messages and schema_search_path use setter methods with additional logic so we’d likely need to keep these as runtime SET commands
  • Timezone is dynamically reconfigurable - handled correctly by existing reconfigure_connection_timezone logic
  • options parameter has ~1024 char limit (unlikely to hit in practice perhaps? or can fallback to individual statements at that point?)

I am happy to propose a PR if there is consensus.