Debian Codegraph @

www/lang_vacuum.html

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