1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>VACUUM</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
VACUUM
</div>
</div>
<h1 id="syntax"><span>1. </span>Syntax</h1>
<p><b><a href="syntax/vacuum-stmt.html">vacuum-stmt:</a></b>
<button id='x2255' onclick='hideorshow("x2255","x2256")'>hide</button></p>
<div id='x2256' class='imgcontainer'>
<div style="max-width:599px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 599.669 64.8">
<circle cx="5" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="32,17 20,21 20,12" style="fill:rgb(0,0,0)"/>
<path d="M9,17L26,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M47,32L108,32A15 15 0 0 0 123 17A15 15 0 0 0 108 2L47,2A15 15 0 0 0 32 17A15 15 0 0 0 47 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="78" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">VACUUM</text>
<polygon points="165,47 153,51 153,43" style="fill:rgb(0,0,0)"/>
<path d="M123,17 L 131,17 Q 138,17 138,32 L 138,32 Q 138,47 149,47 L 159,47" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M180,62L276,62A15 15 0 0 0 292 47L292,47A15 15 0 0 0 276 32L180,32A15 15 0 0 0 165 47L165,47A15 15 0 0 0 180 62Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="228" y="47" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">schema-name</text>
<polygon points="333,17 322,21 322,12" style="fill:rgb(0,0,0)"/>
<path d="M292,47 L 299,47 Q 307,47 307,32 L 307,32 Q 307,17 317,17 L 327,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="375,47 363,51 363,43" style="fill:rgb(0,0,0)"/>
<path d="M333,17 L 341,17 Q 348,17 348,32 L 348,32 Q 348,47 359,47 L 369,47" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M390,62L417,62A15 15 0 0 0 432 47L432,47A15 15 0 0 0 417 32L390,32A15 15 0 0 0 375 47L375,47A15 15 0 0 0 390 62Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="403" y="47" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">INTO</text>
<polygon points="455,47 443,51 443,43" style="fill:rgb(0,0,0)"/>
<path d="M432,47L449,47" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M470,62L524,62A15 15 0 0 0 539 47L539,47A15 15 0 0 0 524 32L470,32A15 15 0 0 0 455 47L455,47A15 15 0 0 0 470 62Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="497" y="47" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">filename</text>
<polygon points="590,17 578,21 578,12" style="fill:rgb(0,0,0)"/>
<path d="M539,47 L 546,47 Q 554,47 554,32 L 554,32 Q 554,17 569,17 L 569,17 L 584,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<circle cx="593" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M123,17L578,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
</svg>
</div>
</div>
<h1 id="description"><span>2. </span>Description</h1>
<p>
The VACUUM command rebuilds the database file, repacking it into a minimal
amount of disk space. There are several
reasons an application might do this:
</p><ul>
<li> <p> Unless SQLite is running in "auto_vacuum=FULL" mode, when a large
amount of data is deleted from the database file it leaves behind empty
space, or "free" database pages. This means the database file might
be larger than strictly necessary. Running VACUUM to rebuild the
database reclaims this space and reduces the size of the database file.
</p></li><li> <p> Frequent inserts, updates, and deletes can cause the database file
to become fragmented - where data for a single table or index is scattered
around the database file. Running VACUUM ensures that each table and
index is largely stored contiguously within the database file. In some
cases, VACUUM may also reduce the number of partially filled pages in
the database, reducing the size of the database file further.
</p></li><li> <p> When content is deleted from an SQLite database, the content is not
usually erased but rather the space used to hold the content is marked as
being available for reuse. This can allow deleted content to be recovered
by a hacker or by forensic analysis. Running VACUUM will clean the database
of all traces of deleted content, thus preventing an adversary from recovering
deleted content. Using VACUUM in this way is an alternative to setting
<a href="pragma.html#pragma_secure_delete">PRAGMA secure_delete=ON</a>.
</p></li><li> <p> Normally, the database <a href="pragma.html#pragma_page_size">page_size</a> and whether or not the database
supports <a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a> must be configured before the database file is
actually created. However, when not in <a href="wal.html">write-ahead log</a> mode, the
<a href="pragma.html#pragma_page_size">page_size</a> and/or <a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a> properties of an existing database may be
changed by using the <a href="pragma.html#pragma_page_size">page_size</a> and/or
<a href="pragma.html#pragma_auto_vacuum">pragma auto_vacuum</a> pragmas and then immediately VACUUMing
the database. When in <a href="wal.html">write-ahead log</a> mode, only the <a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a>
support property can be changed using VACUUM.
</p></li></ul>
<p>By default, VACUUM operates on the main database.
<a href="lang_attach.html">Attached databases</a> can be vacuumed by appending the appropriate
<span class='yyterm'>schema-name</span> to the VACUUM statement.
</p><p><b>Compatibility Warning:</b> The ability to vacuum attached databases was
added in <a href="releaselog/3_15_0.html">version 3.15.0</a> (2016-10-14). Prior to that, a
<span class='yyterm'>schema-name</span> added to the
VACUUM statement would be silently ignored and the "main" schema would be
vacuumed.</p>
<a name="vacuuminto"></a>
<h2 id="vacuum_with_an_into_clause"><span>2.1. </span>VACUUM with an INTO clause</h2>
<p>If the INTO clause is included, then the original database file is
unchanged and a new database is created in a file named by the
argument to the INTO clause. The new database will contain the same
logical content as the original database, fully vacuumed.
</p><p>
The VACUUM command with an INTO clause is an alternative to the
<a href="backup.html">backup API</a> for generating backup copies of a live database.
The advantage of using VACUUM INTO is that the resulting backup
database is minimal in size and hence the amount of filesystem
I/O may be reduced. Also, all deleted content is purged from the
backup, leaving behind no forensic traces. On the other hand,
the <a href="backup.html">backup API</a> uses fewer CPU cycles and can be executed
incrementally.
</p><p>
The filename in the INTO clause can be an arbitrary SQL expression
that evaluates to a string.
The file named by the INTO clause must not previously exist, or
else it must be an empty file, or the VACUUM INTO command will
fail with an error.
</p><p>
The argument to INTO can be a <a href="uri.html">URI filename</a> if URI filenames
are enabled.
URL filenames are enabled if any of the following are true:
</p><ul>
<li> The SQLite library was compiled with <a href="compile.html#use_uri">-DSQLITE_USE_URI=1</a>.
</li><li> The <a href="c3ref/config.html">sqlite3_config</a>(<a href="c3ref/c_config_covering_index_scan.html#sqliteconfiguri">SQLITE_CONFIG_URI</a>,1) interfaces was
invoked at start-time.
</li><li> The <a href="c3ref/sqlite3.html">database connection</a> that is running the VACUUM INTO
statement was originally opened using the
<a href="c3ref/c_open_autoproxy.html">SQLITE_OPEN_URI</a> flag.
</li></ul>
<p>
The VACUUM INTO command is transactional in the sense that
the generated output database is a consistent snapshot of the
original database. However, if the VACUUM INTO command is
interrupted by an unplanned shutdown or power lose, then
the generated output database might be incomplete and corrupt.
Also, SQLite does not invoke fsync() or FlushFileBuffers()
on the generated database to ensure that it has reached
non-volatile storage before completing.
<a name="howvacuumworks"></a>
</p><h1 id="how_vacuum_works"><span>3. </span>How VACUUM works</h1>
<p>The VACUUM command works by copying the contents of the database into
a temporary database file and then overwriting the original with the
contents of the temporary file. When overwriting the original, a rollback
journal or <a href="wal.html">write-ahead log</a> WAL file is used just as it would be for any
other database transaction. This means that when VACUUMing a database,
as much as twice the size of the original database file is required in free
disk space.
</p><p>The VACUUM INTO command works the same way except that it uses the file
named on the INTO clause in place of the temporary database and omits the
step of copying the vacuumed database back over top of the original database.
</p><p>The VACUUM command may change the <a href="lang_createtable.html#rowid">ROWIDs</a> of entries in any
tables that do not have an explicit <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>.
</p>
<p>A VACUUM will fail if there is an open transaction on the database
connection that is attempting to run the VACUUM. Unfinalized SQL
statements typically hold a read transaction open, so the VACUUM
might fail if there are unfinalized SQL statements on the same connection.
VACUUM (but not VACUUM INTO) is a write operation and so if another
database connection is holding a lock that prevents writes, then
the VACUUM will fail.
</p><p>An alternative to using the VACUUM command to
reclaim space after data has been deleted is auto-vacuum mode, enabled using
the <a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a> pragma. When <a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a> is enabled for a database
free pages may be reclaimed after deleting data, causing the file to shrink,
without rebuilding the entire database using VACUUM. However, using
<a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a> can lead to extra database file fragmentation. And <a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a>
does not compact partially filled pages of the database as VACUUM does.
</p>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/lang_vacuum.in?m=6efbaecf3fe739850">2022-06-15 00:04:17</a> UTC </small></i></p>
|
