[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: Re: Options for more aggressive space reclamation in vacuuming?
From: Wolfgang Wilhelm <wolfgang20121964 () yahoo ! de>
Date: 2023-05-16 19:32:33
Message-ID: 1286333072.4954427.1684265553802 () mail ! yahoo ! com
[Download RAW message or body]
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" partition with detaching 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 <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 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. \
RegardsWW 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
--
Wells Oliver
wells.oliver@gmail.com
[Attachment #3 (text/html)]
<html><head></head><body><div class="ydpbb8f804cyahoo-style-wrap" \
style="font-family:Helvetica Neue, Helvetica, Arial, \
sans-serif;font-size:16px;"><div></div> <div>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" \
partition with detaching the sparse and attaching the dense one. From the space point \
of view a vacuum full is about the same.</div><div><br></div>
<div id="ydpbb8f804cyahoo_quoted_5067187650" class="ydpbb8f804cyahoo_quoted">
<div style="font-family:'Helvetica Neue', Helvetica, Arial, \
sans-serif;font-size:13px;color:#26282a;">
<div>
Am Montag, 15. Mai 2023 um 18:26:46 MESZ hat Wells Oliver \
<wells.oliver@gmail.com> Folgendes geschrieben: </div>
<div><br></div>
<div><br></div>
<div><div id="ydpbb8f804cyiv8613544834"><div><div dir="ltr">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? </div><br clear="none"><div id="ydpbb8f804cyiv8613544834yqt93897" \
class="ydpbb8f804cyiv8613544834yqt4647293549"><div \
class="ydpbb8f804cyiv8613544834gmail_quote"><div dir="ltr" \
class="ydpbb8f804cyiv8613544834gmail_attr">On Mon, May 15, 2023 at 8:21 AM Wolfgang \
Wilhelm <<a shape="rect" href="mailto:wolfgang20121964@yahoo.de" rel="nofollow" \
target="_blank">wolfgang20121964@yahoo.de</a>> wrote:<br \
clear="none"></div><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex;" \
class="ydpbb8f804cyiv8613544834gmail_quote"><div><div style="font-family:Helvetica, \
Arial, sans-serif;font-size:16px;"><div></div> <div>Hi,</div><div><br \
clear="none"></div><div>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.</div><div><br \
clear="none"></div><div>Regards</div><div>WW</div><div><br clear="none"></div>
<div id="ydpbb8f804cyiv8613544834m_17118016143311386ydpf9a5455ayahoo_quoted_5110662226">
<div style="font-family:Helvetica, Arial, \
sans-serif;font-size:13px;color:rgb(38,40,42);">
<div>
Am Montag, 15. Mai 2023 um 16:40:47 MESZ hat Jeff Janes <<a \
shape="rect" href="mailto:jeff.janes@gmail.com" rel="nofollow" \
target="_blank">jeff.janes@gmail.com</a>> Folgendes geschrieben: </div>
<div><br clear="none"></div>
<div><br clear="none"></div>
<div><div \
id="ydpbb8f804cyiv8613544834m_17118016143311386ydpf9a5455ayiv4228990930"><div><div \
dir="ltr"><div dir="ltr">On Sun, May 14, 2023 at 5:03 PM Wells Oliver <<a \
shape="rect" href="mailto:wells.oliver@gmail.com" rel="nofollow" \
target="_blank">wells.oliver@gmail.com</a>> wrote:<br \
clear="none"></div><div><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px \
solid rgb(204,204,204);padding-left:1ex;"><div dir="ltr">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. </div></blockquote><div><br clear="none"></div><div>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?</div><div> </div><blockquote style="margin:0px \
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"><div \
dir="ltr">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. \
</div></blockquote><div><br clear="none"></div><div>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)</div><div> </div><blockquote style="margin:0px 0px \
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"><div \
dir="ltr">These tables get auto-vacuumed but clearly it's not, well, \
aggressively reclaiming space.</div></blockquote><div><br \
clear="none"></div><div>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.<br clear="none"></div><div> </div><blockquote style="margin:0px \
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"><div \
dir="ltr"><div><br clear="none"></div><div>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.<br \
clear="none"></div></div></blockquote><div><br clear="none"></div><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 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.</div><div \
id="ydpbb8f804cyiv8613544834m_17118016143311386ydpf9a5455ayiv4228990930yqtfd71590"><div><br \
clear="none"></div><div>Cheers,</div><div><br \
clear="none"></div><div>Jeff</div><blockquote style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"><div dir="ltr"><div \
dir="ltr"></div></div> </blockquote></div></div></div><div \
id="ydpbb8f804cyiv8613544834m_17118016143311386ydpf9a5455ayiv4228990930yqtfd43712"> \
</div></div></div></div> </div>
</div></div></div></blockquote></div></div><br clear="all"><div><br \
clear="none"></div><span class="ydpbb8f804cyiv8613544834gmail_signature_prefix">-- \
</span><br clear="none"><div dir="ltr" \
class="ydpbb8f804cyiv8613544834gmail_signature"><div dir="ltr"><div>Wells Oliver<br \
clear="none"><a shape="rect" href="mailto:wellsoliver@gmail.com" rel="nofollow" \
target="_blank">wells.oliver@gmail.com</a></div></div></div> </div></div></div>
</div>
</div></div></body></html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic