- What are the performance implications of pganalyze?
The performance overhead of the pg_stat_statements extension is small
- it requires a bit of additional shared memory and will consume about ~1% of CPU time.
and here is a quote from a PostgreSQL contributor who says:
I always enable it on production databases. The benefits are well
worth the small performance hit.
Particularly on a production database you want to know which
statements cause the most pain and should be optimized.
From here (another big hitter in the PostgreSQL field):
If you use Postgres and you haven’t yet used pg_stat_statements, it is
a must to add it to your toolbox.
This is old - two separate results:
With enabling pg_stat_statements module, the pgbench score was 468.2
tps. And without (loading) the pg_stat_statements module, pgbench
score was 470.3 tps. The difference (overhead) was just 0.5%
- Shared Buffers : 2048MB
- Checkpoint Segments : 32
- WAL Buffers : 1024kB
- Pgbench: Scale factor 10, 1,000 Transactions * 32 Clients
Under certain (specialised IMHO) conditions, he found that there was a 10% peformance impact.
It strikes me that the first test was a much more "server-like" scenario.
The fact that all of the bigger players in the PostgreSQL sphere (highly) recommend it and the fact that there are no queries either here or on StackOverflow (that I could find) of the sort:
"I enabled pg_stat_statements" and now my server is running slowly", appears to augur well for the tool on that score.
Having said all of the above, there can be no substitute for benchmarking on your own system with your hardware, software and networking configuration. Initially, you could try switching it on and off during quiet times - but bear in mind that doing this requires a server restart!