pgbadger

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 do 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 to analyse PG usage for the time period. There wasn't a solution in place at the time I left, so they're probably still having the issue... ;)

Before we jumped on all kinds of SaaS offerings we used pgbadger. https://github.com/darold/pgbadger

Have you checked if you are using the correct settings in order to extract the max from your hardware? https://pgtune.leopard.in.ua/#/ Are you monitoring your machine to check if it is not starving on cpu? Re the queries, we have been using pgbadger to collect metrics about the usage of the dB and the slowest queries by type. This is helpful as it guides where you should put your efforts. https://github.com/darold/pgbadger/blob/master/README.md This is very good ref about scaling Postgres. https://pyvideo.org/pycon-ca-2017/postgres-at-any-scale.html

> When faced with a technical problem it’s often better to use an existing tool than jump into writing one yourself. Having benchmarked Postgres clusters before, I was already familiar with a tool called pgreplay that I thought could do the job. There is also pg_badger which can run parallel jobs: https://github.com/darold/pgbadger pg_badger is also used by nancy, an benchmark experiment driver, so you can replay your logs, then change something, like add an index, and replay them again and nancy will show you to differential performance: https://gitlab.com/postgres-ai-team/nancy

Probably the closest thing to what you are asking for is pgBadger [0]. If there is a better tool I'd love to hear about it. [0] https://github.com/darold/pgbadger

The most productive language of Gilles, well-known in the Postgres community by the way, is perl, and he can do completely crazy things with it. See for example pgbadger (https://github.com/darold/pgbadger), one of his other projects.