Summary:
- You can't change the default cache_size without compiling SQLite and GDAL by yourself.
- The cache_size can be altered per session with an open option.
- You can't change the default page_size without compiling SQLite and GDAL by yourself.
- Page_size can be changed afterwards with PRAGMA and VACUUM commands, but there should be no need to do that.
The PAGE_SIZE is set when the database is created and changing it requires that the database is re-written. User needs to VACUUM the database for changing the page size https://sqlite.org/pragma.html#pragma_page_size. SQLite documentation does not recommend to change the page size:
The default page size is recommended for most applications.
GDAL has no parameter for changing the page size for the new SQLite databases but you can use this ogrinfo command afterwards.
ogrinfo -oo prelude_statements="pragma page_size=8192;vacuum" my_db.sqlite
The only way to change the default cache_size is to compile the SQLite library by yourself. See https://sqlite.org/pragma.html#pragma_cache_size:
The default suggested cache size can be altered using the
SQLITE_DEFAULT_CACHE_SIZE compile-time options.
However, cache_size can be altered for a session https://sqlite.org/pragma.html#pragma_cache_size. I am not sure if the OGR_SQLITE_CACHE config option is doing that. If it does, you can only know it by running pragma cache_size within the same session where the change was done because all other sessions will use the default size. Another option is to use the open option PRELUDE_STATEMENTS https://gdal.org/drivers/vector/sqlite.html.
As the name suggests, open options have an effect on opening the database. If you create a new database with ogr2ogr they have no effect. The cache size should change on reading, example:
ogrinfo my_db.sqlite -oo PRELUDE_STATEMENTS="PRAGMA cache_size=4000"
BTW. If you want to use the SpatiaLite functions create your SQLite database with option -dsco spatialite=yes.
