Which database for android ensures security and small database file size?

Question

I am building an offline dictionary application. With database file 150,000 rows. I have used Sqlite but I need to secure my database so I use SqlCipher library to encrypt. The problem I encountered after encryption was the speed of reading data and the SqlCipher file after encryption could not be compressed. The database file size is significantly increased.
  Sqlite (9Mb) -> SqlCipher (93MB).
I also tried using the Realm database. It has fast reading speed and good security. However, the databse file is also significantly increased in size.  
  Sqlite (9Mb) -> Realm (50MB).
Is there any way to reduce the size of the database? There is another database for android to overcome the above disadvantages (security, speed, database size)?

Answer 1

You could perhaps consider implementing your own encryption/decryption and then only partial encryption of the actual sensitive data.

For example the demo code below utilises a basic dictionary (word/definition) with 280000 definitions (albeit duplicated definitions). This takes up 20.9mB without encryption and 36.6mB when encrypted.

• mydb being the unencrypted version

• mydbenc the encrypted version

• There again the data actually being stored, say for longer word definitions can have quite an impact, with the same number of words and definitions but with noticeably longer definitions (in the case where one of the 14 definitions (each being repeated 20000 times) was increased then the size of the en-encrypted database grew by 4mB, the encrypted database also grew by about 4mB). (the demo uses the larger DB)

  • 

So the encrypted size of your database would be about 20Mb for your 150,000 rows.

The size would also be impacted by the encryption method. In general the weaker the encryption method, less the overhead but with a lower factor of security.

To overcome the problem of searching, the example App decrypts the data into a temporary table when it is started. This does take just under a minute, which could be unacceptable.

• part of an encrypted string would not equate to that part encrypted on it's own and hence the issue with performing searches.

The example code consists of two Database Helpers one for the unencrypted version used for comparison and the encrypted version. Both use the same table with 3 columns id (which isn't encrypted), word and definition the latter two are encrypted in the encrypted version.

The database, table and name columns are defined via constants in a class named DBConstants as per :-

public class DBConstants {

    public static int FILTEROPTION_ANYWHERE = 0;
    public static int FILTEROPTION_MUSTMATCH = 1;
    public static int FILTEROPTION_STARTSWITH = 2;
    public static int FILTEROPTION_ENDSWITH = 4;

    public static final String DBName = "mydb";
    public static final int DBVERSION = 1;
    public static final String DECRYPTEXTENSION = "_decrypt";

    public static class MainTable {

        public static final String TBLNAME = "main";
        public static final String COL_ID = BaseColumns._ID;
        public static final String COl_WORD = "_word";
        public static final String COL_DEFINITION = "_definition";

        public static final String CRT_SQL = "CREATE TABLE IF NOT EXISTS " + TBLNAME +
                "(" +
                COL_ID + " INTEGER PRIMARY KEY," +
                COl_WORD + " TEXT," +
                COL_DEFINITION + " TEXT" +
                ")";
    }

    public static class DecrtyptedMainTable {
        public static final String TBLNAME = MainTable.TBLNAME + DECRYPTEXTENSION;
        public static final String CRT_SQL = "CREATE TEMP TABLE IF NOT EXISTS " + TBLNAME +
                "(" +
                MainTable.COL_ID + " INTEGER PRIMARY KEY," +
                MainTable.COl_WORD + " TEXT, " +
                MainTable.COL_DEFINITION + " TEXT " +
                ")";
        public static final String CRTIDX_SQL = "CREATE INDEX IF NOT EXISTS " +
                TBLNAME + "_index " +
                " ON " + TBLNAME +
                "(" + MainTable.COl_WORD + ")";
    }
}

A Word class is used to allow Word objects to be extracted as per :-

public class Word {
    private long id;
    private String word;
    private String definition;

    public Word() {
        this.id = -1L;
    }

    public Word(String word, String definition) {
        this(-1L,word,definition);
    }

    public Word(Long id, String word, String definition) {
        this.id = id;
        this.word = word;
        this.definition = definition;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getWord() {
        return word;
    }

    public void setWord(String word) {
        this.word = word;
    }

    public String getDefinition() {
        return definition;
    }

    public void setDefinition(String definition) {
        this.definition = definition;
    }
}

The Database Helper DBHelperStandard.java for the non-encrypted database (exists purely for comparison purposes) is :-

public class DBHelperStandard extends SQLiteOpenHelper {

    SQLiteDatabase db;

    public DBHelperStandard(Context context) {
        super(context, DBConstants.DBName, null, DBConstants.DBVERSION);
        db = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DBConstants.MainTable.CRT_SQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insertWord(String word, String definition) {
        ContentValues cv = new ContentValues();
        cv.put(DBConstants.MainTable.COl_WORD,word);
        cv.put(DBConstants.MainTable.COL_DEFINITION,definition);
        return db.insert(DBConstants.MainTable.TBLNAME,null,cv);
    }

    public long insertWord(Word word) {
        return insertWord(word.getWord(),word.getDefinition());
    }

    public int deleteWord(long id) {
        String whereclause = DBConstants.MainTable.COL_ID + "=?";
        String[] whereargs = new String[]{String.valueOf(id)};
        return db.delete(DBConstants.MainTable.TBLNAME,whereclause,whereargs);
    }

    public int deleteWord(Word word) {
        return deleteWord(word.getId());
    }

    public int updateWord(long id, String word, String defintion) {

        ContentValues cv = new ContentValues();
        if (word != null && word.length() > 0) {
            cv.put(DBConstants.MainTable.COl_WORD,word);
        }
        if (defintion != null && defintion.length() > 0) {
            cv.put(DBConstants.MainTable.COL_DEFINITION,defintion);
        }
        if (cv.size() < 1) return 0;
        String whereclause = DBConstants.MainTable.COL_ID + "=?";
        String[] whereargs = new String[]{String.valueOf(id)};
        return db.update(DBConstants.MainTable.TBLNAME,cv,whereclause,whereargs);
    }

    public int updateWord(Word word) {
        return updateWord(word.getId(),word.getWord(),word.getDefinition());
    }

    public List<Word> getWords(String wordfilter, int filterOption, Integer limit) {
        ArrayList<Word> rv = new ArrayList<>();
        String whereclause = DBConstants.MainTable.COl_WORD + " LIKE ?";
        StringBuilder sb = new StringBuilder();
        switch (filterOption) {
            case 0:
                sb.append("%").append(wordfilter).append("%");
                break;
            case 1:
                sb.append(wordfilter);
                break;
            case 2:
                sb.append(wordfilter).append("%");
                break;
            case 4:
                sb.append("%").append(wordfilter);
        }
        String[] whereargs = new String[]{sb.toString()};
        if (wordfilter == null) {
            whereclause = null;
            whereargs = null;
        }
        String limitclause = null;
        if (limit != null) {
            limitclause = String.valueOf(limit);
        }
        Cursor csr = db.query(
                DBConstants.MainTable.TBLNAME,
                null,
                whereclause,
                whereargs,
                null,
                null,
                DBConstants.MainTable.COl_WORD,
                limitclause
        );
        while (csr.moveToNext()) {
            rv.add(new Word(
                    csr.getLong(csr.getColumnIndex(DBConstants.MainTable.COL_ID)),
                    csr.getString(csr.getColumnIndex(DBConstants.MainTable.COl_WORD)),
                    csr.getString(csr.getColumnIndex(DBConstants.MainTable.COL_DEFINITION))
                    ));
        }
        return rv;
    }
}

The Database Helper DBHelperEncrypted.java for the encrypted database is :-

public class DBHelperEncrypted extends SQLiteOpenHelper {

    private String secretKey;
    private String ivpParemeter;

    SQLiteDatabase db;
    public DBHelperEncrypted(Context context, String secretKey, String ivpParamter) {
        super(context, DBConstants.DBName + DBConstants.DECRYPTEXTENSION, null, DBConstants.DBVERSION);
        this.secretKey = secretKey;
        this.ivpParemeter = ivpParamter;
        db = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) { db.execSQL(DBConstants.MainTable.CRT_SQL); }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insertWord(String word, String definition) {
        ContentValues cv = new ContentValues();
        cv.put(DBConstants.MainTable.COl_WORD,EncryptDecrypt.encrypt(word,secretKey,ivpParemeter));
        cv.put(DBConstants.MainTable.COL_DEFINITION,EncryptDecrypt.encrypt(definition,secretKey,ivpParemeter));
        return db.insert(DBConstants.MainTable.TBLNAME,null,cv);
    }

    public long insertWord(Word word) {
        return insertWord(word.getWord(),word.getDefinition());
    }

    public int deleteWord(long id) {
        String whereclause = DBConstants.MainTable.COL_ID + "=?";
        String[] whereargs = new String[]{String.valueOf(id)};
        return db.delete(DBConstants.MainTable.TBLNAME,whereclause,whereargs);
    }

    public int deleteWord(Word word) {
        return deleteWord(word.getId());
    }

    public int updateWord(long id, String word, String defintion) {

        ContentValues cv = new ContentValues();
        if (word != null && word.length() > 0) {
            cv.put(DBConstants.MainTable.COl_WORD,EncryptDecrypt.encrypt(word,secretKey,ivpParemeter));
        }
        if (defintion != null && defintion.length() > 0) {
            cv.put(DBConstants.MainTable.COL_DEFINITION,EncryptDecrypt.encrypt(defintion,secretKey,ivpParemeter));
        }
        if (cv.size() < 1) return 0;
        String whereclause = DBConstants.MainTable.COL_ID + "=?";
        String[] whereargs = new String[]{String.valueOf(id)};
        return db.update(DBConstants.MainTable.TBLNAME,cv,whereclause,whereargs);
    }

    public int updateWord(Word word) {
        return updateWord(word.getId(),word.getWord(),word.getDefinition());
    }

    public List<Word> getWords(String wordfilter, int filterOption, Integer limit) {
        ArrayList<Word> rv = new ArrayList<>();
        String whereclause = DBConstants.MainTable.COl_WORD + " LIKE ?";
        StringBuilder sb = new StringBuilder();
        switch (filterOption) {
            case 0:
                sb.append("%").append(wordfilter).append("%");
                break;
            case 1:
                sb.append(wordfilter);
                break;
            case 2:
                sb.append(wordfilter).append("%");
                break;
            case 4:
                sb.append("%").append(wordfilter);
        }
        String[] whereargs = new String[]{sb.toString()};
        String limitclause = null;
        if (limit != null) {
            limitclause = String.valueOf(limit);
        }
        Cursor csr = db.query(
                DBConstants.DecrtyptedMainTable.TBLNAME,
                null,
                whereclause,
                whereargs,
                null,
                null,
                DBConstants.MainTable.COl_WORD,
                limitclause
        );
        while (csr.moveToNext()) {
            rv.add(
                    new Word(
                            csr.getLong(csr.getColumnIndex(DBConstants.MainTable.COL_ID)),
                            csr.getString(csr.getColumnIndex(DBConstants.MainTable.COl_WORD)),
                            csr.getString(csr.getColumnIndex(DBConstants.MainTable.COL_DEFINITION))
                    )
            );
        }
        return rv;
    }

    public void buildDecrypted(boolean create_index) {
        db.execSQL(DBConstants.DecrtyptedMainTable.CRT_SQL);
        Cursor csr = db.query(DBConstants.MainTable.TBLNAME,null,null,null,null,null,null);
        ContentValues cv = new ContentValues();
        while (csr.moveToNext()) {
            cv.clear();
            cv.put(DBConstants.MainTable.COL_ID,csr.getLong(csr.getColumnIndex(DBConstants.MainTable.COL_ID)));
            cv.put(DBConstants.MainTable.COl_WORD,
                    EncryptDecrypt.decrypt(csr.getString(csr.getColumnIndex(DBConstants.MainTable.COl_WORD)),secretKey,ivpParemeter));
            cv.put(DBConstants.MainTable.COL_DEFINITION,
                    EncryptDecrypt.decrypt(csr.getString(csr.getColumnIndex(DBConstants.MainTable.COL_DEFINITION)),secretKey,ivpParemeter));
            db.insert(DBConstants.DecrtyptedMainTable.TBLNAME,null,cv);
        }
        csr.close();
        if (create_index) {
            db.execSQL(DBConstants.DecrtyptedMainTable.CRTIDX_SQL);
        }
    }
}

• The main difference is that the (probably not to be utilised as it would appear that the database is shipped) insert,and update methods encrypt the data and also the inclusion of a method buildDecrypted which creates a temporary table from the encrypted table. The temporary table being used for searching and extracting the data.

  • being a temporary table it will be deleted when the database is closed. Typically you would only close the database when the App finishes.

The encryption and decryption is handled by a class EncryotDecrypt as per EncryptDecrypt.java :-

public class EncryptDecrypt {
    public static Cipher cipher;

    /**
     * Encryption, irrespective of the USER type, noting that this should
     * only be used in conjunction with an EncryptDecrypt instance created
     * using the 2nd/extended constructor
     *
     * @param toEncrypt     The string to be encrypted
     * @return              The encrypted data as a string
     */
    public static String encrypt(String toEncrypt, String secretKey, String ivParameterSpec) {
        byte[] encrypted;
        try {
            if (cipher == null) {
                cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
            }
            if (secretKey.length() < 16) {
                secretKey = (secretKey + "                ").substring(0,16);
            }
            SecretKeySpec secretKeySpec = new SecretKeySpec(secretKey.getBytes(),"AES/CBC/PKCS5Padding");
            cipher.init(Cipher.ENCRYPT_MODE,secretKeySpec,new IvParameterSpec(ivParameterSpec.getBytes()));
            encrypted = cipher.doFinal(toEncrypt.getBytes());
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return Base64.encodeToString(encrypted,Base64.DEFAULT);
    }

    /**
     * Decrypt an encrypted string
     * @param toDecrypt     The encrypted string to be decrypted
     * @return              The decrypted string
     */
    public static String decrypt(String toDecrypt, String secretKey, String ivParameterSpec)  {
        byte[] decrypted;
        try {
            if (cipher == null) {
                cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
            }
            if (secretKey.length() < 16) {
                secretKey = (secretKey + "                ").substring(0,16);
            }
            SecretKeySpec secretKeySpec = new SecretKeySpec(secretKey.getBytes(),"AES/CBC/PKCS5Padding");
            cipher.init(Cipher.DECRYPT_MODE,secretKeySpec,new IvParameterSpec(ivParameterSpec.getBytes()));
            decrypted = cipher.doFinal(Base64.decode(toDecrypt,Base64.DEFAULT));
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return new String(decrypted);
    }
}

• Note I'm no expert by any means, but I do believe that you could implement less/more secure encryption methods relatively easily.

Last, putting it all together for this demo, is MainActivity.java :-

public class MainActivity extends AppCompatActivity {

    public static final String SK = "mysecretkey";
    public static final String SALT = "124567890ABCDEFG";
    DBHelperEncrypted mDBE;
    DBHelperStandard mDBS;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDBE = new DBHelperEncrypted(this,SK,SALT);
        mDBS = new DBHelperStandard(this);

        //Stage 1 - Build the demo databases
        ArrayList<Word> wordsanddefinitions = new ArrayList<>();
        for (int i=0; i < 20000; i ++) {
            wordsanddefinitions.add(new Word("Apple","Something that falls on peoples heads that causes them to discover gravity."));
            wordsanddefinitions.add(new Word("Bachelor","An unmarried man."));
            wordsanddefinitions.add(new Word("Bachelor","A person who has been awarded a bachelor's degree."));
            wordsanddefinitions.add(new Word("Bachelor","A fur seal, especially a young male, kept from the breeding grounds by the older males."));
            wordsanddefinitions.add(new Word("Cat","A small domesticated carnivore, Felis domestica or F. catus, bred in a number of varieties."));
            wordsanddefinitions.add(new Word("Dog","A domesticated canid, Canis familiaris, bred in many varieties."));
            wordsanddefinitions.add(new Word("Eddy","A current at variance with the main current in a stream of liquid or gas, especially one having a rotary or whirling motion."));
            wordsanddefinitions.add(new Word("Eddy","A small whirlpool."));
            wordsanddefinitions.add(new Word("Eddy","Any similar current, as of air, dust, or fog."));
            wordsanddefinitions.add(new Word("Eddy","A current or trend, as of opinion or events, running counter to the main current."));
            wordsanddefinitions.add(new Word("Orange","A colour bewteen Red and Yellow."));
            wordsanddefinitions.add(new Word("Orange","a globose, reddish-yellow, bitter or sweet, edible citrus fruit."));
            wordsanddefinitions.add(new Word("Orange","any white-flowered, evergreen citrus trees of the genus Citrus, bearing this fruit, " +
                    "as C. aurantium (bitter orange, Seville orange, or sour orange) " +
                    "and C. sinensis (sweet orange), cultivated in warm countries."));
            wordsanddefinitions.add(new Word("Orange","Any of several other citrus trees, as the trifoliate orange."));
        }
        Log.d("STAGE1","Starting to build the Standard (non-encrypted) DB with " + String.valueOf(wordsanddefinitions.size()) + " definitions");
        mDBS.getWritableDatabase().beginTransaction();
        for (Word w: wordsanddefinitions ) {
            mDBS.insertWord(w);
        }
        mDBS.getWritableDatabase().setTransactionSuccessful();
        mDBS.getWritableDatabase().endTransaction();

        Log.d("STAGE2","Starting to build the Encrypted DB with " + String.valueOf(wordsanddefinitions.size()) + " definitions");
        mDBE.getWritableDatabase().beginTransaction();
        for (Word w: wordsanddefinitions) {
            mDBE.insertWord(w);
        }

        // Decrypt the encrypted table as a TEMPORARY table
        Log.d("STAGE 3","Bulding the temporary unencrypted table");
        mDBE.buildDecrypted(true); // Build with index on word column
        mDBE.getWritableDatabase().setTransactionSuccessful();
        mDBE.getWritableDatabase().endTransaction();

        // Database now usable
        Log.d("STAGE4","Extracting data (all words that include ap in the word) from the Standard DB");
        List<Word> extracted_s = mDBS.getWords("ap",DBConstants.FILTEROPTION_ANYWHERE,10);
        for (Word w: extracted_s) {
            Log.d("WORD_STANDARD",w.getWord() + " " + w.getDefinition());
        }

        Log.d("STAGE5","Extracting data (all words that include ap in the word) from the Encrypted DB");
        List<Word> extracted_e = mDBE.getWords("ap",DBConstants.FILTEROPTION_ANYWHERE,10);
        for (Word w: extracted_e) {
            Log.d("WORD_ENCRYPTED",w.getWord() + " " + w.getDefinition());
        }

        Log.d("STAGE5","Extracting demo data from standard and from encrypted without decryption");
        Cursor csr = mDBE.getWritableDatabase().query(DBConstants.MainTable.TBLNAME,null,null,null,null,null,null,"10");
        DatabaseUtils.dumpCursor(csr);
        csr = mDBS.getWritableDatabase().query(DBConstants.MainTable.TBLNAME,null,null,null,null,null,null,"10");
        DatabaseUtils.dumpCursor(csr);
        mDBS.close();
        mDBE.close();
    }
}

This :-

1. (Stage 1) Creates an ArrayList of Word objects based upon 14 core Word definitions repeated 20000 times (i.e. 280000 objects).
2. (Stage 1) Uses the insert method to build the non-encrypted database.

3. (Stage 2) Uses the insert method (which encrypts the data) for the encrypted database.

4. (Stage 3) Builds the temporary decrypted table and also an index (the index can be skipped by using mDBE.buildDecrypted(false); (it doesn't appear to have much of an imapct, especially as it's built after the inserts)).

5. (Stage 4) extracts some data using the getWords method (filtered) from the non-encrypted dataabse and writes the extracted data to the log.

6. (Stage 5) extracts some data using the getWords method (filtered) from the encrypted database (from the decrypted temporary table) and writes the extracted data to the log.

   • Output from Stage 4 and Stage 5 should match.

7. Extracts the first 10 rows (2 shown for brevity) from the encrypted database's persisted table (i.e. the encrypted data, not the decrypted data).

8. Extracts the first 10 rows (2 shown fro berevity) from the non-encrypted database and dumps the Cursor to the log.

   • Output from 7 and 8 shows whats in the persisted database.

Result

6-05 13:51:36.932  D/STAGE1: Starting to build the Standard (non-encrypted) DB with 280000 definitions
06-05 13:51:59.274 D/STAGE2: Starting to build the Encrypted DB with 280000 definitions
06-05 13:52:45.327 D/STAGE 3: Bulding the temporary unencrypted table
06-05 13:52:45.350 W/CursorWindow: Window is full: requested allocation 111 bytes, free space 98 bytes, window size 2097152 bytes
    .........
06-05 13:53:35.024 D/STAGE4: Extracting data (all words that include ap in the word) from the Standard DB
06-05 13:53:35.346 D/WORD_STANDARD: Apple Something that falls on peoples heads that causes them to discover gravity.
    ..........
06-05 13:53:35.346 D/STAGE5: Extracting data (all words that include ap in the word) from the Encrypted DB
06-05 13:53:35.346 D/WORD_ENCRYPTED: Apple Something that falls on peoples heads that causes them to discover gravity.
    ..........
06-05 13:53:35.347 D/STAGE5: Extracting demo data from standard and from encrypted without decryption
06-05 13:53:35.347 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@d05c965
06-05 13:53:35.347 I/System.out: 0 {
06-05 13:53:35.347 I/System.out:    _id=1
06-05 13:53:35.347 I/System.out:    _word=3mqQlZl55WNjeZhALFQU7w==
06-05 13:53:35.347 I/System.out:    _definition=s9Waa2HLUS2fy8q1uC9/MEKogmImu6m9MIpi9wasD9D3Zom6+/u40DnFfP6zXOyI8IgnQOKcWfQ8
06-05 13:53:35.347 I/System.out: G3uJN9a/YHMoQdEQMDMEEdSE2kWyJrc=
06-05 13:53:35.347 I/System.out: }
06-05 13:53:35.347 I/System.out: 1 {
06-05 13:53:35.347 I/System.out:    _id=2
06-05 13:53:35.347 I/System.out:    _word=LtLlycoBd9fm3eYF9aoItg==
06-05 13:53:35.347 I/System.out:    _definition=B1XJJm0eC8wPi3xGg4XgJtvIS3xL7bjixNhVAVq1UwQ=
06-05 13:53:35.347 I/System.out: }

06-05 13:53:35.348 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@7f1b63a
06-05 13:53:35.348 I/System.out: 0 {
06-05 13:53:35.348 I/System.out:    _id=1
06-05 13:53:35.348 I/System.out:    _word=Apple
06-05 13:53:35.348 I/System.out:    _definition=Something that falls on peoples heads that causes them to discover gravity.
06-05 13:53:35.348 I/System.out: }
06-05 13:53:35.348 I/System.out: 1 {
06-05 13:53:35.348 I/System.out:    _id=2
06-05 13:53:35.348 I/System.out:    _word=Bachelor
06-05 13:53:35.348 I/System.out:    _definition=An unmarried man.
06-05 13:53:35.348 I/System.out: }
06-05 13:53:35.349 I/System.out: <<<<<

Answer 2

did you try to use VACUUM ?

The VACUUM command rebuilds the entire database. There are several reasons an application might do this:

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

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

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. This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space...

Edit

One more thing don't forget using correct data type ,as example TEXT field requires more space than INTEGER field.