Database hanging? How to kill slow queries in SQL

What happens when a database hangs due to excess queries

When a system starts responding with extreme sluggishness or simply stops loading screens, the most common culprit is usually a suffocated database. In practice, this means dozens or hundreds of requests are trying to access the same data simultaneously, creating an unwanted queue. This scenario is frequently worsened by poorly optimized queries that consume excessive memory and processing power. Before restarting the server—a drastic measure that often merely hides the problem—engineers need to audit what is happening under the hood. If you have ever faced this, you know that understanding the relational engine's behavior is the first step to regaining control of the application.

To deeply understand the impact of inefficient queries, it is worth remembering that relational systems like PostgreSQL or MySQL rely on rigid execution plans. When a query does not use proper indexes, the database is forced to read entire tables row by row, a process known as a full table scan. If this heavy query is repeated by multiple users, disk and CPU resources deplete rapidly. When a database suffers from structural bottlenecks, diagnosing the exact origin of the slowdown is usually an essential next step, as detailed in our guide on how to use EXPLAIN ANALYZE in PostgreSQL. The secret to resolving the freeze lies in quickly isolating the offending transaction before it contaminates the rest of the infrastructure.

How to audit active processes and find out what is locking everything

The first practical step to solving a critical slowdown is looking directly at the database control panel. In relational database management systems, there are system views and specific commands showing exactly which queries are running at that moment. In PostgreSQL, for instance, we query the dynamic view called pg_stat_activity to map all active connections. In MySQL, the equivalent command is the classic show processlist, which lists running threads and the time each takes to process. In practice, this audit works like an x-ray: it shows the IP address of the request origin, execution time, and the exact SQL command text clogging the queue.

To get hands-on safely, we need to run audit queries filtering only what matters, ignoring idle connections. Below is a practical example of a query in a PostgreSQL environment to list processes running for more than thirty seconds:

SELECT pid, usename, client_addr, backend_start, query FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '30 seconds';

This quick filter separates the wheat from the chaff, displaying only long-running queries deserving immediate attention. The pid field represents the unique identifier of that specific process in the operating system. It is precisely this number we will use shortly after to order the interruption of the problematic task if the execution plan shows no sign of spontaneous completion.

The difference between just canceling a query and terminating the connection

When we find the command causing the hang, an operational dilemma arises: should we just cancel the query or drop the entire client connection? In database engineering, this decision involves important trade-offs regarding integrity and user experience. Canceling only the query (usually using commands like pg_cancel_backend) sends a signal for the database to stop the current work of that specific thread while keeping the user session open. This is useful when the client application can handle the timeout error gracefully without losing login context or other temporary variables.

On the other hand, terminating the connection entirely (using commands like pg_terminate_backend or kill connection) closes the communication channel abruptly. In practice, this forces the database to execute a transactional rollback process, undoing partial changes to ensure data is not corrupted. If the stuck transaction was only reading data (running SELECT queries), termination is safe and immediate. However, if the query was modifying records (with INSERT, UPDATE, or DELETE commands), the database will need to spend time and effort restoring the previous state of the affected tables before freeing up the network port.

Step-by-step guide to safely interrupting blocking transactions

When the situation demands urgent manual intervention to save the server, following a standardized procedure prevents catastrophic production errors. Below, we present a safe sequential workflow to identify and eliminate blocking processes in PostgreSQL-based relational environments.

1. Open the terminal connected to the database with an administrative user and execute the audit query to list active and long-running connections.
2. Note the numerical identifier (PID) of the problematic query that is locking the system or generating cascading locks.
3. Execute the targeted termination command, replacing the number with the PID collected in the previous step, to free up server resources.

The practical command to terminate a stuck process in PostgreSQL using its numerical identifier is shown in the following block:

SELECT pg_terminate_backend(12345);

This instruction sends a direct signal for the engine to halt that process's activity and deallocate associated memory. After executing the command, immediately check the monitoring panel to ensure the request queue has started moving again. Next, open the application logs to investigate why that specific query took so long to execute.

Best practices to prevent the problem from happening again

Killing slow queries in emergency situations is a necessary fire-fighting measure, but it should never become the primary operating routine of a technology team. To prevent the database from hanging again, focus must shift to continuous prevention and proactive monitoring. Creating indexes on columns most frequently used in search and sorting clauses drastically reduces database effort. Furthermore, configuring strict query execution timeout limits in applications prevents a single malformed query from consuming connections indefinitely until the available pool is exhausted.

Another fundamental practice is periodically reviewing the behavior of automated routines and heavy reports, ensuring they run during low-traffic hours or on isolated read replicas. When the architecture separates daily transaction traffic from heavy analytical queries, the environment gains impressive resilience. Ultimately, keeping a database healthy requires discipline in writing SQL code, constant attention to execution plans, and an engineering culture focused on continuous optimization of computational resources. To complement this implementation in production environments, see our related technical analyses on How to wire a 16x2 LCD display to Arduino without errors and How to Clean Docker Cache on Linux and Free Up Space.

Conclusion

Knowing how to deal with frozen databases and slow queries is an essential skill for any team managing systems in production. By mastering auditing tools and understanding safe process interruption mechanisms, engineers transform a crisis moment into a technical learning opportunity. The secret lies in acting methodically: audit with precision, evaluate transactional risks, and apply definitive structural fixes in code and indexes.

In short, application stability depends directly on the health of its data layer. Maintaining active monitoring routines and applying the best practices discussed here ensures your server continues responding swiftly, even under high traffic loads and complex operations.