search

vitabaks / postgresql_cluster

PostgreSQL High-Availability Cluster (based on "Patroni" and DCS "etcd" or "consul"). Automating with Ansible.
MIT License
1.27k stars 340 forks source link

Analyze a PostgreSQL database (optimizer statistics) immediately after the upgrade #601

Closed vitabaks closed 1 month ago

vitabaks commented 1 month ago

This PR introduces several important changes to the process of upgrading Postgres to the new major version:

  1. Move the execution of the statistics task to directly follow the PostgreSQL upgrade process, specifically after the pg_upgrade execution but before conducting post-upgrade checks and updating extensions.
    • By shifting the statistics collection earlier, we aim to minimize post-upgrade performance dips and maintain system efficiency. This move aligns with best practices for database management and upgrades.
      1. Move "Wait for the analyze to complete" and "Stop pg_terminator script" to Post-Upgrade tasks.
        • This will allow you to continue performing the remaining tasks of the POST-UPGRADE stage instead of waiting for the statistics collection to be completed. Now we check the status of statistics collection after completing all tasks related to the update.
  2. Monitor and terminate the long-running transactions during statistics collection
    • This task is designed to terminate active queries exceeding a specified duration (in seconds) during the collection of statistics, as defined by the 'vacuumdb_analyze_terminate_threshold' variable. If this variable is set to a value greater than 0 (default is '0'), the pg_terminator script automatically cancels transactions exceeding this duration.
    • This preventative measure helps avoid partial performance degradation of the database during the period when statistics are yet to be fully collected. It ensures the database server resources are not overwhelmed and the number of available connections is not saturated, maintaining efficient database operation