PostgreSQL Workload Analyzer

Note

You can try powa at demo-powa.anayrat.info. Just click “Login” and try its features! Note that in order to get interesting metrics, resources have been limited on this server (2 vCPU, 384MB of RAM and 150iops for the disks). Please be patient when using it.

Thanks to Adrien Nayrat for providing it.

PoWA (PostgreSQL Workload Analyzer) is a performance tool compatible with all PostgreSQL versions (down to 9.4) allowing to collect, aggregate and purge statistics gathered from multiple PostgreSQL instances from various Stats Extensions.

Depending on your needs, you can choose one of different approaches to setup powa.

For most people, the preferred approach is to use the provided PoWA-collector daemon to collect the metrics from one or multiple remote servers, and store them on a single (and usually dedicated) repository server. This is called the “remote mode”, It does not require any PostgreSQL restart, and can gather performance metrics from multiple instances - including standby server.

The other approach is called the “local mode”. It’s a self-contained solution that relies on a provided and optional background worker, which requires a PostgreSQL restart to enable it, and more suited for a single-instance setup only.

In both cases, PoWA will include support for various stat extensions:

It also supports the following extension:

  • HypoPG, allowing you to create hypothetical indexes and test their usefulness without creating the real index

Additionally, the PoWA User Interface allows you to make the most of this information.

Note

pg_stat_statements is the main extension and is the basis for all the reports. It’s the only extension that’s not optional. PoWA also relies on the metrics being monotonic, so if you reset the metrics regularly (by calling pg_stat_statements_reset() explicitly or using a tool that calls this function), the various reports will be nonsensical.

Main components

  • PoWA archivist is the PostgreSQL extension, collecting statistics.

  • PoWA-collector is the daemon that gather performance metrics from remote PostgreSQL instances (optional) on a dedicated repository server.

  • PoWA-web is the graphical user interface to powa-collected metrics.

  • the Stats Extensions are the actual source of data.

  • PoWA is the whole project.

You should first take a look at the Quickstart guide, which covers the installation and configuration for the “local mode”. You can then look at the Remote setup guide, which covers the necessary additional steps for a “remote mode” setup.