METHODS FOR DETECTION AND RESOLUTION OF PERFORMANCE PROBLEMS IN MONITORING SYSTEMS AND AUTOMATED QUERY OPTIMIZATION FOR POSTGRESQL

Authors

N. B. Baktybekova Kyrgyz-German Institute of Applied Informatics
S. V. Koryakin Kyrgyz-German Institute of Applied Informatics
E. A. Dzhalbiev Kyrgyz-German Institute of Applied Informatics

Keywords:

PostgreSQL, performance monitoring, automatic optimization, pg_stat_statements, slow queries, indexing, performance analysis, monitoring system, database metrics, query optimization, EXPLAIN ANALYZE, parameter tuning, execution plan analysis

Abstract

This work is dedicated to the development of a monitoring and automatic optimization system for PostgreSQL databases. Existing PostgreSQL monitoring tools are reviewed, and their capabilities and limitations are analyzed. Special attention is given to methods for detecting slow queries, analyzing index usage, identifying performance issues, as well as modern approaches to query optimization and DBMS parameter tuning. The developed automated system includes a metrics collection module, a performance analyzer, and a recommendation generator for optimization. Testing of the system on real data demonstrated a performance increase of 35-40% after applying the automatic recommendations. The conclusion provides recommendations for implementing the PostgreSQL monitoring and automatic optimization system in production environments.

References

1. Database trends of 2025: Rankings and key technology shifts - https://www.baremon.eu/database-trends-of-2025

2. PostgreSQL vs. MySQL in 2025: Choosing the Best Database for Your Backend - https://www.nucamp.co/blog/coding-bootcamp-backend-with-python-2025-postgresql-vs-mysql-in-2025-choosing-the-best-database-for-your-backend

3. PostgreSQL's Proprietary Future? - PostgreSQL Market in 2025 - https://experience.percona.com/postgresql/postgresql-market-in-2025/the-growing-dominance-of-postgresql

4. PostgreSQL: Documentation: Monitoring Database Activity - https://www.postgresql.org/docs/current/monitoring.html

5. Using pg_stat_statements to Optimize Queries - https://www.timescale.com/blog/identify-postgresql-performance-bottlenecks-with-pg_stat_statements

6. Identify PostgreSQL slow queries with pg_stat_statements - https://aiven.io/docs/products/postgresql/howto/identify-pg-slow-queries

7. Detecting slow queries quickly with pg_stat_statements - https://www.cybertec-postgresql.com/en/postgresql-detecting-slow-queries-quickly

8. PostgreSQL: Documentation: The Cumulative Statistics System - https://www.postgresql.org/docs/current/monitoring-stats.html

9. PostgreSQL: Documentation: Run-time Statistics - https://www.postgresql.org/docs/16/runtime-config-statistics.html

10. Key metrics for PostgreSQL monitoring | Datadog - https://www.datadoghq.com/blog/postgresql-monitoring

11. PostgreSQL: Documentation: Statistics Used by the Planner - https://www.postgresql.org/docs/current/planner-stats.html

12. Top PostgreSQL Monitoring Tools and Best Practices in 2024 - https://www.bytebase.com/blog/top-postgres-monitoring-tools

13. PostgreSQL Performance Identifying Hot and Slow Queries - https://virtual-dba.com/blog/postgresql-performance-identifying-hot-and-slow-queries

14. PostgreSQL Monitoring Tools Comparison - https://betterstack.com/community/comparisons/postgresql-monitoring-tools

15. PostgreSQL Monitoring: Key Metrics and Best Practices - https://middleware.io/blog/postgresql-monitoring

16. Dealing With Slow Queries With PostgreSQL - https://pgdash.io/blog/slow-queries-postgres.html

17. PostgreSQL Trends to Watch in 2025: The Database Revolution - https://medium.com/@rizqimulkisrc/postgresql-trends-to-watch-in-2025-4a75ab41df6f

18. Postgres Clever Query Planning System - https://www.crunchydata.com/blog/indexes-selectivity-and-statistics

19. PostgreSQL: настройка и оптимизация производительности - https://habr.com/ru/companies/slurm/articles/716036

20. Производительность запросов в PostgreSQL – шаг за шагом - https://habr.com/ru/companies/oleg-bunin/articles/319018

21. Как ускорить работу PostgreSQL с помощью конфигурации базы - https://habr.com/ru/companies/slurm/articles/684826

22. Why SQL and Postgres Still Rule the Data World in 2025 - https://www.simplyblock.io/blog/why-sql-rules-the-data-world-in-2025

23. 12 Best PostgreSQL Monitoring Tools for 2024 - https://www.comparitech.com/net-admin/best-postgresql-monitoring-tools

24. How to Monitor PostgreSQL Database Performance - https://docs.digitalocean.com/products/databases/postgresql/how-to/monitor-databases

25. Best PostgreSQL Monitoring Tools & Key Performance Metrics - https:/Ы/sematext.com/blog/postgresql-monitoring

26. Верзунов, С. Н. Анализ и ARIMA-модели динамики изменения концентрации РМ2.5 в атмосферном воздухе Г.Бишкек / С. Н. Верзунов, Н. М. Лыченко // Проблемы автоматики и управления. – 2019. – № 1(36). – С. 147–155. – DOI 10.5281/zenodo.3253027. – EDN IKKKUC.