Yandex Cloud
Search
Contact UsGet started
  • Blog
  • Pricing
  • Documentation
  • All Services
  • System Status
    • Featured
    • Infrastructure & Network
    • Data Platform
    • Containers
    • Developer tools
    • Serverless
    • Security
    • Monitoring & Resources
    • ML & AI
    • Business tools
  • All Solutions
    • By industry
    • By use case
    • Economics and Pricing
    • Security
    • Technical Support
    • Customer Stories
    • Gateway to Russia
    • Cloud for Startups
    • Education and Science
  • Blog
  • Pricing
  • Documentation
Yandex project
© 2025 Yandex.Cloud LLC
Tutorials
    • All tutorials
    • Deploying the Apache Kafka® web interface
    • Migrating a database from a third-party Apache Kafka® cluster to Managed Service for Apache Kafka®
    • Moving data between Managed Service for Apache Kafka® clusters using Data Transfer
    • Delivering data from Managed Service for MySQL® to Managed Service for Apache Kafka® using Data Transfer
    • Delivering data from Managed Service for MySQL® to Managed Service for Apache Kafka® using Debezium
    • Delivering data from Managed Service for PostgreSQL to Managed Service for Apache Kafka® using Data Transfer
    • Delivering data from Managed Service for PostgreSQL to Managed Service for Apache Kafka® using Debezium
    • Delivering data from Managed Service for YDB to Managed Service for Apache Kafka® using Data Transfer
    • Delivering data from Managed Service for Apache Kafka® to Managed Service for ClickHouse® using Data Transfer
    • Delivering data from Managed Service for Apache Kafka® to Managed Service for Greenplum® using Data Transfer
    • Delivering data from Managed Service for Apache Kafka® to Managed Service for MongoDB using Data Transfer
    • Delivering data from Managed Service for Apache Kafka® to Managed Service for MySQL® using Data Transfer
    • Delivering data from Managed Service for Apache Kafka® to Managed Service for OpenSearch using Data Transfer
    • Delivering data from Managed Service for Apache Kafka® to Managed Service for PostgreSQL using Data Transfer
    • Delivering data from Managed Service for Apache Kafka® to Managed Service for YDB using Data Transfer
    • Delivering data from Managed Service for Apache Kafka® to Data Streams using Data Transfer
    • Delivering data from Data Streams to Managed Service for YDB using Data Transfer
    • Delivering data from Data Streams to Managed Service for Apache Kafka® using Data Transfer
    • YDB change data capture and delivery to YDS
    • Configuring Kafka Connect to work with a Managed Service for Apache Kafka® cluster
    • Automating Query tasks with Managed Service for Apache Airflow™
    • Sending requests to the Yandex Cloud API via the Yandex Cloud Python SDK
    • Configuring an SMTP server to send e-mail notifications
    • Adding data to a ClickHouse® DB
    • Migrating data to Managed Service for ClickHouse® using Data Transfer
    • Delivering data from Managed Service for MySQL® to Managed Service for ClickHouse® using Data Transfer
    • Asynchronously replicating data from PostgreSQL to ClickHouse®
    • Exchanging data between Managed Service for ClickHouse® and Yandex Data Processing
    • Configuring Managed Service for ClickHouse® for Graphite
    • Fetching data from Managed Service for Apache Kafka® to Managed Service for ClickHouse®
    • Fetching data from Managed Service for Apache Kafka® to ksqlDB
    • Fetching data from RabbitMQ to Managed Service for ClickHouse®
    • Saving a Data Streams data stream in Managed Service for ClickHouse®
    • Asynchronous replication of data from Yandex Metrica to ClickHouse® using Data Transfer
    • Using hybrid storage in Managed Service for ClickHouse®
    • Sharding Managed Service for ClickHouse® tables
    • Data resharding in a Managed Service for ClickHouse® cluster
    • Loading data from Yandex Direct to a data mart enabled by Managed Service for ClickHouse® using Cloud Functions, Object Storage, and Data Transfer
    • Loading data from Object Storage to Managed Service for ClickHouse® using Data Transfer
    • Migrating data with change of storage from Managed Service for OpenSearch to Managed Service for ClickHouse® using Data Transfer
    • Loading data from Managed Service for YDB to Managed Service for ClickHouse® using Data Transfer
    • Migrating databases from Google BigQuery to Managed Service for ClickHouse®
    • Configuring Cloud DNS to access a Managed Service for ClickHouse® cluster from other cloud networks
    • Migrating a Yandex Data Processing HDFS cluster to a different availability zone
    • Importing data from Managed Service for MySQL® to Yandex Data Processing using Sqoop
    • Importing data from Managed Service for PostgreSQL to Yandex Data Processing using Sqoop
    • Mounting Object Storage buckets to the file system of Yandex Data Processing hosts
    • Working with Apache Kafka® topics using Yandex Data Processing
    • Automating operations with Yandex Data Processing using Managed Service for Apache Airflow™
    • Shared use of Yandex Data Processing tables through Metastore
    • Transferring metadata between Yandex Data Processing clusters using Metastore
    • Importing data from Object Storage, processing and exporting to Managed Service for ClickHouse®
    • Migrating to Managed Service for Elasticsearch using snapshots
    • Migrating collections from a third-party MongoDB cluster to Managed Service for MongoDB
    • Migrating data to Managed Service for MongoDB
    • Migrating Managed Service for MongoDB cluster from 4.4 to 6.0
    • Sharding MongoDB collections
    • MongoDB performance analysis and tuning
    • Migrating a database from a third-party MySQL® cluster to a Managed Service for MySQL® cluster
    • Managed Service for MySQL® performance analysis and tuning
    • Syncing data from a third-party MySQL® cluster to Managed Service for MySQL® using Data Transfer
    • Migrating a database from Managed Service for MySQL® to a third-party MySQL® cluster
    • Migrating a database from Managed Service for MySQL® to Object Storage using Data Transfer
    • Migrating data from Object Storage to Managed Service for MySQL® using Data Transfer
    • Delivering data from Managed Service for MySQL® to Managed Service for Apache Kafka® using Data Transfer
    • Delivering data from Managed Service for MySQL® to Managed Service for Apache Kafka® using Debezium
    • Migrating a database from Managed Service for MySQL® to Managed Service for YDB using Data Transfer
    • MySQL® change data capture and delivery to YDS
    • Migrating data from Managed Service for MySQL® to Managed Service for PostgreSQL using Data Transfer
    • Migrating data from AWS RDS for PostgreSQL to Managed Service for PostgreSQL using Data Transfer
    • Migrating data from Managed Service for MySQL® to Managed Service for Greenplum® using Data Transfer
    • Configuring an index policy in Managed Service for OpenSearch
    • Migrating data from Elasticsearch to Managed Service for OpenSearch
    • Migrating data from a third-party OpenSearch cluster to Managed Service for OpenSearch using Data Transfer
    • Loading data from Managed Service for OpenSearch to Object Storage using Data Transfer
    • Migrating data from Managed Service for OpenSearch to Managed Service for YDB using Data Transfer
    • Copying data from Managed Service for OpenSearch to Managed Service for Greenplum® using Yandex Data Transfer
    • Migrating data from Managed Service for PostgreSQL to Managed Service for OpenSearch using Data Transfer
    • Authenticating a Managed Service for OpenSearch cluster in OpenSearch Dashboards using Keycloak
    • Using the yandex-lemmer plugin in Managed Service for OpenSearch
    • Creating a PostgreSQL cluster for 1C:Enterprise
    • Searching for the Managed Service for PostgreSQL cluster performance issues
    • Managed Service for PostgreSQL performance analysis and tuning
    • Logical replication PostgreSQL
    • Migrating a database from a third-party PostgreSQL cluster to Managed Service for PostgreSQL
    • Migrating a database from Managed Service for PostgreSQL
    • Delivering data from Managed Service for PostgreSQL to Managed Service for Apache Kafka® using Data Transfer
    • Delivering data from Managed Service for PostgreSQL to Managed Service for Apache Kafka® using Debezium
    • Delivering data from Managed Service for PostgreSQL to Managed Service for YDB using Data Transfer
    • Migrating a database from Managed Service for PostgreSQL to Object Storage
    • Migrating data from Object Storage to Managed Service for PostgreSQL using Data Transfer
    • PostgreSQL change data capture and delivery to YDS
    • Migrating data from Managed Service for PostgreSQL to Managed Service for MySQL® using Data Transfer
    • Migrating data from Managed Service for PostgreSQL to Managed Service for OpenSearch using Data Transfer
    • Troubleshooting string sorting issues in PostgreSQL after upgrading glibc
    • Migrating a database from Greenplum® to ClickHouse®
    • Migrating a database from Greenplum® to PostgreSQL
    • Exporting Greenplum® data to a cold storage in Object Storage
    • Loading data from Object Storage to Managed Service for Greenplum® using Data Transfer
    • Copying data from Managed Service for OpenSearch to Managed Service for Greenplum® using Yandex Data Transfer
    • Creating an external table from a Object Storage bucket table using a configuration file
    • Migrating a database from a third-party Valkey™ cluster to Yandex Managed Service for Valkey™
    • Using a Yandex Managed Service for Valkey™ cluster as a PHP session storage
    • Loading data from Object Storage to Managed Service for YDB using Data Transfer
    • Loading data from Managed Service for YDB to Object Storage using Data Transfer
    • Processing Audit Trails events
    • Processing Cloud Logging logs
    • Processing CDC Debezium streams
    • Analyzing data with Jupyter
    • Processing files with usage details in Yandex Cloud Billing
    • Entering data into storage systems
    • Smart log processing
    • Transferring data within microservice architectures
    • Migrating data to Object Storage using Data Transfer
    • Migrating data from a third-party Greenplum® or PostgreSQL cluster to Managed Service for Greenplum® using Data Transfer
    • Migrating Managed Service for MongoDB clusters
    • Migrating MySQL® clusters
    • Migrating to a third-party MySQL® cluster
    • Migrating PostgreSQL clusters
    • Creating a schema registry to deliver data in Debezium CDC format from Apache Kafka®

In this article:

  • Getting started
  • Diagnosing inefficient query execution
  • Troubleshooting issues with inefficient queries
  • Diagnosing resource shortages
  • Troubleshooting resource shortage issues
  • Diagnosing locks
  • Troubleshooting locking issues
  • Diagnosing connection errors
  • Troubleshooting connection issues
  • Diagnosing insufficient storage space
  • Troubleshooting problems with insufficient storage space
  1. Building a data platform
  2. Managed Service for PostgreSQL performance analysis and tuning

Managed Service for PostgreSQL performance analysis and tuning

Written by
Yandex Cloud
Updated at March 4, 2025
  • Getting started
  • Diagnosing inefficient query execution
  • Troubleshooting issues with inefficient queries
  • Diagnosing resource shortages
  • Troubleshooting resource shortage issues
  • Diagnosing locks
  • Troubleshooting locking issues
  • Diagnosing connection errors
  • Troubleshooting connection issues
  • Diagnosing insufficient storage space
  • Troubleshooting problems with insufficient storage space

Managed Service for PostgreSQL cluster performance drops most often due to one of the following:

  • Inefficient query execution in PostgreSQL.
  • High CPU load, frequent disk reads and writes, and network requests.
  • Locks.
  • Running out of available connections.
  • Running out of free storage space.

Here are some tips for diagnosing and fixing these issues.

To detect potential issues in a cluster, use tools for comprehensive cluster state analysis.

Getting startedGetting started

  1. Select databases to troubleshoot.

  2. Enable the Access from the management console option in the cluster.

  3. Activate statistics collection about sessions and queries.

  4. Enable the auto_explain module for extended query execution plan logging.

  5. If you want more queries to appear in the performance log, reduce the log_min_duration_statement value in the DBMS settings.

    Warning

    When log_min_duration_statement is set to 0, all queries are logged, regardless of the time they take to complete. In this case, you can run out of storage space quickly.

Diagnosing inefficient query executionDiagnosing inefficient query execution

There are two ways to identify problematic queries:

  • Retrieve them from the PostgreSQL pg_stat_activity system table:

    SELECT NOW() - query_start AS duration, query, state
    FROM pg_stat_activity
    WHERE state != 'idle' ORDER BY 1 DESC;
    

    This will return a list of queries running on the server. Pay attention to queries with a high duration value.

    To learn more about the information in the output, see the PostgreSQL documentation.

  • Get and analyze query statistics using the diagnostics tool integrated into Managed Service for PostgreSQL.

Troubleshooting issues with inefficient queriesTroubleshooting issues with inefficient queries

There are several ways to optimize problematic queries:

  • Analyze the query plan using the EXPLAIN command.

    Check queries that do not use indexes (a large number of rows in the Seq Scan nodes). Such queries increase utilization of both I/O (due to more reads from the disk) and CPU (as it takes more processor time to process large numbers of rows).

    Create or update appropriate indexes.

    Tip

    To visualize the execution plans of the available queries, use the SQL tab on the cluster management page.

    For more information, see SQL queries in Yandex WebSQL.

  • Log the query execution plan automatically using the auto_explain module.

  • Update statistics using the ANALYZE command.

    The query execution plan is based on the statistics collected by the DBMS. If the data in the DBMS is updated frequently, these statistics quickly become outdated. Use the ANALYZE query for the DBMS to perform a new analysis of the table or entire database:

    ANALYZE <table_or_database_name>;
    

    If required, in the DBMS settings, increase the value of default_statistics_target, then run the ANALYZE query again.

    For more information about the default_statistics_target parameter, see the PostgreSQL settings.

  • Create extended statistics objects.

    PostgreSQL does not collect statistics about the correlation of data between columns of the same table. This is because the number of possible combinations of columns can be extremely large. If there is a relationship between columns, create extended statistics objects. The scheduler will then be able to optimize queries based on information about the correlation of data in the columns.

  • Analyze query execution plan statistics in PostgreSQL logs.

    The PostgreSQL auto_explain module outputs info on the query execution plan to the PostgreSQL log. You can collect statistics by searching the lines in the log. For more information, see the PostgreSQL documentation.

If you cannot optimize the identified queries or manage without them, the only option is to change the host class to a higher one.

Diagnosing resource shortagesDiagnosing resource shortages

Resource shortage is a likely cause of cluster performance degradation. Resource shortages become evident from cluster monitoring charts (CPU, disk I/O operations, network connections). If a resource utilization chart continued to rise but then plateaued, the resource load has reached its limit or is exceeding the guaranteed service level.

In most cases, high CPU utilization and high disk I/O are due to suboptimal indexes or a large load on the hosts. Check the data on sessions and queries collected by the performance diagnostics tool.

Troubleshooting resource shortage issuesTroubleshooting resource shortage issues

Try optimizing identified queries that consume a large amount of resources. If the load is still high or there is nothing to optimize, the only option is to upgrade the host class.

Diagnosing locksDiagnosing locks

Cluster performance may degrade because of locks caused by multiple simultaneous attempts to access the same DB resource (table, row).

To detect locks using the performance diagnostics tool:

  1. Go to the folder page and select Managed Service for PostgreSQL.

  2. Click the name of the cluster you need and select the Performance diagnostics tab.

  3. On the Sessions tab, in the Slice field, select WAIT_EVENT_TYPE.

    • Check the Lock chart. It shows the number of queries that were in the locked state during the selected period.
    • To get detailed information about queries running during the selected period, go to the Queries tab.

    For more information about what statistics you can get, see the PostgreSQL documentation.

To diagnose the presence of locks using PostgreSQL, run the query:

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
    ON pl.pid = psa.pid;

For more information about selecting queries with locks, see the PostgreSQL documentation.

Troubleshooting locking issuesTroubleshooting locking issues

Try optimizing the identified queries. If the load is still high or there is nothing to optimize, the only option is to upgrade the host class.

Diagnosing connection errorsDiagnosing connection errors

The number of DB connections is limited by the max_connections parameter and is calculated by the following formula:

200 × <vCPU_share_on_host>: 15

Here, <vCPU_share_on_host> is the product of the number of vCPUs by their guaranteed share, and 15 is the number of reserved service connections. The resulting number of connections is distributed between the database roles.

If the number of open connections reaches the limit, errors appear in the cluster logs:

  • Too many connections for role.
  • Server conn crashed.
  • Invalid server parameter.
  • Query wait timeout.

To get detailed information about the usage of available connections using monitoring tools:

  1. Go to the folder page and select Managed Service for PostgreSQL.

  2. Click the name of the cluster you need and select the Monitoring tab.

  3. Check the Total pooler connections chart.

    Managed Service for PostgreSQL does not allow connections directly to the DBMS; instead, they go to the connection pooler.

    • The Clients parameter reflects the number of client connections to the connection pooler.

    • The Servers parameter reflects the number of connections between the DBMS and the connection pooler.

      Take note of the number of connections: if the values are high, this means some queries keep connections open for too long.

Troubleshooting connection issuesTroubleshooting connection issues

To fix issues with the number of connections:

  • Increase the max_connections value in the DBMS settings.

  • Distribute free connections between users.

  • Optimize queries so that there are no long transactions.

If the load is still high or there is nothing to optimize, the only option is to upgrade the host class.

Diagnosing insufficient storage spaceDiagnosing insufficient storage space

If the cluster shows poor performance and the logs show ERROR: cannot execute INSERT in a read-only transaction, the cluster storage might have run out of free space and switched to read-only mode.

To check for free space in cluster storage:

  1. Go to the folder page and select Managed Service for PostgreSQL.

  2. Click the name of the cluster you need and select the Monitoring tab.

  3. Check the Disk capacity in primary, [bytes] chart.

    Check the value of the Used parameter that shows the degree of cluster storage usage.

Troubleshooting problems with insufficient storage spaceTroubleshooting problems with insufficient storage space

For recommendations on troubleshooting these issues, see Disk space management.

Was the article helpful?

Previous
Searching for the Managed Service for PostgreSQL cluster performance issues
Next
Logical replication PostgreSQL
Yandex project
© 2025 Yandex.Cloud LLC