[Feature Request] Periodically (or on demand) perform sqlite PRAGMA optimize for large databases #517

New Issue

MrUnknownDE · 2026-04-05T16:20:19+02:00

MrUnknownDE commented

2026-04-05 16:20:19 +02:00

Originally created by @TapGhoul on 3/4/2025

Explain in detail what your suggested feature would be used for.
I've seen instances of vrcx with years worth of data which take a substantial amount of start-up time. Having the optimize pragma execute might be useful.

A one-time "force optimize" button to trigger optimize with mask 0x10002 might be nice in settings, and otherwise run with the bitmask 0xfffe (the default) on program startup/every day or so or so might be nice as this puts an analysis limit in play to avoid extended delays.

Alternatively, at startup you can do 0x10002 but set an explicit larger analysis_limit to allow for more aggressive but still limited cleanup of larger DBs without potentially hanging vrcx for an extended period. Though given the kind of write workload of sqlite. While the bit at 0x00010 does set an analysis limit automtically, I'd imagine you'd want to allow for a higher limit than it would normally pick, as the limit automatically picked is targeted to minimize disruption - less of an issue at startup.

This is not the same as VACUUM - vaccum does not actually perform performance-related optimizations to the database, it just frees up disk space by compacting tombstoned data. It will naturally improve performance a little, but optimize does far more work in actually tuning the DB.
SQLITE already

Information on the pragma can be seen at https://www.sqlite.org/pragma.html#pragma_optimize

Describe how it would look if it requires a UI.
Under advanced options, an aggressive "Optimize VRCX Database" button with a popup warning "This may take a while" that runs PRAGMA optimize=0x10002; which runs optimize on all tables and without an analysis limit.

It might also be useful to run the PRAGMA optimize(-1); op at startup, and if this shows anything significant, you show a popup (similar to how the update dialog shows) asking about optimizing the database one-time - if not, you just stick with the default gradual/limited optimize with a limit (bitmask 0x00012 or 0x10012) - however this may well be overkill.

Explain why people would want to use it.
Speeds up startup times and general performance for VRCX when the sheer amount of data stored is causing performance problems.

*Originally created by @TapGhoul on 3/4/2025* **Explain in detail what your suggested feature would be used for.** I've seen instances of vrcx with years worth of data which take a substantial amount of start-up time. Having the optimize pragma execute might be useful. A one-time "force optimize" button to trigger optimize with mask `0x10002` might be nice in settings, and otherwise run with the bitmask `0xfffe` (the default) on program startup/every day or so or so might be nice as this puts an analysis limit in play to avoid extended delays. Alternatively, at startup you can do `0x10002` but set an explicit larger `analysis_limit` to allow for more aggressive but still limited cleanup of larger DBs without potentially hanging vrcx for an extended period. Though given the kind of write workload of sqlite. While the bit at `0x00010` does set an analysis limit automtically, I'd imagine you'd want to allow for a higher limit than it would normally pick, as the limit automatically picked is targeted to minimize disruption - less of an issue at startup. This is not the same as VACUUM - vaccum does not actually perform performance-related optimizations to the database, it just frees up disk space by compacting tombstoned data. It will naturally improve performance a little, but optimize does far more work in actually tuning the DB. SQLITE already Information on the pragma can be seen at https://www.sqlite.org/pragma.html#pragma_optimize **Describe how it would look if it requires a UI.** Under advanced options, an aggressive "Optimize VRCX Database" button with a popup warning "This may take a while" that runs `PRAGMA optimize=0x10002;` which runs optimize on all tables and without an analysis limit. It might also be useful to run the `PRAGMA optimize(-1);` op at startup, and if this shows anything significant, you show a popup (similar to how the update dialog shows) asking about optimizing the database one-time - if not, you just stick with the default gradual/limited optimize with a limit (bitmask `0x00012` or `0x10012`) - however this may well be overkill. **Explain why people would want to use it.** Speeds up startup times and general performance for VRCX when the sheer amount of data stored is causing performance problems.

MrUnknownDE closed this issue

2026-04-05 16:20:19 +02:00

Sign in to join this conversation.

No Label Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Done Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature Feature

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github/VRCX#517