4

I use ogr2ogr (GDAL 3.2.1) to export some Spatialites out of Postgresql. As I was inspecting the exported data via the tools provided by Alessandro Furieri by using PRAGMA cache_size and PRAGMA page_size, I saw that each data has a cache_size of -2000 and page_size of 4096.

According to GDAL WIKI about Configuration Options, I treid to overrule the cache_size by setting the --config OGR_SQLITE_CACHE 8192 but the values of cache_size and page_size remain the same.

My command was:

ogr2ogr -f "SQLITE" {dest} {database} -sql {query} -nln {layer_name} -lco SPATIAL_INDEX=yes -nlt {geometry_type} -nlt PROMOTE_TO_MULTI --config OGR_SQLITE_CACHE 8192

Am I using the option correctly? Any idea?

3

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.

3
  • Many thanks for your detailed answer. I tried both options ogrinfo my_db.sqlite -oo PRELUDE_STATEMENTS="PRAGMA cache_size=4000" and ogrinfo -sql "PRAGMA PAGE_SIZE=8192" my_db.sqlite but the page_size and cache_size of the data remains still 4096 and -2000. As you said, there is apparently no way to change these defaults permanently other than compiling GDAL and SQLite by myself.
    – Arash Gh.
    Sep 24 at 7:07
  • 1
    It is possible to change the page_size but not with my original untested commands. With this command you can change and test that the the change really happened ogrinfo -oo prelude_statements="pragma page_size=8192;vacuum" -sql "pragma page_size" page.sqlite
    – user30184
    Sep 24 at 7:34
  • Thanks again. It worked.
    – Arash Gh.
    Sep 24 at 11:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.