In what order should I call ANALYZE and VACUUM in an SQLite3 database? What is the difference if there is any? My use case is to create a database for lookup, so I fill it with data, call ANALYZE and VACUUM and then never modify the database again.
Insight about whether the order matters for other databases like PostgreSQL and others is also appreciated.
Analyze creates system tables (named sqlite_stat? (where ? is a number)) that are used, if they exist, by the query planner when a query is run to try to optimize queries.
Analyze has little bearing on what VACUUM does, which is akin to defragmentation of a disk drive, in that it reloads the database freeing unused space (pages).
In your case, I don't think it matters which is run first as the VACUUM is unlikely to reduce the size of the sqlite_stat? tables. However, in theory VACUUM should be run last. If you are providing a read only database then you should be running both prior to putting the database into it's final place.
