VACUUM does nothing for the file size of database.sqlite?

Questions
7

So previously I only had DB_SQLITE_VACUUM_ON_STARTUP to true. So I tried setting EXECUTIONS_DATA_PRUNE and EXECUTIONS_DATA_MAX_AGE and shutdown and started up the container–twice in fact-- and my database.sqlite file still remained at 24GB. There were no errors that were listed in the docker logs so unfortunately no clues there.

Afterwards, as suggested, I tried manually running VACUUM. I did a little digging and found this issue: Missing VACUUM of SQLite3 database after deleting execution log · Issue #819 · n8n-io/n8n · GitHub

After installing sqlite3, I opened the database.sqlite file and ran the following commands listed in the issue:

sqlite> .tables
credentials_entity  migrations          workflow_entity
execution_entity    webhook_entity
sqlite> select count(*) from credentials_entity;
7
sqlite> select count(*) from execution_entity;
16287
sqlite> select count(*) from migrations;
5
sqlite> select count(*) from workflow_entity;
18
sqlite> VACUUM;

Now my database is much smaller, down to 1 GB thankfully.
-rw-r--r-- 1 thewiggles thewiggles 1065758720 Aug 17 12:01 database.sqlite

Not exactly sure what happened but at least those entries were marked for deletion and VACUUM did work manually. Hoping that with the environment variables and disabling some of the saved executions that the database won’t balloon to such an absurd size in the future. Thanks for the clues/support!

3 Likes