Slow database? How to track heavy queries in PostgreSQL

Why PostgreSQL needs help revealing what is running slowly

When an application starts responding slowly, the blame almost always falls on the database. PostgreSQL is extremely efficient by default, but it does not spontaneously announce which SQL command is consuming the most server resources. In practice, this means inefficient queries keep running in the background, devouring memory and CPU until the entire system grinds to a halt. To solve this, we must activate slow query tracking logs.

Many developers rely solely on intuition or generic infrastructure alerts to guess the root cause of sluggishness. However, without precise data on which records or tables are struggling, any optimization attempt becomes a blind shot in the dark. Before applying complex fixes or restructuring data, the absolute first step is configuring the database engine itself to confess exactly which commands are taking longer than acceptable.

Where to change things: configuring postgresql.conf

All tracking magic in PostgreSQL happens inside the main configuration file, called postgresql.conf. Depending on how your operating system was set up, this file is usually stored inside the database data folder. To edit it, you need administrator privileges on the Linux server. In practice, opening this file reveals dozens of commented parameters controlling everything from available RAM to how the database handles log files.

The two fundamental parameters you should look for are log_min_duration_statement and logging_collector. The first sets the time limit in milliseconds: any query taking longer than this value is automatically recorded in the database log file. The second activates the internal collector, which organizes these messages neatly. To deepen your investigation into specific command behavior, check out the guide on how to use EXPLAIN ANALYZE to dissect execution plans.

Step-by-step guide to enable slow query logging

To get your hands dirty safely, run the commands below directly in your database server terminal. Remember to adjust paths according to the PostgreSQL version installed in your production or development environment.

1. Open the main configuration file using a text editor like nano with elevated privileges:
   

   sudo nano /etc/postgresql/15/main/postgresql.conf

2. Locate the log recording lines and change the values to enable tracking for queries taking over two hundred milliseconds:
   

   logging_collector = on
   log_min_duration_statement = 200

3. Save the file and restart the PostgreSQL service to apply the new rules to the storage engine:
   

   sudo systemctl restart postgresql

Identifying bottlenecks in massive tables

When tables grow past millions of rows, a simple missing index turns a quick search into a full disk scan. PostgreSQL performs what we call a sequential scan, meaning it looks row by row from start to finish to find what you asked for. When data volume is huge, this operation saturates the disk subsystem and freezes concurrent connections. The slow query log we just configured exposes exactly which tables are suffering from this massive, unnecessary reading pattern.

Another critical point to watch in generated logs is the frequency of repetitive queries. If the same poorly optimized query is fired hundreds of times per minute by a slow API struggling with connection pooling issues, the accumulated impact brings down general server performance. Cross-referencing log data with monitoring tools helps separate wheat from chaff, showing whether a bottleneck is an isolated traffic spike or a chronic software architecture flaw.

Reading logs and clearing the critical path

With logs enabled and running in production, the next challenge is knowing how to read what the database wrote. PostgreSQL log files are stored in the default log directory and carry timestamps, user identifiers, and the exact SQL command that breached the time limit. Reading these files directly can be exhausting, which is why many engineers use simple terminal commands to filter out only the most problematic lines of the week.

To keep the environment healthy long-term, combine log tracking with periodic maintenance routines. Analyze generated reports, create missing indexes on frequently searched columns, and verify that the application is not accumulating ghost connections. Ensuring database health is an ongoing process that protects business stability and dramatically improves the end-user experience. To explore these architectural concepts further in production, see our related technical analysis on How to test electromechanical relays with a multimeter on bench.

Final considerations

Monitoring slow queries in PostgreSQL is not an operational luxury, but a basic necessity for any system aiming to grow sustainably. By correctly configuring log parameters and learning to interpret the signals emitted by the database, you replace guesswork with precision engineering. Large tables cease to be phantom threats and start being managed based on real performance data.

We hope this practical guide helps you shield your applications against hidden bottlenecks. Remember that database optimization is a journey of constant adjustments, where every well-placed index represents precious seconds saved for your users and clients.