Visualizing Data in PostgreSQL With
Grafana
Preetam Jinka
PostgresConf NYC March 2019
About Me
• Runtime + Code Science Infrastructure at ShiftLeft
• Twitter: @PreetamJinka
What is this talk about?
This is about making visualizations from data
that already exists in your database for another
purpose.
Why you should use Grafana with PostgreSQL
• Get a different perspective for monitoring
Logs ? Logs
Metrics Metrics
• Understand what your data looks like
• Build visualizations and reports without
writing code
Grafana is a visualization platform.
Structure of this talk
1. Getting started
2. Simple visualizations
3. Alerts
4. Making dashboards interactive
5. Examples of Grafana+PG "wins" at ShiftLeft
Getting Started: Docker
Docker Compose:
https://github.com/Preetam/compose-postgresql-grafana
1. Run docker-compose up --build
2. Go to http://localhost:3000/ and login using admin/admin.
Getting Started: Manual
• Use a read-only user
• Take advantage of per-table
permissions for sensitive data.
Suppose we had a "users" table for our application.
postgres=# select * from users;
name | created
---------+-------------------------------
alice | 2019-03-17 02:51:49.709148+00
bob | 2019-03-17 02:51:49.727934+00
charlie | 2019-03-18 02:51:49.730087+00
david | 2019-03-18 02:51:49.731717+00
eric | 2019-03-18 02:51:49.733585+00
felix | 2019-03-19 02:51:49.734678+00
george | 2019-03-20 02:51:49.735819+00
harvey | 2019-03-20 02:51:49.737792+00
ian | 2019-03-21 02:51:49.738908+00
(9 rows)
The Simplest Visualization: SELECT * FROM table
Time Series and Charts
Create valuable alerts with the simplest charts
More complicated example: Pipeline Metadata
Column | Type
---------+--------------------------
project | text
stage | integer
started | timestamp with time zone
ended | timestamp with time zone
failed | boolean
project | stage | started | ended | failed
----------------------------------+-------+-------------------------------+-------------------------------+--------
b89c1251cbe068eb1f65da1e10b2d329 | 1 | 2019-03-12 16:04:08.737775+00 | 2019-03-12 16:05:06.068346+00 | f
b89c1251cbe068eb1f65da1e10b2d329 | 2 | 2019-03-12 16:05:08.737775+00 | 2019-03-12 16:05:30.245483+00 | f
b89c1251cbe068eb1f65da1e10b2d329 | 3 | 2019-03-12 16:06:08.737775+00 | 2019-03-12 16:06:59.070184+00 | f
b89c1251cbe068eb1f65da1e10b2d329 | 4 | 2019-03-12 16:07:08.737775+00 | 2019-03-12 16:07:51.519871+00 | f
c687373127df94197bef9c7d9bb6b94b | 1 | 2019-03-13 20:00:24.504441+00 | 2019-03-13 20:01:20.839139+00 | f
c687373127df94197bef9c7d9bb6b94b | 2 | 2019-03-13 20:01:24.504441+00 | 2019-03-13 20:01:44.961685+00 | f
c687373127df94197bef9c7d9bb6b94b | 3 | 2019-03-13 20:02:24.504441+00 | 2019-03-13 20:03:22.930905+00 | f
c687373127df94197bef9c7d9bb6b94b | 4 | 2019-03-13 20:03:24.504441+00 | 2019-03-13 20:04:11.817668+00 | f
ec50747e16992dec38daf913a8bd1c6b | 1 | 2019-03-13 21:31:34.474025+00 | 2019-03-13 21:32:31.656488+00 | f
ec50747e16992dec38daf913a8bd1c6b | 2 | 2019-03-13 21:32:34.474025+00 | 2019-03-13 21:33:23.075038+00 | f
More complicated example: Pipeline Metadata
Create multiple charts using the same data
And yes, create alerts for those too.
A note about alerts...
• Watch out for expensive queries
• Add comments to your queries so you can differentiate them in
query profilers.
• Maybe set:
ALTER ROLE reader SET statement_timeout=30000
Making dashboards interactive with variables
Making dashboards interactive with variables
Making dashboards interactive with variables
Without Filter
With Filter
Grafana+PostgreSQL "wins" at ShiftLeft
Overview
• Before: problems required looking into the database
• One person with knowledge and credentials had to do it
• Took a long time to format or interpret data
• Depends on adhoc queries that weren't always documented
• After: just look at Grafana
• Almost everyone has access to Grafana
• Dashboards and visualizations are easy to interpret even across teams
Grafana+PostgreSQL "wins" at ShiftLeft: Reports
Grafana+PostgreSQL "wins" at ShiftLeft: Reports
Grafana+PostgreSQL "wins" at ShiftLeft: Reports
Grafana+PostgreSQL "wins" at ShiftLeft: Data Exploration
Questions?
Feel free to send me Grafana/PostgreSQL questions on Twitter:
@PreetamJinka
