Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to vacuum sqlite database?

Tags:

sqlite

vacuum

I want to know how to vacuum sqlite database. I tried a syntax MANUAL VACUUM command for the whole database from command prompt:

 $sqlite3 database_name "VACUUM;"; 

But it's giving error as:

near "database_name": syntax error. 

and also AUTO VACUUM:

PRAGMA auto_vacuum = INCREMENTAL; 

And tried it for a particular table as:

VACUUM table_name; 

But no result.

like image 983
meghalee Avatar asked Aug 08 '13 13:08

meghalee


People also ask

How does SQLite VACUUM work?

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 write-ahead log WAL file is used just as it would be for any other database transaction.

Why you should not use SQLite?

High write volumes: SQLite allows only one write operation to take place at any given time, which significantly limits its throughput. If your application requires lots of write operations or multiple concurrent writers, SQLite may not be adequate for your needs.

What does the VACUUM command do?

By default, VACUUM FULL skips the sort phase for any table that is already at least 95 percent sorted. If VACUUM is able to skip the sort phase, it performs a DELETE ONLY and reclaims space in the delete phase such that at least 95 percent of the remaining rows aren't marked for deletion.

How do I query a SQLite database from the command line?

Start the sqlite3 program by typing "sqlite3" at the command prompt, optionally followed by the name the file that holds the SQLite database (or ZIP archive). If the named file does not exist, a new database file with the given name will be created automatically.


2 Answers

You don't to specify the table name in the syntax. Only VACUUM works.

Also, it will clean the main database only and not any attached database files.

For more info, refer to the SQLite documentation.

like image 151
SHANK Avatar answered Sep 24 '22 06:09

SHANK


Give the command like this:

$sqlite3 database_name 'VACUUM;' 

As a matter of fact, this is the way to do also other queries from command line:

$sqlite3 database_name 'select * from tablename;' 

You can use the full path to the db:

$sqlite3 /path/to/db/foo.db 'VACUUM;' 
like image 23
yöliitäjä Avatar answered Sep 25 '22 06:09

yöliitäjä