Server - Table events grow constantly

[luckyguido]

Operating system

Linux

Joplin version

Server - v2.13.3

Desktop version info

Server Joplin v2.13.3 installed on docker with PostgreSQL

Current behaviour

Table events has milion of records, and grows every second.

here's an example of the latest line on my DB, as a workaround can oldest record can be deleted from this table or it will brake something on server sync?
I have 15MB of notes (attachments included) and 400MB of events table

Expected behaviour

Events table should be deleted by a rolling windows

Logs

No response

[laurent22]

Yes there should be something to clean this up. In the meantime you can run a query such as DELETE FROM events WHERE created_time < SOME_TIMESTAMP. Where SOME_TIMESTAMP is old enough such as a week ago, and it's in milliseconds.

[McOrvas]

Hello, I have a problem with a continuously increasing database, too. I have already opened a thread in the forum two weeks ago, but without any answers yet: https://discourse.joplinapp.org/t/joplin-server-sql-dump-increases-every-day/35542

I don't know if it's the same reason, but I suspect it is. I hope it's okay if I reuse my original text here:

Operating system
Linux

Joplin version
Server 2.13.3 / 2.14.2 with PostgreSQL in container

Hello everyone, I am using the Joplin App on different devices (Linux, Windows, Android) with syncing to Joplin Server (v2.13.3 / v2.14.2), which uses PostgreSQL for storage. Every night a backup script runs which executes pg_dumpall. Everything works fine and as intended, but the size of the SQL dump increases every day by approx. 1,2 MB without adding any new content from user side. Even during vacation with no syncs it increases. If I stop the Joplin Server container but keep PostgreSQL running, the dumps are identical every day. So Joplin Server adds a lot of data to the database every day without user interaction. For example: The today's SQL dump has 154,6 MiB, the complete Backup with the desktop app exports a JEX file with only 19,6 MiB. The web frontend of Joplin Server reports "Total size 29.2 MB (0%)". This ~1,2 MB per day are not a problem for the local storage, but generates unnecessary data for further online backup of the dumps.

Therefore my questions are:

• What kind of data is written to the database by the Joplin Server without user interactions / synchronization processed?
• Is it possible to deactivate this behavior?
• Is it possible to clean up the database with Joplin Server to delete the (presumed) data garbage?

Thanks a lot for your support!
McOrvas

[McOrvas]

In the meantime I have created a cron job on the server with the SQL statement provided by laurent22 above. It works, but it would be better and less cumbersome if it was built right into the software.

Out of curiosity: What is the exact purpose of the event entries every second and why was the deletion period of one week chosen in the SQL example above?

[danepowell]

I'm assuming the purpose of the events table is to coordinate syncs. I'm guessing that if you delete events further back than the last sync date of any client, any events prior to that date will be lost, i.e. changes before that date won't be synced to an out-of-date client.

If that's true and you ensure all your clients are synced, it should be safe to delete nearly all events (certainly anything older than 1 week).

But again, these are all complete assumptions based on a general understanding of server/client synchronization and not Joplin specifically.