RE: pg_upgrade —link does it remove table bloat - Mailing list pgsql-general
| From | Jason Ralph |
|---|---|
| Subject | RE: pg_upgrade —link does it remove table bloat |
| Date | |
| Msg-id | BN7PR04MB3826588843793E892430E00ED01A0@BN7PR04MB3826.namprd04.prod.outlook.com Whole thread Raw |
| In response to | Re: pg_upgrade —link does it remove table bloat (Michael Lewis <mlewis@entrata.com>) |
| Responses |
Re: pg_upgrade —link does it remove table bloat
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
| List | pgsql-general |
>There is more than one type of statistics though. Stats on the distribution of data is easily recreated with analyze table_name or analyzing the whole >database. What about the stats on how many rows have been inserted or updated since the last (auto)vacuum and that will be used to trigger autovacuum? >Are those set back to zero by an upgrade? I would assume usage counts like how many times an index scan has been done would be reset, but if the >numbers in pg_stat_user_tables like n_tup_upd or n_tup_del are zero'd out during an upgrade, than it would seem like a manual vacuum would always be a >good idea to ensure a table wasn't 99% of the way to needing one and then the stats got reset by upgrading.
I agree @Michael Lewis, thank you for this comment.
I am thinking a vacuum full is what I am going to need. Or pg_dump / pg_restore. I have tuned auto vacuum after the upgrade to be aggressive, it finishes fine after a couple hours on a large table, statistics look good on the pg_stat_user_tables. However, when I run the bloat check from the wiki https://wiki.postgresql.org/wiki/Show_database_bloat it still shows bloat. Thinking it may be left over from before the pg_upgrade and auto vacuum tuning.
Best,
Jason Ralph
From: Michael Lewis <mlewis@entrata.com>
Sent: Thursday, February 13, 2020 1:02 PM
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Jason Ralph <jralph@affinitysolutions.com>; pgsql-general@lists.postgresql.org
Subject: Re: pg_upgrade —link does it remove table bloat
There is more than one type of statistics though. Stats on the distribution of data is easily recreated with analyze table_name or analyzing the whole database. What about the stats on how many rows have been inserted or updated since the last (auto)vacuum and that will be used to trigger autovacuum? Are those set back to zero by an upgrade? I would assume usage counts like how many times an index scan has been done would be reset, but if the numbers in pg_stat_user_tables like n_tup_upd or n_tup_del are zero'd out during an upgrade, than it would seem like a manual vacuum would always be a good idea to ensure a table wasn't 99% of the way to needing one and then the stats got reset by upgrading.
pgsql-general by date: