0

I have a partially dynamic data that I have to save into Postgres 13 and then visualize it in Grafana.

Example for one document insert (partial)

{
"Timestamp": {
    "$date": {
        "$numberLong": "1623224386151"
    }
},
"Strings": [],
"Coords": [
    {
        "key": "GreySegm",
        "value": {
            "X": 734,
            "Y": 529,
            "Angle": -0.17575337363989894
        }
    }
],
"DataMatrices": [],
"SegmentationRes": [
    {
        "key": "GreySegm",
        "value": {
            "AlgResultBase": {
                "resultCode": 0,
                "errorDescr": ""
            },
            "Regions": [
                {
                    "Name": "reg0",
                    "Id": 1,
                    "Status": "NotSet",
                    "SearchRegionName": "GreySegm",
                    "SearchRegionId": 0,
                    "Position": {
                        "X": 733.8323364257813,
                        "Y": 529.5650634765625,
                        "Angle": -0.1757533699274063
                    }
                }
            ]
        }
    }       
],
"ShapeControl": []

Data is generally a time series data, so no updates and deletes, only inserts. It is about 10k inserts per day but of course grows over months. Grafana doesn't support user friendly DB navigation to build queries if the only column is jsonb. So I have decided to create a view/s and let Grafan use them. This works perfectly but when table grows to thousands of rows queries become extremely slow since no indexes are there, I suspect.

Does this approach with views makes sense or am I abusing the DB design?

My ideas

  1. to use material views and then apply indexes on them, this should help then. My problem is with updates. How to update views relatively frequent and not over the whole table? I do want Grafana to be kind of real time with some lag but definitely not an offline tool with the data from yesterday because I update material view once a day at midnight by cron.

  2. to create indexes on jsonb properties (GIN indexes) and write queries to jsonb directly in Grafana. Then no more user friendliness, only hardcore. And indexes are much less intuitive to build.

2
  • @SahapAsci exporter is only to export Postgres own metrics, not the data, which is contained in DB. I don't think this matches my need.
    – ivans
    Jun 11 at 7:25
  • Wondering about the underlying application but there's a series of videos on YouTube 'Guide to Grafana 101' that might give some ideas... Also if you are PostgreSQL you might find Promscale interesting. Could be worth a look, depends on what you're ultimately trying to achieve. disclosure: I work for Timescale
    – greenweeds
    Aug 19 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.