My retention policy for the default database is set of 7 days. From this default database i use a continuous query to downsample data and move it to the different database (on the same influx instance).
For charts - can I use data from this downsampled database (mainly for aggregation)? if yes - how ?
No, OH only understands and uses it’s own database. Anything moved to some other database is going to be invisible to OH.
Is this something which can be looked at for future i.e. support a “reporting db” for aggregate queries
Anything can be looked at. Being able to access other databases has been requested many times but no one has volunteered to implement it. But even that has been more focused on rules, not charting.
OH really isn’t designed to be a data analysis platform though. When you have such complicated data setups you are often need to look elsewhere for complicated data analysis, such as Grafana.
Thats a fair challenge, I will definitely look at grafana.
However, a query - when the data volume in the OH db grows to say an year or more (which the graphs seem to support), is the system typically able to handle that volume? or is there a best practice I am missing which allow a large volume of data to be retained in the same db without impacting performance.
Back when I used InfluxDB I saw no problems with two years worth of data, though only one year’s worth would . This is a home automation system, not an enterprise level logging aggregator. You’d be hard pressed to generate so much data that you start to see performance problems even on an RPi 4.
Usually the problem comes with disk space.
If disk space is a concern, you get that for free with rrd4j. Or you can figure out how to do the compaction in place instead of to other databases (that’s an option for other DBs like PostgreSQL for example).