Slow database? How to find heavy queries in PostgreSQL

Why does the database suddenly start lagging?

When a web application grows, it is common for certain screens to take precious seconds to load. Most of the time, the bottleneck is not the network infrastructure, but poorly constructed SQL queries that demand excessive effort from the storage engine. Discovering which command is locking up everything requires looking directly at the database control panel, mapping the behavior of each running instruction.

For system administrators, silent latency is one of the toughest daily challenges. The server looks healthy on CPU usage graphs, but users complain about constant freezes. This happens because the hard drive gets overwhelmed searching for data that could be accessed much more directly, as we discuss in our guide on how SQL indexes prevent full table scans. The secret is catching the exact moment when a query fails to be efficient.

How to configure PostgreSQL to log slow queries

PostgreSQL features highly adjustable native mechanisms to monitor internal behavior without installing heavy external tools. The heart of this setup lies in the main parameter file, usually called postgresql.conf. By tweaking specific guidelines in this file, you instruct the database to record everything that takes longer than tolerable to respond.

The main directive controlling this behavior is log_min_duration_statement. Setting this value to a number in milliseconds, like 500, makes any query taking longer than half a second automatically generate a system log entry. In practice, this means creating an intelligent filter that discards fast traffic and focuses only on what actually harms application performance.

Step-by-step to enable tracking without breaking production

Modifying a production database configuration requires extra care to prevent unwanted service downtime. The procedure can be performed directly by editing the parameter file or by executing dynamic alteration commands in the superuser session. Below is the recommended sequence to safely enable this feature:

1. Open the server terminal and access the main configuration file located inside the PostgreSQL data directory.
2. Locate the line related to logging timeout and change the default value to your desired limit in milliseconds, such as

   log_min_duration_statement = 1000

   to log anything above one second.
3. Save the file and apply modifications to the service without a full restart by running the dynamic reload command

   SELECT pg_reload_conf();

   directly inside the database console.

This approach ensures the system immediately watches query behavior without dropping active user connections. For complex environments, combining this auditing technique with a solid structural plan, such as creating partial indexes for specific queries, guarantees a massive speed boost during early analysis hours.

How to read logs and find the problematic command

With tracking enabled, the PostgreSQL log file starts receiving detailed lines containing the exact execution time and the full text of the problematic SQL query. Reading these files manually can feel intimidating due to the volume of data generated every minute, but standard command-line tools like grep help quickly filter what matters.

A typical record displays the timestamp, process identifier, and the statement that exceeded the established threshold. Spotting patterns in these messages helps determine whether issues stem from isolated access spikes or recurring structural flaws in application code. Additionally, when investigating problems inside containerized setups, it is crucial to know how to inspect logs and volumes in Docker containers to ensure database log files are not trapped in inaccessible paths.

Essential precautions to avoid filling up server storage

Enabling slow query logging is a powerful tool, but it brings an important side effect requiring attention: accelerated disk space consumption. If an application handles thousands of simultaneous hits and the configured timeout threshold is too low, the log file can grow by gigabytes within hours, exhausting available storage and crashing the entire server.

To prevent this operational disaster, the practical recommendation is starting with a conservative threshold, like two seconds, and gradually reducing it as major bottlenecks get fixed. Furthermore, configuring log rotation routines stops old files from accumulating indefinitely and consuming vital machine resources.

Final considerations on continuous monitoring

Identifying slow queries in PostgreSQL is not a one-time chore, but a continuous process of maintaining application health. By combining prudent log configuration with periodic analysis of executed instructions, the team gains predictability and responsiveness before users notice any degradation. Keeping the database nimble ensures scalability and longevity for any software project.