I don’t know where to ask this so I will try here. I am sorry if this is not the place for this. If so please let me know where I can ask this.
I am new to Grafana. I know some PostgreSQL but haven’t used Prometheus or InfluxDB.
I watched a few tutorial videos from Grafana and I see that it uses Prometheus mostly. It also seems that InfluxDB is mentioned often. I am not familiar with Prometheus or InfluxDB.
I am considering using PostgreSQL to do some visualization with Grafana as I used PostgreSQL before.
Could you tell me why I may want to consider using Prometheus instead of PostgreSQL? (or using InfluxDB)
If it helps, the data size is not very big and will not grow much bigger, and doesn’t need to scale.
The main purpose is to analyze past system usage data with some time intervals (ex: weekly, monthly, quarterly, yearly) along with Grafana visualization. Data will be collected weekly from multiple systems.
Real-time monitoring is not yet in the scope but it may be added later.
I am asking this, because I’d like to know if there are any restrictions or limits for visualizing with Grafana if I use PostgreSQL instead of Prometheus (or InfluxDB).
I’d like to add to what @grant2 said, and also make a counterpoint.
While timeseries databases are a really good match for Grafana, they’re not the right hammer for every nail - even when it comes to timeseries data.
If you want to do joins across tables for example, you’ll generally want to use a relational database.
People often choose timeseries databases due to their performance. A modestly sized Influx instance, for example, can ingest hundreds of thousands of data points per second, and I believe Prometheus is similar. Query performance on large timeseries datasets can be very good also.
Since you said…
…my take is that you don’t need to worry about performance (or at least not optimize for it). If I were you I would make a decision based on (a) how you’ll be ingesting data, and (b) how you’ll want to query data. Regarding ingestion, both Prometheus and Influx have nice integrations with monitoring tools, so that may be a reason to go in that direction.
If it helps, a few people (including myself) have shared some thoughts on different databases in this thread - mostly relating to IoT use cases. There will be much better resources out there, especially in the context of monitoring, but maybe this is a helpful starting point.
EDIT1:
A system is assigned to pull users’ login data from multiple service systems by cron job weekly. Those multiple service systems are used by multiple user systems.
Data is collected mostly in text format with a few fields.
The current data is simple.
Fields are ‘service system (target)’, ‘login time’, ‘user system (source)’, ‘user’, and a few others.
I am trying to decide which database to load this data so we can use Grafana more effectively.
Someone initially wrote a tool to load the data to PostgreSQL, so we initially thought to use Grafana with the PostgreSQL plugin.
We are familiar with PostgreSQL but we haven’t used Prometheus or InfluxDB.
Thus, the learning curve matters too.
This data will be visualized to show how target systems are used by user systems along with various time intervals as mentioned above.
It’s relatively simple for now and the main purpose is to analyze past data rather than monitoring.
I added some more details as ‘EDIT1’ related to your question.
Do you still think Prometheus or Influx is better in my case? If so, which one do you think fits better between Prometheus and Influx?
Also, which one is easier to learn and use?
Grafana is better tools to bring the observability of your systems, including metrics, logs and traces.
Three of them is always append data, so the timeseries Database is suitable for this situation.
The other hand, RDBMS like Postgresql have a lots of features mostly to handle OLTP, so the upsert is most important features in the Postgresql.
In my experiences, if just always append data, better using TSDB to get the better performance and ROI of the system. I just made some migration to the TSDB for the three pillars of the observability.
Basically, the learning curve of the prometheus, InfluxDB is not so hard if you have some knowledge of the RDBMS. In my experience, just need most likely 1-2 weeks to understand the basic knowledge of them.
So based on the fact that you already have a system assigned to pull users’ login data from multiple service systems by cron job weekly and wrote a tool to load the data to PostgreSQL, I would just stick with that. You should be able to quickly set up a simple dashboard to view that data. Or, perhaps make a mockup of what you expect it to look like and post it here. Often that exercise will lead you to a conclusion about what weaknesses, if any, exist in either the db, the data fields, etc.
I agree with @grant2
There are several ways to get what you want to achieve, it depends on what you have, resources and skill set…
I just telling what I’ve done with my experiences…
EDIT3:
Thank you for sharing your opinions!
It seems that you both shared the same opinion.
It sounds like it’s fine to use PostgreSQL for analysis purposes like our case but it’s better to use TSDB for monitoring purposes.
Since I am new to Grafana and never used Prometheus or InfluxDB, it’s yet unclear to me because I assume Grafana is a tool to visualize any data queries and PostgreSQL can query along with time-record as well.
I guess these questions may help me:
Is there any limit or restriction from Grafana by using PostgreSQL instead of Prometheus or InfluxDB?
If it’s not Grafana, is there any query PostgreSQL can’t do but Prometheus or InfluxDB can for Grafana?
If none of the above, did you suggest TSDB mainly because it can perform better with big-size data and can handle scaling?
Also, could you give me a simple example as a comparison in which case and why Prometheus or InfluxDB works but not PostgreSQL for Grafana Visualization?
Sorry for more questions but I’d really appreciate hearing about these.
Is there any limit or restriction from Grafana by using PostgreSQL instead
of Prometheus or InfluxDB?
Performance. InfluxDB is optimised for both writing and querying time-series
data.
Any DBMS can do it, but a generalised relational database system has other
strengths, and will be less performant at handling time-series data.
If it’s not Grafana, is there any query PostgreSQL can’t do but Prometheus
or InfluxDB can for Grafana?
I would say the opposite. InfluxQL (the query language for Influx 1) is
noticeably limited compared to full SQL database languages - but what it can
do, it does more efficiently. Flux (the query language for Influx 2) is more
powerful than InfluxQL, but still somewhat limited compared to general RDBMS
query languages such as SQL. Again, if it can do the time-based query you
need, though, it will be better at it than SQL.
If none of the above, did you suggest TSDB mainly because it can perform
better with big-size data and can handle scaling?
Both Influx and a general-purpose DBMS can scale well and handle large amounts
of data. Influx is better at writing it with timestamps, and then retreiving
it from timestamp-based queries.
Also, could you give me a simple example as a comparison in which case and
why Prometheus or InfluxDB works but not PostgreSQL for Grafana
Visualization?
I don’t think the choice of data store makes any difference to the
visualisations you can perform with Grafana. Provided your data store
supports the queries you need for the data you want, Grafana will display it.
