[FEAT] PostgreSQL support

[davispuh]

Is your feature request related to a problem? Please describe.

I host a lot of different services and most of them support PostgreSQL so I never had any need for InfluxDB.
I think having to manage whole InfluxDB just for few HDDs seems overkill so it would be very useful to use already existing PostgreSQL.

I also think that for most people they don't work with large enough data where InfluxDB would bring any benefit over PostgreSQL.

Describe the solution you'd like

Option in config to use PostgreSQL instead of InfluxDB

[DarkenLight]

@AnalogJ you have save me countless hours i spent on looking at disk temperature and decoding s.m.a.r.t. attributes, for that I want to express my appreciation for creating such an incredible tool.

The data collector and web components of Scrutiny are working flawlessly on FreeBSD. However, the lack of InfluxDB-v2 support on FreeBSD poses a challenge. and holding me back creating a complete updated FreeBSD Jail (similar to linux docker) template to use in XigmaNAS. I'm currently stuck at v0.3.13 due to this.

InfluxDB’s frequent API and structural changes between major versions often lead to compatibility issues. Supporting SQL databases like MySQL, SQLite, or PostgreSQL would be a great step towards broader adoption and reliability.

I understand that InfluxDB was chosen for its strengths as a time series database, but please consider this use case. Adding support for SQL databases could significantly enhance the flexibility and accessibility of Scrutiny for more users.

[AnalogJ]

Hey everyone!

I recognize that adding another dependency is always annoying (and a huge barrier if the dependency is missing for your architecture/os).

However, I'm not sure if I'll be able to replace InfluxDB with Postgres. My understanding is that the downsampling and timeseries functionality which is native to InfluxDB is just not available for Postgres, and it's required for Scrutiny to work at scale without falling over after a couple of months of data collection.

I dive into the decision in a bit more detail here - https://github.com/AnalogJ/scrutiny/blob/master/docs/TROUBLESHOOTING_INFLUXDB.md#why

I'll keep this issue open, because I agree -- it would be nice to use Postgres, and maybe one of you knows of a better solution that I missed.

[AnalogJ]

No, at that point you can just run SMART yourself via the commandline -- it wouldn't be very useful.

[DarkenLight]

I'll keep this issue open, because I agree -- it would be nice to use Postgres.

Thank you for considering it.

However, I'm not sure if I'll be able to replace InfluxDB with Postgres. My understanding is that the downsampling and timeseries functionality which is native to InfluxDB is just not available for Postgres, and it's required for Scrutiny to work at scale without falling over after a couple of months of data collection.

I completely agree that InfluxDB is the right choice given the requirements, and MySQL likely wouldn't meet the requirement needs.

maybe one of you knows of a better solution that I missed.

You might find it helpful to explore EmonCMS, an open-source project designed for time-series data. It handles averaging, null entry correction, and more. The project initially experimented with various databases, including MySQL and InfluxDB, before eventually adopting a file-based database. I've been using it for two years database size is surprisingly around 150MB, and it's been reliable. Here’s the link for more details—worth checking out. Emoncms

[davispuh]

You're seriously underestimating PostgreSQL performance. You only to create index on timestamp column and it will work perfectly fine as time-series DB. Really I would say just test it out and you'll see it has no issue handling this.

But yes you need more code at application level to create aggregations that come automatically with InfluxDB.

[paxter]

Same issue here. Planned to use this tool for monitoring devices on FreeBSD, but had to stop the installation progress on the InfluxDB requirements. Furthermore, I've seen v3 is planned to not be OSS which would be another showstopper for me.

Please consider a more open source friendly and future robust solution. Thanks.

[AnalogJ]

@paxter completely understand the frustration, and thanks for pointing out that InfluxDB v3 will not be OSS.. which sucks.

I'm open to suggestions or alternatives if any of you have ideas.

[AnalogJ]

@E021ntox you might want to open a new issue for that request. But basically Scrutiny can't do real-time requests because the collectors can only push data to the webui on a schedule -- this is intentional for security reasons. The webUI cannot send commands to the collectors -- so on-demand SMART data is just not possible, a database or some sort of storage will always be necessary.