0

I have a postgresql Master-Slave replication scheme and because of query cancelation due to conflict with wal I need to set the following setting to each replica:

hot_standby_feedback = on

But because this can cause the tables on my master server to have overly large amounts of data that are about to be deleted but not deleted yet, I need to be able to monitor the table size in order to force manually an autovacumm policy on mergency situations (storage starvation or overprice because of storage size).

Therefore, how do I monitor the table size on my database? Also, how I can enforce the AUTOVACUMM in case of my tables having way too much size.

1

force manually an autovacumm policy on mergency situations

This is not going to work. hot_standby_feedback doesn't prevent autovacuum from running. It lets it run but prevents it from removing rows that are not "dead enough". No amount of vacuuming is going to help if you don't get the long running queries on the replicas to go away first.

You can use pg_total_relation_size to get the table size. You can take a query like the below, and just plug it into our favorite monitoring solution.

select pg_total_relation_size('pgbench_accounts');

But that still leaves the question of what to do about it.

  • the pgbench_accounts in your example is a table name? – Dimitrios Desyllas Jan 21 at 8:52
  • 1
    Yes, a table name. – jjanes Jan 21 at 13:28
  • Is that applicable on materialized views as well? – Dimitrios Desyllas Jan 28 at 15:49
  • 1
    @DimitriosDesyllas yes. – jjanes Jan 28 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.