powa

powa : PostgreSQL Workload Analyser-core

Overview

Packages

Source

Install

Make sure PGDG repo available:

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

Install this extension with pig:

pig install powa;		# install via package name, for the active PG version

pig install powa -v 18;   # install for PG 18
pig install powa -v 17;   # install for PG 17
pig install powa -v 16;   # install for PG 16
pig install powa -v 15;   # install for PG 15
pig install powa -v 14;   # install for PG 14

Create this extension with:

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

Usage

powa: PostgreSQL Workload Analyzer

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

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