Step 3: Review auto_explain settings

There are a number of auto_explain settings that allow you to configure which queries will generate EXPLAIN plans. You can read the full Postgres documentation about these here. The right configuration will depend on your server and workload, but we’ve found the following is a good starting point:

Add these to .spec.postgresql.parameters in your Cluster manifest, alongside the pg_stat_statements.* keys already there from Initial Setup, for example:

spec:
  postgresql:
    parameters:
      pg_stat_statements.max: "10000" # already set, keep it
      pg_stat_statements.track: "all" # already set, keep it
      track_io_timing: "on" # already set, keep it
      auto_explain.log_format: "json"
      auto_explain.log_min_duration: "1000"
      auto_explain.log_analyze: "on"
      auto_explain.log_buffers: "on"
      auto_explain.log_timing: "off"
      auto_explain.log_triggers: "on"
      auto_explain.log_verbose: "on"
      auto_explain.log_nested_statements: "on"
      auto_explain.sample_rate: "1"

Apply the change with kubectl apply -f cluster.yaml. Unlike the shared_preload_libraries change in the previous step, these settings can be reloaded without a restart.