Postgresql transaction counter (transactions per second)

There are transactions counters for each database in a cluster.

If you want to find out how many transactions has your system generated at the moment, you should connect to any database as a superuser (postgres) and

select sum(xact_commit) from pg_stat_database;

Easy, but took some time to find the recipe.

Write-heavy setup for Postgresql

My project has a database which is updated almost as frequently, as read. The main bottleneck for the database was disk speed. Here are some tips on how to optimize Postgresql configuration to avoid overusing disk IO. For my case it helped to reduce iowait from ~150 to less then 50 in average.

synchronous_commit. Since users score is not a critical parameter, it is safe to set sychronous_commit to off. The worst thing that can happen is that you loose several last transactions.

checkpoint_segments, checkpoint_timeout. Checkpoint causes all the modified data to be stored in actual table structures. Before the checkpoint happens, WAL guarantees data durability. If you have some frequently modified row, it is checkpointed each time. If checkpoints happen in your database too frequently it is a significant overhead. Increase the parameters to make checkpoint happen less frequently.

Background Writer. It writes dirty pages in background to lower amount of work for checkpoint. Again, some frequently modified value might be written each BW activity round. This is overkill for write-heavy database, because the value will be checkpointed anyways. I turned BW off at all, setting bgwriter_lru_maxpages = 0.

Hope it helps. Comments are extremely welcome.