How can I tell how many queries per second my Postgres database is executing?
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
Update: Konrad corrected my misunderstanding of his question. The goal was to count queries, not transactions.
How to count queries?
Use pg_stat_statements contrib.
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
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.