Sqlite3 slow commit solution

Sqlite3 is a great database to work with prototyping or simple web application. Recently i notice that insert to database is slow event if only inserting 1 – 10 rows. Turn out i need to change PRAGMA synchronous setting of the database which is default to 2 or FULL. In FULL mode database will issue sync command to the disk for each transaction to ensure that data is safely written. This in some case is slow.

In a modern world that a power failure is rare we dont need that all the time sync to disk things. To minimize the sync we can set to 1 or NORMAL, so sqlite will sync less often than FULL mode.

To set to NORMAL mode, execute sql command below.

PRAGMA synchronous = NORMAL;


PRAGMA synchronous = 1;