Course Code: bspsqlgraf
Duration: 14 hours
Prerequisites:
  • A fundamental understanding of SQL and relational databases
  • Basic experience with PostgreSQL, including knowledge of queries, joins, and subqueries
  • Familiarity with data visualisation concepts (helpful but not mandatory)
  • Access to a laptop with PostgreSQL and Grafana installed (details will be shared prior to the course)
Overview:

This course is designed for Database Administrators (DBAs), Data Analysts and Engineers, Developers, and System Administrators.

In this hands-on course, you will gain a comprehensive understanding of advanced PostgreSQL query building and Grafana dashboard creation, including architecture, implementation, and best practices.

By the end of the course, you will be able to do the following:

  • Master advanced PostgreSQL concepts such as window functions, recursive queries, and performance tuning.
  • Implement efficient data aggregation and filtering techniques using advanced SQL features like GROUPING SETS, ROLLUP, and CUBE.
  • Write advanced queries for real-time analytics and integrate PostgreSQL with Grafana as a data source.
  • Build dynamic and customisable dashboards in Grafana with various visualisations, time filters, and variables.
  • Set up alerts, notifications, and dashboard templating for enhanced monitoring capabilities.
  • Optimise dashboard and database performance through caching, indexing, and minimising query load.
  • Apply best practices in security, access control, and sharing of dashboards
  • Gain hands-on experience through case studies, such as monitoring sales databases and infrastructure systems
Course Outline:

Day 1: Advanced PostgreSQL Query Building

 

Introduction to Advanced SQL Concepts

  • Recap of PostgreSQL basics (CTEs, Joins, Subqueries).
  • Window Functions:
    • ROW_NUMBER, RANK, and DENSE_RANK.
    • NTILE, LAG, and LEAD.
  • Recursive Queries:
    • Hierarchical data structures.
    • Practical use cases for recursive CTEs.

Advanced Aggregation and Filtering Techniques

  • GROUPING SETS, ROLLUP, and CUBE.
  • HAVING with complex conditions.
  • Filtering data with window functions.

Data Manipulation and Performance Tuning

  • Query Execution Plans:
    • EXPLAIN and EXPLAIN ANALYSE.
    • Understanding query cost and optimising joins, subqueries, and aggregates.

Introduction to Grafana

 

  • Overview of Grafana architecture.
  • Installation and configuration of Grafana.

Querying PostgreSQL from Grafana

  • Writing raw SQL queries in Grafana.
  • Query builders vs. manual query mode.
  • Handling time-series data in PostgreSQL for Grafana visualisation.

Day 2: Building Dashboards in Grafana

Building Visualisations

  • Types of panels (Graph, Table, Stat, Bar Gauge, etc.).
  • Setting up time filters and variables.
  • Customising dashboard themes and layout.

Advanced Grafana Features

  • Alerts:
    • Setting up alert rules and thresholds.
    • Configuring notifications (Email, Slack, etc.).
  • Dashboard templating:
    • Variables and dynamic panels.
    • Query-based and custom variables.

Case Study 1: Monitoring a Sales Database

  • Creating advanced queries for KPIs (e.g., monthly revenue, product trends).
  • Visualising trends and forecasts in Grafana.

Case Study 2: Infrastructure Monitoring

  • Writing PostgreSQL queries to monitor system metrics (e.g., query execution time, locks, connections).
  • Building dashboards for real-time database health monitoring.

Best Practices and Optimisation

  • Dashboard performance optimisation:
    • Reducing query load on PostgreSQL.
    • Caching and minimising data fetch.
  • Security and sharing:
    • User roles and access control in Grafana.
    • Exporting and importing dashboards.

Final Hands-On Project: Custom Dashboard Creation

  • Participants design a custom dashboard:
    • Query building in PostgreSQL for specific business metrics.
    • Creating visualisations in Grafana.