Heroku PGSettings
Last updated December 15, 2022
Table of Contents
Heroku Postgres non-legacy Standard, Premium, Private, and Shield plans 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
Essential-tier plans include the default settings, which you can’t reconfigure.
log-lock-waits
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 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 logs the duration of each completed statement if the statement ran for at least the specified number of milliseconds, where 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
log-statement controls which normal SQL statements are logged. This can help you debug complex queries or review queries made by your app or any database user. Valid values for log-statement are:
- 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 CREATE, ALTER, and DROP are 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 determines which 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 (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.