Handling pg_toast tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Handling pg_toast tables

satish v-2
Hi,

My table size is 15 MB but toast table size 92GB,i performed Vacuum and vacuum full,but pg_toast table doesn't shrink.how can we shrink the pg_toast table.

Thanks,
Satish.

Reply | Threaded
Open this post in threaded view
|

Re: Handling pg_toast tables

Laurenz Albe
On Thu, 2021-02-25 at 17:10 +0530, satish v wrote:
> My table size is 15 MB but toast table size 92GB,i performed Vacuum and
>  vacuum full,but pg_toast table doesn't shrink.how can we shrink the pg_toast table.

Delete soe rows from the table?  I guess there are some large attributes.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: Handling pg_toast tables

Keith Fiske-2
In reply to this post by satish v-2


On Thu, Feb 25, 2021 at 6:43 AM satish v <[hidden email]> wrote:
Hi,

My table size is 15 MB but toast table size 92GB,i performed Vacuum and vacuum full,but pg_toast table doesn't shrink.how can we shrink the pg_toast table.

Thanks,
Satish.


Running a VACUUM FULL on the base table will clean up any bloat on the toast table. Are you sure there's bloat there? Otherwise that's just the size of the data in the table.

Can use this tool to check - https://github.com/keithf4/pg_bloat_check

It takes into account toast tables, which pgstattuple on its own does not do by default.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
Reply | Threaded
Open this post in threaded view
|

Re: Handling pg_toast tables

Ron-2
In reply to this post by satish v-2
On 2/25/21 5:40 AM, satish v wrote:
> Hi,
>
> My table size is 15 MB but toast table size 92GB,i performed Vacuum and
> vacuum full,but pg_toast table doesn't shrink.how can we shrink the
> pg_toast table.

If your database has many LARGE columns (bytea, xml, json or even long text
values) then... that's how big the tables really are.

--
Angular momentum makes the world go 'round.