Feedback-ID: i76984098:Fastmail
Message-ID: <b5fd053f-9131-4ee4-8f67-8d9efa6b4e97@aklaver.com>
Date: Sun, 16 Feb 2025 09:12:02 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Help in vetting outcome of "vacuumdb --analyze-in-stages" -
 during DB Upgrade from EC2- PGS - Community Edn ver 13.X to 14.X
To: Y_Bharani_mbsv <mailbsv@yahoo.com>,
 Greg Sabino Mullane <htamfids@gmail.com>,
 Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
References: <0558ddd4d71641bdb41fa49b2425f73c@safrangroup.com>
 <1061066336.5835157.1733316137292@mail.yahoo.com>
 <CAKAnmmKZdhnhdNRd3OgDyEco9OPkT=qA_TeWMFMRvUM9pXauKg@mail.gmail.com>
 <1482982714.8486017.1735661703839@mail.yahoo.com>
 <1763130721.4001842.1737733841628@mail.yahoo.com>
 <c3fc88ef-d155-4455-bc01-2c569b401998@aklaver.com>
 <132487461.4068668.1737741687606@mail.yahoo.com>
 <0dc06cb7-33cc-43ba-a95f-535fdf0a0439@aklaver.com>
 <1751608443.5432365.1738081421269@mail.yahoo.com>
 <74599d1d-c8a2-4e59-a50d-019dcc973de8@aklaver.com>
 <200665967.5560583.1738095230696@mail.yahoo.com>
 <21b5d62a-19d1-413f-9d5e-d681cd2bb91b@aklaver.com>
 <47454513.6047834.1738179914107@mail.yahoo.com>
 <1841861276.9581730.1738888679871@mail.yahoo.com>
 <fabc9547-1de0-4efa-baf5-124d91e3fab5@aklaver.com>
 <56243553.9616888.1738893835649@mail.yahoo.com>
 <940531722.732202.1739711614045@mail.yahoo.com>
 <6f44101c-ce01-478e-9fb1-138a10f358d9@aklaver.com>
 <688728245.763369.1739723234892@mail.yahoo.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <688728245.763369.1739723234892@mail.yahoo.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/b5fd053f-9131-4ee4-8f67-8d9efa6b4e97%40aklaver.com>
Precedence: bulk

On 2/16/25 08:27, Y_Bharani_mbsv wrote:
> Adrian
> TQ for the instant reply.
> post DB migration to Ver 14.X (successfully) and Post executing the 
> "vacuumdb --analyze-in-stages", i noticed "read me option" and the 
> caveat on it.

Did you do:

vacuumdb --analyze-in-stages

or

vacuumdb --all --analyze-in-stages

?


> 
> Later, I too did
> a) vacuum(full,verbose,skip_locked) ... each table wise b) analyze 
> (verbose,skip_locked) .. each table wise
> against all the DB's

VACUUM FULL has not purpose at this point as FULL recycles unneeded 
tuples from DELETEs and UPDATEs and at this stage there are none of 
those. Also going table by table is not necessary when you can 
vacuuum/analyze an entire database, which is what you want, with one 
command.

> 
> Any guidance on how to overcome the issue.

There is no issue to overcome.

Per:

https://www.postgresql.org/docs/current/pgupgrade.html

17. Statistics

"Because optimizer statistics are not transferred by pg_upgrade, you 
will be instructed to run a command to regenerate that information at 
the end of the upgrade. You might need to set connection parameters to 
match your new cluster."

Running vacuumdb --all --analyze-in-stages will create the statistics 
you need it just does it in steps(stages) vs doing vacuumdb --all 
--analyze-only which does it in a single pass. Unless you are planning 
to run some large complicated queries immediately upon completion of the 
upgrade you will not notice the difference.

> Any suggestion ?
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com