Is it safe to delete null columns in database ?

[Conz]

I noticed there's a lot of unused columns in the database, is it safe to just remove those and maybe make it a little bit easier on weewx to do the updates every few minutes ?

[John Smith]

If the column only has NULLs you should first stop weewx then you can remove them safely.

Doing that alone shrunk my DB significantly.

On Sat, 28 Feb 2026 at 00:10, Conz <conse...@gmail.com> wrote:

I noticed there's a lot of unused columns in the database, is it safe to just remove those and maybe make it a little bit easier on weewx to do the updates every few minutes ?

--
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 weewx-user+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/weewx-user/33b7cce6-5d82-4231-803f-4357aa7927een%40googlegroups.com.

[DR]

This is nice to  know.

I think many years ago I asked if it would be of help to do so, being
sort of a minimalist and having had every byte is valuable drummed into
me in college.

I believe one of the experts suggested that the amount of saving in
storage space and perhaps time for WeeWx to do processing was
insignificant.


Are there some tests by those who have done so or  know how to do it
safely to give information on  how much, if any, saving happens?  Just
curious.  Dale

[Pablo Sanchez]

Hi Dale,

TL;DR

I would not bother. Disk space is cheap compared to your time and there would not be any measurable performance gains.

Details

These data sets are not large and the read/write rate is peanuts.

The default values for these NULL'able columns is NULL. At the time of INSERT, when a column value is not specified, the database engine will look at column constraints and if there are defaults, set them. For the transaction rate for this application, it would be a minuscule amount of time. Probably microseconds.

For storage, a NULL'able column does take much space but unless the entire table is constantly being read, the working-set of data will be in RAM.

Empirically, I have been running weewx since July 1st, 2025 (ish). Roughly nine months. The last time I updated my OS (I run a rolling distro), was 38 days ago. My "server" (it is a klunker laptop) has 8 GB of RAM. 5.1 GB is in use for buffers (kernel) and cache. I am only using 879 MB.

I forced a report generation and we can see disk usage is very low. I peakd at 77 IOPS (all writes).

01:32:24 PM       tps     rkB/s     wkB/s     dkB/s   areq-sz    aqu-sz     await     %util DEV
01:33:24 PM      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00 sda
01:33:27 PM      6.33     97.33      8.00      0.00     16.63      0.00      0.32      0.17 sda
01:33:30 PM     16.00      0.00    597.33      0.00     37.33      0.01      0.54      0.53 sda
01:33:33 PM      1.00      0.00    140.00      0.00    140.00      0.00      1.67      0.13 sda
01:33:36 PM     77.33      0.00    381.33      0.00      4.93      0.01      0.16      1.17 sda
01:33:39 PM      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00 sda

I am running MariaDB for no reason. Probably dumb of me. I should switch back to SQLite. :)

Cheers!

---
pablo

[DR]

That is in alignment from what I recall some time ago.


Database engines have gotten much smarter and do a lot which I'm no
longer aware of.  I recall the days when we packed 8 binary values into
one word and masked it to get what we wanted. Obviously won't work with
numeric values but shows the lengths we were taught to do in the infancy
of software.  And magic which can occur behind the scenes is often of no
concern to programmers now.  I recall when something termed "Rushmore"
techniques were used to speed indexed services after the first reference.

Thank you for sharing the info  you accumulated about speed and storage.

It keeps this forum fun and fact filled.   Dale

[Conz]

I just wanted to do this because I like a neat db in case i want to mess around with it myself. Just makes it a bit more clear when staring at a table when you see numbers and not just a lot of NULL.

Also I had hoped it would flatten out the cpu spike that it has every 5 minutes because it's just high enough to trigger the cpu fan to speed up and become audible :-)

But it didn't really make any difference.

If anyone else does decide to tidy up their db you might need this bit of sql as the day tables don't seem to get cleaned up by 'weectl database drop-columns'

But make sure to not execute the results blindly and take out the __metadata table. But this checks all the archive_day tables and sees if the corresponding column exists in archive.

Do be sure to stop/restart weewx.

```
mysql -N -B -e "
SELECT CONCAT('DROP TABLE \`weather\`.\`', t.TABLE_NAME, '\`;') AS drop_stmt
FROM information_schema.TABLES t
LEFT JOIN information_schema.COLUMNS c
ON c.TABLE_SCHEMA = 'weather'
AND c.TABLE_NAME = 'archive'
AND c.COLUMN_NAME = SUBSTRING(t.TABLE_NAME, CHAR_LENGTH('archive_day_') + 1)
WHERE t.TABLE_SCHEMA = 'weather'
AND t.TABLE_NAME LIKE 'archive_day\\_%' ESCAPE '\\\\'
AND c.COLUMN_NAME IS NULL
ORDER BY t.TABLE_NAME;
" > drop_archive_day_orphans.sql``` 

Op vrijdag 27 februari 2026 om 19:59:47 UTC+1 schreef DR:

[DR]

Interesting.

I assumed that the spike in activity would occur with any intense
computational activity.

Do  you have timing data on how much shorter the process was?


And what was the size change in your database before vs. after? Dale.

[John Smith]

On Sat, 28 Feb 2026 at 05:23, DR <daleea...@gmail.com> wrote:

I believe one of the experts suggested that the amount of saving in
storage space and perhaps time for WeeWx to do processing was
insignificant.

Disk only part of the picture, at least for me, as getting the database disk size down also decreases wasted memory that's caching NULLs  and I also use an ARM64 CPU which has it's own limitations.

Here's the thread where I wrote about this very topic:

https://groups.google.com/g/weewx-user/c/nLIdzwMGgl8/m/tXF1Y8qlAAAJ

At the end of tuning my database I reduced it's size to almost 1/10th with no loss of information.

[John Smith]

On Sat, 28 Feb 2026 at 05:41, Pablo Sanchez <pa...@hillsandlakes.com> wrote:

These data sets are not large and the read/write rate is peanuts.

I beg to differ, after collecting almost 20 years of data at 5 minute intervals mine had grown to over 1.1GB, after tuning it, I got it down to ~170MB, I wouldn't call that peanuts.

[John Smith]

On Sat, 28 Feb 2026 at 05:59, DR <daleea...@gmail.com> wrote:

one word and masked it to get what we wanted. Obviously won't work with
numeric values but shows the lengths we were taught to do in the infancy

That's the second part of my tuning, by default decimal numbers were being stored as doubles which take 8 bytes and allow 16 decimal places of precision when floats only use 4 bytes since only one decimal place is needs.

I also switch other columns like usUnits and interval to be tinyints which only use 1 byte compared to 4 bytes for an int. 

[Vince Skahan]

Still not understanding why bother than for the joy of fiddling….

My 4 gb pi5 runs two pip weewx sqlite instances (I’ve run 5) and over a half dozen containers for Home Assistant, MQTT, Unifi controller, ZWave, pihole, etc.  It doesn’t even breath hard doing that. All kinds of extra capacity.

I don’t even look at memory usage or cpu or i/o because the os handles it for me.

The defaults are fine and stable on thousands of systems including tiny ancient boxes. Heck I ran for years on a arm32 box with only 64 MB memory. It went literally years without rebooting,

As long as the skins process in a few seconds no need to super over-optimize here…

[DR]

Vince.

I agree and only recently looked at the RaspOS commands to see if there
were a 'task manager' like command to see what was going on with CPU 
usage, etc.

My Pi5 fan comes on very rarely.

And the rock solid stability of Rasp OS along with the WeeWx being free
from bugs which cause shutdowns is also, in my my mind, legendary.  I
have a 4.7 system running great on a Pi3 for over a year.


But to answer the question as to why:  Two guesses come to mind. I grew
up at a time when minimizing CPU and memory were a higher grade on any
coding you did for the professor.  That mind set is hard to abandon,
just like frugality that my mother instilled in us kids.  I still turn
the lights off when I leave a room.


The second thing is some sort of basic personality that tinkers with the
old cars and gets one more horsepower out of the engine or getting the
rough idle out of the carb.  Just bragging rights or a feeling of a job
well done.


Even if the vehicle we're given (WeeWx) is as good as it is.

Dale

(still having a  lot of fun.  thanks for the tools to do it with).

[DR]

Vince,

I've read some of your observations and you are absolutely right. 
Disk/SSD is cheap.  The processors are very snappy and barely get warm. 
No Cray needed to keep things running smoothly.

I grew  up at a time when some of my classmates were working with
space-hardened hardware and exceptionally limited memory requiring very
careful design analysis of the project and storage, and cpu energy was
allocated carefully.  Weather Satellite stuff.  And those who got  the
jobs to work on those U of Wis projects were the cream of the crop at
squeezing performance and space out of what they were given.  The
bragging rights for having served on those projects was what drove many
of them.  The rest of us went on to other jobs.


That being said, a leopard has a hard time changing its spots.  I still
re-use plastic bags and anti-static material, just an old habit that I
feel happier doing.

I have no ability to tweak the database as some of those here are doing,
but am interested in the basic science of doing so.


There was a fellow by the name of Steve Gibson who did almost everything
in assembler, even doing some Windows API stuff just to show it could be
done.  A few minutes with the Windows toolkit got months of his work
done.  He just did it for fun.


The development team keeping WeeWx fun and interesting and decide the
future are not burdened with hacking every compound word.  You guys just
make new things happen and keep the stuff we  use working incredibly
well, to which I say thank you very much. I'd rather  you do that than
try to please a small handful who want a few kilobytes smaller database.

[Vince Skahan]

Fiddlin’ around can’t break anybody else of course. Go appropriately wild…but if you break it you bought it :-)

A play system or ‘pip’ instance running the Simulator driver is the way to do so safely.

No PDP8 assembly language nor punch cards for a FortranIV program required….

[John Smith]

On Sat, 28 Feb 2026 at 10:41, Vince Skahan <vince...@gmail.com> wrote:

Still not understanding why bother than for the joy of fiddling….

Not all of us want to run weeWX on the latest and greatest hardware, not to mention what the AI rush is doing to memory, SSD and CPU prices at present and into the future.

Further more not all of us are running a dedicated system just for weeWX, so a different mind set is needed.