0

In PostgreSQL it is necessary to vacuum periodically to prevent data loss of very old data due to transaction ID wraparound. I am concerned that data loss might be an issue with SQLite3 databases as well if they are not vacuumed routinely.

Additionally, does the workload that the SQLite3 database experiences matter? I am currently thinking of using SQLite3 in a few scenarios including:

  • as a file format for a program where people might share files and use them across different machines
  • to store application settings
  • to store logs for an application which might log multiple times per second (queries on recent data might be performed every hour)

Also would the frequency of updates and deletes matter?

Preetpal
  • 172
  • 7

1 Answers1

1

VACUUM

  • removes fragmentation, so it helps when you have both lots of deletions and inserts, and many read-only queries that scan entire tables, and
  • frees unused pages, so it helps when you have delete lots of data, and have very few insertions afterwards.

But these are merely optimizations. Fragmentation typcially matters only on rotating disks, and freeing space is not necessary unless you're running out of space.

SQLite uses a different transaction locking mechanism (which is much simpler and faster, but not scalable) and does not require maintenance.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • I apologize for the way I phrased the question earlier. I have edited the question now to clarify my concern. I am aware of the performance and disk space benefits of vacuuming a database, but I do not know whether it is absolutely necessary to vacuum a SQLite3 database to prevent data loss or any other sort of corruption over a period of continuous usage (in the case where performance and disk space do not matter). – Preetpal Jan 22 '16 at 00:04