Toast Tables, pg_repack, and AWS RDS

From: Preston Hagar <prestonh(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org General" <pgsql-general(at)postgresql(dot)org>
Subject: Toast Tables, pg_repack, and AWS RDS
Date: 2022-11-15 19:55:42
Message-ID: CAK6zN=0c6cqYpTQgy4HvET1F+40th_NmBRznt2xnUCh7h=W6Cw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I'm working with a PG 11.13 database in RDS. We've been using pg_repack to
reclaim space/reduce bloat successfully. I have a question regarding toast
tables though:

We have few really large tables (TB+) where probably 80-90% of the space is
taken up by the toast table. Because of limitations of RDS, I can't use
pgstattuple to get toast table stats (permission denied to the pg_toast
schema). I also found an off-hand comment on stackoverflow that says
"pg_repace still does not allow to reduce the size of TOAST Segments in
RDS" [1]

Here are my questions:

* Is that stack overflow indicating you can't pg_repack a toast table
directly, or if I run pg_repack against my "regular" table, will it not
reclaim space from/recreate the toast table and instead just keep the
existing one?

* If the toast tables will get repacked/space reclaimed, does anyone know
of a way to get an estimate of how much bloat is in the toast tables in
RDS?

We've done a lot of work in our application and the DB directly to delete
old records that would fill up the toast tables and stop new ones from
being generated, so I think if we could repack the table, we'd get a bunch
of space back, but the problem is I don't currently have 2x the table size
of free space available, only about 1.2 to 1.5. I think we should be under
that, but if I'm wrong, AWS will auto-expand our RDS, which you can't
shrink and we'll be stuck with even higher storage costs.

Thanks in advance.

[1]
https://stackoverflow.com/questions/53624270/shrinking-pg-toast-on-rds-instance

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Munro 2022-11-15 20:55:17 Re: PANIC: could not flush dirty data: Cannot allocate memory
Previous Message Frank Cazabon 2022-11-15 19:10:47 Re: Calling function from VFP changes character field to Memo