Pablo, I know you said you're a database guy, but does any of this really matter at all ?
My sqlite3 db is 500 MB in size after a couple weeks short of 19 years. I can live with that. It's stable. It's fast enough. It works. On Monday, November 17, 2025 at 9:06:17 AM UTC-8 Pablo Sanchez wrote: > One thought is to to ensure that the data types also cover metric. > > --- > pablo > > > > On 2025-11-17 11:15, John Smith wrote: > > I haven't touched indexes, just the number type on the columns.. > > I went about this by getting unique values for the column and then used > that to optimise the column. > > The only exception was the winddir column, it had decimal places, but a > fraction of a degree doesn't seem worth having. > > On Tue, 18 Nov 2025 at 02:51, Pablo Sanchez <[email protected]> > wrote: > >> That's impressive! >> >> The key is to ensure each data type for all Use Cases captures all data. >> >> On my mental to-do is to enable query tracing to ensure when needed, >> supporting indexes are available. >> --- >> pablo >> >> On November 17, 2025 10:18:13 John Smith <[email protected]> wrote: >> >>> 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 >>> >>> <https://groups.google.com/d/msgid/weewx-user/CAGTinV5%3DgG_GkeSucYVX%3DrQSP3%3DL5odJkqvfK%2BFQR3ZCRuVTkQ%40mail.gmail.com?utm_medium=email&utm_source=footer> >>> . >>> >> >> -- >> 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/19a9283a788.2848.44b74f35a7a433c111dbfaee4cca1c2d%40hillsandlakes.com >> >> <https://groups.google.com/d/msgid/weewx-user/19a9283a788.2848.44b74f35a7a433c111dbfaee4cca1c2d%40hillsandlakes.com?utm_medium=email&utm_source=footer> >> . >> > -- > 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/CAGTinV6Eq-R9p_5%2BgrMSRxByTXa-O2CQjCUersr-A2Mj9zy3_g%40mail.gmail.com > > <https://groups.google.com/d/msgid/weewx-user/CAGTinV6Eq-R9p_5%2BgrMSRxByTXa-O2CQjCUersr-A2Mj9zy3_g%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > > -- 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/615dea8a-e05f-46e6-84e1-f5e4cd54c377n%40googlegroups.com.
