Using SQLite

Name

cache_size — Set the size of the database page cache

Common Usage

PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;

Description

The cache_size pragma can get or temporarily set the maximum size of the in-memory page cache. The pages value represents the number of pages in the cache. Normally, each attached database has an independent cache.

Adjustments made by the cache_size pragma only persist for the lifetime of the database connection. A common use of this pragma is to temporarily boost the cache size for I/O intensive operations. When building a new index on a large table, raising the cache size (sometimes as much as 100× or even 1,000× the default size) can often result in a considerable performance increase.

Care must be taken when using extremely large cache sizes, however. If the cache size is so large that the cache grows to exceed the available physical memory, the overall performance is likely to be much lower than simply using a smaller cache. The actual amount of memory used by the cache is determined by the size of the database pages (plus some overhead) and the cache size (in pages).

The built-in page cache has a default size of 2,000 pages and a minimum size of 10 pages. The full data cache is not allocated immediately, but grows on demand with the cache size acting as a limiter on that growth. If the cache size is made larger, the limit is simply raised. If the cache size is made smaller, the limit is lowered but the cache is not necessarily immediately ...