SQLite 3.49.1 PRAGMA changes stopped at a 1.6x INSERT gain

In SQLite 3.49.1, inserting 100,000 rows produced 534,050 rows/s with FULL+delete and 863,750 rows/s with OFF+memory.

The difference was 1.6x. I expected synchronous=OFF and journal_mode=memory to produce a much more dramatic gain, so the result was underwhelming. This was a single run on the night of June 9, 2026, using Python 3.12.10, with the machine recorded as ARM64.

The modest-looking gain was probably caused by how the rows were inserted. I used Python's standard sqlite3, called executemany, and committed all 100,000 rows as one transaction. Starting from a setup with few fsync operations leaves little headroom for lowering synchronous. If that condition is forgotten, the conclusion about PRAGMA settings can take on a life of its own. Was the commit unit, rather than the setting difference, the main factor?

Inserting in one transaction

I tried three configurations: FULL+delete, NORMAL+wal, and OFF+memory. I recorded INSERT seconds, INSERT rows/s, index creation, a point SELECT of 100 rows, and the final database file size.

import sqlite3

conn = sqlite3.connect(path)
conn.execute("PRAGMA synchronous=FULL")
conn.execute("PRAGMA journal_mode=delete")
conn.executemany("INSERT INTO t VALUES (?, ?)", rows)
conn.commit()

I am omitting the full script here. The point of this article is not the details of the Python loop or SQL statement, but how much the combination of synchronous and journal_mode changes. The condition that all rows were grouped into one transaction still matters considerably.

SELECT results are affected by cache state and page placement, so over-reading that one number would distort the comparison. INSERT is the main subject; SELECT and index_s are supporting checks. I should have examined the effect of the commit unit first, and the test design reflects that lesson.

The INSERT difference is visible, but not a change of order

The table contains the measured values as-is.

Comparing the slowest FULL+delete with the fastest OFF+memory, elapsed time fell from 0.187 seconds to 0.116 seconds. Rows/s rose from 534,050 rows/s to 863,750 rows/s, so the difference is noticeable. It still does not look like a win large enough to change the order of magnitude.

NORMAL+wal landed in the middle. At 0.153 seconds and 654,606 rows/s, it was faster than FULL+delete, and it was also behind OFF+memory. However, considering how much safety was given up, OFF+memory did not feel overwhelmingly faster. The PRAGMA names alone are not enough to draw a conclusion.

It would be wrong to read this as “SQLite PRAGMA settings do not matter.” This test already reduced the commit count to one. With one-row-at-a-time autocommit, FULL+delete would probably struggle more, but that case remains outside this table.

Do not make the 1.13 ms SELECT the main result

The 100-row point SELECT took 11.21ms with FULL+delete, 1.13ms with NORMAL+wal, and 7.86ms with OFF+memory. I expected little difference between configurations, so the 1.13ms value stood out.

Still, SELECT should not be the winner of this comparison. Each configuration created the SELECT index, and the row and query counts were the same. journal_mode primarily affects write safety and how logging is handled, so it would be unreasonable to attribute the point-SELECT difference directly to PRAGMA effectiveness.

Index creation should be treated similarly. index_s was 0.054 seconds, 0.038 seconds, and 0.065 seconds. That is not a large difference. NORMAL+wal was shortest and OFF+memory longest. Since that order did not match the INSERT ranking, it is more natural to regard these as values that include measurement variation. Short operations are easy to move around.

db_bytes stayed the same with a memory journal

db_bytes was 3,743,744 bytes for all three configurations. I had also misunderstood this until I laid out the values. Hearing journal_mode=memory makes it sound as if nothing remains in a file and the size should change. In hindsight, expecting the file size to change was a careless reading.

I corrected my understanding: a memory journal puts the rollback journal in memory; it is not the same as making the main database file memory-only. Since db_bytes here is the final .db file size, equality across all three configurations is natural.

If this distinction is missed, journal_mode=memory can be misread as “a mode that does not create a file.” In this JSON, there was no difference in final file size. The setting changes how the write-time journal is held, not the capacity of the main database.

The easy-to-miss factor is the commit unit

The most important condition in this measurement is that 100,000 rows were inserted in one transaction. Forgetting that leaves only the vague conclusion that synchronous=OFF helped less than expected.

One-row-at-a-time autocommit would probably produce a very different result. Synchronization would run on every commit, and FULL+delete should suffer more. But I did not measure it, so I will not add new numbers here. That is the first split I want to make in the next test.

The defensible statement from this measurement is that, when rows are grouped with executemany, pushing these PRAGMA settings produced only about a 1.6x difference. There were too few runs and too few conditions to establish a general limit for SQLite 3.49.1 or Python 3.12.10. It is also a record of a mistake, including my partial misunderstanding of what a memory journal means.

Measure before choosing the risky side

This result did not make me want to use synchronous=OFF everywhere. OFF+memory reached 863,750 rows/s, but FULL+delete still reached 534,050 rows/s. After trying it, I found the latter fast enough for a short batch. It was a single short run, so I do not want to oversell the ranking.

OFF+memory may be reasonable for temporary data that can be recreated if corrupted, such as an intermediate table from scraping or a local test database. For work logs or manually collected data that would be painful to lose, I would lean toward FULL+delete or NORMAL+wal.

Even after trading away safety, this test did not produce a dramatic win. This is less a question of whether SQLite is fast or slow than of how the transaction is split. Next I want to measure one-row autocommit and the median across multiple runs. Declaring the strength of these PRAGMAs from this one run would be unsafe. To understand why it stopped at 1.6x, I need to change the commit unit rather than keep talking from the setting names alone.