/ Check-in [b3e2cdae]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add documentation on the incremental vacuum feature. (CVS 3924)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b3e2cdaed3638f033437b5561705cb253449c392
User & Date: drh 2007-05-04 19:16:30
Context
2007-05-05
11:48
Refactoring. Split btreeInt.h off from btree.c. Split malloc.c off from util.c. Expect much more to follow. (CVS 3925) check-in: 16041116 user: drh tags: trunk
2007-05-04
19:16
Add documentation on the incremental vacuum feature. (CVS 3924) check-in: b3e2cdae user: drh tags: trunk
19:03
Fix compilation and testing with OMIT_INCRBLOB defined. (CVS 3923) check-in: a0f8adc6 user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to www/pragma.tcl.

1
2
3
4
5
6
7
8
9
10
11
..
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
...
216
217
218
219
220
221
222






















223
224
225
226
227
228
229
#
# Run this Tcl script to generate the pragma.html file.
#
set rcsid {$Id: pragma.tcl,v 1.22 2007/04/02 00:53:19 drh Exp $}
source common.tcl
header {Pragma statements supported by SQLite}

proc Section {name {label {}}} {
  puts "\n<hr />"
  if {$label!=""} {
    puts "<a name=\"$label\"></a>"
................................................................................
}

Section {Pragmas to modify library operation} modify

puts {
<ul>
<a name="pragma_auto_vacuum"></a>
<li><p><b>PRAGMA auto_vacuum;
       <br>PRAGMA auto_vacuum = </b><i>0 | 1</i><b>;</b></p>

    <p> Query or set the auto-vacuum flag in the database.</p>


    <p>Normally, when a transaction that deletes data from a database is
    committed, the database file remains the same size. Unused database file 
    pages are marked as such and reused later on, when data is inserted into 


    the database. In this mode the <a href="lang_vacuum.html">VACUUM</a>
    command is used to reclaim unused space.</p>

    <p>When the auto-vacuum flag is set, the database file shrinks when a
    transaction that deletes data is committed (The VACUUM command is not
    useful in a database with the auto-vacuum flag set). To support this
    functionality the database stores extra information internally, resulting
    in slightly larger database files than would otherwise be possible.</p>








    <p>It is only possible to modify the value of the auto-vacuum flag before
    any tables have been created in the database. No error message is 
    returned if an attempt to modify the auto-vacuum flag is made after
    one or more tables have been created.





















    </p></li>

<a name="pragma_cache_size"></a>
<li><p><b>PRAGMA cache_size;
       <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses about 1.5K of memory.
................................................................................
       <br>PRAGMA fullfsync = </b><i>0 | 1</i><b>;</b></p>
    <p>Query or change the fullfsync flag. This flag affects
    determines whether or not the F_FULLFSYNC syncing method is used
    on systems that support it.  The default value is off.  As of this
    writing (2006-02-10) only Mac OS X supports F_FULLFSYNC.
    </p>
</li>























<a name="pragma_legacy_file_format"></a>
<li><p><b>PRAGMA legacy_file_format;
       <br>PRAGMA legacy_file_format = <i>ON | OFF</i></b></p>
    <p>This pragma sets or queries the value of the legacy_file_format
    flag.  When this flag is on, new SQLite databases are created in
    a file format that is readable and writable by all versions of



|







 







|
|
>
|

>
|

<
>
>
|
|

|
<
<
<
|
>
>
>
>
>
>
>

<
<
<
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
..
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
...
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
#
# Run this Tcl script to generate the pragma.html file.
#
set rcsid {$Id: pragma.tcl,v 1.23 2007/05/04 19:16:30 drh Exp $}
source common.tcl
header {Pragma statements supported by SQLite}

proc Section {name {label {}}} {
  puts "\n<hr />"
  if {$label!=""} {
    puts "<a name=\"$label\"></a>"
................................................................................
}

Section {Pragmas to modify library operation} modify

puts {
<ul>
<a name="pragma_auto_vacuum"></a>
<li><p><b>PRAGMA auto_vacuum;<br>
          PRAGMA auto_vacuum = </b>
            <i>0 | none | 1 | full | 2 | incremental</i><b>;</b></p>
    <p>Query or set the auto-vacuum flag in the database.</p>

    <p>Normally, (that is to say when auto_vacuum is 0 or "none")
    when a transaction that deletes data from a database is
    committed, the database file remains the same size. Unused database file 

    pages are added to a "freelist" are reused for subsequent inserts.  The
    database file does not shrink.
    In this mode the <a href="lang_vacuum.html">VACUUM</a>
    command can be used to reclaim unused space.</p>

    <p>When the auto-vacuum flag is 1 (full), the freelist pages are



    moved to the end of the file and the file is truncated to remove
    the freelist pages at every commit.
    Note, however, that auto-vacuum only truncates the freelist pages
    from the file.  Auto-vacuum does not defragment the database nor
    repack individual database pages the way that the
    <a href="lang_vacuum.html">VACUUM</a> command does.  In fact, because
    it moves pages around within the file, auto-vacuum can actually
    make fragmentation worse.</p>





    <p>Auto-vacuuming is only possible if the database stores some
    additional information that allows each database page to be
    traced backwards to its referer.  Therefore, auto-vacuuming must
    be turned on before any tables are created.  It is not possible
    to enable or disable auto-vacuum after a table has been created.</p>

    <p>When the value of auto-vacuum is 2 (incremental) then the additional
    information needed to do autovacuuming is stored in the database file
    but autovacuuming does not occur automatically at each commit as it
    does with auto_vacuum==full.  In incremental mode, the separate
    <a href="#pragma_incremental_vacuum">incremental_vacuum</a> pragma must
    be invoked to cause the vacuum to occur.  The incremental vacuum mode
    is not persistent.  It must be set anew with each new database 
    connection.  When a database with incremental vacuum is closed and
    reopened, it comes up in auto_vacuum==full mode until explicitly
    changed to incremental mode using this pragma.</p>

    <p>The database connection can be changed between full and incremental
    autovacuum mode at will.  However, the connection cannot be changed
    in and out of the "none" mode after any table has been created in the
    database.
    </p></li>

<a name="pragma_cache_size"></a>
<li><p><b>PRAGMA cache_size;
       <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses about 1.5K of memory.
................................................................................
       <br>PRAGMA fullfsync = </b><i>0 | 1</i><b>;</b></p>
    <p>Query or change the fullfsync flag. This flag affects
    determines whether or not the F_FULLFSYNC syncing method is used
    on systems that support it.  The default value is off.  As of this
    writing (2006-02-10) only Mac OS X supports F_FULLFSYNC.
    </p>
</li>

<a name="pragma_incremental_vacuum"></a>
<li><p><b>PRAGMA incremental_vacuum</b><i>(N)</i><b>;</b></p>
    <p>The incremental_vacuum pragma causes up to <i>N</i> pages to
    be removed from the freelist.  The database file is truncated by
    the same amount.  The incremental_vacuum pragma has no effect if
    the database is not in
    <a href="#pragma_auto_vacuum">auto_vacuum==incremental</a> mode
    or if there are no pages on the freelist.  If there are fewer than
    <i>N</i> pages on the freelist, then the entire freelist is cleared.</p>

    <p>As of version 3.3.18 (the first version that supports
    incremental_vacuum) this feature is still experimental.  Possible
    future changes include enhancing incremental vacuum to do
    defragmentation and node repacking just as the full-blown
    <a href="lang_vacuum.html">VACUUM</a> command does.  And
    incremental vacuum may be promoted from a pragma to a separate
    SQL command, or perhaps some variation on the VACUUM command.
    Programmers are cautioned to not become enamored with the
    current syntax or functionality as it is likely to change.</p>
</li>


<a name="pragma_legacy_file_format"></a>
<li><p><b>PRAGMA legacy_file_format;
       <br>PRAGMA legacy_file_format = <i>ON | OFF</i></b></p>
    <p>This pragma sets or queries the value of the legacy_file_format
    flag.  When this flag is on, new SQLite databases are created in
    a file format that is readable and writable by all versions of