Query Logging

Just like in any complex system, stuff sometimes goes sideways in PostgreSQL: queries take forever, server load spikes, and users start freaking out. Logging is how you peek under the hood of your database to:

1. Spot slow queries — the ones that are dragging your app down.
2. Optimize performance — by analyzing how queries behave.
3. Diagnose errors — like constraint violations or bad syntax.
4. Enable auditing — tracking who did what on the server.

Key Logging Parameters

The PostgreSQL config files (postgresql.conf) have a bunch of parameters related to logging. Let’s break down the most important ones.

1. log_statement — SQL Query Logging

The log_statement parameter controls which SQL queries get written to the log. It can be set to:

• none — turn off query logging.
• ddl — log only data definition commands (like CREATE, ALTER, DROP).
• mod — log all commands that change data (like INSERT, UPDATE, DELETE).
• all — log every SQL query (including plain old SELECT).

Example:

To log every query, set this in your postgresql.conf file:

log_statement = 'all'

If you only want to log data-changing commands:

log_statement = 'mod'

After changing the config, don’t forget to restart the server:

sudo systemctl restart postgresql

2. log_duration — Query Duration Logging

The log_duration parameter lets you log how long each query takes. Super useful for figuring out which queries are eating up your time.

Example:

To turn on logging of query durations, set:

log_duration = on

If you only want to log slow queries, use the log_min_duration_statement parameter (we’ll get to that in a sec).

3. log_min_duration_statement — Logging Slow Queries

This parameter logs only those queries that take longer than the specified time (in milliseconds). Super handy for finding the “slowpokes” in your database.

Example:

To log queries that run longer than 1 second:

log_min_duration_statement = 1000

If you want to log EVERY query no matter how fast, just set it to 0:

log_min_duration_statement = 0

Setting it to -1 turns off duration-based logging.

4. log_line_prefix — Log Message Structure

The log_line_prefix parameter lets you customize the format of each log message. It’s great for adding context to every logged query (like username, PID, date, and time).

Example:

To log username, database, time, and process, use:

log_line_prefix = '%t [%p]: [%d]: [%u]: '

Here’s what those mean:

• %t — query time.
• %p — process PID.
• %d — database name.
• %u — username.

Full list of options: PostgreSQL Docs.

5. logging_collector — Log Collector

The logging_collector parameter turns on the mechanism that writes logs to files. If it’s off, logs only go to standard output (stdout), which can be a pain.

To activate the log collector:

logging_collector = on

Don’t forget to set the log file path with log_directory and log_filename:

log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d.log'

Practical Use of Parameters

Let’s see how to use logging parameters in real-life scenarios.

Scenario 1: Logging All Queries

If you’re just starting with a new database and want to see everything that’s happening, set:

log_statement = 'all'
log_line_prefix = '%t [%p]: [%d]: [%u]: '

Now every query that runs in the DB will be logged — super handy for debugging!

Scenario 2: Finding Slow Queries

If you notice your server is “lagging” sometimes and want to find the problem queries, set:

log_min_duration_statement = 500  # Log queries longer than 500 ms
log_line_prefix = '%t [%p]: [%d]: [%u]: [%r] '

Here, %r adds the client’s IP address. After this setup, you’ll easily spot queries that are overloading your server.

Scenario 3: Minimal Logging Mode

If your server is running smoothly but you want a minimal set of logs for auditing:

log_statement = 'mod'
log_duration = off

This way, only data-changing queries (like INSERT, UPDATE) are logged.

Log Analysis

Once your logs start filling up, it’s important to know how to analyze them! You can:

Open the log file in a text editor:

cat /var/log/postgresql/postgresql.log

Use tools like grep to find slow queries:

grep "duration: " /var/log/postgresql/postgresql.log

Use advanced log analysis tools like pgBadger.

Pro Tips

Logging too much detail can put extra load on your disk. For production systems, only use the parameters you really need.

Regularly clean up or archive old log files so you don’t run out of disk space.

Logging works best when you only collect what you need. For example, log_min_duration_statement helps you stay focused and save resources.

So, what do you think? Now it’s no big deal for us to set up logging in PostgreSQL and use it to analyze queries and boost performance. Like one (well, not just one) unknown but definitely awesome developer said: “Logs are our way to look into the past of queries and fix the future of the database.”