How To Compress Your SQLite 3 Database With VACUUM

Sqlite is a database engine used in small projects because it is easy to manage (No import exports, just upload the file and you are done). Though mysql is recommended for bigger projects because it is faster and feature rich than sqlite.

If your sqlite database is bigger than 10 megabytes, you cannot use it in infinityfree. If you want to still use sqlite database, and not switch to mysql, you can try compressing it.

Just execute the following SQLite command and you database will be compressed!:

VACUUM;

To enable auto-vacuum:

PRAGMA auto_vacuum=FULL;

If still your database is bigger than 10MB, you should consider about switching to mysql.

I will say that I like SQLite for some applications. But these are mostly simple, light weight applications with little traffic. Especially in scenarios where setting up a full fat MySQL server is overkill.

But one statement surprises me:

What are you basing this on?

I would argue the opposite for most intents and purposes.

Please do note that switching from SQLite to MySQL is not trivial. Database dumps from one database system can’t be imported in another one just like that. They need to be converted. And if you’re writing your own SQL queries, those probably won’t work either on another database system just like that.

So if you’re building an application and anticipate you may need to switch to MySQL in the future, you’ll safe yourself a massive headache down the line if you use MySQL right away.

4 Likes

I agree. I’ll edit the post.

Well, yes I thought that they were faster because they were faster on my machine, but I’ll remove that.

Yes it’s hard. I know. So you should use mysql if you your project is big

Hmm, I’ll just remove these unnecessary stuff and only put the needed part.

Thank you for pointing out.

1 Like