MIME-Version: 1.0
References: <0d11ad5e-5903-49ce-8fff-4650919f4b44@app.fastmail.com>
In-Reply-To: <0d11ad5e-5903-49ce-8fff-4650919f4b44@app.fastmail.com>
From: Guillaume Lelarge <guillaume@lelarge.info>
Date: Tue, 3 Dec 2024 07:13:20 +0100
Message-ID: <CAECtzeVZ1C_CmK_AxMV1B5VdhKRoH3DV7zu9Omr0yCijHQXa+g@mail.gmail.com>
Subject: Re: VACUUM FULL, power failure results in unrecoverable space
To: Pierre Barre <pierre@barre.sh>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000077ac4b0628579031"
Archived-At: <https://www.postgresql.org/message-id/CAECtzeVZ1C_CmK_AxMV1B5VdhKRoH3DV7zu9Omr0yCijHQXa%2Bg%40mail.gmail.com>
Precedence: bulk

--00000000000077ac4b0628579031
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi,


Le mar. 3 d=C3=A9c. 2024, 01:02, Pierre Barre <pierre@barre.sh> a =C3=A9cri=
t :

> Hello,
>
> I encountered an issue while attempting to reclaim space from a heavily
> bloated table:
>
> Initial analysis using
> https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_=
bloat.sql indicated
> approximately 600GB of bloat in the table.
>
> I initiated a VACUUM FULL operation to reclaim this space, but during
> execution, the server experienced a power failure.
>
>
> After server recovery:
>
> 1. The database came back online successfully
> 2. The space used by the in-progress table reconstruction was not freed
>

Because doesn't know it's here.

3. The newly wasted space (bloat + partial VACUUM FULL) is not shown using
> the above query, only the initial bloat.
>

Because it's not the same kind of bloat. Vacuum full builds another set of
files for the table, and these files aren't yet connected to the table,
since vacuum full didn't finish. You've got some orphaned files you'll have
to delete.


>
> Is there a way to reclaim this space without resorting to a full pg_dump
> and pg_restore cycle?
>

You can dump and restore (which will drop all bloat of this database), but
will probably take too long to finish. You can also see pg_orphaned to know
which files to delete (https://github.com/bdrouvot/pg_orphaned).

Regards.


--=20
Guillaume.

--00000000000077ac4b0628579031
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto"><div><div>Hi,</div><br><br><div class=3D"gmail_quote"><di=
v dir=3D"ltr" class=3D"gmail_attr">Le mar. 3 d=C3=A9c. 2024, 01:02, Pierre =
Barre &lt;<a href=3D"mailto:pierre@barre.sh">pierre@barre.sh</a>&gt; a =C3=
=A9crit=C2=A0:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0=
 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><u></u><div><div>Hel=
lo,<br></div><div><br></div><div>I encountered an issue while attempting to=
 reclaim space from a heavily bloated table:<br style=3D"max-width:100%;hei=
ght:auto"></div><div><br style=3D"max-width:100%;height:auto"></div><div>In=
itial analysis using=C2=A0<a href=3D"https://github.com/ioguix/pgsql-bloat-=
estimation/blob/master/table/table_bloat.sql" target=3D"_blank" rel=3D"nore=
ferrer">https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/=
table_bloat.sql</a>=C2=A0indicated approximately 600GB of bloat in the tabl=
e.<br></div><div><br></div><div>I initiated a VACUUM FULL operation to recl=
aim this space, but during execution, the server experienced a power failur=
e.<br></div><div><br></div><div><br style=3D"max-width:100%;height:auto"></=
div><div>After server recovery:<br style=3D"max-width:100%;height:auto"></d=
iv><div><br style=3D"max-width:100%;height:auto"></div><div>1. The database=
 came back online successfully<br style=3D"max-width:100%;height:auto"></di=
v><div>2. The space used by the in-progress table reconstruction was not fr=
eed<br style=3D"max-width:100%;height:auto"></div></div></blockquote></div>=
</div><div dir=3D"auto"><br></div><div dir=3D"auto">Because doesn&#39;t kno=
w it&#39;s here.</div><div dir=3D"auto"><br></div><div dir=3D"auto"><div cl=
ass=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0=
 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div></div><div>3. =
The newly wasted space (bloat + partial VACUUM FULL) is not shown using the=
 above query, only the initial bloat.<br style=3D"max-width:100%;height:aut=
o"></div><div></div></div></blockquote></div></div><div dir=3D"auto"><br></=
div><div dir=3D"auto">Because it&#39;s not the same kind of bloat. Vacuum f=
ull builds another set of files for the table, and these files aren&#39;t y=
et connected to the table, since vacuum full didn&#39;t finish. You&#39;ve =
got some orphaned files you&#39;ll have to delete.=C2=A0</div><div dir=3D"a=
uto"><br></div><div dir=3D"auto"><div class=3D"gmail_quote"><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;pa=
dding-left:1ex"><div><div><br style=3D"max-width:100%;height:auto"></div><d=
iv><br style=3D"max-width:100%;height:auto"></div><div>Is there a way to re=
claim this space without resorting to a full pg_dump and pg_restore cycle?=
=C2=A0<br></div><div></div></div></blockquote></div></div><div dir=3D"auto"=
><br></div><div dir=3D"auto">You can dump and restore (which will drop all =
bloat of this database), but will probably take too long to finish. You can=
 also see pg_orphaned to know which files to delete (<a href=3D"https://git=
hub.com/bdrouvot/pg_orphaned">https://github.com/bdrouvot/pg_orphaned</a>).=
</div><div dir=3D"auto"><br></div><div dir=3D"auto">Regards.</div><div dir=
=3D"auto"><br></div><div dir=3D"auto"><br></div><div dir=3D"auto">--=C2=A0<=
/div><div dir=3D"auto">Guillaume.</div></div>

--00000000000077ac4b0628579031--