Postgres Logging for Performance Optimization
19 min readMore by this author
A modern-day Postgres instance creates robust and comprehensive logs for nearly every facet of database and query behavior. While Postgres logs are the go-to place for finding and debugging critical errors, they are also a key tool in application performance monitoring.
Today let’s get set up with logging for Postgres - starting with the basics of what to log, how to log what you want, and as reward for your hard work - how to use these to monitor and improve performance. The Postgres docs on logs are excellent, so please consult those for the most up to date and comprehensive configurations. This blog reads between the lines a bit beyond the docs to offer some practical advice and settings. As always, your mileage may vary.
Let’s get into it and talk about:
- Setting up logging levels, logging SQL, rotating logs
- Logging for performance monitoring
- Extracting and parsing logs into usable information
WAL note: This post is limited to the server’s message and error logging - not the transaction Write Ahead Log (WAL). While that is a log, the purpose of WAL is to log all data and schema changes for backups, disaster recovery, and replication streaming.
Initiating logging for Postgres
First thing, out of the box Postgres just sends logs to the terminal. To turn on sending things to log files, turn on the logging collector.
logging_collector = on
What file format do you want for logs?
The log message formatting is determined by log_destination parameter, which can be set to one or more of: stderr, csvlog, jsonloglog, and syslog. stderr is the default. Use commas to separate values when using more than one logging destination:
-- setting multiple log destinations
log_destination = 'stderr,json'
If logging_collector = 'on', then stderr, csvlog, and jsonlog logging will go to files in the directory specified by log_directory, with csv and json requiring that the logging collector be enabled.
There are many reasons why logs may be written in multiple file formats. Many hosted and fully managed systems will have logs available in different formats for use by different tools. For Crunchy Bridge we have the live logs and log tail in our CLI of the syslog destination. We often use jsonlog for all of our internal logs. syslog is what you’ll have on servers for using the log shipping to an external logging host.
What level of logging do you want?
All log messages generated by the server will have one of these severity levels:
- PANIC: Severe errors — system must shut down and recover.
- FATAL: Errors that cause the session to terminate. LOG : Server events such as checkpoints.
- ERROR: Errors that abort the current operation but not the session.
- WARNING: Potential problems — deprecated features, possible data issues
- NOTICE: Significant messages — non-critical issues like “table doesn’t exist.”
- INFO: Low informational messages — autovacuum, config reloads.
- DEBUG1-5: Basic debug to most verbose
Log messages will look like this :
-- background worker crash
ERROR: background worker "logical replication launcher" crashed
--disk i/o
ERROR: could not fsync file "pg_wal/0000000100000000000000A3": Input/output error
--out of disk space for temp files
ERROR: could not create temporary file: No space left on device
--vacuum warning
WARNING: relation "public.large_table" contains more than "autovacuum_vacuum_threshold" dead tuples
The log_min_messages server setting is use to determine which log messages are actually logged to the configured log destination(s). All messages with the configured severity level or above will be sent. The default is error and that’s a generally good setting. Warning may also be useful for debugging.
log_min_messages='warning'
So WARNING includes all messages with warning, error, log, fatal, and panic severities. In general, the debug levels would only be used in development or for a specific purpose.
Logging SQL statements
In addition to log severity selection covered above, SQL queries can be selected for logging based on the log_statement parameter. The values you can choose from are:
- none - Log nothing. This will by default not log SQL statements, but if there are warnings or errors, those will still appear with log_min_message configuration.
- ddl - Log data definition changes only, so this will log any change to table definitions, columns, and indexes.
- mod - Log modification to data including all DDL plus inserts, updates, and deletes.
- all - Log every SQL statement, query, and all DDL (note: this is generally not recommended for production).
DDL is a good choice for production.
log_statement = 'ddl';
Statements with syntax errors or that fail during their parsing or planning stages are not covered by log_statement. These are covered by log_min_error_statement, which should be set to ERROR or lower to log them.
log_min_error_statement=ERROR
SQL errors will look like this, with the HINT line appearing when relevant. If you’re logging the actual statement with log_min_error_statement = 'error', that appears last.
2025-05-09 14:02:37 UTC [28561] ERROR: operator does not exist: integer == integer at character 33
2025-05-09 14:02:37 UTC [28561] HINT: Perhaps you meant to use the standard operator "=".
2025-05-09 14:02:37 UTC [28561] STATEMENT: SELECT * FROM users WHERE id == 42;
Logging of prepared statements & sensitive data
One common concert for many is ensuring that sensitive data such as credit card numbers or PII is not included in query data that is logged. The log_parameter_max_length and log_parameter_max_length_on_error parameters allow you to limit the length of prepared statement bind parameter values logged with query and error log messages for prepared statements, respectively, to the specified number of bytes. This will apply to bind parameters of both explicit, named prepared statements run with PREPARE / EXECUTE and the “unnamed” prepared statements run by application database drivers that use the extended query protocol.
The default values of -1 for these will log all bind parameters in full. Set them to 0 to fully disable bind parameter logging.
log_parameter_max_length = 0
log_parameter_max_length_on_error = 0
If you only need this done for certain queries or transactions these can also be set on-the-fly with SET SESSION and SET LOCAL, or they can be set for all queries for a given user with ALTER USER, on a given database with ALTER DATABASE, and even for all queries for a given user on a specific database.
# set for an entire session
SET SESSION log_parameter_max_length = 0;
SET SESSION log_parameter_max_length_on_error = 0
# set for a transaction
BEGIN;
SET LOCAL log_parameter_max_length = 0;
SET LOCAL log_parameter_max_length_on_error = 0;
...
COMMIT;
# set for all queries run by user bob
ALTER ROLE bob SET log_parameter_max_length = 0;
ALTER ROLE bob SET log_parameter_max_length_on_error = 0;
# set for all traffic on database pii_db
ALTER DATABASE pii_db SET log_parameter_max_length = 0;
ALTER DATABASE pii_db SET flog_parameter_max_length_on_error = 0;
# set for all queries run by bob on the pii_db
ALTER ROLE bob IN DATABASE SET og_parameter_max_length = 0;
ALTER ROLE bob IN DATABASE SET log_parameter_max_length_on_error = 0;
Formatting the log entries
Out of the box, Postgres log entries look like this:
The timestamp and process ID portion come from the default We often recommend folks use a better prefix to give more context for what generated the log message. If you set a prefix, make sure to keep the process ID ( See the log_line_prefix documentation for a full list of valid printf-style The Beyond the server and query logs, you can also audit user behavior with the Use of the Audit Logging records more granular data, such as who performed the action, when it happened, what the exact changes were. This allows for tracking specific user actions, including inserts, updates, deletes, and administrative commands. Possible values for the pgaudit log are: Audit logs look like this and are comma separated (CSV) If you noticed that the regular logs and audit logs overlap each other, you’re right. For Crunchy Bridge users, we audit everything except the application user role. So by default your main Postgres role and generated user roles are fully audited. The The default goes to hour, minute, second which is probably unnecessary so this is a good simple one: Log files written by Postgres for Now we have set up some logs …. BUT if you don’t set up rotation you will just fill up your disk with logs. This sets the rotation at 1 day. This sets a file size for rotation, in case its bigger than 10MB before that 1 day is up. If a If a Now that you have some proper logging set up, you can use this to pinpoint particular system problems. Usually working with Postgres logs looks something like this: Ok, if you’re still with me here, you’re up to the point where we have some logs, we know what they look like and what they’re used for and we can use them to troubleshoot critical errors. But we’re good little DBA boys and girls, so we never have errors. We just want logs to make our queries faster. So let’s do that. If you would like to capture information about queries that run longer than some time period, you can configure that using the As you start working on query performance, logging the slowest queries is a great way to see what isn’t efficient. You can log any time your query is waiting on a lock by turning on With lock wait logging enabled the Logged lock waits will look like this: There we can see: Effcience Postgres memory useage is a huge part of fast and snappy database operations. If your queries are having to perform reads or sorts on disk, instead of in the memory buffer, it might mean you need to increase A very common way to log memory performance is to log when Postgres creates temp files. By default this is turned off, Ideally you set the log_temp_files to be the same size as your working memory, The actual logs for temp files will look like this You can set up auto_explain to log queries of various durations There’s other settings for auto_explain to show buffers and a couple other things so see the auto_explain docs. Auto_explain generates a lot of logs so proceed with caution here. For really big queries or queries on partitioned tables, these plans can be really long. An alternative to auto_explain all the time is setting it for a single session. Auto_explain logs will look something like this: Whether or not a given autovacuum job is logged is controlled by the Here is an example of what this logs for an autovacuum And here is what they look like in prior major versions: For most folks with large applications, it is recommended to do something with your logs rather than just toss them aside. Having your logs accessible and searchable is critical for bug fixing and really helpful as you’ve seen above for performance. Logs are a little like insurance. You may not need them every day, but when you have a problem, you’re glad they’re there. There’s an open source project for analyzing Postgres logs called pgBadger. If you have spent any time looking through logs by hand, this project is going to seem like magic. It will turn Postgres logging output into an html page with zoomable charts. Most folks run periodically or on demand to analyze PostgreSQL logs and generate HTML reports. If you’re using a managed cloud Postgres service, if you can get logs out in a file, you can use pgBadger. On Crunchy Bridge you can tail logs to pgBadger like this: There’s quite a few folks out there that are happy to host, parse, and let you search your logs. We work with customers that are quite happy with pgAnalyze, Datadog, Honeybadger, and many others. These may run as an agent, small pod container, or another service to export the logs. For folks using a cloud host, these are a really good idea. It is probably not surprise that logs today for a large scale application require their own management. Some teams are opting to do their own hosting and querying of logs. Systems like Snowflake, Clickhouse, Crunchy Data Warehouse, and many others can offer a SQL based storage and query engine for high throughput logs. When stored as flat files in object storage, these logging systems can be very cost effective. Here’s a big summary of the settings for everything I covered today. But guys …. it depends …. it depends on a lot of things, so review the docs and your individual application needs. Don’t just copy paste this in. The first thing I hope you learned today is that Postgres has a lot of logs - and a lot of flexibility in how to use them. The second thing, is hopefully you’ll tune your logs so you’re keeping some details to help you with your database. Archiving your logs is really helpful and if you’re on a hosted system, set up a log drain. This ensures your logs are ready to search when you need them. The flip side to my plea to set up logging is that they are expensive. Logs can easily fill up your disk and waste quite a bit of your company’s hard earned profits if you aren’t careful. So its a tradeoff. Keep logs that are helpful. Have something setup to search them. But don’t keep logs if you’re not actively using them. Set up rotation so they’re thrown after after a few days or get to a certain size. When you’re actively working on performance, pay attention to slow queries and log those full queries and query plans with auto_explain. Log temp files so you know when Postgres isn't able to use the cache. Set up logging for your development and staging environments. This will give you chance to find bugs, check on query plans, without breaking the bank on production logging. Moderation is the key to both after-work happy hour …. and Postgres logging. Cheers!2025-05-19 13:49:04.908 EDT [3108283] ERROR: column "asdfklasdf" does not exist at character 8
log_line_prefix:log_line_prefix = '%m [%p] '
log_line_prefix = '%m [%p]%q %u@%d '
%p) as that is a huge huge help when troubleshooting a specific process to find/stop. %u will add the user and %d the database which is helpful if you’re using more than the postgres db in a single instance.% escape sequences.log_error_verbosity setting is used to determine how verbose the log message itself is.terse shortens errors with a SQL state error code and short logsdefault which includes the error and hint messagesverbose which includes additional error context like sources and function names. This is not recommended for production but could be a good useful setting for development.log_error_verbosity = 'default'
Audit logging
PGAudit extension. PGAudit is not a core extension that ships with Postgres itself, but there are packages for it in the repositories for all major OS distributions.PGAudit extension requires that pgaudit is in shared_preload_libraries, the extension is created in each database where auditing is needed, and pgaudit.log is set to a value other than none.-- add to preloaded libraries
shared_preload_libraries = 'pgaudit'
-- add extension
CREATE EXTENSION pgaudit
-- enable the pgaudit.log
pgaudit.log = ddl
read, write, role, ddl, misc.ALTER ROLE audited_user SET pgaudit.log = 'read, write, ddl';
2025-05-09 12:34:56.789 UTC [12345] myuser@mydb LOG: AUDIT: SESSION,1,SELECT,pg_catalog.pg_stat_activity,SELECT * FROM pg_stat_activity;
pgAudit provides detailed audit logging (including session-level information, roles, and changes) in addition to Postgres’s built-in logging. If you only need to log DDL statements and you're not interested in the additional auditing features provided by pgAudit, then log_statement = 'ddl' may be sufficient.Log file naming and locations
log_filename setting is used to specify the format of the log filenames using strftime escape patterns. By default the log file name has postgresql with a timestamp.log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_filename = 'postgresql-%Y-%m-%d'
stderr log files will have a .log suffix, csvlog files will have a .csv suffix, and jsonlog files will have a .json suffix.stderr, csvlog, and jsonlog logging are located in the directory specified by log_directory. This can be either a full absolute path or a relative path that will be relative to the data_directory path. The location of log files written to syslog will be up to the system’s syslog configuration.-- where on the host is the data directory
SHOW data_directory;
-- where on the host is the log directory
SHOW log_directory;
-- what do the log file names look like
SHOW log_filename;
-- exact location of the current log file
SELECT pg_current_logfile();
Rotating logs
log_rotation_age = '1d'
log_rotation_size = '10MB'
log_filename format specification would result in the re-use of log filename, e.g. ‘postgresql-Mon.logwill be used on every Monday,log_truncation_on_rotationwill cause the given log to be truncated before each subsequent use. Iflog_truncate_on_rotation` is not enabled then the existing log will be appended to instead of truncated.log_truncate_on_rotation = 'on'
log_filename format is used that does not result in automatic file name re-use, e.g. postgresql-%Y-%m-%d.log, it is recommended to use an external log rotation tool such as Linux’s logrotate to handle removing old logs as needed, perhaps after they have been archived to a separate long-term storage location, to avoid excessive disk space usage.Troubleshooting with logs
Logging for performance bottlenecks in logs
Logging long running queries
log_min_duration_statement parameter. This is PostgreSQL’s slow query logging threshold so it is helpful in particular for debugging long-running queries.log_min_duration_statement = '1s'
-- example log for a query that took 1000+ seconds
LOG: duration: 2001.342 ms statement: SELECT count(*) from orders;
Logging locks and lock waits
log_lock_waits. Lock waits in your logs can be a good indicator that processes are being contentious. There is virtually no overhead on enabling this and it’s very safe for production databases. This is set to “on” by default on Crunchy Bridge clusters:log_lock_waits = 'on'
deadlock_timeout setting is used as the threshold for logging lock waits, e.g. with deadlock_timeout = '1s' any query that waits for a lock for 1s or longer will have that wait logged.2024-05-16 14:45:12.345 UTC [45678] user@database LOG: process 45678 still waiting for ShareLock on transaction 123456 after 1000.001 ms
2024-05-16 14:45:12.345 UTC [45678] user@database DETAIL: Process holding the lock: 12345. Wait queue: 45678, 45670.
2024-05-16 14:45:12.345 UTC [45678] user@database STATEMENT: UPDATE orders SET status = 'shipped' WHERE id = 42;
Logging temp files
work_mem, expand your memory capacity in some way. Adding indexes or query rewrites can also reduce the amount of data given queries need process to return their results.-1. A setting of 0 will record in your Postgres logs whenever a temporary file is created - generally not a good idea.work_mem. work_mem is the memory limit per operation before Postgres needs to spill to disk. If an operation fits within work_mem, the system won’t create temp files, so no logging needed. If the operation spills to disk, it creates a temp file at least as big as work_mem. So here’s you’re logging whenever a temp file exceeds the size of the memory allowed for one operation*.*-- log temp files >4mb in kb, set to current work_mem setting
log_temp_files = '4096'
2024-05-16 14:23:05.123 UTC [12345] user@database LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp1234.0", size 245760
2024-05-16 14:23:05.123 UTC [12345] user@database DETAIL: Sort operation used temporary file because work_mem was exceeded
Query logging with auto_explain
auto_explain is a Postgres extension that automatically logs EXPLAIN plans of queries, which is useful for debugging and performance tuning. Auto_explain ships with Postgres but it has to be turned on explicitly.-- add to preloaded libraries
-- create the extension
CREATE EXTENSION IF NOT EXISTS auto_explain;
-- restart Postgres after this
-- Log plans for queries that run longer than 1000ms
auto_explain.log_min_duration = '1000ms';
LOG: duration: 1008.035 ms plan:
May 17 02:42:06 z7j4asvir5dufokh5hpzoy postgres[43712]: [29-2]
Query Text: select count(*) from page_hits limit 1000;
Autovacuum logging
log_autovacuum_min_duration parameter, which defaults to 10 minutes since PG15 and was disabled by default in previous versions with a value of -1. As autovacuum’s log entries contain all of the same information seen in the output of manual VACUUM VERBOSE commands many folks will lower this to a second or two a detailed record of the work done by the autovacuum daemon, or often all the way down to 0 seconds to log all of its work.log_autovacuum_min_duration = '1s'
VACUUM operation since PostgreSQL major version 15:[3506673][autovacuum worker][501/2614][0] LOG: automatic vacuum of table "testdb.public.pgbench_accounts": index scans: 1
pages: 0 removed, 327869 remain, 81969 scanned (25.00% of total)
tuples: 0 removed, 14769015 remain, 2000000 are dead but not yet removable
removable cutoff: 929, which was 3 XIDs old when operation ended
new relfrozenxid: 929, which is 11 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 49181 pages from table (15.00% of total) had 2999999 dead item identifiers removed
index "pgbench_accounts_pkey": pages: 54840 in total, 8224 newly deleted, 8224 currently deleted, 0 reusable
I/O timings: read: 174.219 ms, write: 0.000 ms
avg read rate: 26.491 MB/s, avg write rate: 22.489 MB/s
buffer usage: 276192 hits, 41175 misses, 34955 dirtied
WAL usage: 123002 records, 57432 full page images, 75538789 bytes
system usage: CPU: user: 0.64 s, system: 0.27 s, elapsed: 12.14 s
[17656][autovacuum worker][5/463][0] LOG: automatic vacuum of table "testdb.public.pgbench_accounts": index scans: 1
pages: 0 removed, 327869 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 0 removed, 14740860 remain, 2000000 are dead but not yet removable, oldest xmin: 760
index scan needed: 49181 pages from table (15.00% of total) had 2999999 dead item identifiers removed
index "pgbench_accounts_pkey": pages: 54840 in total, 8224 newly deleted, 8224 currently deleted, 0 reusable
I/O timings: read: 488.030 ms, write: 238.542 ms
avg read rate: 55.609 MB/s, avg write rate: 21.009 MB/s
buffer usage: 192958 hits, 124428 misses, 47008 dirtied
WAL usage: 122981 records, 0 full page images, 19019531 bytes
system usage: CPU: user: 1.14 s, system: 0.80 s, elapsed: 17.48 s
Extracting and parsing logs
pgBadger
--send the CLI logs to a local text file
cb logs qzyqhjdg3focnta3zvleomq > pglogs.txt
-- pgBadger reads the text file and provides html output
pgbadger -f syslog pglogs.txt -o out.html
3rd party log drain tools
--set up syslog drain
log_destination = 'syslog';
Data warehousing for logs
Summary of recommended logging configurations
-- Set up logging collector
ALTER SYSTEM SET logging_collector = 'on';
-- log system error messages
ALTER SYSTEM SET log_min_messages='error'
-- log all data definition changes
ALTER SYSTEM SET log_statement = 'ddl';
-- log the full statement for sql errors
ALTER SYSTEM SET log_min_error_statement= 'ERROR';
-- set log file name
ALTER SYSTEM SET log_filename = 'postgres-%Y-%m-%d';
-- add database name and process id to log prefix
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d ';
-- rotate logs every day
ALTER SYSTEM SET log_rotation_age = '1d'
-- enable the pgaudit.log
ALTER SYSTEM SET pgaudit.log = 'ddl';
-- log queries longer than 1000ms
ALTER SYSTEM SET log_min_duration_statement = '1000';
-- Log lock waits
ALTER SYSTEM SET log_lock_waits = 'on';
-- log temp files, when postgres needs disk instead of cache, set to your work_mem
ALTER SYSTEM SET log_temp_files = '4096';
-- Log plans for queries that run longer than 1000ms
ALTER SYSTEM SET auto_explain.log_min_duration = '1000ms';
-- Set up some kind of log destination to a place to search logs
ALTER SYSTEM set log_destination = 'syslog';
Final thoughts