SQLite Forum

autovacuum in sqlite VS autovacuum in PG
Login
Let me be the first to ask what PG is.  I'm going to assume PostgreSQL.  I don't know enough about PostgreSQL to discuss it.

SQLite autovacuum can shrink the file.  Since you ask, here's the short simplified version.

SQLite database files are made up of pages: blocks of a fixed length.  Each page is devoted to a specific table or index.  It is possible that an entire page would be released (e.g. DELETE FROM deleting lots of rows of a table, which would also delete lots of entries of an index).  It is also possible that part of a page would be released (e.g. DELETE FROM deleting one just row, which would also delete an entry in an index).  to release the maximum amount of filespace, you need to worry about both kinds of unused space.  To find out how autovacuum does this, read this:

<https://www.sqlite.org/pragma.html#pragma_auto_vacuum>

However, auto_vacuum can introduce unpredictable delays in operations, and this may be inconvenient to your program.  Instead you might prefer to do a VACUUM at a time when a delay doesn't matter.  VACUUM reconstructs the entire database (ignoring freed space) from the data in tables.  This means that not only can it free up filespace but it can also get rid of corrupted index data.

Come back to us if you have specific questions.