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.

New contributor
ivans is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
3
  • you can use postgres_exporter to export time series data to Prometheus then visualize it from graphana. – Sahap Asci Jun 10 at 22:56
  • @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 2 days ago
  • You can define your own queries here is an example – Sahap Asci 2 days ago

Your Answer

ivans is a new contributor. Be nice, and check out our Code of Conduct.

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.