Re: how to slow down parts of Pg

On Tue, 2020-04-21 at 20:30 +0000, Kevin Brannen wrote:
> I have an unusual need:  I need Pg to slow down. I know, we all want our DB to go faster,
> but in this case it's speed is working against me in 1 area.
> 
> We have systems that are geo-redundant for HA, with the redundancy being handled by DRBD to keep the disks in sync,
> which it does at the block level. For normal operations, it actually works out fairly well. [...]
> The part that hurts so bad is when we do maintenance operations that are DB heavy, like deleting really old records out of
> archives (weekly), moving older records from current tables to archive tables plus an analyze (every night),
> running pg_backup (every night), other archiving (weekly), and vacuum full to remove bloat (once a quarter).
> All of this generates a lot of disk writes, to state the obvious.
> 
> The local server can handle it all just fine, but the network can't handle it as it tries to sync to the other server.

The obvious and best answer is: get a faster network, or choose a different
storage solution.

Other than that, you can try to make the maintainance operations less
resource intense:

- partition the tables so that you can get rid of old data with DROP TABLE.
  The ANALYZE won't hurt, if you treat only the required tables.
- use "pg_basebackup" with the "--max-rate" option

About VACUUM, you may have a problem.  Avoid the need for VACUUM (FULL) at any price.
That usually requires tuning autovacuum to be faster, which means using more I/O.

If you cannot find a sweet spot there, you have no alternative but getting better I/O
(which, as I said in the beginning, would be the correct solution anyway).

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com