Understanding PostgreSQL Logging Levels and How They Impact Your Project

Last edited: 1/18/2025

Since each Supabase project uses PostgreSQL as its underlying database engine, it’s common to adjust logging settings for various reasons—whether for debugging issues, monitoring database performance, or auditing actions. However, modifying logging levels improperly can lead to an excessive amount of log data being generated, which can quickly fill up your disk space and cause significant performance degradation or even system failure.

1. Overview of PostgreSQL logging levels

PostgreSQL provides multiple logging levels that allow you to control how much information gets logged. These include:

Log LevelDescription
DEBUG1-5Logs very detailed information about the operations of the database, useful only for deep debugging.
INFOLogs information about routine database operations that aren’t necessarily errors but may still be relevant to track.
NOTICELogs messages that are not errors but may still be noteworthy.
WARNINGLogs warnings, which indicate issues that don’t prevent execution but could cause problems later.
ERRORLogs errors that cause statements to fail.
LOGLogs general messages such as startup, shutdown, or checkpoints.
FATALLogs errors that cause the database session to fail.
PANICLogs critical issues that force the database to shut down.

Each of these log levels is useful in specific situations. Here's an example of what messages tagged with each severity level look like:


_10
DEBUG: server process (PID 12345) exited with exit code 0
_10
INFO: vacuuming "example_schema.public.example_table"
_10
NOTICE: identifier "very_very_very_long_table_name_exceeding_63_characters" will be truncated to "very_very_very_long_table_name_exceedin"
_10
WARNING: SET LOCAL can only be used in transaction blocks
_10
LOG: statement: UPDATE example_table SET column_name = 'Example Value' WHERE id = 10;
_10
ERROR: relation "exam" does not exist at character 7
_10
FATAL: role "admin" does not exist
_10
PANIC: database system shutdown requested

The default log level is set to WARNING through the log_min_messages setting, and we recommend keeping it that way.

2. How high log levels can affect your database

When users alter a high level of log settings, the database can start generating an overwhelming number of log entries. This can quickly escalate to issues such as:

  • Disk Space Exhaustion: Log files can grow exponentially if verbose levels like DEBUG, INFO, or NOTICE are enabled for long periods. And running out of disk space due to log bloat can cause your database to stop accepting writes and slow down query performance.

  • I/O Overload: Writing too many logs increases input/output (I/O) operations, which can negatively impact database speed. A database bogged down by heavy logging will take longer to process requests, leading to slower application performance.

  • Database Lockups: In extreme cases, if the disk is filled to capacity with logs, your database could lock up, leading to downtime or severe performance degradation.

3. Common scenarios that cause log overload

Here are a few common scenarios where excessive logging can become a problem:

  • Extended Use of DEBUG Logging: While useful for troubleshooting, leaving DEBUG logging enabled for a long period can lead to the accumulation of massive amounts of logs.

  • Setting INFO for Monitoring: Logging INFO can be helpful for tracking general database activity, but if left on indefinitely, it can still result in a lot of noise and unnecessary log growth.

  • Frequent Write Operations: If your database processes a lot of write operations (such as inserts, updates, or deletes), even low-level logs (like NOTICE or INFO) can lead to significant log accumulation.

4. How to manage PostgreSQL log levels effectively

a. Choose the Right Log Level For most users, setting PostgreSQL logs to WARNING or ERROR is sufficient for regular operations. Here’s a general guideline:

  • WARNING: Use this level for general logging during normal operations. It captures issues that may not be critical but are worth paying attention to.

  • ERROR: This level is ideal for production environments. It logs only failures that prevent queries from being executed, reducing log noise significantly.

  • DEBUG, INFO, or NOTICE: Use these levels sparingly and only for short-term debugging or diagnostics. Always remember to revert the setting once you've collected the necessary information.

b. How to Adjust Log Levels You can adjust log levels using SQL commands in the SQL Editor or any connected PostgreSQL client:

  • To check the current log level:

_10
SHOW log_min_messages;

  • To set the log level to WARNING (recommended default):

_10
ALTER ROLE postgres SET log_min_messages TO 'WARNING';

  • To set the log level to ERROR:

_10
ALTER ROLE postgres SET log_min_messages TO 'ERROR';

  • To reset to the default level:

_10
ALTER ROLE postgres RESET log_min_messages;

5. Conclusion

PostgreSQL logs provide a powerful way to gain valuable insights into your database activity and performance when properly configured, but the key lies in finding the right balance. When set up properly, they can be incredibly useful.

6. Other resources

a. What Events Are Logged in Postgres For a detailed explanation of the types of events logged in your database (such as connection events, checkpoint events, long-running queries, cron jobs, and severity-based logging), you can refer to the official documentation here:

What Events Are Logged in Supabase

b. PGAudit: Postgres Auditing for Compliance and Security

We support PGAudit extension, which extends PostgreSQL’s built-in logging capabilities to track database activities for auditing purposes.

How to Enable the PGAudit Extension

For detailed configuration instructions and logging options, refer to the complete documentation: PGAudit Configuration Guide

c. Debugging Functions For more information on how to debug functions in Supabase, refer to the official guide: Debugging Functions.