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 1pybcE-0002J5-QB for pgsql-admin@arkaria.postgresql.org; Mon, 15 May 2023 17:04:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pybcD-0008HO-FK for pgsql-admin@arkaria.postgresql.org; Mon, 15 May 2023 17:04:05 +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 1pybcD-0008HF-2i for pgsql-admin@lists.postgresql.org; Mon, 15 May 2023 17:04:05 +0000 Received: from mail-yb1-xb29.google.com ([2607:f8b0:4864:20::b29]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pybcA-002uk7-EK for pgsql-admin@lists.postgresql.org; Mon, 15 May 2023 17:04:04 +0000 Received: by mail-yb1-xb29.google.com with SMTP id 3f1490d57ef6-b9d8b458e10so17093038276.1 for ; Mon, 15 May 2023 10:04:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1684170240; x=1686762240; 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=AEmzuIuWfArnkQPfdlyPQ4b1JKlFCzOPDC83oseZohU=; b=M96U+CUAydGaMCIwCcJF4qFfA90RdKzdkjZd6IDHnUso3JmSQ7HCKwkrBLlPZlOfXM hIDkcV401DxFX8cHQ6SxSR5Pm0ER8PKIu8jiHqszf4RFe2tQOcn6pc87+rLyTg7LUWtO GafCXZu9dadfT4DjDDxe7K1eI5QVqJ9XbKupj2pBg3/fCdGHcf3h9bZbj9qVZMhJ1heL g1D7ZdH/EIeKyVTPqQAwot/MwG0mWE206O+XS1qlT3ZpB2fjghPZdMKAQg8JJvUggP4m gWKmCLMq+0PnsB7qpMmQQO4hDU+b6XEXa+D/2VPDXNABnD8f4dz3hjN3zT3RzRdd4bs0 mfRw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1684170240; x=1686762240; 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=AEmzuIuWfArnkQPfdlyPQ4b1JKlFCzOPDC83oseZohU=; b=Y6DsTek5PkE9h4uMPei4BYhBVssnrZKdnoN1qmQqwbwdnL2T/YiTa0jrStfMyDM9oy FL23qeOFLnjekLb1sYrvOKM5M0xfqK2GudKnMYYVqsc7XAPLE+KMR3y62OM4aIPZ6bmO e3JLTO5vIzAivXaOXcgSiUMnDrxI9CZBc975m9MICmW4I78kFawbJ0QPF3MfI7kxLvo9 0giDYfAtjxstJUvHXBNkwMKyIXRn41YlgF/2bOU/Z28fVBrgPV2ed+/ojDH/w6GTuZcQ 0F8h8KKV/wiZYtyqB1nnp+3ULpBGh2MC/L0dPnGUUngMT9TMGUKacEgojSSsXNCf7U9T 2ZoQ== X-Gm-Message-State: AC+VfDzV6x99/AmNaGhs/9LywiB47FJR55dg+T7qZVT0Xy95NL1L+TjR Nd9E2DOqCdbMaRomFqw9wFXEgBFPuUk= X-Google-Smtp-Source: ACHHUZ5xdJpDJXtEs8IiKcxQ14YTdOOkHkTcQIvOQBnp07zegPppv2CpFE3adBQOqtzdSmwT+rBxJQ== X-Received: by 2002:a25:1e8b:0:b0:b8e:cb88:1b69 with SMTP id e133-20020a251e8b000000b00b8ecb881b69mr30427586ybe.34.1684170240585; Mon, 15 May 2023 10:04:00 -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 d2-20020a5b0c42000000b00b7b0aba5cccsm70750ybr.22.2023.05.15.10.03.59 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 15 May 2023 10:03:59 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------04Ls5e0Lx7jZj4LCo0sxAyfd" Message-ID: <587adf00-67c5-c2fe-4fe7-610e8eda6484@gmail.com> Date: Mon, 15 May 2023 12:03:59 -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: <2042595996.3880251.1684164024807@mail.yahoo.com> 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. --------------04Ls5e0Lx7jZj4LCo0sxAyfd Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit (A billion row table really should be partitioned.  That ship sailed long ago, of course...) Sure you can "manually" adjust it.  cron is your friend!! But... how long does it take to vacuum a freshly VACUUM FULLed table?  Because I'm wondering if it would be more efficient to proactively keep the problem at bay by, for example, doing a plain VACUUM every... 2, 3, 4, 6 (take your pick) hours, along with a plain VACUUM right after the monthly purge. On 5/15/23 11:26, Wells Oliver wrote: > 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 > the month where it's reached its general capacity, e.g. maybe 1bn rows, > but at the beginning of the month is too aggressive and would cause too > frequent vacuuming given the significantly lower volume of records? Simple > manual adjustment through a month, or something easier? > > On Mon, May 15, 2023 at 8:21 AM Wolfgang Wilhelm > wrote: > > Hi, > > what do you mean by "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 only when at least a certain > amount of blocks at the end of the file with tuple data are empty. > > Regards > WW > > Am Montag, 15. Mai 2023 um 16:40:47 MESZ hat Jeff Janes > Folgendes geschrieben: > > > On Sun, May 14, 2023 at 5:03 PM 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. > > > 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 > series 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 nearly 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)?  Index bloat is harder to prevent than > table bloat, but is easier to deal with after the fact (reindex > concurrently) > > 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. > > > 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 partition against concurrent changes (but still allow reads) 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 surely less restrictive than a VACUUM FULL. > > Cheers, > > Jeff > > > > -- > Wells Oliver > wells.oliver@gmail.com -- Born in Arizona, moved to Babylonia. --------------04Ls5e0Lx7jZj4LCo0sxAyfd Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit (A billion row table really should be partitioned.  That ship sailed long ago, of course...)

Sure you can "manually" adjust it.  cron is your friend!!

But... how long does it take to vacuum a freshly VACUUM FULLed table?  Because I'm wondering if it would be more efficient to proactively keep the problem at bay by, for example, doing a plain VACUUM every... 2, 3, 4, 6 (take your pick) hours, along with a plain VACUUM right after the monthly purge.

On 5/15/23 11:26, Wells Oliver wrote:
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 the month where it's reached its general capacity, e.g. maybe 1bn rows, but at the beginning of the month is too aggressive and would cause too frequent vacuuming given the significantly lower volume of records? Simple manual adjustment through a month, or something easier? 

On Mon, May 15, 2023 at 8:21 AM Wolfgang Wilhelm <wolfgang20121964@yahoo.de> wrote:
Hi,

what do you mean by "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 only when at least a certain amount of blocks at the end of the file with tuple data are empty.

Regards
WW

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 PM 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 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.

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 series 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 nearly 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)?  Index bloat is harder to prevent than table bloat, but is easier to deal with after the fact (reindex concurrently)
 
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.
 

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 partition against concurrent changes (but still allow reads) 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 surely less restrictive than a VACUUM FULL.

Cheers,

Jeff


--

--
Born in Arizona, moved to Babylonia.
--------------04Ls5e0Lx7jZj4LCo0sxAyfd--