Joplin Server sqlite db size

[benjp]

Operating system

Linux

Joplin version

3.0.1

Sync target

Joplin Server

What issue do you have?

Hey all,
I self-host Joplin server and backup the sqlite.db nightly. I've noticed that the DB size seems to keep increasing way out of step with the size of my notes. If I log into the Joplin Server admin console, I can see that the total size for my 2 users is about 250MB combined, but sqlite.db is 778MB. It seems to grow by a couple of MB a day.

Initially I thought I might just be creating more data than I realized, so I set up a nightly jex backup from Joplin Desktop. Over the last month, the jex files have gone from 94.0MB to 94.1MB.

Does anyone know what the cause could be? Is there some sort of scheduled maintenance I should be running on Joplin Server or sqlite?

Cheers,

Ben

[0x2A]

I am just guessing here, but could it be previous versions of notes?
joplin has a note history feature and maybe that eats up a lot of spaces.
also check your conflicts "folder" to see if there's something there eating space

[benjp]

Thanks for the ideas. It's not conflicts (I resolve/delete those when they crop up). I could be previous versions (I edit the same few notes many times) but I'd have expected that to come up as part of the "total size" on the user admin page.

I guess I could dig through sqlite and see exactly where the data is.

[benjp]

I queried my sqlite db and it looks like the events table is the culprit, and found this related issue on github: Server - Table events grow constantly · Issue #9931 · laurent22/joplin · GitHub. Someone was working on a PR to fix it, but it seems to have been closed.

Edit:
In case anyone else is interested, here's what I did. It feed up about 400MB for me.

$> docker stop container joplin
$> cd /svr/joplin
$> cp db.sqlite3 db.sqlite3.bak
$> sqlite3

You'll now be in a sqlite session:

sql> .open db.sqlite3
sql> select count(*) from events;

This gave me ~4m rows;

sql> select count(*) from events where created_time > 1727784000000;

That time is 1 Oct 2024 in epoc milliseconds (I used https://www.epochconverter.com/). This gave me ~85k rows.

Now we'll delete anything older than 1 Oct 24 (nb the change from > to <)

sql> delete from events where created_time < 1727784000000;
sql> vacuum; 

vacuum is needed to shrink the on-disk size of the DB fie after you've removed.

sql> .exit
$> docker container start joplin

I made sure the joplin server site was up, then synced a couple of my joplin apps. Everthing seemed fine so:

$> rm db.sqlite3.bak