Influxdb structure advice needed

[877]

New to influxdb, I have it working and can send data from node red in json format.

My question is how to arrange the database?
A database for each type (i.e. weather, sensors, lights, motion etc..)
Or by location (i.e. lounge, dining, garden, garage).
Or all in one database and use tags..

As an example of my devices scattered across the whole house, each one publishing mqtt to my broker:

Zigbee2mqtt:
Aqara temp and humidity
Aqara motion
Aqara buttons
Aqara door sensors

Tasmota:
Sonoff S20 smart sockets
Sonoff basics

ESP8266's:
A dozen running custom Arduino code publishing to mqtt

Any advice is much appreciated before I get too far in my setup.

Example of my first attempt::

(Function node)

msg.payload = [
    {
        batteryPercent: msg.payload.battery,
        humidity: msg.payload.humidity,
        linkQuality: msg.payload.linkquality,
        pressure: msg.payload.pressure,
        temperature: msg.payload.temperature,
        batteryVolts: msg.payload.voltage
    },
    {
        topic: "Xiaomi_3_weather",
        make: "Aqara",
        location: "lounge"
    }
];

return msg;

The database name is "mydb", measurement is "weather", and I am using tags as you can see.

[TotallyInformation]

@877

My question is how to arrange the database?

You will want a single database for your environmental data since that makes it fairly easy to get temperatures across locations or compare temperature and humidity, etc.

As for the rest, anything that is numeric should be a value and anything that is text should be a tag. So, for example, your locations will be tags but your sensor readings should be numbers (such as temperature in deg. C). One thing to note that caught me out when I was starting with InfluxDB, if you have movement or open/close or on/off sensors (switches, PIR, magnetic closure sensors, etc), feed them into InfluxDB as 1's and 0's (e.g. as values) as this will make it easier to chart them.

Also consider how much data you are feeding in. Especially on older Pi's (32bit architecture limits the file sizes). InfluxDB is pretty good at handling large quantities of data (using sharding for example) but eventually, that will use up too much memory and you may find your device slowing to a crawl periodically.

Another of InfluxDB's strengths is the ability to auto-trim its data and to auto-summarise data. So my environmental sensors typically report about once a minute. I feed these into a data table that retains everything for 7 days. I use the auto-summarising feature to aggregate that detail data to hourly data that I keep pretty much indefinitely.

[877]

@877

weather, sensors, lights, motion etc..)

Thank you Julian, looks like you are suggesting this kind of database setup, based on data type.

Your tip about 1’s or 0’s is much appreciated.

I’m using a Pi4 4GB and 128GB sata drive so I am hoping it will be ok long term.

I need to look at the data aggregation, I was thinking for weather maybe 5min revolution for 7 days, then hourly for 12 months, then daily averages indefinitely.

Other sensor events would be only once or twice per day so could store the whole lot. I think it’s all numerical data too, can’t think of any text values.

Still more to learn!

[TotallyInformation]

@877

Pi4 4GB and 128GB sata drive

Nice setup, I think the Pi4 might be 64bit? Can't quite remember. I used to try and use MongoDB before InfluxDB but that was terrible, it would crash and corrupt the db well below its supposed max db size.

I'm moving away from my 2 Pi's because I found myself with a spare, slightly older laptop. 8gb i5. Running in low-power mode with the screen off. The fans never spin up so it is just as quiet as a Pi but a lot more powerful even than a Pi4.

@877

I need to look at the data aggregation, I was thinking for weather maybe 5min revolution for 7 days, then hourly for 12 months, then daily averages indefinitely.

Well, that would do it. Just remember to check in to see how the device is performing every year or so - Node-RED and InfluxDB are generally so stable that I often forget.

@877

can’t think of any text values

Your location names. You need those. You might also want to record the sensor name, that is really helpful when one is playing up. Also helpful when integrating external services - I integrate data from weather services for example so the service name is the sensor name in that case.

From my own db:

[877]

I think it is 64 bit but the operating system isn’t. But it’s nice and small, a laptop sound ideal.

@TotallyInformation

Your location names. You need those.

Ah yes of course, it’s a lot to remember at the beginning

Thanks very much for the examples, looks nice. Will take me a while to get there, some more reading to do.

Thanks again!