When you’re tweaking SQLite for performance, the page_size is one of the most impactful settings, but most people don’t realize it’s not just about making pages bigger for fewer I/O operations.
Let’s see it in action. Imagine a database with a page_size of 4096 bytes, storing a million rows of simple integer data. A typical SELECT query might look like this:
SELECT COUNT(*) FROM my_table WHERE some_column = 12345;
If some_column is indexed, SQLite will traverse the B-tree. Each node in the B-tree is a page. A page size of 4096 means each node can hold a certain number of entries. If a page is too small, you might need to read more pages to find your data. If it’s too big, you might read a lot of unused space within a page.
The fundamental problem page_size tuning addresses is the trade-off between I/O efficiency and memory utilization, and how that trade-off aligns with your specific data access patterns. A larger page_size reduces the number of I/O operations for sequential reads and for fetching large amounts of data, as more data is read per disk seek. However, it also increases memory usage per page cache entry and can lead to wasted space if your rows are small and don’t fill up pages efficiently. Conversely, a smaller page_size can be more memory-efficient and might be better if your rows are very small and you’re doing a lot of random lookups where you only need a small fraction of a page’s contents.
To inspect your current page_size, you can run:
sqlite3 mydatabase.db "PRAGMA page_size;"
Let’s say it returns 4096. If you’re dealing with very large text blobs or wide rows and your workload is primarily sequential scans, you might consider increasing it. The allowed values are powers of 2, from 512 up to 65536.
To change it (for a new database, or to rebuild an existing one), you’d typically set it before creating tables or inserting data:
sqlite3 mydatabase.db "PRAGMA page_size = 8192; PRAGMA cache_size = -1000; CREATE TABLE my_table (...); -- other setup"
The cache_size PRAGMA is also crucial here. Setting it to a negative value means SQLite will use 1/cache_size of the available system memory for its page cache. For example, PRAGMA cache_size = -1000; tells SQLite to use roughly 1/1000th of available RAM. A larger page_size will consume more of this cache per page.
If your workload involves many small, independent transactions and you have limited RAM, a smaller page_size might be beneficial. For example, if you have tiny records and a highly concurrent workload with many readers and writers, a page_size of 1024 might be more appropriate:
sqlite3 mydatabase.db "PRAGMA page_size = 1024; PRAGMA cache_size = -2000; CREATE TABLE small_records (...);"
This reduces the memory footprint per page and potentially reduces contention on the page cache if pages are frequently updated.
The optimal page_size is often a function of your average row size and your dominant access pattern. If your rows are consistently larger than half your current page_size, increasing it could be beneficial. If your rows are much smaller than your page_size, you might be wasting memory and I/O bandwidth. You can estimate average row size by looking at your schema and typical data. For instance, if you have a table with a few INT columns and one VARCHAR(50), the overhead of a page_size of 4096 might be acceptable, but 8192 might start to show diminishing returns or even negative impact if most rows are only a few hundred bytes.
The most surprising true thing about page_size is that it can be changed on an existing database, but it’s not a simple PRAGMA command that alters the file in place. To effectively change the page_size of an existing database, you must dump its contents and then reload them into a new database file created with the desired page_size. This is done using the .output and .read commands within the sqlite3 shell, or programmatically by reading all tables and writing them to a new file.
# Example for changing page_size on an existing db
sqlite3 old_db.db ".output dump.sql" ".schema" ".dump"
sqlite3 new_db.db "PRAGMA page_size = 8192; PRAGMA cache_size = -1000; .read dump.sql"
mv new_db.db old_db.db # Replace the old with the new
This process effectively rebuilds the database, reorganizing all pages according to the new size. The key here is that SQLite’s page structure is fixed at creation time and doesn’t dynamically reallocate pages to different sizes.
After tuning your page_size, you’ll likely want to look at how SQLite manages transactions and concurrency.