25

How can I tell how many queries per second my Postgres database is executing?

4
20

Use this query to read total number of transactions executed in all databases:

SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database;

If you want the same counter for just one database, use:

SELECT xact_commit+xact_rollback FROM pg_stat_database WHERE datname = 'mydb';

To calculate TPS (transactions per second), run the query several times and calculate difference over time interval.

There are ready made tools for that, one of them is http://bucardo.org/wiki/Check_postgres

More info: http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW


Update: Konrad corrected my misunderstanding of his question. The goal was to count queries, not transactions.

How to count queries?

Method 1

Use pg_stat_statements contrib.

Method 2

Enable full logging of queries for a representative period of time.

To enable full logging, for PostgreSQL 9.0 - 9.3, change following settings in postgresql.conf

logging_collector = on
log_line_prefix = '%t '
log_rotation_size = 1GB
log_statement = all

If you want to see also query duration, you can set log_min_duration_statement = 0 instead of log_statement = all. This is very useful for query tuning.

Then reload config (restart or HUP) and collect enough log to estimate traffic.

Note: neither method will include queries embedded in user-defined functions.

3
  • I know how to calculate transactions per second. The question is about queries per second specifically. One transaction can have 0 or more queries. Mar 7 '13 at 17:25
  • @KonradGarus How do you have a transaction with 0 queries? Jul 20 '20 at 9:08
  • fencepost error? begin;rollback; is how many?
    – Jasen
    Dec 7 '20 at 2:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.