pgbadger VS pg_wait_sampling

It might also help to use pgbadger or something similar to process your postgres logs and see whether some event is aligned with your outages.

Interestingly, when a place does get to the point where the single instance has capacity issues (after upgrading to EPYC and lots of flash drives) then other non-obvious stuff shows up too.

For example, at one place just over a year ago they were well into this territory. One of weird problems for them was with pgBadger's memory usage (https://github.com/darold/pgbadger). That's written in perl, which doesn't seem to go garbage collection well. So even on a reporting node with a few hundred GB's of ram, it could take more than 24 hours to do a "monthly" reporting run.

There wasn't a solution in place at the time I left, so they're probably still having the issue... ;)

First of all I used a profiler (pgbadger and netdata) to figure out where the lags were coming from. I then tried the usual stuff (increasing shared_buffers, max_wal_size, min_wal_size from their ultra low defaults), but the biggest performance gain came from moving the database from eMMC to a mechanical hard drive :-D

Look into PgBadger (a log parser/analyser): https://github.com/darold/pgbadger

pg_wait_sampling together with pg_stat_statements gets you nearer to Oracle's ASH/AWR capabilities. PoWA can integrate that (and other interesting extensions) to generate some nice reports.

Another Another source of high CPU could be wait events. There are no built-in tools in Postgres to monitor them (unless RDS provides some). The approach I'd take on a "regular" Postgres installation is to sample the content of pg_stat_activity and then later analyze that after spikes have occurred. There are several extensions that already provide this, e.g. pg_profile or pg_wait_sampling or pgsentinel

pg_wait_sampling