I Set mmap_size to 8 GiB and Four Libraries Returned Two Numbers

Every article on this blog lives in a single SQLite file called content.db:
the MDX files are rendered into it, and the server reads its own copy on every
page request.

I should say up front which copy I worked with, because every measurement in
this article ran against it. The data/content.db on my machine is a local
snapshot dated 15 September 2026: 54,743,040 bytes, 13,365 pages of 4,096
bytes, 2,498 records — 1,249 Turkish and 1,249 English. The repository holds
2,810 MDX files today, so this file is not current. The deployed copy on the
server is larger too: while writing the wal_checkpoint
piece
it came to 59,981,824 bytes. The file is not tracked in git either — it is a
build artifact, not a source.

For the measurements this does not matter; what I needed was a 52 MiB SQLite
file with a real schema and a real text payload, and that is exactly what this
is. But if you read the percentages below without knowing which file produced
them, you will learn the wrong thing. Hence the disclosure first.

One of the first things that comes to mind for speeding up a file like that is
SQLite's memory-mapped read mode: PRAGMA mmap_size. The idea is clean — map
the first N bytes of the database straight into your address space and SQLite
stops copying from the kernel buffer into a user buffer on every read.

So I was generous and asked for 8 GiB. The file is 52 MiB; 8 GiB covers it many
times over, with room to grow, I thought.

What I got back was not 8 GiB. I also got no error.

Four libraries, two ceilings

This Mac has four separate SQLite builds installed at the same time: the
operating system's own sqlite3 command, Python's sqlite3 module, Node's
node:sqlite module, and better-sqlite3, which is what the blog actually
uses. I wrote the same request to all four:

sqlite3 content.db "PRAGMA mmap_size=8589934592;" "PRAGMA mmap_size;"

Their answers:

Library	SQLite version	Build ceiling	Returned for 8 GiB
/usr/bin/sqlite3 (Apple)	3.51.0	1073741824	1,073,741,824
Python 3.13.3 · sqlite3	3.49.1	0x7fff0000	2,147,418,112
Node 22.16.0 · node:sqlite	3.49.1	0x7fff0000	2,147,418,112
better-sqlite3 12.9.0	3.53.0	0x7fff0000	2,147,418,112

Four libraries, two numbers. The sqlite3 that ships with the OS stops at
exactly 1 GiB; the other three stop at 2,147,418,112 — 64 KiB short of 2 GiB.
None of them prints a warning and none returns an error. The PRAGMA quietly
accepts a smaller number and carries on living with it.

The library will tell you its ceiling, if you think to ask:

sqlite3 ":memory:" "PRAGMA compile_options;" | grep MMAP
# DEFAULT_MMAP_SIZE=0
# MAX_MMAP_SIZE=1073741824

You can run the other three yourself; each is a one-liner:

python3 -c "import sqlite3;c=sqlite3.connect('content.db');c.execute('PRAGMA mmap_size=8589934592');print(c.execute('PRAGMA mmap_size').fetchone()[0])"
node -e "const{DatabaseSync}=require('node:sqlite');const d=new DatabaseSync('content.db');d.exec('PRAGMA mmap_size=8589934592');console.log(d.prepare('PRAGMA mmap_size').get().mmap_size)"

All three report MAX_MMAP_SIZE=0x7fff0000. Two builds, two different
compile-time constants.

Where the ceiling comes from

This number traces back to a #define chosen by whoever compiled SQLite. It
sits in src/sqliteInt.h in a block that branches on platform: the default is
0x7fff0000, or 2,147,418,112, for Linux, Windows, macOS, Solaris, FreeBSD and
DragonFly, and 0 for platforms not on that list. OpenBSD and QNX are
explicitly forced to zero in the block just above it.

Here is the detail that matters: macOS is on that list. SQLite's own source
gives macOS 2,147,418,112. In the build Apple ships with the operating system I
see almost exactly half of that — 1 GiB. This is not version drift either: the
three high-ceiling libraries on my machine span SQLite 3.49.1 through 3.53.0 and
all three keep the upstream value. The odd one out is neither the newest nor the
oldest version; it is the one with a different compiler behind it. (These are
the versions on this machine; SQLite's current release is further along, but
nothing changed on the mmap side through 2026.)

The compile-time constant is not the whole story, though, and the gap bears
directly on the checklist. The documentation says the same ceiling can be
lowered at startup through sqlite3_config(SQLITE_CONFIG_MMAP_SIZE): the
hard upper bound cannot be raised above the compile-time setting, but it "can be
reduced or zeroed." So whoever embeds SQLite — a language runtime, a framework,
a wrapper — may have tightened the limit somewhere you never look. And
compile_options will not show it: that prints the compile-time constant, not
the limit in force. The effective ceiling is the smaller of the two. Which is
why the only reliable question stays the same one: write it, then read it back.

On the default side all four agree: DEFAULT_MMAP_SIZE=0. The comment in the
source states this as a rule rather than a platform detail — mmap is off by
default everywhere. So when you hear "SQLite already uses mmap," know that in
most installations it does not. If you did not turn it on, it is not on.

A silent cut is not an error, it is a partial mapping

Once I understood that hitting the ceiling produces no error message, the
question that stuck with me was: how much does a truncated request actually
cost? The documentation says it plainly — the mapping covers the first N
bytes of the file. The rest arrives through the normal read path. So the
ceiling behaves less like a door and more like a ratio.

So I ran it against my own database. I took a copy of content.db (I did not
touch the live file), squeezed the page cache down to 2 MiB so the scan could
not fit in it, and ran a scan that walks the entire body_html column. Six
mmap_size values, three separate runs, eleven rounds per run; the figure below
is the median of the run medians:

Request	Share of file that can be mapped	Median scan	vs. off
0 (off)	0%	34.4 ms	—
8 MiB	15%	33.9 ms	−1.5%
16 MiB	31%	33.5 ms	−2.6%
32 MiB	61%	32.4 ms	−5.8%
52.2 MiB (whole file)	100%	31.1 ms	−9.6%
256 MiB	100%	31.1 ms	−9.6%

The gain follows the mapped share closely: map 15% of the file and you get 16%
of the full-mapping gain, map 31% and you get 27%, map 61% and you get 60%. The
middle row is four points off the line; I am printing all three, because
printing only the rows that cooperate turns a table from evidence into
decoration. The last two rows are an answer on their own: asking for the whole
file and asking for 256 MiB produced the same number, 31.1 ms. Once there
is nothing left to map, asking for more buys nothing.

The practical consequence: hitting the ceiling does not mean the setting failed.
Open a 12 GiB database with Apple's sqlite3 and ask for 8 GiB, and you get a
1 GiB window — less than a tenth of the file. The setting "works." It just does
not do as much as you assumed.

Where it helps and where it does not

The numbers above come from Python's library. The blog is actually served by
better-sqlite3, so the measurement ran again with that one, using the blog's
own two query shapes: a full scan (the script that builds the search index does
exactly this) and a single-record lookup (what an article page does). Three runs
each, thirteen rounds per run:

• Full scan: 23.6 ms → 20.6 ms, or −12.7%. Across the three runs the medians stayed between 23.5 and 23.8 ms with mmap off, and between 20.5 and 20.8 ms with it on. The two sets do not overlap at all; this is a clean win. I ran it on a readonly: true connection, the same way the blog opens the file in production — opening read-only does not disable mmap.
• 2,000 single-record lookups: here I cannot give you a number. In two of the three runs the mmap-on median came out around 34.5 ms against 37.4 ms with it off — but in the third run the mmap-on median jumped to 38.4 ms, which is above the off figure. When a measurement flips sign in one run out of three, what you are holding is noise, not an effect. The honest statement: for indexed single-record lookups no difference showed up that I would trust.

One more question that comes up: all of these numbers were taken on
content.db's journal mode, which is DELETE, while most installations run WAL.
Running the same scan against a WAL copy of the same file gave 32.5 ms down to
29.6 ms, or −8.9%. SQLite's mmap page talks only about the database file and
never mentions the WAL file or WAL mode; as far as these runs go, the two work
together and the gain stays in the same range.

That split is not surprising, and SQLite's own documentation points the same
way: the benefit is mainly in queries rather than writes, and it is not always a
benefit — the docs plainly say you can construct cases where performance drops.
What came out of the runs is consistent with the mechanism itself: the thing
being saved is copying, and copying only becomes visible when you move a lot of
bytes. A query that reads three pages and stops has no copying to save.

When I filled in sqlite_stat1 by hand and six query plans did not change at
all,
I ended up in a similar place: opening a setting's door does not mean there is
work waiting to walk through it.

Three traps

After I understood the mechanism, there were three edges I ran into while
measuring. All three are silent.

Writing -1 does not mean "unlimited." Per the documentation, a negative
value reverts the limit to the default. And the default — as we saw above — is
zero. So PRAGMA mmap_size=-1 does not enable mmap; it disables it. The
read-back proves it: on a connection set to 256 MiB, writing -1 gives back
0. I should add that this follows from the default being zero in all four of
my builds; on a library compiled with a non-zero SQLITE_DEFAULT_MMAP_SIZE, the
same -1 would enable mmap. A negative value means "go back to the default,"
not "no limit" — and what the default is depends on the build.

On an in-memory database the PRAGMA returns no row. Not zero — nothing. An
empty result set. A file-based database returns [{"mmap_size":0}], while
:memory: and temporary databases return []. On the Node side the cost of
that is immediate:

const mem = new DatabaseSync(':memory:');
mem.prepare('PRAGMA mmap_size').get().mmap_size;
// TypeError: Cannot read properties of undefined (reading 'mmap_size')

If you write a health check that reads the setting back — and you should, as we
are about to see — that line will blow up against the in-memory database in your
tests. A check that works in production and crashes in tests is a check people
stop trusting. Writing the line defensively costs nothing:

const row = db.prepare('PRAGMA mmap_size').all()[0];
const effective = row ? row.mmap_size : null;   // null on an in-memory db

Incidentally, every fresh connection reported a default of 0 — so the setting
belongs to the connection and is not stored in the database file. Setting it on
one connection does not set it on the others in your pool.

Changing the value later is not guaranteed. The PRAGMA documentation says
the value cannot be changed while the mapped region is in active use, and that
the pragma may end up a no-op while other work is running on the same
connection. I could not trigger that: after setting 256 MiB and running a query,
asking for 512 MiB really did move the value to 512 MiB, even with an open
cursor. So on my build and with this workload it was not a no-op. But if the
documentation will not guarantee it, neither will I: set it the moment you
open the connection, before the first query. A setting applied later is a
setting whose state you do not know.

The cost, in numbers: address space tracks the file, not your request

The most common reason people avoid this setting is "if I ask for 256 MiB I have
reserved 256 MiB of address space for nothing." The numbers say otherwise.

I ran the same scan under three settings and read the process's own ps numbers
before and after the scan. Increases, in KiB:

Setting	Effective value	VSZ increase	RSS increase
off	0	+1,888	+4,272
256 MiB	268,435,456	+55,360	+55,536
1 GiB	1,073,741,824	+55,360	+55,552

Asking for 256 MiB and asking for 1 GiB produce the same result: +55,360 KiB.
And that number has nothing to do with what I requested — it tracks the file.
Subtract the 1,888 KiB baseline and 53,472 KiB remains: 54,755,328 bytes, which
is the 54,743,040-byte file plus a few KiB. mmap_size is not a reservation but
an upper bound: SQLite maps what the file actually contains, and the ceiling only
says how far it is allowed to go. So writing a generous number to "leave room to
grow" costs you no address space. The only problem with a generous number is
that the number you think you set may not be the one in effect — which is what
this whole article is about.

There is a tension with the documentation here and I would rather not paper over
it. SQLite's mmap page says the limit applies separately to each database file,
so the total address space a process could use is "the mmap_size times the
number of open database files." My figure does not refute that: the doc gives a
potential upper bound, mine is what actually gets mapped. The two coincide
only when your files are bigger than the ceiling. In practice: with one 52 MiB
file the distinction is irrelevant, but with a connection pool where twenty
connections each open large files, your budget is file size times connection
count. I profiled a single-file, read-only setup; if yours is not that, do the
multiplication yourself.

The RSS side came out opposite to what I expected. With mmap off, the scan adds
4.2 MiB to the process's resident set; with it on, 54 MiB. The documentation says
mmap may need less RAM because it shares pages with the operating system's page
cache. My number does not contradict that, because the figure above is the
process's RSS and not system-wide memory: mapped file pages are shared, but they
still land in the process's column. The same physical pages do not exist twice;
they are just counted somewhere else now. I did not profile the system-wide
footprint, so I am not making a claim about it. But there is a practical
consequence: if you live with something that watches RSS — a cgroup limit, a
monitoring alert — this difference will catch its eye. One caveat on my own
caveat: cgroup v2's memory.current already includes the page cache, so mapped
pages are probably counted there anyway and the gap may be smaller than it
looks. Do not assume which counter counts what without checking — that is the
subject of this entire article. On a 50 MiB database none of it matters; on a
5 GiB one it is a budget conversation.

The real cost: an I/O error you cannot catch

Address space turned out to be cheap. Durability does not tell the same story.

SQLite cannot catch an I/O error on a memory-mapped file. On the normal read
path a bad sector comes back to you as an error code, your application handles
it, maybe retries. In mapped memory the same event becomes a signal, and if the
application does not catch it the process dies. So enabling mmap_size moves a
class of disk error out of the "handleable error" box and into the "process
death" box.

The practical part of that is being able to recognise it afterwards: a death
like this leaves no tidy error line in your application log. What remains is the
process manager's record — a line saying signal=BUS under systemd, exit code
135 in a container. If you go looking for a stack trace you will not find one,
because no exception was ever caught.

For a database that has backups, is opened read-only, and holds nothing but one
blog's content, that is a tolerable trade: worst case a process dies, systemd
restarts it, and I regenerate the corrupted file. I would not write that
sentence as comfortably for a service carrying several tenants' data in one
process — there, one read error takes down requests from tenants who never saw
the error.

Two more edges I do not hit but you should know. Windows cannot truncate a
memory-mapped file, so a VACUUM or auto_vacuum shrink there can fail
silently and leave unused space at the end of the file — though the docs add
that no data is lost and the space is reused as the database grows, so it is
delayed reclamation rather than a leak. On platforms where mmap is not supported
at all the PRAGMA is a silent no-op; OpenBSD and QNX are pinned to zero right
in the source. And the 2 GiB ceiling is itself a reminder: address space is
plentiful in a 64-bit world, and not on a 32-bit target.

If you are keeping count: every failure path in this mechanism is silent.

Where my own blog stands

After the measurement I looked at my own code. src/lib/content-db.ts opens
content.db read-only and never sets mmap. The file carries this comment:

readonly + migrate'te DELETE journal'a alındı → ekstra pragma gerekmez.

Read-only, and the migrate step moved it to a DELETE journal, so no extra pragma
is needed. The comment is correct in its own context — no extra pragma really is
needed for the journal mode. But "no extra pragma is needed" leads you somewhere
wrong if you do not know that a setting's default is zero. That line went into
the repository on 29 June 2026; since then, for more than four months, this blog
has served every page request with mmap off. And every time I passed through
that file, I read the comment and moved on thinking the settings were in place.

Now there are two jobs, and both follow from the measurement:

1. The script that builds the search index does a full scan, and that is where the −12.7% showed up. Setting mmap_size there pays for itself — right as the connection opens, with a value like 64 MiB that comfortably covers the file.
2. On the SSR path, which pulls a single record per page, no measurable gain turned up. I am not adding another setting there — for a gain that never appeared in the numbers, the only thing I would be carrying is the signal risk above.

Having to settle the two separately was the useful part: the setting stops being
"a good thing to switch on" and becomes a property of the workload.

Checklist

If you are considering mmap_size on your own setup, in this order:

1. Ask for the ceiling: MAX_MMAP_SIZE in PRAGMA compile_options;. Ask every library you use separately — the same machine can give different answers, and mine did. But treat that output as a starting point, not an answer: it will not show a limit lowered at startup via sqlite3_config.
2. Request it, then read it back. Write PRAGMA mmap_size=N; and then read what you got with PRAGMA mmap_size;. Only this tells you the limit actually in force; the compile-time constant does not. The gap between those two lines is this entire article.
3. Compare against the file size. If the effective value is smaller than the file, you have a partial mapping — expect a proportional share of the gain.
4. Set it before the first query, and do not rely on changing it later. The setting belongs to the connection, so a pool needs it set on each one.
5. Do not write a negative value. The -1 you read as "unlimited" reverts to the default, which on most builds means off.
6. Measure with your own workload. Scan or point query? The answer differs, and a single run does not give it to you — I ran every number at least three times and took the median, and for one of them the noise came out larger than the effect, so I dropped the number entirely.
7. Multiply if you open many files. The limit is per database file; every open database in a pool wants its own share.
8. Accept the signal risk, or leave the setting alone. A disk error in mapped memory is not a catchable error, and it leaves no clean log line behind.

Incidentally, the kernel side of mapping has its own "the number you asked for
is not the number you got" story; that one turned up while writing about
read_ahead_kb believing the
disk.

What I took away

This article is not really about a performance setting. We are talking about 3
milliseconds on a 52 MiB database; given my blog's load profile, that is an
improvement nobody will notice.

What actually stopped me was that writing 8 GiB and getting 1 GiB left no trace
anywhere. No log line, a successful return code, no objection from the PRAGMA.
I wrote the setting and believed what I wrote — exactly as I did in this blog's
wal_checkpoint counting 15,000 frames and moving none of
them.
Neither case involved an error; both involved an assumption of mine.

So I no longer count writing a setting as the job. Reading it back is. The line
you write records your intent; the number the system hands back records reality.
Until you measure the gap between them, that gap may be working against you —
and nobody is going to tell you about it.

Official Sources

• SQLite — Memory-Mapped I/O
• SQLite — PRAGMA mmap_size
• SQLite — Compile-time Options
• SQLite source — src/sqliteInt.h
• Node.js — node:sqlite