1

Sqlite, contrary most SQL-compatible DB engine, is working directly on files instead over network sockets. It is also not very good in concurrency, beside that deletion operators are mostly advisory (i.e. do not free the places of the deleted entities).

That results that an sqlite database needs regularly be VACUUM-ed, at least those with regular modify or delete operations. Vacuum works by rebuilding the database file, without the free spaces in it. Needless to say, it is absolutely non-concurrent with anything, making it to a costly operation - we have downtime for the vacuum.

However, I think there should exist some indicator which compares the size of the database file to the size of the actual data in it.

Using this indicator, it could be said, do we need a vacuum-ing or not.

Does it exist?

peterh
  • 2,137
  • 8
  • 28
  • 41

1 Answers1

1

I do not think such indicator exists.

You can play with pragmas quick_check and integrity_check. They can show some issues with DB which can be solved by vacuuming. But they wont tell you specifically do you need do it or not.

Usually it is just empirically found time-period "How often should I vacuum in my particular environment".

You can also play with auto_vacuum and incremental_vacuum pragmas. They can, in theory, help if DB needs often defragmentation. But usability and effectiveness in your particular case can only be found by experimentation.

White Owl
  • 1,029
  • 3
  • 9