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 1pz0Qe-0005O9-GB for pgsql-admin@arkaria.postgresql.org; Tue, 16 May 2023 19:33:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pz0Qc-0002Zm-Vi for pgsql-admin@arkaria.postgresql.org; Tue, 16 May 2023 19:33:46 +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 1pz0Qc-0002Zd-5v for pgsql-admin@lists.postgresql.org; Tue, 16 May 2023 19:33:46 +0000 Received: from sonic312-26.consmr.mail.ir2.yahoo.com ([77.238.178.97]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pz0QY-000B8t-JC for pgsql-admin@postgresql.org; Tue, 16 May 2023 19:33:44 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.de; s=s2048; t=1684265618; bh=IlrETHaC/+5OQSBco4GVE94P9mIW4NB853fmMVmRYbs=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=sHzFHtRj4Bz9LxGXYkpkXja9MgjcDXR1WdOAXHfdbBCUQEvZkPDgOHcU75Ao5MxvmeXzPpWTU1FNyrnMeVTcpNbVCj7IiAKAuqXwqpU2z+KqbQaVufi8pa4PFk2hKUebdxALWchpgEnYwxD5TtqGyJY3ZCxfRSg9epN75ZWtNMBSJRtKD6mrp6e8XJRhE6DA1PQlXUeeVuIm+5/UkUcm0KpXwT3Z2wPGMkR9EziIQGAb75OLeBH7SaI4peqc/H7vUpYbB8sPytU/2bp5Tq09GlwkvDlmo6tXl5F8PSJeL+YqkvpnZNQeQh5aCpzgTjmlge3upmtUwnNvYSWJBJ/qOw== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1684265618; bh=LwBxRpNxgqS9AHSyyUoqdBHrCoLQdAeQL/rhuQMaRY4=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=AdAt4P8JXRRwJ7t9360HHTCMa4YVRdfO2S6Gdub9tSlYHl0B4hCggVa/Tw1c7gboC0ru5F6IqfMmM2koudwkTVP74ubO3qYLdiZSq+g0yUQ08+NapPOC17agdLFtyy/LhGWYIRkrPz93lRkhYEkRN82jQU7WLjUAzORTGHBI1lfUGHE8lU3pZWTMvP51uaNBjGKb7hjvMTWnoCMbpMkkUIFoSo3GkpXPvI6kbDl3pFTSSbAihIAhLJG4b9Z4IsKZhyd0qJABZZKx+XCi//qPXq40oockju3JpkBTl0BmkeLbo7W9oDX1e1dxdySGNHIHiEZMTAxlZ5OMk0NtCPtLqg== X-YMail-OSG: ABoVcB0VM1mWk6OT7cCQ6JXhSOaneeka8a5urBxvwRhJqvbKP31dqOqaHytNJ4t 3YA0MEpgnf0gU9qUjL4MUnu1EXDUYcPo1aeBz_g_fbCJ5WYoO2aOcrFo5_FnCySfnjLDcsfCwwKN jnbXm18xnupmAbJ2PWgpBSQ3l_QATnBc8D2qZGx1cVJQak8VB70tr7pLwIoT.j4kPhvMMtL65siK ywJwqqNk_VQTxvCfPJcLqXqZbz3qq5O1j0jLOnAdgi7lAHnd0n48YhhjDAfUnMXo_JrqRDxBOF2G yvFiDmF24WTvdVmefL6951p4skZancPDQXAcSN2LunH.VZQtCLRG8jcdYZmaL8jeBtwtcqJ4GJvS hjMD708EBCXoovf9_YVhg8rDupr48id1iVhIvxLziY4vyBUfxnANrGu3i6QcdoqEWc3VnSx1GmCH h55vgdL9m3o_7JZ3sKRi.UnpE61Wmp2lAyCnbHEraaeg1APqAR0yj7PLQH8nf5GHEmEzC79dGJ0x qUbx8buqwFTUlys54ShxvFpcdKGlYD1mi_u8.UKKjFz1sS9XmPk92RyMtq0RMXGotwshAijE8gV8 VqL5q7kyXv8yjI6OH.q4TOyRmYcPF4nlD0uDBaGLhCmD7aYQqJA.2u9XILAYxaxcQ.tlCeKkFVKG z9MnVQiZtJN9bBgDlc6Mo2190VqEXHYBqzmyTpiAbTtNPMV4hjAYtKRvzhnbviuE6X3MkNgKe1DJ snix1q9pKswsLQaHu5GRF7gDovS0KrFpf0BWdzN3sccXXomv4p9_g_4I6kqfAfwUMjG4V9.3sHCE 7ZIlmR_uWGq_Awg7J1U9zjVPwvTrxNu1RzrRn.sgsQqaUbl0VCU3lKw4MsAnP4oDwi2cgxkj.mRX qe.gjvl33enXiVz5BRHc6n.An.xBckSvWhADIz9FQLZPhktDOH_mqqBTv_H_t4oA1EI4.ncyKvEP Hdx77YTY2NK561PO6SyKPLCg6n5_wtqARGfZuIYbYH4C4VnVU1LmBB5j4hsyef0rqDyFIhUrjVrf AOpHgNxxXbvWeGvug7Tu9rEj_xQWJ8yZRXFC7XRUSUJMnxNkWTpSyu2NT8M8iHLztu6Uid1uWN44 qwNad892Rnrf9vQNsM1sUb5uK9CWBAKlLGJHtsY9VrEl7o16Lec30s_5KtqKdPCGDYZ6y8jKKtHS BoWzwVdzbINieQtXIBg3YB.tMJrtsblQkmff4JN8AEUfouz.01EBXTaTl9k1.tW6gwoK3E922rsw DKdjnxPvOSXUMa4eJQOiUx0HXKf5QOA6N_mLVguSXOEvDMfc9qcHpVOYtOimg_qvYUR_Z4W_w9RY 9H5SZIz9V_uChhf5b.iRXCVaZQ5nGRKnztGrC2K5DoVlnalH25KvYwH5hfFvinAOBrPOjEHliiS3 oi23DPadX6RhNxvIo5pIPogXceLe8sEyRHP5YUcsy58cpbiR1aaL3C6vFDN_mvjJ7rGB7DWRDJTG BkPjdFEfLERfA9JMu6zmAUPVSOjGOaJHTwuqrzwd3rXheV7x1_WqskW_lfP7H2FUkB0d686YpouH gTVy4zRov8ZlHO0QOSCI1tuz.fqXvLCeVsE_l7o.K91eKikXHH2D8U6sL2nlpOpJ7qddxvxc13Iu qhLPMYtc9LPj7Y_aBiI65SwkLY7VuOVasgqu7tc_5id5p07u9asU8siqVPkKwXpfl1skeN4V6OUY YCwmlAYhSfZeMcbD6SeZsS8rHbYto.DUlhE0U.8Bk8pAtk6cW1cL8O7tijChbtJAtY5_HSEv4rtV l2g5NzKSY4H2QFEC3IW_X_w6ANl21JmtA3sjwgTLwtXxjfUdV1OtGWTAAZqUgVdVsRFnLIy8Yxo6 0lWxkJgH6MwV5WNIO1PgbjtIDvkNAanDRkGGvSvIs9W0iPnDSItYPlEneqcvFjXE4eH3Pv3_y3iq .6yFeqzVW8JBOk.eAEbBoGvZ62H_rm189rShc2B8HfxXjlWClPckwTsLK5bWwNlPxA2FkGY6Pp2v jwMey2XhDqHa6SLzO.OL.4tnM0dA5cZSnocEwox95NP.g401yvJHLJjeXYX.Sfk7KGnwL216cqpX IkTrTPa6wmWXhKzHhDqYX9u6KRZqa6niiO96XAZ4NOZT_lYPPnCTe7a_OTi5UeY1hV7tU3mGCO9t ErY2clUaNKt1Yptm8Vu8TxcPjP0uzr5IcfbokAfay.lv7wiofvbDDbWFl1bu1yg4rrHf2w0NPDeW eu1E_gx67PqMV7wjU_nstXeLR_.csP8nKTlrdzUPYCWll8ySZzwsqhOBwqkpCZx4vfJRNBaLZLA8 FTCAHo3wIC9vy X-Sonic-MF: X-Sonic-ID: 59c8c00c-957e-43de-acbe-8acd5d8b4504 Received: from sonic.gate.mail.ne1.yahoo.com by sonic312.consmr.mail.ir2.yahoo.com with HTTP; Tue, 16 May 2023 19:33:38 +0000 Date: Tue, 16 May 2023 19:32:33 +0000 (UTC) From: Wolfgang Wilhelm To: Wells Oliver Cc: Jeff Janes , pgsql-admin Message-ID: <1286333072.4954427.1684265553802@mail.yahoo.com> In-Reply-To: References: <2042595996.3880251.1684164024807@mail.yahoo.com> Subject: Re: Options for more aggressive space reclamation in vacuuming? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4954426_1545772010.1684265553799" X-Mailer: WebService/1.1.21471 YMailNoble Content-Length: 11920 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_4954426_1545772010.1684265553799 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable If you are playing with this kind of thoughts how about rising the fill fa= ctor for the partitions which aren't updated anymore? Of course you have to= copy data from the "sparse" partition to a new "dense" partition with deta= ching the sparse and attaching the dense one. From the space point of view = a vacuum full is about the same. Am Montag, 15. Mai 2023 um 18:26:46 MESZ hat Wells Oliver Folgendes geschrieben: =20 =20 This brings to mind another question.. is there a clever solution for the = situation where .001 vacuum factor makes sense for a table at the end of th= e month where it's reached its general capacity, e.g. maybe 1bn rows, but a= t the beginning of the month is too aggressive and would cause too frequent= vacuuming given the significantly lower volume of records? Simple manual a= djustment through a month, or something easier?=C2=A0 On Mon, May 15, 2023 at 8:21=E2=80=AFAM Wolfgang Wilhelm wrote: Hi, what do you mean by "agressively reclaiming space"? Do you expect to see mo= re usable space in the file system? If this assumption is true you are mist= aken about the way autovacuum works. It deletes dead tuples _in_ the blocks= and frees space only when at least a certain amount of blocks at the end o= f the file with tuple data are empty. RegardsWW Am Montag, 15. Mai 2023 um 16:40:47 MESZ hat Jeff Janes Folgendes geschrieben: =20 =20 On Sun, May 14, 2023 at 5:03=E2=80=AFPM Wells Oliver wrote: I think our use-case here is nothing special: just very large tables that a= re deleted from and re-inserted with some frequency. They store monthly dat= a and exist as partitions. As an example, July of 2022 (summer months are p= eak) had=C2=A01,630,558,336 rows. We delete from this and insert daily.=20 You delete and reinsert every=C2=A0row every day, or just some lesser numbe= r of rows each day?=C2=A0 In one giant transaction daily, or in a series of= short mostly non-overlapping transactions throughout the day?=C2=A0 We rarely get new, relevant data once the month is over. The n_dead_tup fro= m=C2=A0pg_stat_all_tables here was over 7m rows, and clearing that out gave= us back nearly 50GB, and the file size estimate on this partition was ~200= GB.=20 Are these sizes for the entire relation=C2=A0size, or just for the core tab= le (not TOAST, not indexes)?=C2=A0 Index bloat is harder to prevent than ta= ble bloat, but is easier to deal with after the fact (reindex concurrently)= =C2=A0 These tables get auto-vacuumed but clearly it's not, well, aggressively=C2= =A0reclaiming space. 50 GB out of 200 GB seems roughly in accord with the default setting of=C2= =A0autovacuum_vacuum_scale_factor.=C2=A0 So maybe just lowering that from 0= .2 to, say, 0.05 would=C2=A0 be good enough,=C2=A0either globally or just f= or these tables. =C2=A0 Given that they are partitions, VACUUM FULL locks the parent table, so new = writes/deletes can't happen, and anyway people like to run queries against = these tables quite frequently. Since you rarely get new data once the month is over, you could lock the pa= rtition against concurrent changes (but still allow reads) once it is no lo= nger=C2=A0"on the run" while you copy the data into a new tightly-packed ta= ble and index that and add constraints.=C2=A0 Then you need only a very bri= ef access exclusive lock while you detach/drop the old partition and attach= the new table in its place.=C2=A0 Of course you can't drop the weaker lock= while you acquire the stronger one lest the old table change during the ga= p so lock management can be a bit tedious, but it is surely less restrictiv= e than a VACUUM FULL. Cheers, Jeff =20 --=20 Wells Oliver wells.oliver@gmail.com =20 ------=_Part_4954426_1545772010.1684265553799 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
If you are playing with this kind of thoughts how about rising= the fill factor for the partitions which aren't updated anymore? Of course= you have to copy data from the "sparse" partition to a new "dense" partiti= on with detaching the sparse and attaching the dense one. From the space po= int of view a vacuum full is about the same.

=20
=20
Am Montag, 15. Mai 2023 um 18:26:46 MESZ hat Wells Oliv= er <wells.oliver@gmail.com> Folgendes geschrieben:


This brings to mind another question.. is there a clever solution for = the situation where .001 vacuum factor makes sense for a table at the end o= f the month where it's reached its general capacity, e.g. maybe 1bn rows, b= ut at the beginning of the month is too aggressive and would cause too freq= uent vacuuming given the significantly lower volume of records? Simple manu= al adjustment through a month, or something easier? 

On Mon, May = 15, 2023 at 8:21=E2=80=AFAM Wolfgang Wilhelm <wolfga= ng20121964@yahoo.de> wrote:
Hi,

what do you mean b= y "agressively reclaiming space"? Do you expect to see more usable space in= the file system? If this assumption is true you are mistaken about the way= autovacuum works. It deletes dead tuples _in_ the blocks and frees space o= nly when at least a certain amount of blocks at the end of the file with tu= ple data are empty.

Regards
WW

=20
=20
Am Montag, 15. Mai 2023 um 16:40:47 MESZ hat Jeff Janes= <jeff.janes@gmail.com> Folgendes geschrieben:


On Sun, Ma= y 14, 2023 at 5:03=E2=80=AFPM Wells Oliver <wells.olive= r@gmail.com> wrote:
I think our use-case here is nothing special: = just very large tables that are deleted from and re-inserted with some freq= uency. They store monthly data and exist as partitions. As an example, July= of 2022 (summer months are peak) had 1,630,558,336 rows. We delete fr= om this and insert daily.

=
You delete and reinsert every row every day, or just some lesser = number of rows each day?  In one giant transaction daily, or in a seri= es of short mostly non-overlapping transactions throughout the day?
 
We rarely get = new, relevant data once the month is over. The n_dead_tup from pg_stat= _all_tables here was over 7m rows, and clearing that out gave us back nearl= y 50GB, and the file size estimate on this partition was ~200 GB.

Are these sizes for the entire= relation size, or just for the core table (not TOAST, not indexes)?&n= bsp; Index bloat is harder to prevent than table bloat, but is easier to de= al with after the fact (reindex concurrently)
 
These tables get auto-vacuumed but c= learly it's not, well, aggressively reclaiming space.

50 GB out of 200 GB seems roughly in a= ccord with the default setting of autovacuum_vacuum_scale_factor. = ; So maybe just lowering that from 0.2 to, say, 0.05 would  be good en= ough, either globally or just for these tables.
 

Given that they are partitions, VACUUM FULL locks = the parent table, so new writes/deletes can't happen, and anyway people lik= e to run queries against these tables quite frequently.
<= /div>

Since you rarely= get new data once the month is over, you could lock the partition against = concurrent changes (but still allow reads) once it is no longer "on th= e run" while you copy the data into a new tightly-packed table and index th= at and add constraints.  Then you need only a very brief access exclus= ive lock while you detach/drop the old partition and attach the new table i= n its place.  Of course you can't drop the weaker lock while you acqui= re the stronger one lest the old table change during the gap so lock manage= ment can be a bit tedious, but it is surely less restrictive than a VACUUM = FULL.

Cheers,
<= div>
Jeff

<= br clear=3D"none">
--
------=_Part_4954426_1545772010.1684265553799--