Small. Fast. Reliable.
Choose any three.

SQLite Requirement Matrix Details
lang_vacuum.html

Index Summary Markup Original


R-51469-36013-06451-37309-14378-27141-44520-41230 tcl slt th3 src

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.

tcl/e_vacuum.test:72

/* IMP: R-51469-36013 */
# EVIDENCE-OF: R-51469-36013 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.

R-60541-63059-13982-01066-19481-07947-17056-17575 tcl slt th3 src

Running VACUUM to rebuild the database reclaims this space and reduces the size of the database file.

tcl/e_vacuum.test:77

/* IMP: R-60541-63059 */
# EVIDENCE-OF: R-60541-63059 Running VACUUM to rebuild the database
# reclaims this space and reduces the size of the database file.

R-50943-18433-57597-04082-04676-55042-26161-32461 tcl slt th3 src

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.

tcl/e_vacuum.test:112

/* IMP: R-50943-18433 */
# EVIDENCE-OF: R-50943-18433 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.

R-05791-54928-02769-08459-30778-25258-29853-24680 tcl slt th3 src

Running VACUUM ensures that each table and index is largely stored contiguously within the database file.

tcl/e_vacuum.test:116

/* IMP: R-05791-54928 */
# EVIDENCE-OF: R-05791-54928 Running VACUUM ensures that each table and
# index is largely stored contiguously within the database file.

R-52747-41357-44885-51166-33553-21008-38098-18892 tcl slt th3 src

In some cases, VACUUM may also reduce the number of partially filled pages in the database, reducing the size of the database file further.

/* IMP: R-52747-41357 */
# EVIDENCE-OF: R-52747-41357 In some cases, VACUUM may also reduce the
# number of partially filled pages in the database, reducing the size of
# the database file further.

R-20474-44465-10719-25363-46434-31101-34097-17120 tcl slt th3 src

Normally, the database page_size and whether or not the database supports auto_vacuum must be configured before the database file is actually created.

tcl/e_vacuum.test:148

/* IMP: R-20474-44465 */
# EVIDENCE-OF: R-20474-44465 Normally, the database page_size and
# whether or not the database supports auto_vacuum must be configured
# before the database file is actually created.

R-08570-19916-06094-14315-21140-23229-64814-49543 tcl slt th3 src

However, when not in write-ahead log mode, the page_size and/or auto_vacuum properties of an existing database may be changed by using the page_size and/or pragma auto_vacuum pragmas and then immediately VACUUMing the database.

tcl/e_vacuum.test:163

/* IMP: R-08570-19916 */
# EVIDENCE-OF: R-08570-19916 However, when not in write-ahead log mode,
# the page_size and/or auto_vacuum properties of an existing database
# may be changed by using the page_size and/or pragma auto_vacuum
# pragmas and then immediately VACUUMing the database.

R-48521-51450-40029-38325-35476-53863-12365-01087 tcl slt th3 src

When in write-ahead log mode, only the auto_vacuum support property can be changed using VACUUM.

tcl/e_vacuum.test:176

/* IMP: R-48521-51450 */
# EVIDENCE-OF: R-48521-51450 When in write-ahead log mode, only the
# auto_vacuum support property can be changed using VACUUM.

R-40347-36128-09894-54444-62089-62339-21392-16913 tcl slt th3 src

By default, VACUUM operates on the main database.

tcl/e_vacuum.test:193

/* IMP: R-40347-36128 */
# EVIDENCE-OF: R-40347-36128 By default, VACUUM operates on the main
# database.

R-36598-60500-12425-47640-29499-27621-13512-27511 tcl slt th3 src

Attached databases can be vacuumed by appending the appropriate schema-name to the VACUUM statement.

tcl/e_vacuum.test:209

/* IMP: R-36598-60500 */
# EVIDENCE-OF: R-36598-60500 Attached databases can be vacuumed by
# appending the appropriate schema-name to the VACUUM statement.

R-23512-47460-29978-55073-11417-46179-04281-45022 tcl slt th3 src

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.

/* IMP: R-23512-47460 */
# EVIDENCE-OF: R-23512-47460 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.

R-16711-29980-24951-32335-36937-22599-07259-26262 tcl slt th3 src

The filename in the INTO clause can be an arbitrary SQL expression that evaluates to a string.

th3/cov1/vacuum10.test:77

/* IMP: R-16711-29980 */
# EVIDENCE-OF: R-16711-29980 The filename in the INTO clause can be an
# arbitrary SQL expression that evaluates to a string.

R-46334-60999-49221-04702-27230-06175-42868-32331 tcl slt th3 src

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.

th3/cov1/vacuum10.test:84

/* IMP: R-46334-60999 */
# EVIDENCE-OF: R-46334-60999 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.

R-25072-57278-41815-02342-40750-19788-27895-44607 tcl slt th3 src

The argument to INTO can be a URI filename if URI filenames are enabled.

/* IMP: R-25072-57278 */
# EVIDENCE-OF: R-25072-57278 The argument to INTO can be a URI filename
# if URI filenames are enabled.

R-27809-56550-37281-65484-19482-39813-11020-02299 tcl slt th3 src

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.

/* IMP: R-27809-56550 */
# EVIDENCE-OF: R-27809-56550 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.

R-48974-49065-61621-06146-19676-25971-04970-57642 tcl slt th3 src

When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction.

/* IMP: R-48974-49065 */
# EVIDENCE-OF: R-48974-49065 When overwriting the original, a rollback
# journal or write-ahead log WAL file is used just as it would be for
# any other database transaction.

R-13903-46567-21188-22822-60227-64496-63410-06513 tcl slt th3 src

This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space.

/* IMP: R-13903-46567 */
# EVIDENCE-OF: R-13903-46567 This means that when VACUUMing a database,
# as much as twice the size of the original database file is required in
# free disk space.

R-02754-60157-05684-14835-18385-12207-59168-42343 tcl slt th3 src

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.

/* IMP: R-02754-60157 */
# EVIDENCE-OF: R-02754-60157 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.

R-17495-17419-60080-02439-41171-12294-54829-31710 tcl slt th3 src

The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.

tcl/e_vacuum.test:214   th3/cov1/vacuum01.test:79

/* IMP: R-17495-17419 */
# EVIDENCE-OF: R-17495-17419 The VACUUM command may change the ROWIDs of
# entries in any tables that do not have an explicit INTEGER PRIMARY
# KEY.

R-12218-18073-29146-37627-08161-54063-61072-19112 tcl slt th3 src

A VACUUM will fail if there is an open transaction on the database connection that is attempting to run the VACUUM.

src/vacuum.c:168   tcl/e_vacuum.test:289

/* IMP: R-12218-18073 */
# EVIDENCE-OF: R-12218-18073 A VACUUM will fail if there is an open
# transaction on the database connection that is attempting to run the
# VACUUM.

R-15610-35227-39836-10138-16838-61419-33727-13478 tcl slt th3 src

Unfinalized SQL statements typically hold a read transaction open, so the VACUUM might fail if there are unfinalized SQL statements on the same connection.

src/vacuum.c:172

/* IMP: R-15610-35227 */
# EVIDENCE-OF: R-15610-35227 Unfinalized SQL statements typically hold a
# read transaction open, so the VACUUM might fail if there are
# unfinalized SQL statements on the same connection.

R-19879-20948-53153-63685-13139-09400-29655-64256 tcl slt th3 src

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.

/* IMP: R-19879-20948 */
# EVIDENCE-OF: R-19879-20948 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.

R-55138-13241-38479-09846-46068-29398-26502-36438 tcl slt th3 src

An alternative to using the VACUUM command to reclaim space after data has been deleted is auto-vacuum mode, enabled using the auto_vacuum pragma.

tcl/e_vacuum.test:316

/* IMP: R-55138-13241 */
# EVIDENCE-OF: R-55138-13241 An alternative to using the VACUUM command
# to reclaim space after data has been deleted is auto-vacuum mode,
# enabled using the auto_vacuum pragma.

R-64844-34873-54978-44161-37358-50690-61472-16160 tcl slt th3 src

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

tcl/e_vacuum.test:325

/* IMP: R-64844-34873 */
# EVIDENCE-OF: R-64844-34873 When auto_vacuum 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.

R-41548-42669-47727-24774-07572-16450-39601-00236 tcl slt th3 src

However, using auto_vacuum can lead to extra database file fragmentation.

/* IMP: R-41548-42669 */
# EVIDENCE-OF: R-41548-42669 However, using auto_vacuum can lead to
# extra database file fragmentation.

R-49389-26640-51376-48756-08197-40049-61248-27840 tcl slt th3 src

And auto_vacuum does not compact partially filled pages of the database as VACUUM does.

/* IMP: R-49389-26640 */
# EVIDENCE-OF: R-49389-26640 And auto_vacuum does not compact partially
# filled pages of the database as VACUUM does.