SQLite

my database of choice for personal projects

Table of Contents

Performance

Once upon a time sqlite was limited to the memory capacity or to 2 GB of storage (32 bits) or other popular numbers... well, that was a long time ago.

Official limitations are listed here. Practically sqlite is likely to work as long as there is storage available. It works well with dataset larger than memory, it was originally created when memory was thin and it was a very important point from the start.

There is absolutely no issue with storing 100 GB of data. It could probably store a TB just fine but eventually that's the point where you need to question whether SQLite is the best tool for the job and you probably want features from a full fledged database (remote clients, concurrent writes, read-only replicas, sharding, etc...).

Database size

When you delete data from a SQLite database, the file size does not automatically shrink. This is because SQLite uses a mechanism called "auto-vacuuming" to reclaim disk space. By default, auto-vacuuming is enabled in SQLite, but it only runs when certain conditions are met, such as when a significant amount of data has been deleted.

To force SQLite to immediately reclaim disk space and reduce the file size after deleting data, you can run the VACUUM command. The VACUUM command rebuilds the database file, effectively removing any unused space.

VACUUM;

links

social