Database monitoring: In search of bottlenecks

Database monitoring is an essential practice for software engineering teams seeking to optimize their database performance and identify bottlenecks that can impede system efficiency. Bottlenecks can occur at various points within a database environment, including CPU utilization, disk I/O, memory usage, and query execution.

There exist many tools that are robust and can tackle the task, but PostgreSQL offers a suite of built-in utilities that can be invaluable in this quest. One such indispensable tool is pg_stat_statements, a PostgreSQL extension that provides detailed insights into query performance and resource consumption.

Unlike external monitoring solutions, pg_stat_statements is seamlessly integrated into PostgreSQL, making it easily accessible and highly efficient. This extension not only captures vital information about query execution, including execution times and resource usage but also retains the actual SQL statements themselves. With this granular data at your disposal, you can precisely identify which queries are taxing your database, enabling you to pinpoint and address bottlenecks swiftly.

pg_stat_statements

Since pg_stat_statements isn’t enabled out of the box there are some steps we need to take before we can work with it and discover potential bottlenecks.

Setup

First things first, we need to go into the database docker container console.

After we have the terminal opened we need to use the following commands:

$ su - postgres
$ cd data

Next, we need to install Vim to be able to edit the postgresql.conf file. For that, we need to run these two commands:

$ apt-get update
$ apt-get install vim

After Vim has been successfully installed we need to run

$ vi postgresql.conf

and add these settings at the end of the file:

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000
pg_stat_statements.track = all

Last but not least, we need to go into our SQL client and run the following expression:

CREATE EXTENSION pg_stat_statements;

Usage

After conducting all the steps above, every query that will be executed in our app will be tracked together with some additional data like calls, total_exec_time, etc. in pg_stat_statements

To inspect all of this information, we will need to run the following query in our SQL client:

SELECT * FROM pg_stat_statements
WHERE dbid = (
	SELECT
		oid
	FROM 
		pg_database
	WHERE
		datname = 'YOUR_DB_NAME'
);

This will return similar output:

In the example above we can see that the first two queries are being called 8,000+ times and their total_exec_time is extremely high compared to other queries. This is a sign that something is off with these queries and further inspection of them is required.

Some of the common issues with unoptimized SQL queries might be:

• Missing indexes,

• Unnecessary joins,

• Suboptimal filter conditions,

• Lack of data filtering

• Ineffective use of database features.

Why monitoring is key for scaling projects

Monitoring may not be necessary for MVPs due to the limited codebase or feature growth, but it becomes increasingly important as projects scale up.

Database monitoring for scale-up projects can offer several significant advantages, including:

1. Improved Performance: Database monitoring allows you to identify and address performance bottlenecks and inefficiencies, ensuring that your application runs smoothly even as it scales up.

2. Cost Savings: By optimizing queries and resource usage, you can reduce operational costs associated with database infrastructure and hosting, potentially saving money on hardware and cloud services.

3. Enhanced User Experience: Monitoring helps maintain a responsive and reliable application, providing a positive experience for users as your project gains traction.