0

I have a Postgres 9.6 RDS instance and it is growing 1GB a day. We have made some optimizations to the relation related to the pg_toast but the pg_toast size is not changing.

Autovacuum is on, but since autovacuum/VACUUM FREEZE do not reclaim space and VACUUM FULL does an exclusive lock, I am not sure anymore what the best approach is.

The data in the table is core to our user experience and although following this approach makes sense, it would take away the data our users expect to see during the vacuum full process.

What are the other options here to shrink the pg_toast?

Here is some data about table sizes. You can see in the first two images, that the relation scoring_responsescore is relation associated with the pg_toast.

enter image description here enter image description here

Autovacuum settings

enter image description here

Results from current running autovacuum process for that specific pg_toast. It might help. enter image description here

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
nael
  • 1,441
  • 19
  • 36

2 Answers2

2

VACUUM (FULL) is the only method PostgreSQL provides to reduce the size of a table.

Is the bloated TOAST table such a problem for you? TOAST tables are always accessed via the TOAST index, so the bloat shouldn't be a performance problem.

I know of two projects that provide table reorganization with only a short ACCESS EXCLUSIVE lock, namely pg_squeeze and pg_repack, but you probably won't be able to use those in an Amazon RDS database.

To keep the problem from getting worse, you should first try to raise autovacuum_vacuum_cost_limit to 2000 for the affected table, and if that doesn't do the trick, lower autovacuum_vacuum_cost_delay to 0. You can use ALTER TABLE to change the settings for a single table.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 2
    pg_repack works on RDS: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.pg_repack – Phil Frost Jan 22 '19 at 17:38
0

pg_repack still does not allow to reduce the size of TOAST Segments in RDS. And in RDS we cannot run pg_repack with superuser privileges, we have to use "--no-superuser-check" option. With this it will not be able to access the pg_toast.* tables.

Som
  • 1