Feature request: Make sqlite parameters user-configurable

[ghost]

It would be great if certain sqlite parameters, e.g. cache_size were user-configurable (or alternatively, auto-adjusted / dynamic). These parameters usually depend on the current hardware and the type of work the given pb instance will execute.

[mlusetti]

I'm curious if anyone has done some benchmarks or tests about these settings.

[BrettFraley]

@mlusetti > I'm curious if anyone has done some benchmarks or tests about these settings.

SQLite is extremely robust by default and there is a lot of research available of it's capabilities and limitations,
however SQLite does provide many lesser utilized optimization, storage usage, and performance configuration techniques. Some features are here:

This document describes limitations of SQLite (the maximum length of a string or blob, the maximum size of a database, the maximum number of tables in a database, etc.) and how these limits can be altered at compile-time and run-time.
https://www.sqlite.org/limits.html

SQLite is safe to use in multi-threaded programs. This document provides the details and hints on how to maximize performance.
https://www.sqlite.org/threadsafe.html

Unlock Notify → The "unlock notify" feature can be used in conjunction with shared cache mode to more efficiently manage resource conflict (database table locks).

[mlusetti]

@mlusetti > I'm curious if anyone has done some benchmarks or tests about these settings.
[..]
Unlock Notify → The "unlock notify" feature can be used in conjunction with shared cache mode to more efficiently manage resource conflict (database table locks).

Hi Brett, I'm well aware of the capabilities of SQLite and have read some papers, even some you linked.
My question was more about what gains could these configurable settings bring to pocketbase usage. Mine was just curiosity if there's anyone who have done some measurements about it.

[cweagans]

If it's easier, I'd be interested in injecting my own sqlite connection into the bootstrap process. One use case would be configuring the sqlite params. Another would be to use something like https://github.com/psanford/donutdb or https://rqlite.io