[ENH] run `PRAGMA optimize` on startup

[codetheweb]

Recommended by SQLite docs to help the query planner make better decisions.

On most startups this should be effectively a no-op; but when users first upgrade to a version with this change or their amount of data substantially changes (increased/decreased by 10x at time of writing according to SQLite docs) this adds additional latency to startup.

Using this script to create a test database, I observed that the duration of PRAGMA optimize scales roughly linearly with database size:

• 1GB: 178ms
• 10GB: 6,054ms
• 20GB: 13,228ms

[github-actions[bot]]

Reviewer Checklist

Please leverage this checklist to ensure your code review is thorough before approving

Testing, Bugs, Errors, Logs, Documentation

• [ ] Can you think of any use case in which the code does not behave as intended? Have they been tested?
• [ ] Can you think of any inputs or external events that could break the code? Is user input validated and safe? Have they been tested?
• [ ] If appropriate, are there adequate property based tests?
• [ ] If appropriate, are there adequate unit tests?
• [ ] Should any logging, debugging, tracing information be added or removed?
• [ ] Are error messages user-friendly?
• [ ] Have all documentation changes needed been made?
• [ ] Have all non-obvious changes been commented?

System Compatibility

• [ ] Are there any potential impacts on other parts of the system or backward compatibility?
• [ ] Does this change intersect with any items on our roadmap, and if so, is there a plan for fitting them together?

Quality

• [ ] Is this code of a unexpectedly high quality (Readability, Modularity, Intuitiveness)

[HammadB]

Interesting. Can we expect any obvious/known performance benefits from this?