The Cloud SQL high-transaction-ID-utilizationrecommender proactivelygenerates recommendations that help you avoid potential transaction ID wraparound forCloud SQL for PostgreSQL instances.The Cloud SQL high-transaction-ID-utilization recommender is supported only forCloud SQL Enterprise Plus edition instances.

You can apply this recommendation when a Cloud SQL instance is trendingtoward a transaction ID wraparound problem.This page describes how the Cloud SQL high-transaction-ID-utilization recommenderworks and how you can use it.

How it works

A transaction ID is assigned when the transaction starts, and it's frozen untilthe transaction is vacuumed. Transaction ID utilization is the number of unvacuumed transactions(assigned minus frozen) expressed as a fraction of the maximum value of 2 billion.Under the default PostgreSQL settings, with vacuum processes performing optimallyand without interruption, most databases experience transaction ID utilization in the regionof approximately 10%. Higher transaction ID utilization levels can be observed in busy databaseswhere regular workloads frequently take precedence over vacuum. If the transaction ID utilization trends towardsvery high values (80% or more), the database might be at risk of transaction ID exhaustion.Transaction ID utilization reaching 100% is termed as transaction ID wraparound.Once the transaction ID utilization percentage reaches 100%, PostgreSQL stops accepting write queries.

The Cloud SQL high-transaction-ID-utilization recommender analyzes transaction IDutilization on a Cloud SQL for PostgreSQL instance.

If the transaction ID utilization percentage is more than or equal to 80%, it's recommended to take actions toavoid transaction ID wraparound.

The Cloud SQL high-transaction-ID-utilization recommender is in theStandardRecommender pricing tier.

Before you begin

Before you can view recommendations and insights, do the following:

• To get the permissions to view and work with insights and recommendations,ensure that you have the requiredroles.

  Tasks	Roles
  View recommendations	One of these roles:recommender.cloudsqlViewer orcloudsql.viewer.
  Apply recommendations	One of these roles:recommender.cloudsqlAdmin,cloudsql.editor, orcloudsql.admin.

  For more information about roles, seeunderstanding roles andgranting IAM permissions.

• Enable the Recommender API.

  Roles required to enable APIs

  To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

  Enable the API

List Improve Instance Performance recommendations

You can list the Improve Instance Performance recommendations byusing the Google Cloud console,gcloud CLI, or the Recommender API.

The improve instance performance recommendations are shown only if you have instances thatare nearing a transaction ID wraparound.

gcloud recommender recommendations list \--project=PROJECT_ID \--location=LOCATION \--recommender=google.cloudsql.instance.PerformanceRecommender \--filter=recommenderSubtype=POSTGRES_HIGH_TRANSACTION_ID_UTILIZATION_BEST_PRACTICE

GET https://recommender.googleapis.com/v1beta1/projects/PROJECT-ID/locations/LOCATION/recommenders/google.cloudsql.instance.PerformanceRecommender.PostgresHighTransactionIdUtilizationBestPractice/recommendations

If the recommender detects instances with high transaction ID utilization, it lists them in a tablewith other performance recommendations.Each row shows the instance ID, a brief recommendation, database engine, the location, and the last refresh date.

View insights and detailed recommendations

You can view insights and detailed recommendations about instancesthat are trending toward a transaction ID wraparound using the Google Cloud console,gcloud CLI, or the Recommender API.

gcloud recommender insights list \--project=PROJECT_ID \--location=LOCATION \--insight-type=google.cloudsql.instance.PerformanceInsight \--filter=insightSubtype=POSTGRES_HIGH_TRANSACTION_ID_UTILIZATION

GET https://recommender.googleapis.com/v1beta1/projects/PROJECT-ID/locations/LOCATION/insightTypes/google.cloudsql.instance.PerformanceRecommender.PostgresHighTransactionIdUtilization/insights

The following table lists the insight and recommendation that the Cloud SQLhigh-transaction-ID-utilization recommender generates. The subtypes are visible in thegcloud CLI andAPI results.

Apply recommendations

Evaluate the recommendations carefully and do any of the following:

• To examine the recommendation, clickView instance. SeeOptimize the performance of your instance orPrevent transaction ID wraparound and follow the recommendations.

• To dismiss the recommendation so that it's de-emphasized and appears dimmed,clickDismiss.

• To close the panel without applying or dismissing the recommendation, clickCancel.

Optimize the performance of your instance

To address the transaction ID wraparound problem with the instance, do the following:

1. Find the database and table causing the wraparound.

2. Explore and interpret transaction ID utilization metrics on your database instances.

3. Check for a stuck transaction ID.

4. Remove VACUUM blockers.

5. Check and accelerate VACUUM speed.

For additional information, also see the blog postUsing VACUUM to accelerate transaction ID freezing in Cloud SQL for PostgreSQL.

Prevent transaction ID wraparound

To prevent transaction ID wraparound for an instance, run the following command:

SELECT * FROM google_vacuum_mgmt.pg_fix_wraparound();

Example output:

postgres=> select * from google_vacuum_mgmt.pg_fix_wraparound();-[ RECORD 1 ]-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------issue_description | Cloud SQL for PostgreSQL has detected an open prepared transaction on your instance which is blocking VACUUM. Monitor the transaction ID utilization and commit or rollback the transaction, as needed.query             | ROLLBACK PREPARED 'trx_id_pin'; or COMMIT PREPARED 'trx_id_pin';recommendation    | To commit a prepared transaction, you must be connected as the same user that originally executed the transaction: postgresinsights          | Transaction ID Utilization: 88.49%

What's next

• Monitor disk availability
• Identify idle Cloud SQL instances
• Reduce overprovisioned Cloud SQL instances
• ExploreGoogle Cloud recommenders