ANALYZE command in azure database for postgresql

sql user 86 Reputation points
2021-01-24T00:47:16.68+00:00

Hi Team,

Looking for some inputs on the below.

We are in the process of moving our on premises postgres v10 to azure database for postgresql(v11) single server. Do we need to manually run ANALYZE on all tables on the PAAS platform (azure database for postgresql) or does the service automatically takes care of updating statistics on the tables?.

If we have to run ANALYZE periodically on Azure database for postgresql, how do we create a job?

Kevin

Azure Database Migration service
Azure Database for PostgreSQL
0 comments
Accepted answer
  1. KalyanChanumolu-MSFT 8,316 Reputation points
    2021-02-02T05:48:00.603+00:00

    <Posting as answer because of a 1000-character limit on comments>

    @sql user

    Quoting from the PostgreSQL wiki

    PostgreSQL keeps two different sets of statistics about tables. The first set has to do with how large the table is. This information is stored in the pg_class system table. The "relpages" field is the number of database pages that are being used to store the table, and the "reltuples" field is the number of rows in the table. The value of reltuples/relpages is the average number of rows on a page, which is an important number for the planner to know.

    On our documentation, we have incorrectly called this to be a constant and we are making changes accordingly.
    You can adjust the Autovacuum threshold based on the value of reltuples.

    To trigger the job manually, connect to the database and issue this command: "VACUUM".
    This causes a run in "lazy mode" that can be used during normal production use

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. KalyanChanumolu-MSFT 8,316 Reputation points
    2021-01-24T11:37:09.027+00:00

    @sql user Welcome to Microsoft Q&A forums and thank you for your interest in Azure Database for PostgreSQL.

    You can trigger a vacuum job manually or on a scheduled basis to run ANALYZE command on the tables using Autovacuum.
    It issues VACUUM and ANALYZE commands as needed for tables in the database.

    Please refer to the Autovacuum configurations documentation for more details.

    Do let us know if you have any further questions.

    ----------

    If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.