1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>Temporary Files Used By SQLite</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
123
<h1 align="center">SQLite's Use Of Temporary Disk Files</h1>
125
<h2>1.0 Introduction</h2>
128
On of the <a href="different.html">distinctive features</a> of
129
SQLite is that a database consists of a single disk file.
130
This simplifies the use of SQLite since moving or backing up a
131
database is a simple as copying a single file. It also makes
132
SQLite appropriate for use as an
133
<a href="whentouse.html#appfileformat">application file format</a>.
134
But while a complete database is held in a single disk file,
135
SQLite does make use of many temporary files during the
136
course of processing a database.
140
This article describes the various temporary files that SQLite
141
creates and uses. It describes when the files are created, when
142
they are deleted, what they are used for, why they are important,
143
and how to avoid them on systems where creating temporary files is
148
The manner in which SQLite uses temporary files is not considered
149
part of the contract that SQLite makes with applications. The
150
information in this document is a correct description of how
151
SQLite operates at the time that this document was written or last
152
updated. But there is no guarantee that future versions of SQLite
153
will use temporary files in the same way. New kinds of temporary
154
files might be employed and some of
155
the current temporary file uses might be discontinued
156
in future releases of SQLite.
159
<a name="seventypes"></a>
161
<h2>2.0 Seven Kinds Of Temporary Files</h2>
164
SQLite currently uses seven distinct types of temporary files:
168
<li>Rollback journals</li>
169
<li>Master journals</li>
170
<li>Statement journals</li>
171
<li>TEMP databases</li>
172
<li>Materializations of views and subqueries</li>
173
<li>Transient indices</li>
174
<li>Transient databases used by VACUUM</li>
178
Additional information about each of these temporary file types
182
<a name="rollbackjrnl"></a>
184
<h3>2.1 Rollback Journals</h3>
187
A rollback journal is a temporary file used to implement
188
atomic commit and rollback capabilities in SQLite.
189
(For a detailed discussion of how this works, see
190
the separate document titled
191
<a href="atomiccommit.html">Atomic Commit In SQLite</a>.)
192
The rollback journal is always located in the same directory
193
as the database file and has the same name as the database
194
file except with the 8 characters "<b>-journal</b>" appended.
195
The rollback journal is usually created when a transaction
196
is first started and is usually deleted when a transaction
197
commits or rolls back.
198
The rollback journal file is essential for implementing the
199
atomic commit and rollback capabilities of SQLite. Without
200
a rollback journal, SQLite would be unable to rollback an
201
incomplete transaction, and if a crash or power loss occurred
202
in the middle of a transaction the entire database would likely
203
go corrupt without a rollback journal.
207
The rollback journal is <i>usually</i> created and destroyed at the
208
start and end of a transaction, respectively. But there are exceptions
213
If a crash or power loss occurs in the middle of a transaction,
214
then the rollback journal file is left on disk. The next time
215
another application attempts to open the database file, it notices
216
the presence of the abandoned rollback journal (we call it a "hot
217
journal" in this circumstance) and uses the information in the
218
journal to restore the database to its state prior to the start
219
of the incomplete transaction. This is how SQLite implements
224
If an application puts SQLite in
225
<a href="pragma.html#pragma_locking_mode">exclusive locking mode</a> using
230
PRAGMA locking_mode=EXCLUSIVE;
234
SQLite creates a new rollback journal at the start of the first
235
transaction within an exclusive locking mode session. But at the
236
conclusion of the transaction, it does not delete the rollback
237
journal. The rollback journal might be truncated, or its header
238
might be zeroed (depending on what version of SQLite you are using)
239
but the rollback journal is not deleted. The rollback journal is
240
not deleted until exclusive access mode is exited.</p>
243
Rollback journal creation and deletion is also changed by the
244
<a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>.
245
The default journaling mode is DELETE, which is the default behavior
246
of deleting the rollback journal file at the end of each transaction,
247
as described above. The PERSIST journal mode foregoes the deletion of
248
the journal file and instead overwrites the rollback journal header
249
with zeros, which prevents other processes from rolling back the
250
journal and thus has the same effect as deleting the journal file, though
251
without the expense of actually removing the file from disk. In other
252
words, journal mode PERSIST exhibits the same behavior as is seen
253
in EXCLUSIVE locking mode. The
254
OFF journal mode causes SQLite to forego creating a rollback journal
255
in the first place. The OFF journal mode disables the atomic
256
commit and rollback capabilities of SQLite. The ROLLBACK command
257
is not available when OFF journal mode is set. And if a crash or power
258
loss occurs in the middle of a transaction that uses the OFF journal
259
mode, no recovery is possible and the database file will likely
263
<a name="masterjrnl"></a>
265
<h3>2.2 Master Journal Files</h3>
268
The master journal file is used as part of the atomic commit
269
process when a single transaction makes changes to multiple
270
databases that have been added to a single <a href="c3ref/sqlite3.html">database connection</a>
271
using the <a href="lang_attach.html">ATTACH</a> statement. The master journal file is always
272
located in the same directory as the main database file
273
(the main database file is the database that is identified
274
in the original <a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/open.html">sqlite3_open16()</a>, or
275
<a href="c3ref/open.html">sqlite3_open_v2()</a> call that created the <a href="c3ref/sqlite3.html">database connection</a>)
276
with a randomized suffix. The master journal file contains
277
the names of all of the various attached auxiliary databases
278
that were changed during the transaction. The multi-database
279
transaction commits when the master journal file is deleted.
280
See the documentation titled
281
<a href="atomiccommit.html">Atomic Commit In SQLite</a> for
286
The master journal file is only created in cases where a single
287
<a href="c3ref/sqlite3.html">database connection</a> is talking with two or more databases files
288
as a result of using <a href="lang_attach.html">ATTACH</a> to connection to auxiliary databases,
289
and where a single transaction modifies more than one of those
291
Without the master journal, the transaction commit on a multi-database
292
transaction would be atomic for each database individually, but it
293
would not be atomic across all databases. In other words, if the
294
commit were interrupted in the middle by a crash or power loss, then
295
the changes to one of the databases might complete while the changes
296
to another database might roll back. The master journal causes all
297
changes in all databases to either rollback or commit together.
300
<a name="stmtjrnl"></a>
302
<h3>2.3 Statement Journal Files</h3>
305
A statement journal file is used to rollback partial results of
306
a single statement within a larger transaction. For example, suppose
307
an UPDATE statement will attempt to modify 100 rows in the database.
308
But after modifying the first 50 rows, the UPDATE hits
309
a constraint violation which should block the entire statement.
310
The statement journal is used to undo the first 50 row changes
311
so that the database is restored to the state it was in at the start
316
A statement journal is only created for an UPDATE or INSERT statement
317
that might change multiple rows of a database and which might hit a
318
constraint or a RAISE exception within a trigger and thus need to
319
undo partial results.
320
If the UPDATE or INSERT is not contained within BEGIN...COMMIT and if
321
there are no other active statements on the same database connection then
322
no statement journal is created since the ordinary
323
rollback journal can be used instead.
324
The statement journal is also omitted if an alternative
325
<a href="lang_conflict.html">conflict resolution algorithm</a> is
332
UPDATE OR REPLACE ...
335
INSERT OR REPLACE ...
340
The statement journal is given a randomized name, not necessarily
341
in the same directory as the main database, and is automatically
342
deleted at the conclusion of the transaction. The size of the
343
statement journal is proportional to the size of the change implemented
344
by the UPDATE or INSERT statement that caused the statement journal
348
<a name="tempdb"></a>
350
<h3>2.4 TEMP Databases</h3>
352
<p>Tables created using the "CREATE TEMP TABLE" syntax are only
353
visible to the <a href="c3ref/sqlite3.html">database connection</a> in which the "CREATE TEMP TABLE"
354
statement is originally evaluated. These TEMP tables, together
355
with any associated indices, triggers, and views, are collectively
356
stored in a separate temporary database file that is created as
357
soon as the first "CREATE TEMP TABLE" statement is seen.
358
This separate temporary database file also has an associated
360
The temporary database file used to store TEMP tables is deleted
361
automatically when the <a href="c3ref/sqlite3.html">database connection</a> is closed
362
using <a href="c3ref/close.html">sqlite3_close()</a>.
366
The TEMP database file is very similar to auxiliary database
367
files added using the <a href="lang_attach.html">ATTACH</a> statement, though with a few
369
The TEMP database is always automatically deleted when the
370
<a href="c3ref/sqlite3.html">database connection</a> is closed.
371
The TEMP database always uses the
372
<a href="pragma.html#pragma_synchronous">synchronous=OFF</a> and <a href="pragma.html#pragma_journal_mode">journal_mode=PERSIST</a>
374
And, the TEMP database cannot be used with <a href="lang_detach.html">DETACH</a> nor can
375
another process <a href="lang_attach.html">ATTACH</a> the TEMP database.
379
The temporary files associated with the TEMP database and its
380
rollback journal are only created if the application makes use
381
of the "CREATE TEMP TABLE" statement.
386
<h3>2.5 Materializations Of Views And Subqueries</h3>
388
<p>Queries that contain subqueries must sometime evaluate
389
the subqueries separately and store the results in a temporary
390
table, then use the content of the temporary table to evaluate
392
We call this "materializing" the subquery.
393
The query optimizer in SQLite attempts to avoid materializing,
394
but sometimes it is not easily avoidable.
395
The temporary tables created by materialization are each stored
396
in their own separate temporary file, which is automatically
397
deleted at the conclusion of the query.
398
The size of these temporary tables depends on the amount of
399
data in the materialization of the subquery, of course.
403
A subquery on the right-hand side of IN operator must often
404
be materialized. For example:
408
SELECT * FROM ex1 WHERE ex1.a IN (SELECT b FROM ex2);
412
In the query above, the subquery "SELECT b FROM ex2" is evaluated
413
and its results are stored in a temporary table (actually a temporary
414
index) that allows one to determine whether or not a value ex2.b
415
exists using a simple binary search. Once this table is constructed,
416
the outer query is run and for each prospective result row a check
417
is made to see if ex1.a is contained within the temporary table.
418
The row is output only if the check is true.
422
To avoid creating the temporary table, the query might be rewritten
427
SELECT * FROM ex1 WHERE EXISTS(SELECT 1 FROM ex2 WHERE ex2.b=ex1.a);
431
Recent versions of SQLite (version 3.5.4 and later)
432
will do this rewrite automatically
433
if an index exists on the column ex2.b.
437
If the right-hand side of an IN operator can be list of values
441
SELECT * FROM ex1 WHERE a IN (1,2,3);
444
List values on the right-hand side of IN are treated as a
445
subquery that must be materialized. In other words, the
446
previous statement acts as if it were:
449
SELECT * FROM ex1 WHERE a IN (SELECT 1 UNION ALL
454
A temporary index is always used to hold the values of the
455
right-hand side of an IN operator when that right-hand side
460
Subqueries might also need to be materialized when they appear
461
in the FROM clause of a SELECT statement. For example:
465
SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;
469
Depending on the query, SQLite might need to materialize the
470
"(SELECT b FROM ex2)" subquery into a temporary table, then
471
perform the join between ex1 and the temporary table. The
472
query optimizer tries to avoid this by "flattening" the
473
query. In the previous example the query can be flattened,
474
and SQLite will automatically transform the query into
478
SELECT ex1.*, ex2.b FROM ex1 JOIN ex2 ON ex2.b=ex1.a;
482
More complex queries may or may not be able to employ query
483
flattening to avoid the temporary table. Whether or not
484
the query can be flattened depends on such factors as whether
485
or not the subquery or outer query contain aggregate functions,
486
ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth.
487
The rules for when a query and cannot be flattened are
488
very complex and are beyond the scope of this document.
491
<a name="transidx"></a>
493
<h3>2.6 Transient Indices</h3>
496
SQLite may make use of transient indices to
497
implement SQL language features such as:
501
<li>An ORDER BY or GROUP BY clause</li>
502
<li>The DISTINCT keyword in an aggregate query</li>
503
<li>Compound SELECT statements joined by UNION, EXCEPT, or INTERSECT</li>
507
Each transient index is stored in its own temporary file.
508
The temporary file for a transient index is automatically deleted
509
at the end of the statement that uses it.
513
SQLite strives to implement ORDER BY clauses using a preexisting
514
index. If an appropriate index already exists, SQLite will walk
515
the index, rather than the underlying table, to extract the
516
requested information, and thus cause the rows to come out in
517
the desired order. But if SQLite cannot find an appropriate index
518
it will evaluate the query and store each row in a transient index
519
whose data is the row data and whose key is the ORDER BY terms.
520
After the query is evaluated, SQLite goes back and walks the
521
transient index from beginning to end in order to output the
522
rows in the desired order.
526
SQLite implements GROUP BY by ordering the output rows in the
527
order suggested by the GROUP BY terms. Each output row is
528
compared to the previous to see if it starts a new "group".
529
The ordering by GROUP BY terms is done in exactly the same way
530
as the ordering by ORDER BY terms. A preexisting index is used
531
if possible, but if no suitable index is available, a transient
536
The previous two paragraphs describe the implementation of SQLite
537
as of version 3.5.8. There are known problems with this approach
538
for very large results sets - result sets that are larger than the
539
available disk cache. Future versions of SQLite will likely address
540
this deficiency by completely reworking the sort algorithm for
541
cases when no suitable preexisting sort index is available. The
542
new sort algorithm will also use temporary files, but not in the
543
same way as the current implementation, the temporary files
544
for the new implementation will probably not be index files.
548
The DISTINCT keyword on an aggregate query is implemented by
549
creating a transient index in a temporary file and storing
550
each result row in that index. As new result rows are computed
551
a check is made to see if they already exist in the transient
552
index and if they do the new result row is discarded.
556
The UNION operator for compound queries is implemented by creating
557
a transient index in a temporary file and storing the results
558
of the left and right subquery in the transient index, discarding
559
duplicates. After both subqueries have been evaluated, the
560
transient index is walked from beginning to end to generate the final output.
564
The EXCEPT operator for compound queries is implemented by creating
565
a transient index in a temporary file, storing the results of the
566
left subquery in this transient index, then removing the result
567
from right subquery from the transient index, and finally walking
568
the index from beginning to end to obtain the final output.
572
The INTERSECT operator for compound queries is implemented by
573
creating two separate transient indices, each in a separate
574
temporary file. The left and right subqueries are evaluated
575
each into a separate transient index. Then the two indices
576
are walked together and entries that appear in both indices
581
Note that the UNION ALL operator for compound queries does not
582
use transient indices by itself (though of course the right
583
and left subqueries of the UNION ALL might use transient indices
584
depending on how they are composed.)
586
<a name="vacuumdb"></a>
588
<h3>2.7 Transient Database Used By <a href="lang_vacuum.html">VACUUM</a></h3>
591
The <a href="lang_vacuum.html">VACUUM</a> command works by creating a temporary file
592
and then rebuilding the entire database into that temporary
593
file. Then the content of the temporary file is copied back
594
into the original database file and the temporary file is
599
The temporary file created by the <a href="lang_vacuum.html">VACUUM</a> command exists only
600
for the duration of the command itself. The size of the temporary
601
file will be no larger than the original database.
604
<a name="tempstore"></a>
606
<h2>3.0 The SQLITE_TEMP_STORE Compile-Time Parameter and Pragma</h2>
609
The rollback journal, master journal,
610
and statement journal files are always written
612
But the other kinds of temporary files might be stored in memory
613
only and never written to disk.
614
Whether or not temporary files other than the rollback,
615
master, and statement journals are written to disk or stored only in memory
616
depends on the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter, the
617
<a href="pragma.html#pragma_temp_store">temp_store pragma</a>,
618
and on the size of the temporary file.
622
The <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter is a #define whose value is
623
an integer between 0 and 3, inclusive. The meaning of the
624
<a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter is as follows:
629
Temporary files are always stored on disk regardless of the setting
630
of the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
633
Temporary files are stored on disk by default but this can be
634
overridden by the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
637
Temporary files are stored in memory by default but this can be
638
overridden by the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
641
Temporary files are always stored in memory regardless of the setting
642
of the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
647
The default value of the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter is 1,
648
which means to store temporary files on disk but provide the option
649
of overriding the behavior using the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
653
The <a href="pragma.html#pragma_temp_store">temp_store pragma</a> has
654
an integer value which also influences the decision of where to store
655
temporary files. The values of the temp_store pragma have the
661
Use either disk or memory storage for temporary files as determined
662
by the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter.
665
If the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter specifies memory storage for
666
temporary files, then override that decision and use disk storage instead.
667
Otherwise follow the recommendation of the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time
671
If the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter specifies disk storage for
672
temporary files, then override that decision and use memory storage instead.
673
Otherwise follow the recommendation of the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time
679
The default setting for the <a href="pragma.html#pragma_temp_store">temp_store pragma</a> is 0,
680
which means to following the recommendation of <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time
685
To reiterate, the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter and the
686
<a href="pragma.html#pragma_temp_store">temp_store pragma</a> only
687
influence the temporary files other than the rollback journal
688
and the master journal. The rollback journal and the master
689
journal are always written to disk regardless of the settings of
690
the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter and the
691
<a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
694
<a name="otheropt"></a>
696
<h2>4.0 Other Temporary File Optimizations</h2>
699
SQLite uses a page cache of recently read and written database
700
pages. This page cache is used not just for the main database
701
file but also for transient indices and tables stored in temporary
702
files. If SQLite needs to use a temporary index or table and
703
the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter and the
704
<a href="pragma.html#pragma_temp_store">temp_store pragma</a> are
705
set to store temporary tables and index on disk, the information
706
is still initially stored in memory in the page cache. The
707
temporary file is not opened and the information is not truly
708
written to disk until the page cache is full.
712
This means that for many common cases where the temporary tables
713
and indices are small (small enough to fit into the page cache)
714
no temporary files are created and no disk I/O occurs. Only
715
when the temporary data becomes too large to fit in RAM does
716
the information spill to disk.
720
Each temporary table and index is given its own page cache
721
which can store a maximum number of database pages determined
722
by the SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time parameter.
723
(The default value is 500 pages.)
724
The maximum number of database pages in the page cache is the
725
same for every temporary table and index. The value cannot
726
be changed at run-time or on a per-table or per-index basis.
727
Each temporary file gets its own private page cache with its
728
own SQLITE_DEFAULT_TEMP_CACHE_SIZE page limit.
added
removed
tempfiles.html
