^(almost) murphy.de

TL;DR If you want to truncate a SQLite table, use DELETE FROM table; VACUUM.

While cleaning up my blog (backend, frontend and everything) for the new year, I noticed that the SQLite database I use to store its data had become quite huge — 28 MB huge to be exact. Since I could not dream of having written this much text, I had no idea what happened.

Since I despise writing SQL to inspect tables, I first searched for a simple, free graphical SQLite client (for OS X). No success in the Mac App Store. What I found then was a Firefox plugin called “SQLite Manager”. It’s horrible (Windows 3.1 style icons and poor interface design), but it did its job: After it hung up when I tried to look at the “sessions” table (connecting over SFTP wasn’t such a good idea), the culprit was found: ActiveRecord was storing sessions for each visitor without ever cleaning them up.

*sigh*

So, back to SQL:

$ sqlite3 murfy.db
sqlite> SELECT COUNT(*) FROM sessions;
269194

Oops. Now, how to get rid of these?

sqlite> TRUNCATE sessions;
Error: near "TRUNCATE": syntax error

Not quite. That’s a MySQL command. Back to basic SQL:

sqlite> DELETE FROM sessions;
sqlite> SELECT COUNT(*) FROM sessions;
0

Yay! That should have crushed the file into a reasonable size…

$ ls -l murfy.db
-rw-rw----  1 murphy www-data 28281856 2013-01-05 22:54 murfy.db

The fuck! It didn’t get one bit smaller. Seems there’s some non-optimizing optimization going on here: DELETE — The Truncate Optimization says:

When the WHERE is omitted from a DELETE statement and the table being deleted has no triggers, SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually. This “truncate” optimization makes the delete run much faster.

…and a bit pointless. How do I turn that off?

The truncate optimization can be permanently disabled for all queries by recompiling SQLite with the SQLITE_OMIT_TRUNCATE_OPTIMIZATION compile-time switch.

Eat camel poo, you son of a lazy person!

Now usually, the tool that broke it can be used to fix it. Stack Overflow to the rescue:

Q: The problem is that the size of the database file (*.db3) remained the same after it had been cleared.

A: The VACUUM command rebuilds the entire database. There are several reasons an application might do this: 1. Unless SQLite is running in “auto_vacuum=FULL” mode, when a large amount of data is deleted from the database file it leaves behind empty space, or “free” database pages. This means the database file might be larger than strictly necessary.

“might be larger than strictly necessary” — understatement of the (quite young) year.

$ sqlite3 murfy.db VACUUM
$ ls -l murfy.db
-rw-rw----  1 murphy www-data   289792 2013-01-05 23:09 murfy.db

Problem solved. I think the blog is even running faster now.

I should have found out how to enable this “auto_vacuum=FULL mode”, but I rather used my time to write this blog post. Happy New Year!