Heroku PGSettings
Last updated October 11, 2021
Table of Contents
Heroku Postgres non-legacy production plans (Standard, Premium, Private and Shield) can manage database settings by configuring parameters via the pg:settings command.
$ heroku pg:settings postgresql-large-1234 -a example-app
=== postgresql-large-1234
log-lock-waits: true
log-min-duration-statement: 2000
log-statement: ddl
Heroku Postgres settings is available only to production-class plans (Standard, Premium, Private, and Shield). Hobby-tier plans include the default settings, which cannot be reconfigured.
log-lock-waits
log-lock-waits controls whether a log message is produced when a session waits longer than 1 second to acquire a lock. This is useful in determining if lock waits are causing poor performance issues. The default value in Heroku Postgres is on.
$ 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
log-min-duration-statement causes the duration of each completed statement to be logged if the statement ran for at least the specified number of milliseconds, where a value of 0 will log everything and a value of -1 will disable logging. This setting can be helpful in tracking 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 executed every second, this setting can result in log files growing pretty quickly and could hamper performance of the database.
$ 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
log-statement controls which normal SQL statements are logged. This feature is useful when hunting a bug that involves complex queries or inspecting queries made by your app or any database user. Valid values for log-statement are:
- none: Stops logging normal queries. Other logs will still be generated such as slow query logs, queries waiting in locks, and syntax errors
- ddl: All data definition statements, such as CREATE, ALTER and DROP will be logged
- mod: Includes all statements from ddl as well as data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, COPY
- all: All statements are logged
The default value of log-statement in Heroku Postgres is ddl.
$ heroku pg:settings:log-statement all postgresql-large-1234 -a example-app
log-statement has been set to all for postgresql-large-1234.
track-functions
track-functions controls what, if any, functions have their execution statistics tracked. These statistics can be found in the pg_stat_user_functions view. Valid values for track-functions are:
- none: No function statistics are recorded. This is the default.
- pl: Procedural language function statistics are recorded (e.g. 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.