How many queries per second is my Postgres executing?

Question

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

Answer 1

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.