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
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.
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)));
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.
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 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.
- Install pgVis
- Getting Started With pgVis
- Stored Visualisations
- Supported Visualisations
- pgVis SQL Reference
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
Licence And Dependencies
pgVis is open source, under a BSD style licence.
The pgVis renderer makes uses Chart.js for all the chart elements.