Heroku PGSettings
Last updated 12 September 2017
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 sushi
=== 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) on Postgres 9.6 and above. Hobby-tier plans include the default settings, which cannot be reconfigured.
lock-log-waits
lock-log-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 sushi
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 sushi
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 sushi
log-statement has been set to all for postgresql-large-1234.