From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Kristjan Mustkivi <sonicmonkey(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: TOAST table size in bytes growing despite working autovacuum |
Date: | 2020-06-15 13:37:41 |
Message-ID: | 78fa342abe358a96601c675729dd1eff0fe5481d.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, 2020-06-15 at 13:47 +0300, Kristjan Mustkivi wrote:
> Still, pgstattuple reveals that the table size is 715MB while live
> tuple len is just 39MB and 94% of the table is vacant. I do not have
> much experience in interpreting this but it would seem that it is
> still getting bloated. Should the autovacuum be made even more
> aggressive? E.g toast.autovacuum_vacuum_scale_factor=0.01 instead of
> 0.05 and tweaked further when necessary until the size stabilizes
> (more precisely pgstattuple will reflect the bloat to be under
> control):
>
> SELECT * FROM pgstattuple('pg_toast.pg_toast_293406');
> ─[ RECORD 1 ]──────┬──────────
> table_len │ 715776000
> tuple_count │ 25545
> tuple_len │ 39241366
> tuple_percent │ 5.48
> dead_tuple_count │ 1116
> dead_tuple_len │ 1930508
> dead_tuple_percent │ 0.27
> free_space │ 669701052
> free_percent │ 93.56
Indeed, the table is almost entirely air.
You should schedule down time and run a VACUUM (FULL) on that table.
That will rewrite the table and get rid of the bloat.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
From | Date | Subject | |
---|---|---|---|
Next Message | Kristjan Mustkivi | 2020-06-15 13:42:44 | Re: TOAST table size in bytes growing despite working autovacuum |
Previous Message | Laurenz Albe | 2020-06-15 13:35:33 | Re: SV: pg_service.conf and client support |