Rapid-fire Postgres times series queries from Grafana

Grafana PostgreSQL
1

Our typical dashboard consists of a number of Postgres queries usually of the form

DROP TABLE IF EXISTS mytable;
CREATE TEMPORARY TABLE mytable(
    "date" text, 
    "Forecast (YoY)" DOUBLE PRECISION, 
    "Forecast (MoM)" DOUBLE PRECISION, 
    "Market" DOUBLE PRECISION);
COPY mytable FROM PROGRAM './fetch company_forecast_data';
SELECT "date",  "Forecast (YoY)",  "Forecast (MoM)", "Market" FROM mytable;

A dashboard may have 15 or more such queries and are executed by Grafana in rapid succession. Note that the actual data extraction is done by the fetch script which grabs data from a company proprietary database and copies it into a temporary table just for grafana. The script itself sits with the Grafana executable in
/var/lib/postgresql/10/main/fetch

Generally this has been working reliably however there is the occasional problem where the Grafana server hangs and needs to be restarted. Sorry, no error messages or logs.

Grafana itself runs on Linux, the problem has been observed on Grafana v11.3.0 (Debian bookworm) and v9.2.3 (Ubuntu 22.04). Postgres rather old, currently version 10. Clients all use Windows 10.

PostgreSQL Connection

Host postgres:5432
Database quant
User pterodactyl
Password configured
TLS/SSL Mode disable

Connection limits
Max open unlimited
Max idle 2
Max lifetime 14400

PostgreSQL details
Version 10
TimescaleDB
Min time interval 1m

We will be upgrading to Postgres 16 with a dedicated, empty Grafana database specifically for temporary tables, that is temporary tables which would be created and destroyed during a connection’s brief liftetime. The current Postgres 10 database has its Grafana usage shared with a handful of tables.

Would there be any suggestions as to improve the connection configuration? Also, are there plans (or means) to have Grafana create a single connection per datasource per dashboard?

2

Welcome @walter42

Your question seems to be more db design best practice than grafana

The 15 queries that are ran in succession to populate the data used by grafana, so they populate 15 different tables and then how are they used in a dashboard

This seems to be very expensive approach and would hit scaling issues which might be the reason for grafana hanging and need to restart it