Hey Tom,
Some of the disk space is taken up by the indices, so it's not a
straight linear relationship between entries and database size.
IIRC, aside from the DB journal, SQLite stashes all the information in
the file: table + indexes.
If you're really concerned about disk space, it certainly makes sense
to drop unused columns.
I think you mean that for someone else. :).
Cheers!
---
pablo
On 2025-11-17 14:46, Tom Keffer wrote:
Until V4.0 (released 30-Apr-2020), the default database was what we
call the "wview" database, with 49 observation types. After that, the
default became the "wview_extended" database with 122 entries. That's
over twice the size.
Some of the disk space is taken up by the indices, so it's not a
straight linear relationship between entries and database size.
If you're really concerned about disk space, it certainly makes sense
to drop unused columns.
-tk
On Mon, Nov 17, 2025 at 11:03 AM Pablo Sanchez
<[email protected]> wrote:
Hi Vince,
Everything is in memory eh? The only question is how much of it. ;)
The DBMS either performs an in-memory I/O (logical I/O) or a
physical I/O followed by a lio.
As for memory leaks, it's doubtful that it is an O/S issue but
more than likely a user land leak. Ah, in fact, you mention that
it may be related to a Python library (user land).
I'm curious why John's archive table is large compared to yours.
Perhaps the MariaDB data types are different than sqlite.
Side note: I mentioned elsewhere, I'm very new to weewx (June of
this year). I thought about using Sqlite rather than MariaDB. In
the end, I thought that I could (eventually?) contribute by
ensuring that the queries had supporting indexes when needed. Not
all queries need indexing. I could use the MariaDB tools to
possibly fold in changes to Sqlite.
Thx!
---
pablo
On November 17, 2025 13:19:30 vince <[email protected]> wrote:
Disk is cheap. The db is not in memory so size of the db does
not matter.
Weewx uses a small amount of memory. I used to run it on an
ancient ARM box that had 128 MB of RAM. It ran fine.
The absolute amount of RAM you use depends on os and platform.
On a pi5 mine is around 110 MB RSS and 12 MB shared memory used.
If you watch for weeks you might see what looks like memory
leaking depending on your os, what actual graphing you are doing,
and what underlying python imaging library versions are
installed. This is python imaging library at fault under the
hood. Again this doesn't really matter, since the os handles it
anyway automatically.
For my sqlite3 db....
archive table is 439 MB in 1,866,971 records
the entire db is 501 MB
for a couple weeks short of 19 years data
We looked at the size of the archive table in detail back in
early 2019 (wow) during weewx 4.0 development and concluded it
was worth the flexibility to cook up the wview_extended bigger
schema rather than to try to try to needlessly optimize for size.
You could delete all kinds of columns that aren't used, but
it's not really going to save you anything worth the time to do
so, and you'll put yourself at risk of all kinds of side effects
if you delete things the various skins expect to be there
(example - Belchertown takes 'ages' to run its reports if you are
missing a couple columns that it expects)
On Monday, November 17, 2025 at 9:28:09 AM UTC-8 Pablo Sanchez wrote:
Hi Vince,
From John's findings, a single table (the archive table) went
from 1.1 GB to 420 MB - 62.7% decrease.
What I do not know is of your 500 MB, how many MBs is your
archive table contributing. Let us suppose that it is 1 MB -
you are 100% correct. Meh! OTOH, let us suppose it was 480
MB, reduced by 62.7% would be 179 MB. For small form factor
devices, that is sizeable: 500 - 480 + 179 = 199 MB.
For me, I have an old laptop with 8 GB of memory. weewx is
now in steady-state (up 15 days since last OS update) and I
use very little RAM (692MB). However, I *just* started using
WeeWX a few months ago.
I hope that helps!
---
pablo
On 2025-11-17 12:15, vince wrote:
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
<https://groups.google.com/d/msgid/weewx-user/615dea8a-e05f-46e6-84e1-f5e4cd54c377n%40googlegroups.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/05d7deb5-0d16-40c4-9b54-b4c861f0af18n%40googlegroups.com
<https://groups.google.com/d/msgid/weewx-user/05d7deb5-0d16-40c4-9b54-b4c861f0af18n%40googlegroups.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/19a9332bfc0.2848.44b74f35a7a433c111dbfaee4cca1c2d%40hillsandlakes.com
<https://groups.google.com/d/msgid/weewx-user/19a9332bfc0.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/CAPq0zEDRd-wT%2B56uik8cCp6q0%2BZKsZEgcf7yPc8SbcB0%2BabVMQ%40mail.gmail.com
<https://groups.google.com/d/msgid/weewx-user/CAPq0zEDRd-wT%2B56uik8cCp6q0%2BZKsZEgcf7yPc8SbcB0%2BabVMQ%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/495fc014-4015-40ef-812c-d104f53664cd%40hillsandlakes.com.
