Skip to Main Content
Using SQLite
book

Using SQLite

by Jay A. Kreibich
August 2010
Intermediate to advanced content levelIntermediate to advanced
526 pages
23h 39m
English
O'Reilly Media, Inc.
Content preview from Using SQLite

Name

VACUUM — Recover free space and optimize database

Syntax

image with no caption

Common Usage

VACUUM;

Description

The VACUUM command recovers free space from the database file and releases it to the filesystem. VACUUM can also defragment database structures and repack individual database pages. VACUUM can only be run against the main database (the database used to create the database connection). VACUUM has no effect on in-memory databases.

When data objects (rows, whole tables, indexes, etc.) are deleted or dropped from a database, the file size remains unchanged. Any database pages that are recovered from deleted objects are simply marked as free and available for any future database storage needs. As a result, under normal operations the database file can only grow in size.

Additionally, as rows are inserted and deleted from the database, the tables and indexes can become fragmented. In a dynamic database that normally experiences a high number of inserts, updates, and deletes, it is common for free pages to be scattered all across the database file. If a table or index requires additional pages for more storage, these will first be allocated off the free list. This means the actual parts of the database file that hold a particular table or index may become scattered and mixed all across the database file, lowering seek performance.

Finally, as rows are inserted, updated, and deleted, unused data blocks ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning SQL, 3rd Edition

Learning SQL, 3rd Edition

Alan Beaulieu
High Performance MySQL, 4th Edition

High Performance MySQL, 4th Edition

Silvia Botros, Jeremy Tinley

Publisher Resources

ISBN: 9781449394592Errata PageSupplemental Content