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]https://stackoverflow.com/questions/53624270/shrinking-pg-toast-on-rds-instance
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
https://stackoverflow.com/questions/53624270/shrinking-pg-toast-on-rds-instance