What's Postgres transaction ID utilization and why it matters | C2C Community

What's Postgres transaction ID utilization and why it matters


Userlevel 6
Badge +11

PostgreSQL uses transaction IDs (also called TXIDs or XIDs) to implement Multi-Version Concurrency Control semantics (MVCC).

To prevent transaction ID wraparound, PostgreSQL uses a vacuum mechanism, which operates as a background task called autovacuum (enabled by default), or it can be run manually using the VACUUM command. A vacuum operation freezes committed transaction IDs and releases them for further use. You can think of this mechanism as “recycling” of transaction IDs that keeps the database operating despite using a finite number to store the transaction ID.

Vacuum can sometimes be blocked due to workload patterns, or it can become too slow to keep up with database activity. If transaction ID utilization continues to grow despite the freezing performed by autovacuum or manual vacuum, the database will eventually refuse to accept new commands to protect itself against TXID wraparound. To help you monitor your database and ensure that this doesn’t happen, Cloud SQL for PostgreSQL introduced three new metrics:

  • transaction_id_utilization
  • transaction_id_count
  • oldest_transaction_age

 

👉 Read further Monitor transaction ID utilization in Cloud SQL PostgreSQL | Google Cloud Blog


6 replies

Userlevel 7
Badge +65

Hi @seijimanoan,

great post. Thanks for sharing!

Can you tell us if you have used the Vaccum mechanism? And if yes, when do you leave the default or disable and use the VACUUM command to enable it?

@Vick have you used it?

Userlevel 7
Badge +12

Great post @seijimanoan 

@ilias , nope 😊 

Userlevel 7
Badge +65

Oh, I see.

Thanks, @Vick 

Userlevel 6
Badge +11

@ilias actually I've left the default settings 😆 but it seems reasonable to make the monitoring to know its daily status quo

Userlevel 7
Badge +65

@seijimanoan in most cases people are using the default settings. Mainly because they don’t know what they need and they are afraid to play around.

Totally agree that it is reasonable to monitor daily. 

Userlevel 7
Badge +29

Great post, @seijimanoan! Thanks for sharing! :) 

Reply