Обсуждение: PG 9.5 2 tables same DDL with diff size
HI List I am trying to understand the following : have 2 identical PG cluster on diff hosts, same postgresql.conf, same db schema : same tale DDL and row counts but different size ( 14GB diff ), I run reindex and full vacuum analyze, but I can not decrease the size of larger table(50GB) to match the size in second PG cluster. any tips what can make this 2 tables to have diff size except the host ( same OS and PG version 9.5.3)? Thank you
-----Original Message----- From: ghiureai [mailto:isabella.ghiurea@nrc-cnrc.gc.ca] Sent: Tuesday, January 09, 2018 5:54 PM To: pgsql-performance@postgresql.org Subject: PG 9.5 2 tables same DDL with diff size HI List I am trying to understand the following : have 2 identical PG cluster on diff hosts, same postgresql.conf, same db schema : same tale DDL and row counts but different size ( 14GB diff ), I run reindex and full vacuum analyze, but I can not decreasethe size of larger table(50GB) to match the size in second PG cluster. any tips what can make this 2 tables to have diff size except the host ( same OS and PG version 9.5.3)? Thank you ________________________________________________________________________________________________ Table is still bloated because of some long running transactions, which don't allow full vacuum to do its job? Regards, Igor Neyman
I run full vacuum and reindex on largest table (50GB) while there was no server activities so I assume no transaction was holding a lock on table since the full vacuum was able to run, anything where I should consider looking ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
-----Original Message----- From: Isabella Ghiurea [mailto:isabella.ghiurea@nrc-cnrc.gc.ca] Sent: Wednesday, January 10, 2018 10:48 AM To: pgsql-performance@postgresql.org Subject: RE: PG 9.5 2 tables same DDL with diff size Attention: This email was sent from someone outside of Perceptron. Always exercise caution when opening attachments or clickinglinks from unknown senders or when receiving unexpected emails. I run full vacuum and reindex on largest table (50GB) while there was no server activities so I assume no transaction washolding a lock on table since the full vacuum was able to run, anything where I should consider looking ? __________________________________________________________________________________________________________ Yes, in pg_stat_activity look for idle transactions that started long time ago. To prevent vacuum from doing its job they don't need to lock the table, they could just prevent from cleaning "old" row versions. Regards, Igor Neyman
Thank you Igor, I was able to eliminate the 15GB bloating for a 35GB table size , only after I restart the Pg server with one single connections and run a full vacuum for table. Isabella On 10/01/18 11:10 AM, Igor Neyman wrote: > -----Original Message----- > From: Isabella Ghiurea [mailto:isabella.ghiurea@nrc-cnrc.gc.ca] > Sent: Wednesday, January 10, 2018 10:48 AM > To: pgsql-performance@postgresql.org > Subject: RE: PG 9.5 2 tables same DDL with diff size > > Attention: This email was sent from someone outside of Perceptron. Always exercise caution when opening attachments orclicking links from unknown senders or when receiving unexpected emails. > > > I run full vacuum and reindex on largest table (50GB) while there was no server activities so I assume no transactionwas holding a lock on table since the full vacuum was able to run, anything where I should consider looking ? > > > __________________________________________________________________________________________________________ > > Yes, in pg_stat_activity look for idle transactions that started long time ago. > To prevent vacuum from doing its job they don't need to lock the table, they could just prevent from cleaning "old" rowversions. > > Regards, > Igor Neyman >