I spoke too soon, I changed the winddir and windgustdir fields to small ints, saving 2 bytes per value which may not sound like much but it adds up over time...
I also changed the humidity columns to be tiny ints as well... The above changes saved a further 20MB, and the archive table is just a little over 170MB in size... On Tue, 18 Nov 2025 at 01:05, John Smith <[email protected]> wrote: > I have always been hesitant to mess with the weeWX DB in case I broke it, > but I ended up modifying the archive table and made a custom schema to > match just in case. > > It turned out more fruitful than I expected as I dropped the columns that > were only full of NULLs. Worst case I need to re-add them and then the > NULLs will be re-added too. > > The extended schema has some interesting columns, such as 5 regarding > hail, but does anyone know of any hail sensors? > > The archive table went from 1.1GB down to 420MB in the process... > > The only problem after doing this was weeWX exited on start because I > didn't set a default value on the new columns I added, however after > setting default values weeWX was happy again. > > Then I changed the usUnits and interval columns from Int to tiny int, what > that means is instead of MariaDB allocating 4 bytes per value it only uses > 1 byte per value. I'm not sure setting those columns as ENUM type would > reduce things further, but the usUnits column only has the number 17 stored > and the interval column is always set to 5... > > It was then I noticed that nearly all the columns were of type double, > which seems like over kill to me as most of the time there really is only a > need for 1 decimal place, not 16... > > So I altered all the double columns and changed them to floats, since > doubles use 8 bytes per value verses 4 bytes for floats and this again > greatly dropped the table size down to 190MB all up this works out to be > almost 1/10th of it's original size. > > At this point I think that's the extent that I can take things and thought > others might like to know that trimming the archive table, without loosing > data, but recover a lot of storage space which will then also speed up > other things like backups and reduce the space needed to store backups. > -- You received this message because you are subscribed to the Google Groups "weewx-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/weewx-user/CAGTinV5%3DgG_GkeSucYVX%3DrQSP3%3DL5odJkqvfK%2BFQR3ZCRuVTkQ%40mail.gmail.com.
