The database is frequently the biggest performance bottleneck in corporate web systems. As the volume of records in the database grows, poorly optimized SQL queries (known as Slow Queries) start locking server connections, driving CPU usage up to 100% and causing sluggishness for end users.
In this article, we present the key database engineering techniques to analyze and optimize query speeds in PostgreSQL.
1. Locating Slow Queries with pg_stat_statements
The first step is to map out which queries are indeed the main culprits of application lag. To do this, we enable the native pg_stat_statements extension.
Activation:
In the postgresql.conf configuration file, add:
shared_preload_libraries = 'pg_stat_statements'Restart PostgreSQL and create the extension in the application database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;The query below lists the ten statements that consumed the most total time. On PostgreSQL 13 or newer the columns are total_exec_time and mean_exec_time. On version 12, use total_time and mean_time.
SELECT
round(total_exec_time::numeric, 1) AS total_ms,
calls,
round(mean_exec_time::numeric, 1) AS mean_ms,
left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;A high total_ms with a high calls count is the query worth an index. A high mean_ms with few calls is a one-off report, not the day's bottleneck.
2. Deciphering the Execution Plan with EXPLAIN ANALYZE
With the query from that list, run the real plan. BUFFERS shows whether the read came from disk:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM pedidos
WHERE cliente_id = 42
ORDER BY data_criacao DESC
LIMIT 20;PostgreSQL returns the plan, not the rows. A Seq Scan on pedidos with a high shared read is the whole table on disk. After the index in the next section, the same command shows an Index Scan.
What to look for in the report:
- Seq Scan (Sequential Scan): Indicates that the database had to read the entire table, row by row, on disk. In tables with millions of records, this is catastrophic.
- Index Scan: Means that the search utilized an index cached in memory to find the record instantly, which is the ideal scenario.
3. Creating Smart Indexes (B-Tree and Composite Indexes)
To eliminate Sequential Scans in WHERE clauses, we create indexes on the most frequently searched fields.
Example:
If the system performs frequent searches combining customer_id and created_at: CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC);
Avoid excess: Each new index created speeds up read queries (SELECT), but slows down write operations (INSERT/UPDATE), as PostgreSQL needs to rebuild the index tree on every record modification.
4. Shared Memory Calibration
The default PostgreSQL configuration is conservative to run on modest hardware. For production, tune critical parameters in your postgresql.conf:
shared_buffers: Amount of RAM dedicated to data caching. Configure this to between 25% and 40% of the total memory available on the server.work_mem: Memory used for internal sorting operations (ORDER BY, DISTINCT) per connection. Raising this moderately avoids writing temporary data to disk during sorts.
Conclusion
Keeping a database healthy requires constant monitoring of the execution plan and the server's memory metrics. By applying planned composite indexes and adjusting PostgreSQL buffer variables, your enterprise application will run much more smoothly and cost-effectively.