Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPSzl-0002uL-UQ for pgsql-admin@arkaria.postgresql.org; Tue, 07 Feb 2023 18:47:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pPSzk-0007q1-QH for pgsql-admin@arkaria.postgresql.org; Tue, 07 Feb 2023 18:47:08 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPSzk-0007pi-BY for pgsql-admin@lists.postgresql.org; Tue, 07 Feb 2023 18:47:08 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPSzh-0005NH-P3 for pgsql-admin@lists.postgresql.org; Tue, 07 Feb 2023 18:47:07 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 926503804B9; Tue, 7 Feb 2023 19:47:03 +0100 (CET) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id kQuKUql4jdpy; Tue, 7 Feb 2023 19:47:03 +0100 (CET) Received: from [192.168.0.178] (ip-109-42-177-68.web.vodafone.de [109.42.177.68]) by rs.plausibolo.de (Postfix) with ESMTPSA id 363CC380332; Tue, 7 Feb 2023 19:47:03 +0100 (CET) Message-ID: <7927f0f1-517b-c3e3-a46a-edae56dee442@jakobs.com> Date: Tue, 7 Feb 2023 19:47:02 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.6.1 Subject: Re: Vacuum Full not clean the table To: pgsql-admin@lists.postgresql.org References: <8aacabc6-5919-5f37-9c9a-2a3cd67adfa3@jakobs.com> From: Holger Jakobs In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------M59cOe7xiTP0kEiqwaFQuuZM" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------M59cOe7xiTP0kEiqwaFQuuZM Content-Type: multipart/mixed; boundary="------------457DOyw4A01WLQrGgws0FMOD"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: <7927f0f1-517b-c3e3-a46a-edae56dee442@jakobs.com> Subject: Re: Vacuum Full not clean the table References: <8aacabc6-5919-5f37-9c9a-2a3cd67adfa3@jakobs.com> In-Reply-To: --------------457DOyw4A01WLQrGgws0FMOD Content-Type: multipart/alternative; boundary="------------lxprxtwUvEhBF0DzFS2oEdpj" --------------lxprxtwUvEhBF0DzFS2oEdpj Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 QW0gMDcuMDIuMjMgdW0gMTk6NDUgc2NocmllYiBFcmlrIFNlcnJhbm86DQo+IFRoYW5rIHlv dSB2ZXJ5IG11Y2ggSG9sZ2VywqAgZm9yIGFuc3dlcmluZywgSSdsbCB0ZWxsIHlvdS4uLiBy ZWdhcmRpbmcgDQo+IHlvdXIgcXVlc3Rpb24gYWJvdXQgd2hldGhlciBpdCBoYXMgZW5vdWdo IHByb3Zpc2lvbmVkIHNwYWNlIHRvIGJlIGFibGUgDQo+IHRvIGRvIHRoZSB2YWN1dW0sIHll cy4uLiB5ZXMgaXQgZG9lcy4NCj4gUmVnYXJkaW5nIHlvdXIgaW5kaWNhdGlvbiBvZiB0aGUg dmVyc2lvbiB3ZSBhcmUgd29ya2luZyBvbiwgSSBrbm93IA0KPiB0aGF0IDkuMiBpcyBvdXQg b2YgYWxsIHN1cHBvcnQgYW5kIHRoYXQgaXRzIHVwZGF0ZSBpcyBpbW1pbmVudC4uLiBCdXQg DQo+IHRoYXQncyBob3cgaXQgaXMgcmlnaHQgbm93IGFuZCBJIGhhdmUgdG8gZGVhbCB3aXRo IGl0Li4uDQo+DQo+IEZvciB0aGlzIHJlYXNvbiwgSSBhcHByZWNpYXRlIHRoYXQgeW91IGNh biBldmVuIGd1aWRlIG1lIHRvIHNlZSB3aGF0IA0KPiBpcyBoYXBwZW5pbmcgb3IgY291bGQg aGF2ZSBoYXBwZW5lZCByZWdhcmRpbmcgdGhlIGVuZCBvZiB0aGUgcHJvY2VzcyANCj4gd2l0 aG91dCB0aGUgZXhwZWN0ZWQgcmVzdWx0LCB3aGljaCBpcyBjbGVhbmluZyB0aGUgdGFibGUu DQo+DQo+IFRoYW5rIHlvdSBpbiBhZHZhbmNlIGZvciB5b3VyIGhlbHANCj4NCj4gLyoNCj4g Ki8NCj4gLypFcmlrIFIuIFNlcnJhbm8gU2FhdmVkcmEqLw0KPiAvKkluZ2VuaWVybyBkZSBT aXN0ZW1hcyBJbmZvcm3DoXRpY29zKi8NCj4gLyrCoCDCoCDCoCBEYXRhIEJhc2UgQWRtaW5p c3RyYXRvciovDQo+IC8qZXNlcnJhbm9zQGdtYWlsLmNvbQ0KPiAqLw0KPiAvKsKgIMKgIMKg IMKgIMKgIMKgIMKgIMKgIMKgOTk4NTk2NjkxKi8NCj4gLyoqLw0KPg0KPg0KPiBFbCBtYXIs IDcgZmViIDIwMjMgYSBsYXMgMTM6NTYsIEhvbGdlciBKYWtvYnMgKDxob2xnZXJAamFrb2Jz LmNvbT4pIA0KPiBlc2NyaWJpw7M6DQo+DQo+ICAgICBBbSAwNy4wMi4yMyB1bSAxNzo1MSBz Y2hyaWViIEVyaWsgU2VycmFubzoNCj4+ICAgICBEZWFyIEFsbCwNCj4+DQo+PiAgICAgQWxv bmcgd2l0aCBzYXlpbmcgaGVsbG8sIEkgd291bGQgbGlrZSB0byBjb25zdWx0IGFib3V0IGEg ZmFpbHVyZQ0KPj4gICAgIHRoYXQgaXMgaGFwcGVuaW5nIHRvIG1lIHdpdGggYSBQRyA5LjIg YmFzZSBhbmQgd2l0aCBhIHRhYmxlIHRoYXQNCj4+ICAgICB3ZWlnaHMgMTE2R0IgYW5kIGl0 cyBpbmRleCAxM0diLCBtb3VudGVkIG9uIGEgTGludXggQ2VudG9zIDcuMg0KPj4gICAgIHdp dGggNENvcmUgYW5kIDMyUmFtLg0KPj4gICAgIFRoZSBmYWlsdXJlIG9jY3VycyBtYWlubHkg aW4gdGhhdCBJIGV4ZWN1dGUgYSBWYWN1dW0gRnVsbCBvbiB0aGUNCj4+ICAgICB0YWJsZSBh bmQgaXQgZW5kcyBhZnRlciA2IGhvdXJzLCBidXQgaXQgZG9lcyBub3QgY2xlYW4gYW55dGhp bmcuDQo+PiAgICAgV2hlbiBjaGVja2luZyB0aGUgZGVhZCB0dXBsZXMgSSByZWFsaXplIHRo YXQgdGhleSBmb2xsb3cgdGhlIHNhbWUNCj4+ICAgICBkaXJ0eSByZWNvcmRzIGluIHRoZSB0 YWJsZS4gUkVGOiAodGFibGUgb2YgNDIwTU0gcmVjb3JkcyB3aXRoDQo+PiAgICAgMjBNTSBk ZWFkIHR1cGxlcykNCj4+DQo+PiAgICAgSSBob3BlIHlvdSBjYW4gZ3VpZGUgbWUgaW4gZmlu ZGluZyBhIHNvbHV0aW9uIHRvIGNsZWFuIHRoYXQgdGFibGUuDQo+Pg0KPj4gICAgIEdyZWV0 aW5ncyBhbmQgbWFueSB0aGFua3MgdG8gdGhlIFBvc3RncmVzcWwub3JnIGNvbW11bml0eQ0K Pj4gICAgIEF0dGUuDQo+Pg0KPj4gICAgIC8qDQo+PiAgICAgKi8NCj4+ICAgICAvKkVyaWsg U2VycmFubyovDQo+Pg0KPg0KPiAgICAgSGkgRXJpaywNCj4NCj4gICAgIERvZXMgaXQgZW5k IGNsZWFubHkgb3Igd2l0aCBhbiBlcnJvciBhZnRlciA2IGhvdXJzPw0KPg0KPiAgICAgRG8g eW91IGhhdmUgZW5vdWdoIGRpc2sgc3BhY2UgZm9yIHRoZSB2YWN1dW0gZnVsbD8gVGhlIG9s ZCBhbmQgdGhlDQo+ICAgICBuZXdseSB3cml0dGVuIGRhdGEgaGF2ZSB0byBleGlzdCBhdCB0 aGUgc2FtZSB0aW1lLiBPbmx5IGFmdGVyIGENCj4gICAgIHN1Y2Nlc3NmdWwgdmFjdXVtaW5n IHRoZSBvbGQgZGF0YSBnZXQgZGVsZXRlZC4NCj4NCj4gICAgIEJUVywgUEcgOS4yIGhhcyBi ZWVuIG91dCBvZiBzZXJ2aWNlIGZvciBxdWl0ZSBzb21lIHRpbWUuIE9ubHkNCj4gICAgIHZl cnNpb25zIDExIHRvIDE1IGFyZSBzdXBwb3J0ZWQsIHdpdGggdGhlIHN1cHBvcnQgZm9yIHZl cnNpb24gMTENCj4gICAgIGxhc3RpbmcgbGVzcyB0aGFuIGEgeWVhci4NCj4NCj4gICAgIEtp bmQgUmVnYXJkcywNCj4NCj4gICAgIEhvbGdlcg0KPg0KPiAgICAgLS0gDQo+ICAgICBIb2xn ZXIgSmFrb2JzLCBCZXJnaXNjaCBHbGFkYmFjaCwgVGVsLiArNDktMTc4LTk3NTkwMTINCj4N ClNjb3R0IFJpYmUgaXMgcmlnaHQsIHRoZSBwZXJjZW50YWdlIG9mIGRlYWQgdHVwbGVzIGlz IG5vdCBoaWdoLiBTbyB0aGVyZSANCm5vIHJlYWwgcmVhc29uIHRvIGNoYW5nZSBhbnl0aGlu Zy4NCg0KDQotLSANCkhvbGdlciBKYWtvYnMsIEJlcmdpc2NoIEdsYWRiYWNoLCBUZWwuICs0 OS0xNzgtOTc1OTAxMg0KDQo= --------------lxprxtwUvEhBF0DzFS2oEdpj Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Am 07.02.23 um 19:45 schrieb Erik Serrano:
Thank you very much Holger=C2=A0 for answering, I'= ll tell you... regarding your question about whether it has enough provisioned space to be able to do the vacuum, yes... yes it does.
Regarding your indication of the version we are working on, I know that 9.2 is out of all support and that its update is imminent... But that's how it is right now and I have to deal with it...

For this reason, I appreciate that you can even guide me to see what is happening or could have happened regarding the end of the process without the expected result, which is cleaning the table.

Thank you in advance for your help


Erik R. Serrano Saavedr= a
Ingeniero de Sistemas Inform=C3=A1ticos
=C2=A0 =C2=A0= =C2=A0 Data Base Administrator
=C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0eserranos@g= mail.com
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0998596691


El mar, 7 feb 2023 a las 13:56, Holger Jakobs (<holg= er@jakobs.com>) escribi=C3=B3:
=
Am 07.02.23 um 17:51 schrieb Erik Serrano:
Dear All,

Along with saying hello, I would like to consult about a failure that is happening to me with a PG 9.2 base and with a table that weighs 116GB and its index 13Gb, mounted on a Linux Centos 7.2 with 4Core and 32Ram.
= The failure occurs mainly in that I execute a Vacuum Full on the table and it ends after 6 hours, but it does not clean anything. When checking the dead tuples I realize that they follow the same dirty records in the table. REF: (table of 420MM records with 20MM dead tuples)

I hope you can guide me in finding a solution to clean that table.

Greetings and many thanks to the Postgresql.org community
Atte.


Erik Serrano<= /font>


Hi Erik,

Does it end cleanly or with an error after 6 hours?

Do you have enough disk space for the vacuum full? The old and the newly written data have to exist at the same time. Only after a successful vacuuming the old data get deleted.

BTW, PG 9.2 has been out of service for quite some time. Only versions 11 to 15 are supported, with the support for version 11 lasting less than a year.

Kind Regards,

Holger

--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Scott Ribe is right, the percentage of dead tuples is not high. So there no real reason to change anything.


--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------lxprxtwUvEhBF0DzFS2oEdpj-- --------------457DOyw4A01WLQrGgws0FMOD-- --------------M59cOe7xiTP0kEiqwaFQuuZM Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCY+KcpgUDAAAAAAAKCRCKV5yTsxlhugfi AJ9zwERXpwK6zXU0qAj8hIZQBe+KAQCgrKBQYtDMVK1tCXZi/lR8H5dtsmU= =edig -----END PGP SIGNATURE----- --------------M59cOe7xiTP0kEiqwaFQuuZM--