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
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 |