[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for PostgreSQL](../index.md) > [Step-by-step guides](index.md) > Logs and monitoring > Performance diagnostics

# Performance diagnostics in Managed Service for PostgreSQL

Managed Service for PostgreSQL provides a built-in tool for diagnosing your database cluster performance. This tool helps you analyze PostgreSQL performance metrics for [sessions](#get-sessions) and [queries](#get-queries).

To learn how to identify and resolve cluster performance issues, see [Performance analysis and optimization](../tutorials/profiling.md) and [Troubleshooting cluster performance](../tutorials/performance-problems.md).

## Enabling statistics collection {#activate-stats-collector}

{% list tabs %}

- Management console {#console}

    When [creating a cluster](cluster-create.md) or [updating its settings](update.md#change-additional-settings):
    
    1. Enable **Statistics sampling** (disabled by default).
    1. Set the **Sessions sampling interval** and **Statements sampling interval**. Valid values:
        
        * For sessions: From `5` to `86400` seconds.
        * For queries: From `60` to `86400` seconds.

- CLI {#cli}

    If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

    The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

    To enable statistics collection, add the `--performance-diagnostics` option to the cluster update command:

    ```bash
    yc managed-postgresql cluster update <cluster_name_or_ID> \
        ...
        --performance-diagnostics enabled=true,`
                                 `sessions-sampling-interval=<session_sampling_interval>,`
                                 `statements-sampling-interval=<statement_sampling_interval> \
        ...
    ```

    Allowed values:

    - `sessions-sampling-interval`: From `5` to `86400` seconds.
    - `statements-sampling-interval`: From `60` to `86400` seconds.

- Terraform {#tf}

    1. Open the current Terraform configuration file with the infrastructure plan.

        To learn how to create this file, see [Creating a cluster](cluster-create.md).

        For the complete list of configurable Managed Service for PostgreSQL cluster fields, see [this Terraform provider guide](../../terraform/resources/mdb_postgresql_cluster.md).

    1. To set up statistics collection, add the `performance_diagnostics` block to the `config` section:
       
       ```hcl
       resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
         ...
         config {
           ...
           performance_diagnostics {
             enabled                      = <activate_statistics_collection>
             sessions_sampling_interval   = <session_sampling_interval>
             statements_sampling_interval = <statement_sampling_interval>
           }
           ...
         }
         ...
       }
       ```
       
       Where:
       
       * `enabled`: Enables statistics collection, `true` or `false`.
       * `sessions_sampling_interval`: Session sampling interval, from `5` to `86400` seconds.
       * `statements_sampling_interval`: Statement sampling interval, from `60` to `86400` seconds.

    1. Make sure the settings are correct.

        1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.
        1. Run this command:
        
           ```bash
           terraform validate
           ```
        
           Terraform will show any errors found in your configuration files.

    1. Confirm updating the resources.

        1. Run this command to view the planned changes:
        
           ```bash
           terraform plan
           ```
        
           If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.
        
        1. If everything looks correct, apply the changes:
           1. Run this command:
        
              ```bash
              terraform apply
              ```
        
           1. Confirm updating the resources.
           1. Wait for the operation to complete.

        {% note warning "Timeouts" %}
        
        The Terraform provider sets the following timeouts for Managed Service for PostgreSQL cluster operations:
        
        * Creating a cluster, including restoration from a backup: 30 minutes.
        * Updating a cluster: 60 minutes.
        * Deleting a cluster: 15 minutes.
        
        Operations exceeding the timeout are aborted.
        
        {% cut "How can I change these timeouts?" %}
        
        Add a `timeouts` section to the cluster description, e.g.:
        
        ```hcl
        resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
          ...
          timeouts {
            create = "1h30m" # 1 hour 30 minutes
            update = "2h"    # 2 hours
            delete = "30m"   # 30 minutes
          }
        }
        ```
        
        {% endcut %}
        
        {% endnote %}

- REST API {#api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. To enable the collection of statistics when creating a cluster:

     1. Include `configSpec.performanceDiagnostics` option to the [cURL command](cluster-create.md#create-cluster) implementing the [Cluster.Create](../api-ref/Cluster/create.md) method:

        ```bash
        curl \
          --request POST \
          --header "Authorization: Bearer $IAM_TOKEN" \
          --header "Content-Type: application/json" \
          --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters' \
          --data '{
                    "configSpec": {
                      "performanceDiagnostics": {
                        "enabled": <enable_statistics_collection>,
                        "sessionsSamplingInterval": "<session_sampling_interval>",
                        "statementsSamplingInterval": "<statement_sampling_interval>"
                      },
                      ...
                    },
                    ...
                  }'
        ```

        Where `configSpec.performanceDiagnostics` represents the statistics collection settings:

        * `enabled`: Enables statistics collection, `true` or `false`.
        * `sessionsSamplingInterval`: Session sampling interval. Allowed values range from `5` to `86400`.
        * `statementsSamplingInterval`: Statement sampling interval. Allowed values range from `60` to `86400`.

     1. Check the [server response](../api-ref/Cluster/create.md#yandex.cloud.operation.Operation) to make sure your request was successful.

  1. To enable the collection of statistics when updating an existing cluster:

     1. Call the [Cluster.Update](../api-ref/Cluster/update.md) method, e.g., via the following [cURL](https://curl.se/) request:

        {% note warning %}
        
        The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the `updateMask` parameter as a single comma-separated string.
        
        {% endnote %}

        ```bash
        curl \
          --request PATCH \
          --header "Authorization: Bearer $IAM_TOKEN" \
          --header "Content-Type: application/json" \
          --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>' \
          --data '{
                    "updateMask": "configSpec.performanceDiagnostics",
                    "configSpec": {
                      "performanceDiagnostics": {
                        "enabled": <enable_statistics_collection>,
                        "sessionsSamplingInterval": "<session_sampling_interval>",
                        "statementsSamplingInterval": "<statement_sampling_interval>"
                      }
                    }
                  }'
        ```

        Where `configSpec.performanceDiagnostics` represents the statistics collection settings:

        * `enabled`: Enables statistics collection, `true` or `false`.
        * `sessionsSamplingInterval`: Session sampling interval. Allowed values range from `5` to `86400`.
        * `statementsSamplingInterval`: Statement sampling interval. Allowed values range from `60` to `86400`.

     1. Check the [server response](../api-ref/Cluster/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

- gRPC API {#grpc-api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
     
     ```bash
     cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
     ```
     
     Below, we assume that the repository contents reside in the `~/cloudapi/` directory.
  1. To enable collection of statistics when creating a cluster:

     1. Include the `config_spec.performance_diagnostics` option to the [grpcurl command](cluster-create.md#grpc-api) implementing the [ClusterService.Create](../api-ref/grpc/Cluster/create.md) method:

        ```bash
        grpcurl \
          -format json \
          -import-path ~/cloudapi/ \
          -import-path ~/cloudapi/third_party/googleapis/ \
          -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/cluster_service.proto \
          -rpc-header "Authorization: Bearer $IAM_TOKEN" \
          -d '{
                "config_spec": {
                  "performance_diagnostics": {
                    "enabled": <enable_statistics_collection>,
                    "sessions_sampling_interval": "<session_sampling_interval>",
                    "statements_sampling_interval": "<statement_sampling_interval>"
                  },
                  ...
                },
                ...
              }' \
          mdb.api.cloud.yandex.net:443 \
          yandex.cloud.mdb.postgresql.v1.ClusterService.Create
        ```

        Where `config_spec.performance_diagnostics` represents the statistics collection settings:

        * `enabled`: Enables statistics collection, `true` or `false`.
        * `sessions_sampling_interval`: Session sampling interval. Allowed values range from `5` to `86400`.
        * `statements_sampling_interval`: Statement sampling interval. Allowed values range from `60` to `86400`.

     1. Check the [server response](../api-ref/grpc/Cluster/create.md#yandex.cloud.operation.Operation) to make sure your request was successful.

  1. To enable collection of statistics when updating an existing cluster:

     1. Call the [ClusterService.Update](../api-ref/grpc/Cluster/update.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

        {% note warning %}
        
        The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the `update_mask` parameter as an array of `paths[]` strings.
        
        {% cut "Format for listing settings" %}
        
        ```yaml
        "update_mask": {
            "paths": [
                "<setting_1>",
                "<setting_2>",
                ...
                "<setting_N>"
            ]
        }
        ```
        
        {% endcut %}
        
        {% endnote %}

        ```bash
        grpcurl \
          -format json \
          -import-path ~/cloudapi/ \
          -import-path ~/cloudapi/third_party/googleapis/ \
          -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/cluster_service.proto \
          -rpc-header "Authorization: Bearer $IAM_TOKEN" \
          -d '{
                "cluster_id": "<cluster_ID>",
                "update_mask": {
                  "paths": [
                    "config_spec.performance_diagnostics"
                  ]
                },
                "config_spec": {
                  "performance_diagnostics": {
                    "enabled": <enable_statistics_collection>,
                    "sessions_sampling_interval": "<session_sampling_interval>",
                    "statements_sampling_interval": "<statement_sampling_interval>"
                  }
                }
              }' \
          mdb.api.cloud.yandex.net:443 \
          yandex.cloud.mdb.postgresql.v1.ClusterService.Update
        ```

        Where `config_spec.performance_diagnostics` represents the statistics collection settings:

        * `enabled`: Enables statistics collection, `true` or `false`.
        * `sessions_sampling_interval`: Session sampling interval. Allowed values range from `5` to `86400`.
        * `statements_sampling_interval`: Statement sampling interval. Allowed values range from `60` to `86400`.

     1. Check the [server response](../api-ref/grpc/Cluster/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

{% endlist %}

## Getting session statistics {#get-sessions}

{% list tabs %}

- Management console {#console}

    1. Navigate to **Managed Service for&nbsp;PostgreSQL**.
    1. Click the name of your cluster and select **Performance diagnostics** → **Sessions**.

    To view session statistics:

    1. Specify the time interval.
    1. Optionally, configure filters.
    1. Select a data segment.

    To show or hide individual categories, click the category name in the chart legend.

    To see the query history within a session:

    1. Specify the required time interval.
    1. Optionally, configure filters.

- gRPC API {#grpc-api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
     
     ```bash
     cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
     ```
     
     Below, we assume that the repository contents reside in the `~/cloudapi/` directory.
  1. Call the [PerformanceDiagnosticsService.ListRawSessionStates](../api-ref/grpc/PerformanceDiagnostics/listRawSessionStates.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

     ```bash
     grpcurl \
       -format json \
       -import-path ~/cloudapi/ \
       -import-path ~/cloudapi/third_party/googleapis/ \
       -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/perf_diag_service.proto \
       -rpc-header "Authorization: Bearer $IAM_TOKEN" \
       -d '{
             "cluster_id": "<cluster_ID>",
             "from_time": "<time_range_left_boundary>",
             "to_time": "<time_range_right_boundary>"
           }' \
       mdb.api.cloud.yandex.net:443 \
       yandex.cloud.mdb.postgresql.v1.PerformanceDiagnosticsService.ListRawSessionStates
     ```

     Where:

     * `from_time`: Start of the time range in [RFC-3339](https://www.ietf.org/rfc/rfc3339.html) format, e.g., `2024-09-18T15:04:05Z`.
     * `to_time`: End of the time range in the same format as `from_time`.

     You can get the cluster ID with the [list of clusters in the folder](cluster-list.md#list-clusters).

  1. Check the [server response](../api-ref/grpc/PerformanceDiagnostics/listRawSessionStates.md#yandex.cloud.mdb.postgresql.v1.ListRawSessionStatesResponse) to make sure your request was successful.

{% endlist %}

For more on displayed data, see [this PostgreSQL guide](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW).

## Getting query statistics {#get-queries}

{% list tabs %}

- Management console {#console}

    1. Navigate to **Managed Service for&nbsp;PostgreSQL**.
    1. Click the name of your cluster and select **Performance diagnostics** → **Queries**.

    To view query statistics for a specific time interval:

    1. Select the time interval.
    1. Optionally, configure filters.

    To get details on relative changes in query statistics:

    1. In the **Interval 1** field, select the time interval to use as the baseline for calculations.
    1. In the **Interval 2** field, select the time interval to compare against the baseline interval.
    1. Optionally, configure filters.

    Suppose, 10 `SELECT * FROM cities` queries were executed in the first interval and 20 in the second. A comparison of the statistics will show a `+100%` difference for the <q>query count</q> metric (the `Calls` table column).

- gRPC API {#grpc-api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
     
     ```bash
     cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
     ```
     
     Below, we assume that the repository contents reside in the `~/cloudapi/` directory.
  1. Call the [PerformanceDiagnosticsService.ListRawStatements](../api-ref/grpc/PerformanceDiagnostics/listRawStatements.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

     ```bash
     grpcurl \
       -format json \
       -import-path ~/cloudapi/ \
       -import-path ~/cloudapi/third_party/googleapis/ \
       -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/perf_diag_service.proto \
       -rpc-header "Authorization: Bearer $IAM_TOKEN" \
       -d '{
             "cluster_id": "<cluster_ID>",
             "from_time": "<time_range_left_boundary>",
             "to_time": "<time_range_right_boundary>"
           }' \
       mdb.api.cloud.yandex.net:443 \
       yandex.cloud.mdb.postgresql.v1.PerformanceDiagnosticsService.ListRawStatements
     ```

     Where:

     * `from_time`: Start of the time range in [RFC-3339](https://www.ietf.org/rfc/rfc3339.html) format, e.g., `2024-09-18T15:04:05Z`.
     * `to_time`: End of the time range in the same format as `from_time`.

     You can get the cluster ID with the [list of clusters in the folder](cluster-list.md#list-clusters).

  1. Check the [server response](../api-ref/grpc/PerformanceDiagnostics/listRawStatements.md#yandex.cloud.mdb.postgresql.v1.ListRawStatementsResponse) to make sure your request was successful.

{% endlist %}

For more on displayed data, see the [pg_stat_statements](https://www.postgresql.org/docs/current/pgstatstatements.html) and [pg_stat_kcache](https://github.com/powa-team/pg_stat_kcache/blob/master/README.rst) extension guides.

## Getting query plan details {#auto-explain-enable}

The [`auto_explain` module](https://www.postgresql.org/docs/current/auto-explain.html) allows you to log slow query plans automatically, eliminating the need for the [`EXPLAIN` command](https://www.postgresql.org/docs/current/sql-explain.html). It helps to identify non-optimized queries. This module uses the general PostgreSQL log.

You can enable query logging in the [DBMS settings](update.md#change-postgresql-config):

1. In the **Shared preload libraries** field, select `auto_explain`.
1. Enable **Auto explain log analyze**.
1. Configure the `auto_explain` module settings:

    {% note warning %}

    Selecting `0` for **Auto explain log min duration** or enabling **Auto explain log timing** can significantly reduce cluster performance.

    {% endnote %}

    * [**Auto explain log buffers**](../concepts/settings-list.md#setting-auto-explain-log-buffers)
    * [**Auto explain log min duration**](../concepts/settings-list.md#setting-auto-explain-log-min-duration)
    * [**Auto explain log nested statements**](../concepts/settings-list.md#setting-auto-explain-log-nested-statements)
    * [**Auto explain log timing**](../concepts/settings-list.md#setting-auto-explain-log-timing)
    * [**Auto explain log triggers**](../concepts/settings-list.md#setting-auto-explain-log-triggers)
    * [**Auto explain log verbose**](../concepts/settings-list.md#setting-auto-explain-log-verbose)
    * [**Auto explain sample rate**](../concepts/settings-list.md#setting-auto-explain-sample-rate)