PoWA 2: Finding the Missing Index ! - Mailing list pgsql-announce

_Paris, March 2 2015_

DALIBO is proud to present the second release of PoWA, the PostgreSQL
Workload Analyzer.

## Complete rewrite of the User Interface

The first major change is the new graphic design : we've rebuilt the web
interface from scratch and placed it on a separate module called
powa-web (https://github.com/dalibo/powa-web). It is now easier to move
the PoWA interface out of the database server.

The new interface is based on the previous version and it adds many
improvements, including:

* More graphs
* New types of graph : Bar / Pie Charts
* New configuration view
* New index suggestion widget
* New physical resource consumption graphs
* Better Global Query Chart
* Breadcrumbs
* Python 2.6, 2.7 and 3.4 compatibility
* Better browser compatibility

Screenshot:

http://powa.readthedocs.org/en/latest/stats_extensions/pg_stat_statements.html#where-is-it-used-in-powa-web


## Advanced Filesystem Stats

Second, the core-engine of PoWA is now a separate module too: it's
called powa-archivist (https://github.com/dalibo/powa-archivist) and
we've extended it to archive any kind of internal stats. The main source
of stats remains the pg_stat_statements extension but we've integrated 2
other extensions: pg_qualstats and pg_stat_kcache.

With pg_stat_kcache, PoWA is now able to gather statistics about the
physical disk access and the CPU consumption of each PostgreSQL backend.
This allows the DBA to get a glimpse of the OS-level cache hit ratio.

Screenshot:

http://powa.readthedocs.org/en/latest/stats_extensions/pg_stat_kcache.html#where-is-it-used-in-powa-web


##  Missing Index Finder + Advanced WHERE Clause Analyzer

Third major improvement, with pg_qualstats PoWA is now collecting
metrics about the operations in the WHERE clause of each query. With
this data, you can know what are the most common values in your SQL
statements and evaluate their "selectivity". And for columns with a very
high selectivity and no index, PoWA will suggest index creation to speed
up you queries.

Screenshot:

http://powa.readthedocs.org/en/latest/stats_extensions/pg_qualstats.html#where-is-it-used-in-powa-web


## Disruptive changes

It was a hard choice but if we want to implement ground-breaking
features like the Missing Index Finder, we need to use the latest
capacities of PostgreSQL and thus we decided to drop the compatibility
with PostgreSQL 9.3.

Of course, we will continue to maintain PoWA v1.2 and you can continue
to use it on your PostgreSQL 9.3 servers. In a nutshell, If you already
use PoWA here's what you need to know:

* If you're running PostgreSQL 9.4, we recommend that you switch to PoWA
2.0
* If you're running PostgreSQL 9.3, you can either keep PoWA 1.2 or
upgrade your PostgreSQL instance and switch to PoWA 2.0

Here's a detailed explanation on why we are Moving Fast and Breaking
Things : https://github.com/dalibo/powa/wiki/Moving-Fast-and-Breaking-Things


## Credits & Licence

DALIBO would like to thank all the developers who contributed to this
release: Rodolphe Quiedeville, Hyunjun Kim, Grégoire Pineau, Ahmed
Bessifi, exordium-frozen, Christopher Liu, menardorama and especially
Ronan Dunklau  and Julien Rouhaud for the hard work on the GUI.

PoWA is an open project available under the PostgreSQL License. Any
contribution to build a better tool is welcome. You just have to send
your ideas, features requests or patches
using the GitHub tools or directly to powa@dalibo.com

## Links :

For PoWA 2.0:
* Download: http://dalibo.github.io/powa/
* Demo: http://demo-powa.dalibo.com  (login/pass = powa/demo)
* Doc: http://powa.readthedocs.org/
* Mailing list: https://groups.google.com/forum/?hl=fr#!forum/powa-users
* Issues: https://github.com/dalibo/powa/issues

For the new stats extensions:
* pg_stat_kcache : https://github.com/dalibo/pg_stat_kcache
* pg_qualstats : https://github.com/dalibo/pg_qualstats

For the complete list of changes, please checkout the release note on

http://powa.readthedocs.org/en/latest/releases/v2.0.html



--------------

**About POWA** :

PoWA is a PostgreSQL Workload Analyzer that gathers performance stats
and provides real-time charts and graph to help monitor and tune your
PostgreSQL servers. It is similar to Oracle AWR or SQL Server MDW.

Code & Demo at http://dalibo.github.io/powa/

--------------

**About DALIBO** :

DALIBO is the leading PostgreSQL company in France, providing support,
trainings and consulting to its customers since 2005. The company
contributes to the PostgreSQL community in various ways, including :
code, articles, translations, free conferences and workshops

Check out DALIBO's open source projects at http://dalibo.github.io