Last updated April 28, 2023
Heroku Postgres non-legacy Standard, Premium, Private, and Shield plans can manage database settings by configuring parameters via the
$ heroku pg:settings postgresql-large-1234 -a example-app === postgresql-large-1234 log-lock-waits: true log-min-duration-statement: 2000 log-statement: ddl track-functions: pl auto-explain: true auto-explain.log-analyze: false auto-explain.log-buffers: false auto-explain.log-min-duration: 200 auto-explain.log-nested-statements: false auto-explain.log-triggers: false auto-explain.log-verbose: false
Essential-tier plans include the default settings, which you can’t reconfigure.
log-lock-waits determines whether a log message is produced when a session waits longer than 1 second to acquire a lock. Lock waits can cause performance issues. The default value in Heroku Postgres is
$ heroku pg:settings:log-lock-waits off -a example-app log-lock-waits has been set to false for postgresql-large-1234. When a deadlock is detected, no log message will be emitted in your application's logs.
log-min-duration-statement logs the duration of each completed statement if the statement ran for at least the specified number of milliseconds. A value of
0 logs everything and a value of
-1 disables logging. This setting can help you track down unoptimized queries in your applications. The default value of log-min-duration-statement in Heroku Postgres is set to 2000 milliseconds (2 seconds).
In a system with hundreds of queries executing every second, log files can grow quickly and hamper database performance.
$ heroku pg:settings:log-min-duration-statement 3000 postgresql-large-1234 -a example-app log-min-duration-statement has been set to 3000 for postgresql-large-1234.
log-statement controls which normal SQL statements are logged. This setting can help you debug complex queries or review queries made by your app or any database user. Valid values for
- none: Stops logging normal queries. Other logs are still generated such as slow query logs, queries waiting in locks, and syntax errors
- ddl: All data definition statements, such as
- mod: Includes all statements from ddl as well as data-modifying statements such as
- all: All statements are logged
The default value of
log-statement in Heroku Postgres is
$ heroku pg:settings:log-statement all postgresql-large-1234 -a example-app log-statement has been set to all for postgresql-large-1234.
- none: No function statistics are recorded. This value is the default.
- pl: Procedural language function statistics are recorded (for example, PL/pgSQL functions).
- all: All functions, including SQL and C language function statics are recorded.
$ heroku pg:settings:track-functions pl postgresql-large-1234 -a example-app track-functions has been set to pl for postgresql-large-1234. Track only procedural-language functions.
auto_explain logs query execution plans automatically without having to run
EXPLAIN by hand. Running
auto-explain can help identify queries that run slowly and can help you understand how to optimize your database’s performance. Enable
$ heroku pg:settings:auto-explain on -a example-app
auto_explain can cause performance impacts and a significant increase in log volume so use with caution and monitor your database performance.
auto-explain enables the module for all future Heroku Postgres connections. Existing connections must be reestablished before
auto_explain logging occurs.
log-min-duration configures a minimum log duration in milliseconds. Setting a log duration of
-1 disables all logging, while setting a log duration of
0 logs all executed queries. The default value is
$ heroku pg:settings:auto-explain:log-min-duration 200 -a example-app
EXPLAIN ANALYZE on all queries regardless if they’re logged or not. This setting can have a significant performance impact on your database so use with caution. The default value is
$ heroku pg:settings:auto-explain:log-analyze on -a example-app
log-buffers is equivalent to calling
EXPLAIN BUFFERS and can only be used with
pg:settings:auto-explain:log-analyze turned on. The default value is
$ heroku pg:settings:auto-explain:log-buffers on -a example-app
log-nested-statements adds nested statements to the execution plan’s log. The default value is
$ heroku pg:settings:auto-explain:log-nested-statements on -a example-app
log-triggers includes trigger execution statistics in the execution plan’s logs. The default value is
$ heroku pg:settings:auto-explain:log-triggers on -a example-app
log-verbose includes verbose details in the execution plan’s logs. This setting is equivalent to running
EXPLAIN VERBOSE. The default value is
$ heroku pg:settings:auto-explain:log-verbose on -a example-app