Good-to-Know about SQLite

OGR supports SQLite and some SQLite based Formats (eg. GeoPackage, SpatiaLite, MBTiles and RasterLite) if compiled with SQLite3. SQLite has many advantages and some quirkes. Some of which are listed here.

Please contribute to this list if you know of additional problems or can help with the language''

Do not write on Network File Systems / Samba Shares!

"SQLite databases often do not work well over NFS, or some other networked file system protocols due to the poor support for locking. It is safest to operate only on SQLite files on a physical disk of the local system." (​OGR SQLite Documentation)

The "[...] locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS [...]."(​SQLite FAQ) See also ​SQLite Documentation.

Delete and Change Column Names

"SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table. If you want to make more complex changes in the structure of a table, you will have to recreate the table." (​SQLite FAQ)

R-Tree Spatial Index

How does the R-Tree spatial index work and what makes them difficult to use with spatial views? ​http://osgeo-org.1560.x6.nabble.com/gdal-dev-GeoPackege-views-and-spatial-index-td5312752.html

Journal Modes

SQLite supports different journal modes. The default is the rollback journal. Beginning with version 3.7.0 (2010-07-21), a new "Write-Ahead Log" option (WAL) is available. On one hand WAL is significantly faster in most scenarios and provides more concurrency. But on the other hand WAL does not work over a network filesystem, it is not possible to open WAL databases from read-only media (​SQLite Documentation on WAL).

Performance

See the section Performance hints at the bottom of ​OGR SQLite Documentation.