<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<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>SQLite Query Language: 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.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;
}
</script>
</div>
<div class=nosearch><h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>VACUUM</h2></div><p><b><a href="syntax/vacuum-stmt.html">vacuum-stmt:</a></b>
<button id='x1991' onclick='hideorshow("x1991","x1992")'>hide</button></p>
 <div id='x1992' class='imgcontainer'>
 <img alt="syntax diagram vacuum-stmt" src="images/syntax/vacuum-stmt.gif" />
</div>


<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:

<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.

  <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.

  <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.
</ul>

<p>By default, VACUUM only works only 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><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>

<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>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, or if there are one or
more active SQL statements when it is run.

<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>



