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 1pya0t-00068e-8F for pgsql-admin@arkaria.postgresql.org; Mon, 15 May 2023 15:21:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pya0r-0006lm-AJ for pgsql-admin@arkaria.postgresql.org; Mon, 15 May 2023 15:21:25 +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 1pya0q-0006lc-EI for pgsql-admin@lists.postgresql.org; Mon, 15 May 2023 15:21:24 +0000 Received: from sonic311-31.consmr.mail.ir2.yahoo.com ([77.238.176.163]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pya0i-002TXb-K5 for pgsql-admin@postgresql.org; Mon, 15 May 2023 15:21:22 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.de; s=s2048; t=1684164072; bh=oACKbleLYRF+Uyz9pHGVGRiOGvl/h7XBu2PZ3ITcJVA=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=GQKCZV8mfzz49/lyTAAgDlCe57NpROP7pw/FK4bQTUPub24pCcIk9XBYJA6aZwuwE1N532jSK269p1sEf1r3p0+pPG+sQJLDZ3Rj+mBTWRarR9BmZ2xuP7WoPhNP5uNoAu9pVMn/qVs3US3lvRCZxucM6XEL1GTfT/JQtewqM3cQ5SktBpyU2AeK8b0jGl415UvxiBY5WLbHxYw0r0XtwiuYmGrOy/4JlufWfCvONDs3vQW7gFFy9ebtQ0hH2/oaeCyK3lsF6XwNpjnvqxIGnLRZ60gGEqcz7qqdZd7k3VaOur8VqGzz7+omn0pSehhWuH7Yt48+e3mDsrZKuBrW0g== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1684164072; bh=yJFDg0bJMIUXhtBjBFv+4IE+lq2+hiohVTO+i7Au1GQ=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=EGvMAdKuVTPdBK9y3g+vLe2YEC1nYHDBa12rlIO6tm5tLE6bzOlwjd+YHTi8ByTrNNtAdDLqAmW7bJ9S3o3odxtaKvP/0aqc/e7wVd2BCHeHkVEJYqa3S7hGAysZ6FTOAajOvmPDoURyivQc6+g04Z9+eGOZ1VvKQRIzh9miO8UiTCXjDbEXcUomC1yztOdnJe5oOaZRWZcTWmtmWZIifBun0edMYHCO1Bwa3qJpuUA5w9rGgVD7t4fU9CCBISGC0I4SOfB4/vCmiWwqyOX3u6vYkLqwc1zbd4rdJFrXdbQbfnqHu0xXAs7CIYUGVfb4cIhxUZXoFXd5oc6/gQZCow== X-YMail-OSG: oYDWZfEVM1mh8pkviXHGmKeS8zgqlifOCM60FbjJkIMFGLXEaNfM9MYy.Ka1wz9 .eYM2FMI74bKcgGbvwrRfuZV2Izdy9Q.eUsf2vR2El1Lw7hnG5jTLEd_CAReBnoC2Rnc.yYdoFA4 wQN7Aocx8sJESMFRbjrGW11k0XA.we35VZW5Wm_VJcuOzXJOfTx4MrryTa_cwN9NvfDuwjIzc223 Py7m09tDPw7QV9HHBNr.OCeLRAZseUWjQAsA6VWcniQOLVt7BysYmZI.Z4YJXAw2FZHc2XU5cAKH ohlsSuHVBMFkno3m2h5p696Sns91oRHmYfFMCrLsQI7lBcfwAkMtvmeJiCcFChkX7mn_t2O6REwq .RuJxmIC8xroZDFkI2A5bFf2RkKLdAH66bNoEgWDaYWAHzQL.RHlpCFaBgUy6GLKJSKvl.Sfto0d FgA8ksC503XtzRgr4Eyrq9_mV81ePgT5ia71.EEq5z87YZ_1cCSepn6c.Wn3dxIiAVo3fmPQ7YNg YSVYtoNwvRXzaB2CP2uOdOhmz2J3PNHtLVUmj7FHvAPZrEH4zDfs.zVLyxe5dxig1vPbZ9S3f9TR Z5CfXI__fq0RbA6DkFAXk6R6OQUnGDVfK8HA42KD8AoINzRB9oEkAm6ZNGVZH.rKsDG4Vb4LiZKH ZLm3pwcWe7.iGaKJwX33ifPYXOhc85G6FXEl5lcBF3oZV9oiycpKPxH6gkhg7EmWZxPdoaJfB6F3 YjnZeHCbI40WsK0BW8NTvVK2aRI7Qn3E06ByhSWMctlrf0YrTsKiqrHC23NikOHo94s6kXrRvUAp VcidsfY6ldj9iQ5vv9.sduUSCC2qxSuhCjgUdUEAhOSS_KCG3pSvXgyVw0mBRgSUX7N2.Dfvj.nh qUsTYoFaGcWiY_c4sCphnHYI3GBQHBLbG2Kpeux99eKYZI5uZOKAEvws_1yS06oNKcuD5Ujlila4 5UmmwVy3QIo8aMInHl7FjZjTY6cvX.EBl_b9aX9Ei4wmObh6vJF2O8IbrlVS4KQanWEVip9RchwF x7uRPtBOwssy_IYr6Is60zgxBU6YJTwCrYc357qBXZlNqu1PwJnah4bMEd5wsSPw.svWVzQjiu6w StWcuwhwo.krQwCxYasZfRXI0Ck28iRIRzMGZe_JfTQAH9M1fsncmyKNUDOx2b8T2pD96Qhq3I_. P.CoLP1AclW53SMQDTbAy0RDdzg2KNCdh5.GUFxsiBzu5Pjwxd2LyKx.ozAh__sPhGLPEItUZyEd q171KK4JCe_08cgVB_goxmIcVEdlsQB1ZR4LTgzS1JtuebfnRD5j6gck1FHhgc5RkVHXw3dVBwnY BZZf59o1VivUd2lHgHIgIU96cBsU3q8M6QdhfmUjjBeKrxhAbNoTgUvRMmSZe38stB9w490XSoI7 Y44kuYM4vQs2.0kXCn0d3wbMrDhMX4Z_Zk5cpcIayaXirNB7b7SAk5nje_mcHA5eKgp9fGJpbB1R 7M7FOcvVIzwALW8ZEQjbWUh6yM37Q8TprJKX.HSoWcRmstYLv_fqYMhtuqRLQ.YtKNu94SzdIzhS hHTp7TrDV6JbIZXwDPqA9a80yJ7p5y0605niUtGZU.JpM54rduq498DI.bITXJHgkvpYB.A_aze_ 1Un0b7mBaaHtlFsMH9DuOTBCa_gDd8uvK8Le0.kPEnthDT_sMrYTn6q4bcugGCbQLQK1lICH74YP Wcyi47IQgV2PRtlsGA7QzUsGWWWpqAiPiIxcqPe00KWbCcFBq4zAVqUvuuCEvbDDW3vmwNuNB2Lu yVO_idFL3GH71KrcGKNYXBFimWnXsuzI8uM_7DphRgSjLJbvyuUfbWaRPmBGZ6GQH3.iVTynQVpp yl5KpuStlRtrlOi4KOclooUgaAEKLfFGOXsIYsb3i7PSWbzLlIKMblWT2ofOjvzrUyYF2m90Hk60 pKiYHSmXiAUIxZMbkhVwG00NA.IJV_PK77a5tRLNMr24eCHxDNTVK8rM2IaQZqzU2luYjbK5gKBG Apkg9FySAXf_Omii_rsQXuAWuh1Gj0cRkVygd0.tgVrLm2d4YuXDaUqDzttRk9h4nrtfALHp5lrL eU_rccKLsIzHHUtsUFo54.o254_V6xGQcquUEx9Ww8rLwWp0_cEIM0edU_obzJDiw.Ug30TokeMq y_Af7yTqpyZcXwLmXQpaJ67r6innRIb6Szvdem0yMFjIeP_aXA_Ypk3cXxYDxXAA2d0UPJymylS4 53E2_p.S5FMlwQXi_iByX1a0VdBFIlaL30MrUf8JydHjk3_C3X2yypsHq9sHlOpm4An5OYZo3WaL frwG5tSnLYpf0uUSo3GU- X-Sonic-MF: X-Sonic-ID: b0565192-6453-4173-8d3c-6c1b681dc4d9 Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.ir2.yahoo.com with HTTP; Mon, 15 May 2023 15:21:12 +0000 Date: Mon, 15 May 2023 15:20:24 +0000 (UTC) From: Wolfgang Wilhelm To: Wells Oliver , Jeff Janes Cc: pgsql-admin Message-ID: <2042595996.3880251.1684164024807@mail.yahoo.com> In-Reply-To: References: Subject: Re: Options for more aggressive space reclamation in vacuuming? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3880250_869097707.1684164024805" X-Mailer: WebService/1.1.21471 YMailNoble Content-Length: 8372 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_3880250_869097707.1684164024805 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 ------=_Part_3880250_869097707.1684164024805 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi,

what do you mean by "agressively = reclaiming space"? Do you expect to see more usable space in the file syste= m? If this assumption is true you are mistaken about the way autovacuum wor= ks. It deletes dead tuples _in_ the blocks and frees space only when at lea= st a certain amount of blocks at the end of the file with tuple data are em= pty.

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, May 14, 2023 at 5:03=E2=80=AFPM Wells Oliver = <wells.oliver@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 frequency. They store monthly da= ta and exist as partitions. As an example, July of 2022 (summer months are = peak) had 1,630,558,336 rows. We delete from this and insert daily.

You delete and reinsert= every row every day, or just some lesser number of rows each day?&nbs= p; In one giant transaction daily, or in a series of short mostly non-overl= apping 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 nearly 50GB, and the file size estimate on this partition was = ~200 GB.

Are these si= zes for the entire relation size, or just for the core table (not TOAS= T, not indexes)?  Index bloat is harder to prevent than table bloat, b= ut is easier to deal with after the fact (reindex concurrently)
&= nbsp;
These tables get auto-vacuumed but clearly it's= not, well, aggressively reclaiming space.

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

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 qu= ite frequently.

Since you rarely get new data once the month is over, = you could lock the partition against concurrent changes (but still allow re= ads) once it is no longer "on the run" while you copy the data into a = new tightly-packed table and index that and add constraints.  Then you= need only a very brief access exclusive lock while you detach/drop the old= partition and attach the new table in its place.  Of course you can't= drop the weaker lock while you acquire the stronger one lest the old table= change during the gap so lock management can be a bit tedious, but it is s= urely less restrictive than a VACUUM FULL.
Cheers,

Jeff
------=_Part_3880250_869097707.1684164024805--