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 1pyKLF-0005Zp-NB for pgsql-admin@arkaria.postgresql.org; Sun, 14 May 2023 22:37:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pyKLE-0004tt-Lk for pgsql-admin@arkaria.postgresql.org; Sun, 14 May 2023 22:37:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pyKLE-0004tk-AE for pgsql-admin@lists.postgresql.org; Sun, 14 May 2023 22:37:24 +0000 Received: from mail-yw1-x1134.google.com ([2607:f8b0:4864:20::1134]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pyKLB-002lFT-0f for pgsql-admin@lists.postgresql.org; Sun, 14 May 2023 22:37:23 +0000 Received: by mail-yw1-x1134.google.com with SMTP id 00721157ae682-55b7630a736so178680097b3.1 for ; Sun, 14 May 2023 15:37:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1684103838; x=1686695838; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=6hsHcl2uRdWHorOQGswkQqLDxgJ0wvpLV4hjFhhK3aM=; b=KbyGQT0AqQHfs8hjydGlw6D0YiCsIqjrTFAuUTEUiu1qdi97O0qsslJPzdFuq0IP30 Ff4oulPYyHqMaXtvfRzyx9HxUTAG2SQi2L5y7udC7df+WyirVIzrlkTfjJvNQJPgHVqp m6aPYH03ZnKWAkA+p6Sw6wYLC2wNBla6EslhsRSJrk6h1aUJd+y/o0PchuvJZVm0QF12 Wdorhx6MjqJKu6b2+JpxBhwGa2/CcUseUkjYOaZeAJvUrRu3Kknujdi5gu6/BHmCETif VChkJIBUBAfbP/knFc+f9MzunJ/rsHaWwSPCgVQAcPjqmVg6zob6Phi0XCoYyV6WnRqM u/zg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1684103838; x=1686695838; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=6hsHcl2uRdWHorOQGswkQqLDxgJ0wvpLV4hjFhhK3aM=; b=ZnuGJwOuhkjhHCmrGwa3ssJU2Z3e3VtZxHrG4H3y9Tm2V3opbe4q/Al6MfI0gMoGJI HZ0YPppDLvcQXZ4IQNfVKrrXJySVkkoYlx/tqb/Wrk3X0OF9/kZTQsYDEnH1wzfTjMPZ pZ2poDgBWFokOFWMu5Ho/1MJc7kyFgWYD64l40hbLC1w4dYw9X9kBpIobw0zlhYILyQQ z2dLeCA2mxdXmoW2CHemLF+7RxSL5P8xKcOLKAObsYy2XNGQGaP4RGjLNXu4T60hIEjX tL5mio4cAEzfbeD96paiFoIYXmdZUobVkTJwoXyHB8IU9jJZf0R0N/Rw/iw7RcjkxGAe 9Kcg== X-Gm-Message-State: AC+VfDxj0tM76z7neXYltHVapOYBUt142eP1Sy5vVw+hj18EVYhlcJQx APFEA2uOcUC3AxjoWlQQj4VUmJDCVz4= X-Google-Smtp-Source: ACHHUZ7g0rx9FQLVC+tt1YrslKSHucn+0+RaYfKlPI8rafl9aXE7Fdag7w9y/8uR2uJhvnvzu2yfoA== X-Received: by 2002:a81:a511:0:b0:556:c778:9d60 with SMTP id u17-20020a81a511000000b00556c7789d60mr29541728ywg.43.1684103838143; Sun, 14 May 2023 15:37:18 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:80f6:4741:9863:ef90? ([2600:1700:bd2c:b810:80f6:4741:9863:ef90]) by smtp.googlemail.com with ESMTPSA id m67-20020a817146000000b0055a8b115f6esm7322418ywc.128.2023.05.14.15.37.17 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 14 May 2023 15:37:17 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------H26zSeaadJWaOg9yftPnYdYe" Message-ID: <766eee62-9095-2b12-8ec3-b7037a59f006@gmail.com> Date: Sun, 14 May 2023 17:37:17 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: Options for more aggressive space reclamation in vacuuming? Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------H26zSeaadJWaOg9yftPnYdYe Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Nope.  Bog-standard VACUUM.  It marks space free, and Postgresql uses it for new records. On 5/14/23 17:26, Wells Oliver wrote: > Just to be clear, you regularly vacuum full, i.e. locking full-on > hard-core all-out vacuum? > > On Sun, May 14, 2023 at 3:25 PM Ron wrote: > > We regularly vacuum, and *definitely* vacuum after an archive-delete. > > On 5/14/23 16:02, Wells Oliver 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 data 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. 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. These tables get >> auto-vacuumed but clearly it's not, well, aggressively reclaiming space. >> >> 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. >> >> Glad to add any detail I can. >> >> On Sun, May 14, 2023 at 11:32 AM Jeff Janes wrote: >> >> On Sun, May 14, 2023 at 1:46 PM Wells Oliver >> wrote: >> >> Noticed over the last few days how much more space VACUUM >> FULL reclaims (for obvious reasons) and I am wondering if >> there are any solutions for a more fruitful VACUUM without >> the locks of FULL? >> >> >> There are lots of theories which are all compatible with the >> minimal amount of info given here.  If the space is freed from >> the indexes, then just reindexing (which can be done concurrently >> in recent versions) could reclaim the space. If it is from the >> table itself then something like pg_squeeze >> (https://github.com/cybertec-postgresql/pg_squeeze/) might help. >> Or you can implement your own squeezing, by using a CTE with a >> ctid scan to delete tuples from the end of the table and reinsert >> them in the front, then use regular vacuum to truncate the >> now-free space off the end of the table. >> >> I would think the more fruitful VACUUM would be to prevent the >> bloat from occuring in the first place but without knowing the >> root cause I can't offer anything but platitudes there. >> >> Cheers, >> >> Jeff >> >> >> >> -- >> Wells Oliver >> wells.oliver@gmail.com > > -- > Born in Arizona, moved to Babylonia. > > > > -- > Wells Oliver > wells.oliver@gmail.com -- Born in Arizona, moved to Babylonia. --------------H26zSeaadJWaOg9yftPnYdYe Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Nope.  Bog-standard VACUUM.  It marks space free, and Postgresql uses it for new records.

On 5/14/23 17:26, Wells Oliver wrote:
Just to be clear, you regularly vacuum full, i.e. locking full-on hard-core all-out vacuum?

On Sun, May 14, 2023 at 3:25 PM Ron <ronljohnsonjr@gmail.com> wrote:
We regularly vacuum, and definitely vacuum after an archive-delete.

On 5/14/23 16:02, Wells Oliver 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 data 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. 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. These tables get auto-vacuumed but clearly it's not, well, aggressively reclaiming space.

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.

Glad to add any detail I can.

On Sun, May 14, 2023 at 11:32 AM Jeff Janes <jeff.janes@gmail.com> wrote:
On Sun, May 14, 2023 at 1:46 PM Wells Oliver <wells.oliver@gmail.com> wrote:
Noticed over the last few days how much more space VACUUM FULL reclaims (for obvious reasons) and I am wondering if there are any solutions for a more fruitful VACUUM without the locks of FULL?

There are lots of theories which are all compatible with the minimal amount of info given here.  If the space is freed from the indexes, then just reindexing (which can be done concurrently in recent versions) could reclaim the space. If it is from the table itself then something like pg_squeeze (https://github.com/cybertec-postgresql/pg_squeeze/) might help. Or you can implement your own squeezing, by using a CTE with a ctid scan to delete tuples from the end of the table and reinsert them in the front, then use regular vacuum to truncate the now-free space off the end of the table.

I would think the more fruitful VACUUM would be to prevent the bloat from occuring in the first place but without knowing the root cause I can't offer anything but platitudes there.

Cheers,

Jeff


--

--
Born in Arizona, moved to Babylonia.


--

--
Born in Arizona, moved to Babylonia.
--------------H26zSeaadJWaOg9yftPnYdYe--