Thread: Toast Tables, pg_repack, and AWS RDS
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.