How are you storing the metrics in a postgres data source?

Grafana PostgreSQL
1

I can’t figure out the proper keywords for finding some guides on the tables definitions for storing the metrics.

Is it worth adding a separate table for “series” (unique tags combinations) and creating a view for grafana queries? Should I use a stored procedure for handling the new values in that “series” table (and if so, are there any examples)?

What are the best practices for handling the changes in the set of tags over time? Adding new columns (with default null) or a different set of tables?

Any hints are more than welcome!

2

Welcome @edutu

Which metrics are you trying to capture? Where is the data coming from?

3

I’ll start with some functional tests, storing the execution time along with around 10 tags (some about the tests, some about the execution environment, some about the target application environment).

The data will be sent from a python script but I wasn’t able to find any examples about the postgres table structure that should hold the metrics and the associated tags.

InfluxDB would be a much better option, it’s true, but it’s not an option for the moment.

4

influxdb is an amazing option but other time series will also do. depends in your environment and what you feel comfortable in. up to you if you want to learn something new like influxdb. but if you are also new to postgres then I would definitely make the jump to influxdb

so what are the data points coming from your functional tests and what kind of visualization are you aiming for, what stories/insights do you want the visualization to provide you. can you please provide some
sampling of test results, if there is sensitive data just obfuscate it. the more you provide the better guidance forum can provide you. otherwise we will just prescribe placebo pill :wink:

5

I was hoping for something like a catalog of battle-tested solutions, to be honest. I would start with a couple of pilot projects with the corresponding dashboards and then help others to implement their own needs.

The InfluxDB schema design is a great material about fields and tags. But how should things be stored for metrics with just a few fields/tags and what should be done differently for metrics with more than 10 tags?

I mean, for any given metric I could use either distinct tables for each tag or a big “series” table (with unique combination of tags) or a few smaller tables (for subsets of tags).
Also, it’s most likely to have to reinvent a few wheels to achieve an easier reporting.

The first pilot project will provide some test executions dashboards, similar to the ones provided by Report Portal

A sample point may look like this:

{
    "metric": "test_execution",
    "timestamp": 1659554954,
    "fields": {
        "elapsed": 12.3,
        "retry_count": 1
    },
    "tags": {
        "app_environment": "prod",
        "app_version": "12.3",
        "app_setup": "small_company",
        "test_region": "EU",
        "test_version": "1.23",
        "test_status": "passed",
        "test_campaign": "regression",
        "test_suite": "login",
        "test_name": "create new account"
    }
}

For a given test campaign, the dashboards should expose:

  • pass/fail status, both summary and per test (should make it easy to see if a given test is flaky or is failing all the time and needs to be fixed)
  • test duration, both total and per test
  • test count (new tests will be added over time)
  • and so on
6

While I could design the tables for this metric like I would do for any other application, it’s very likely to receive frequent change requests (regarding the set of collected fields/tags). Also, the future metrics could be different enough to require a different tables structure.
As such, since I strongly prefer to avoid becoming the part-time DBA for this solution, I’m looking for some best practices that could be used by everyone.

7

might be worth looking in here for “battle tested” dashboards. I prefer to carve my own.

8

I think influxdb is a perfect choice for this. notice a sample bucket

_time,_value,_field,_measurement
2022-07-26T22:30:00Z,prod,app_environment,test_execution
2022-07-27T22:30:00Z,12.3,app_version,test_execution
2022-07-27T22:30:00Z,small_company,app_setup,test_execution
2022-07-27T22:30:00Z,EU,test_region,test_execution

so gorgeous!

possibilities are endless

9

I meant battle-tested tables designs :smiley:
Sadly, I’m only allowed to use postgres.

10

hello i think tag usage it’s more about key to groups of series , for me you should store your “tag” as “_field” (like a config series) and join on your unique execution series of a test on id

{
    "metric": "test_param_storing",
    "timestamp": 1659554954,
    "fields": {
        "app_environment": "prod",
        "app_version": "12.3",
        "app_setup": "small_company",
        "test_region": "EU",
        "test_version": "1.23",
        "test_status": "passed",
        "test_campaign": "regression",
        "test_suite": "login"
    },
"tags": { "test_name": "unique_id_test",
"id_script" : "unique_id_script" }
}
1 Like
11

Why not save it all in one json column? fake nosql
sad you can only use postgres. that sounds problematic to me.

imported your sample json into a pg table with json column and voila!

image
image1180×643 74 KB

1 Like