agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feeddelete and pg_toast = shrink database?
5+ messages / 4 participants
[nested] [flat]
* delete and pg_toast = shrink database?
@ 2025-02-27 21:18 Edwin UY <edwin.uy@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Edwin UY @ 2025-02-27 21:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
DB is currently 500G. About 75% of this is pg_toast.
Had advised the application team to check for data purging as there are
some very old data.
Is it correct to assume that doing so should shrink the database size?
I believe I have to run vacuum full/table at some stage to really shrink it?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: delete and pg_toast = shrink database?
@ 2025-02-27 21:23 David G. Johnston <david.g.johnston@gmail.com>
parent: Edwin UY <edwin.uy@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2025-02-27 21:23 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Thu, Feb 27, 2025 at 2:19 PM Edwin UY <edwin.uy@gmail.com> wrote:
> Hi,
>
> DB is currently 500G. About 75% of this is pg_toast.
> Had advised the application team to check for data purging as there are
> some very old data.
> Is it correct to assume that doing so should shrink the database size?
> I believe I have to run vacuum full/table at some stage to really shrink
> it?
>
>
Yes, most likely to reclaim the space to the OS you will need to use vacuum
full or equivalent.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: delete and pg_toast = shrink database?
@ 2025-02-27 23:29 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Edwin UY <edwin.uy@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2025-02-27 23:29 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, Feb 27, 2025 at 4:19 PM Edwin UY <edwin.uy@gmail.com> wrote:
> Hi,
>
> DB is currently 500G. About 75% of this is pg_toast.
> Had advised the application team to check for data purging as there are
> some very old data.
>
I've been in that same situation. Had to write the purge job myself...
> Is it correct to assume that doing so should shrink the database size?
>
No. DELETE + VACUUM frees space in the data files. (This is good once you
get a regular purge process running, since the post-purge vacuum will free
up space for the next records. The tables' free space percentages will
then hover between relatively high and low ranges, unless insert volume is
growing.)
I believe I have to run vacuum full/table at some stage to really shrink it?
>
Also, no. But then yes.
VACUUM FULL (use pg_repack instead!)
1. *copies* the remaining data to a new (temporary) table and rebuilds the
indices,
2. deletes the old files,
3. renames the temporary table to the original name.
Thus, you'll temporarily need *more* disk space.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: delete and pg_toast = shrink database?
@ 2025-02-27 23:51 Jean-Paul POZZI <jp.pozzi@izzop.net>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Jean-Paul POZZI @ 2025-02-27 23:51 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello,
As a precaution, save the data to be purged so that you can possibly use it in the future.
Regards
JP P
De: "Ron Johnson" <ronljohnsonjr@gmail.com>
À: "Pgsql-admin" <pgsql-admin@lists.postgresql.org>
Envoyé: vendredi 28 février 2025 00:39
Objet: Re: delete and pg_toast = shrink database?
On Thu, Feb 27, 2025 at 4:19 PM Edwin UY <edwin.uy@gmail.com> wrote:
Hi,
DB is currently 500G. About 75% of this is pg_toast.
Had advised the application team to check for data purging as there are some very old data.
I've been in that same situation. Had to write the purge job myself...
Is it correct to assume that doing so should shrink the database size?
No. DELETE + VACUUM frees space in the data files. (This is good once you get a regular purge process running, since the post-purge vacuum will free up space for the next records. The tables' free space percentages will then hover between relatively high and low ranges, unless insert volume is growing.)
I believe I have to run vacuum full/table at some stage to really shrink it?
Also, no. But then yes.
VACUUM FULL (use pg_repack instead!)
1. copies the remaining data to a new (temporary) table and rebuilds the indices,
2. deletes the old files,
3. renames the temporary table to the original name.
Thus, you'll temporarily need more disk space.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: delete and pg_toast = shrink database?
@ 2025-02-28 00:23 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Jean-Paul POZZI <jp.pozzi@izzop.net>
0 siblings, 0 replies; 5+ messages in thread
From: Ron Johnson @ 2025-02-28 00:23 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Sometimes yes, and sometimes no.
On Thu, Feb 27, 2025 at 6:51 PM Jean-Paul POZZI <jp.pozzi@izzop.net> wrote:
> Hello,
>
>
>
> As a precaution, save the data to be purged so that you can possibly use
> it in the future.
>
>
>
>
>
> Regards
>
> JP P
>
>
>
> ------------------------------
> *De:* "Ron Johnson" <ronljohnsonjr@gmail.com>
> *À:* "Pgsql-admin" <pgsql-admin@lists.postgresql.org>
> *Envoyé:* vendredi 28 février 2025 00:39
> *Objet:* Re: delete and pg_toast = shrink database?
>
> On Thu, Feb 27, 2025 at 4:19 PM Edwin UY <edwin.uy@gmail.com> wrote:
>
>> Hi,
>>
>> DB is currently 500G. About 75% of this is pg_toast.
>> Had advised the application team to check for data purging as there are
>> some very old data.
>>
>
> I've been in that same situation. Had to write the purge job myself...
>
>
>> Is it correct to assume that doing so should shrink the database size?
>>
>
> No. DELETE + VACUUM frees space in the data files. (This is good once you
> get a regular purge process running, since the post-purge vacuum will free
> up space for the next records. The tables' free space percentages will
> then hover between relatively high and low ranges, unless insert volume is
> growing.)
>
>
>> I believe I have to run vacuum full/table at some stage to really shrink
>> it?
>>
>
> Also, no. But then yes.
>
> VACUUM FULL (use pg_repack instead!)
> 1. *copies* the remaining data to a new (temporary) table and rebuilds
> the indices,
> 2. deletes the old files,
> 3. renames the temporary table to the original name.
>
> Thus, you'll temporarily need *more* disk space.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-02-28 00:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-27 21:18 delete and pg_toast = shrink database? Edwin UY <edwin.uy@gmail.com>
2025-02-27 21:23 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-27 23:29 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-02-27 23:51 ` Jean-Paul POZZI <jp.pozzi@izzop.net>
2025-02-28 00:23 ` Ron Johnson <ronljohnsonjr@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox