Thread

1. Copy Statistics Tables During Upgrade

   Virendra Kumar <viru_7683@yahoo.com> — 2021-03-31T18:15:23Z

   Hello Team,
   I was doing an upgrade of one of our PG (9.6.11 to 11.9) database and came across the question that what is reason PostgreSQL is not doing copy of stats and we have to analyze right after upgrade. There are two situations where this will help when we use this DB as our enterprise database:
   1. If DB is in magnitude of hundreds of TB and we have to do analyze it might take several hours before we handover this to application services.
   2. We are loosing critical information like most common values and most common freqs because these values are populated over time when many-many sessions hit table and queries with different values of a column.
   Any input on this is higly appreciated.
   
   
   Regards,
   Virendra Kumar
   
   

2. Re: Copy Statistics Tables During Upgrade

   pierre.forstmann@gmail.com — 2021-04-02T07:06:59Z

   Hello,
   
   This is a pg_upgrade known limitation that has been summarized by Greg 
   Sabino Mullane on 
   https://www.endpoint.com/blog/2016/12/07/postgres-statistics-and-pain-of-analyze
   
   this way:
   
   The nominal reason for not copying the data is that the table format may 
   change from version to version. The real reason is that nobody has 
   bothered to write the conversion logic yet, for pg_upgrade could 
   certainly copy the pg_statistics information: the table has not changed 
   for many years.
   
   Regards
   
   Pierre Forstmann
   
   Le 31/03/2021 à 20:15, Virendra Kumar a écrit :
   > Hello Team,
   >
   > I was doing an upgrade of one of our PG (9.6.11 to 11.9) database and 
   > came across the question that what is reason PostgreSQL is not doing 
   > copy of stats and we have to analyze right after upgrade. There are 
   > two situations where this will help when we use this DB as our 
   > enterprise database:
   >
   > 1. If DB is in magnitude of hundreds of TB and we have to do analyze 
   > it might take several hours before we handover this to application 
   > services.
   >
   > 2. We are loosing critical information like most common values and 
   > most common freqs because these values are populated over time when 
   > many-many sessions hit table and queries with different values of a 
   > column.
   >
   > Any input on this is higly appreciated.
   >
   >
   > Regards,
   > Virendra Kumar
   >