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>SQLite Query Language: VACUUM</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>
120
<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>VACUUM</h2><h4><a href="syntaxdiagrams.html#vacuum-stmt">vacuum-stmt:</a></h4><blockquote> <img alt="syntax diagram vacuum-stmt" src="images/syntax/vacuum-stmt.gif"></img> </blockquote>
124
The VACUUM command rebuilds the entire database. There are several
125
reasons an application might do this:
128
<li> <p> Unless SQLite is running in "auto_vacuum=FULL" mode, when a large
129
amount of data is deleted from the database file it leaves behind empty
130
space, or "free" database pages. This means the database file might
131
be larger than strictly necessary. Running VACUUM to rebuild the
132
database reclaims this space and reduces the size of the database file.
134
<li> <p> Frequent inserts, updates, and deletes can cause the database file
135
to become fragmented - where data for a single table or index is scattered
136
around the database file. Running VACUUM ensures that each table and
137
index is largely stored contiguously within the database file. In some
138
cases, VACUUM may also reduce the number of partially filled pages in
139
the database, reducing the size of the database file further.
141
<li> <p> Normally, the database <a href="pragma.html#pragma_page_size">page_size</a> and whether or not the database
142
supports <a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a> must be configured before the database file is
143
actually created. However, when not in <a href="wal.html">write-ahead log</a> mode, the
144
<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
145
changed by using the <a href="pragma.html#pragma_page_size">page_size</a> and/or
146
<a href="pragma.html#pragma_auto_vacuum">pragma auto_vacuum</a> pragmas and then immediately VACUUMing
147
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>
148
support property can be changed using VACUUM.
151
<p>VACUUM only works on the main database. It is not possible to VACUUM an
152
attached database file.
154
<p>The VACUUM command works by copying the contents of the database into
155
a temporary database file and then overwriting the original with the
156
contents of the temporary file. When overwriting the original, a rollback
157
journal or <a href="wal.html">write-ahead log</a> WAL file is used just as it would be for any
158
other database transaction. This means that when VACUUMing a database,
159
as much as twice the size of the original database file is required in free
162
<p>The VACUUM command may change the <a href="lang_createtable.html#rowid">ROWIDs</a> of entries in any
163
tables that do not have an explicit <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>.
166
<p>A VACUUM will fail if there is an open transaction, or if there are one or
167
more active SQL statements when it is run.
169
<p>As of SQLite version 3.1, an alternative to using the VACUUM command to
170
reclaim space after data has been deleted is auto-vacuum mode, enabled using
171
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
172
free pages may be reclaimed after deleting data, causing the file to shrink,
173
without rebuilding the entire database using VACUUM. However, using
174
<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>
175
does not compact partially filled pages of the database as VACUUM does.
added
removed
lang_vacuum.html
