pg_stat_statements vs query store performance

Question

Hi!

I tested the performance with pg_bench -c 10 -j 2 -t 1000 against a basic instance (1vCore) with pg_stat_statements enabled and other test with query store enabled. There are the results:

With pg_stat_statements (default conf): tps = 39.035860 With query store (only TOP as query_capture_mode conf): tps = 37.688766

Why the doc says in https://learn.microsoft.com/en-us/azure/postgresql/single-server/how-to-optimize-query-stats-collection#use-pg_stats_statements:

"This module hooks into every query execution and comes with a non-trivial performance cost.

The Query Store feature in Azure Database for PostgreSQL provides a more effective method to track query statistics"

I understand pg_stat_statements use a file as store with an in-memory hashtable storing statistics and query store i imagine something in memory with db tables as store, i understand that pg_stat_statements use a file for query texts but the 15m default for flush the in memory stats into azure_sys also comes with a perf impact, why the doc claims that sentences against pg_stat_statements?

Thanks!

Answer 1

Hi, @Andres Rodriguez Welcome to the Microsoft Q&A forum, Thanks for posting your question.

The short test doesn't really prove much, there is a difference in 2 transactions out of 1000, maybe you can run a test that takes at least 10-20 minutes, run it 3 times and take the average of the runs. Did you make sure that autovacuum, checkpoints, and WALs do not skew the test results?

Regards

Geetha

Answer 2

Performance might not be the biggest concern. Tools such as pg_watch use pg_state_statements and in order to use Query_store you have to send a log of data to log analytics. The upload charges will be > $2 per gig which renders this unusable by most companies.