SQLite vacuuming / fragmentation and performance degradation

Let's say I periodically insert data into a SQLite database, then purge the first 50% of the data, but I don't vacuum.

Do I have something like zeroed-out pages for the first 50% of the file now? If I add another batch of data, am I filling in those zeroed-out pages?

The manual mentions fragmentation of data:

Frequent inserts, updates, and deletes can cause the database file to become fragmented - where data for a single table or index is scattered around the database file.

VACUUM ensures that each table and index is largely stored contiguously within the database file. In some cases, VACUUM may also reduce the number of partially filled pages in the database, reducing the size of the database file further.

But it doesn't indicate that there's necessarily a performance degradation from this. It mostly hints at the wasted space that could be saved from vacuuming.

Is there a noticeable performance gain for data in strictly contiguous pages? Could I expect "terrible" performance from a database with a lot of fragmented data?

SQLite automatically reuses free pages.

Fragmented pages can result in performance degradation only if

• the amount of data is so large that it cannot be cached, and
• your storage device does seeks relatively slowly (e.g. hard disks or cheap flash devices), and
• you access the data often enough that the difference matters.

There is only one way to find out whether this is the case for your application: measure it.