Simple Visualisations For PostgreSQL

pgVis is a PostgreSQL centric tool for building simple and elegant data visualisation dashboards. A simple to use SQL DSL and a PostgreSQL centric approach which fits around your workflow. Visualisations can be viewed in any modern browser in both online and offline modes and even exported directly from psql .

pgVis offers a range of visulations including: bar chart, line chart, scatter chart, pie chart, polar area chart, radar chart, data tables. It's designed to be easy to embed into applications, with little to no processing needed in middleware. Making it ideal to rapidly prototype data centric applications.

Usage 

WITH write_4k AS (
    -- The data analysis query we're going to visualise
    SELECT device, buffered, direct
    FROM benchmarks.ssd_write_thoughput
)
-- Define the visualisation
SELECT vis.visualise(
    'SSD Performance',
    -- Add a bar chart
    vis.bar_chart('SSD Write Throughput (KiB/s)',
        -- Chart dataset, serialising as a JSON array
        vis.dataset( (SELECT jsonb_agg(q.*) FROM write_4k q) ),
        -- Add two data series onto the chart
        vis.series('Buffered IO Throughput', 'device', 'buffered'),
        vis.series('Direct Throughput', 'device', 'direct')
    ),
    -- Hide the dashboard header
    vis.option('header', false)
);

WITH data AS (
    SELECT 
        current_date + i AS day, 
        (random() * 10000)::integer AS uk_sales, 
        (random() * 10000)::integer AS us_sales
    FROM generate_series(1, 7, 1) i
)
SELECT vis.visualise(
    'Daily Sales',
    vis.line_chart('Daily Sales',
        vis.dataset( (SELECT jsonb_agg(q.*) FROM data q) ),
        vis.series('UK Sales', 'day', 'uk_sales'),
        vis.series('US Sales', 'day', 'us_sales')
    ),
    vis.option('header', false)
);

WITH data AS (
    SELECT 
        current_date + i AS day, 
        random() * 10000 AS sales 
    FROM generate_series(1, 7, 1) i
)
SELECT vis.html(vis.quick_pie_chart('Daily Sales', 'day', 'sales', (SELECT jsonb_agg(q.*) FROM data q)));

See More Examples

Getting Started With pgVis

Straight Out Of Psql

You can directly save a visulation from psql using the pgVis HTML wrapper function: 

\pset pager off
\pset format unaligned
\pset tuples_only on

WITH data AS (
    SELECT 
        current_date + i AS day, 
        random() * 10000 AS sales 
    FROM generate_series(1, 7, 1) i
)
SELECT vis.html(vis.quick_bar_chart('Daily Sales', 'day', 'sales', (SELECT jsonb_agg(q.*) FROM data q)));

\g quick_bar_chart.html

You can open the saved file in any moden browser.

Goal Of pgVis

pgVis aims to offer a simple and elegant way to visualise data without needing to leave the PostgreSQL ecosystem. It's designed to enable defining visualisations directly in SQL with ease, as well as leveraging PostgreSQL as much as possible.

It's targeted at people who like using SQL and PostgreSQL for data analysis and want a simple tool to work with them not against them. Which integrates with their existing PostgreSQL workflows, development and deployment processes.

Be as simple to install / deploy as possible and to be easily embeddable / reusable by people. It's not intended to be a BI platform, but should offer enough for small teams to keep management happy.

Components

You can get started with using pgVis with nothing but the PostgreSQL extension. If you wish to share visualisation dashboards within your organisation the optional pgVis server will be of use. For more advanced usecases the pgVis rendering libary can be used in custom web applications.

pgVis PostgreSQL Extension

The main part of pgVis is a PostgreSQL extension which provides an SQL DSL (domain specific language) to define visualisation dashboards. This extension is a set of stored functions which leverages PostgreSQL JSON handling to build a JSON document which in turn is rendered by the pgVis Javascript rendering libary.

The extension is capable of outputting a simple HTML wrapper page to allow you to view visualisations without the need to run the optional visualisation server.

The pgVis extension can either be installed as a PostgreSQL extension or manually installed via the provided SQL scripts.

pgVis Server

There is a basic visualisations server which can be optionally deployed. This will let you display and share visualisation dashboards easily, be it for management reports or monitoring screens. The server uses PostgreSQL for autentication and access control.

pgVis Rendering Libary

The rendering of the visualisation dashboards is handled by a Javascript library which makes use of Chart.js to render the actual chart visualisations. This library is easily used in modern web browsers and handles rendering the dashboard based of the JSON definition generated from the pgVis PostgreSQL extension.

Licence And Dependencies

pgVis is open source, under a BSD style licence.

The pgVis renderer makes uses Chart.js for all the chart elements.