PASH Viewer (like ASH for PostgreSQL)

PostgreSQL ASH Viewer (PASH Viewer) provides graphical view of active session history data within the PostgreSQL database

Github Releases

PostgreSQL ASH Viewer (PASH Viewer) provides graphical view of active session history data within the PostgreSQL database.
PASH Viewer provides graphical Top Activity, similar Top Activity analysis of Oracle Enterprise Manager performance page. PASH Viewer store ASH data locally using embedded database Oracle Berkeley DB Java Edition. The default capture rate is one snapshot every 1 seconds. PASH Viewer support PostgreSQL 10+ (and 9.4 - 9.6 with limited functions).

CREATE USER pgmonuser WITH password 'pgmonuser';
GRANT pg_monitor TO pgmonuser;

How it works

Оnce a second PASH-Viewer make a request to the database view pg_stat_activity:

SELECT current_timestamp,
  datname, pid, usesysid, usename, application_name, backend_type,
  coalesce(client_hostname, client_addr::text, 'localhost') as client_hostname,
  wait_event_type, wait_event, query, query_start,
  1000 * EXTRACT(EPOCH FROM (clock_timestamp()-query_start)) as duration
from pg_stat_activity
where state = 'active' and pid != pg_backend_pid();

Each 15 second PASH-Viewer averages data and displays it on the graph, grouping by WAIT_EVENT_TYPE (like WAIT_CLASS in Oracle). You can switch to the Details tab and inspect wait events of the specific type.

You can select a period on the graph and view the Top SQLs of that period. For this to work, PASH-Viewer parse query text and normalize it - replace literals with $1, $2 etc... It allows to group the same queries with different variables into the single SQL_ID.

pgbench (pg load test util) (docs)

pgbench — run a benchmark test on PostgreSQL. pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL commands over and over, possibly in multiple concurrent database sessions, and then calculates the average transaction rate (transactions per second). By default, pgbench tests a scenario that is loosely based on TPC-B, involving five SELECT, UPDATE, and INSERT commands per transaction. However, it is easy to test other cases by writing your own transaction script files.

pgbench -i -h localhost -U postgres
pgbench -h localhost -U postgres -b simple-update -c 30 -T600 -R 3000 postgres