powa

Overview

Latest stable upstream/PGXN and PGDG DEB are 5.2.0; PGDG RPM remains at 5.1.0.

Version

Install

You can install powa directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install powa;          # Install for current active PG version

pig ext install -y powa -v 18  # PG 18
pig ext install -y powa -v 17  # PG 17
pig ext install -y powa -v 16  # PG 16
pig ext install -y powa -v 15  # PG 15
pig ext install -y powa -v 14  # PG 14

dnf install -y powa_18       # PG 18
dnf install -y powa_17       # PG 17
dnf install -y powa_16       # PG 16
dnf install -y powa_15       # PG 15
dnf install -y powa_14       # PG 14

apt install -y postgresql-18-powa   # PG 18
apt install -y postgresql-17-powa   # PG 17
apt install -y postgresql-16-powa   # PG 16
apt install -y postgresql-15-powa   # PG 15
apt install -y postgresql-14-powa   # PG 14

Create Extension:

CREATE EXTENSION powa CASCADE;  -- requires: plpgsql, pg_stat_statements, btree_gist

Usage

Sources:

• PoWA Archivist 5.2.0 README
• PoWA Archivist 5.2.0 control file
• PoWA Archivist 5.2.0 release
• PoWA documentation

PoWA is a workload analysis framework that collects performance statistics and provides real-time charts through a web UI. The core extension (powa-archivist) snapshots statistics from multiple stat extensions.

Architecture

PoWA consists of several components:

• powa-archivist: The PostgreSQL extension that collects and stores statistics
• powa-web: A web UI for visualizing performance data
• pg_stat_statements: Required for query statistics
• pg_qualstats: Optional, provides predicate statistics and index suggestions
• pg_stat_kcache: Optional, provides OS-level metrics (CPU, I/O)
• pg_wait_sampling: Optional, provides wait event sampling

Enable the extension in the repository database. Its control file requires PL/pgSQL, pg_stat_statements, and btree_gist:

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION btree_gist;
CREATE EXTENSION powa CASCADE;

pg_stat_statements itself must be configured in shared_preload_libraries before PostgreSQL starts.

Taking Snapshots

PoWA periodically snapshots statistics from all registered extensions:

-- Manual snapshot
SELECT powa_take_snapshot();

-- Check snapshot status
SELECT * FROM powa_snapshot_metas;

Configuration

-- Register stat extensions (done automatically on CREATE EXTENSION)
SELECT powa_register_server(hostname => 'localhost', port => 5432);

-- Configure snapshot interval and retention
ALTER EXTENSION powa UPDATE;

Key Tables and Views

-- View collected query statistics
SELECT * FROM powa_statements;

-- View snapshot history
SELECT * FROM powa_snapshot_metas;

Web UI

The PoWA web interface (installed separately) provides:

• Real-time query performance dashboards
• Per-query drill-down with plan details
• Index suggestions based on pg_qualstats
• Wait event analysis
• System resource usage graphs

Documentation: powa.readthedocs.io

Version and Deployment Notes

• PoWA Archivist 5.2.0 adds PostgreSQL 19 support, including collectors for the new pg_stat_recovery and pg_stat_lock statistics views. The core snapshot workflow remains compatible with the 5.1 series.
• powa is the database extension; powa-web is a separate visualization service, and powa-collector is used for remote collection architectures. Installing the extension alone does not deploy the UI.
• Retention and snapshot frequency directly affect repository growth. Monitor the PoWA repository database and size retention for the number of databases, queries, and enabled optional modules.