SQLite - database size after DELETE FROM
Good day to all. I would like to share interesting, in my opinion, experience with SQLite.
I myself am a RubyOnRails developer, so for small projects I try to use the standard configuration that uses SQLite. I see no reason to drag a heavyweight DBMS into a dependency of a simple application.
One day my attention was attracted by the size of the database, the tables of which were cleared a few minutes ago. An interesting fact: when you remove an object from the database from it, free space is left in the database file. Perhaps for experienced specialists this fact is obvious, but the volume of search results on this issue did not exceed my expectations.
Consider the example with which we are dealing.
')
Create a database, and in it - a table with a simple structure: id (INTEGER, AI), phone (TEXT). Next, fill it with arbitrary values:
This database is 850,944 bytes . Number of entries - 41 922.
Run “DELETE FROM mytable”:
Database file size is 850,944 bytes . Number of entries - 0.
After importing the same 41,922 records:
Database file size is 871,424 bytes . Number of entries - 41 922.
In the latter case, volume growth is associated with the types used to store the auto-increment id field. For field values of up to 65,536, an unsigned short appears to be used . After the “delete from” and ai import, the index began with 41 923 and ended with 82 946, therefore, unsigned int or unsigned long is already used to store the values of the id field — I did not count, I don’t know for sure. And maybe not generally unsigned types are used - that's not the point.
The key question is: why after deleting data from a table, the size of the database file remains the same?
At first, I thought that the data was not deleted at all, but simply marked as deleted and then overwritten with new entries. However, this was not the case. Looking into the file after deleting the data, I did not find the previous values. This means that deleting actually deletes the data, only the memory is not released, but is clogged with ASCII 00 values.
What can be done with this? There is a special team - VACUUM.
Run the DELETE FROM mytable again. The table has been cleared, but its size is still 851 KB - everything is as before.
Execute the VACUUM command: the file size has decreased to 3 KB.
During this operation, the contents of our database were copied to a temporary file, which was overwritten by the original database file. Technically, the original file is overwritten using the Write-Ahead Logging method. Read more - here . This means that to perform the VACUUM operation on the hard disk, you must have free space - at least twice the amount of the original database.
Pay attention to the following feature - the vacuum command can change the ROWID values (for more details, click here ) for tables that do not have a primary key.
In general, using the VACUUM operation is appropriate if auto_vacuum mode is not enabled and we need to remove the extra “empty” space, thereby reducing the size of the database file.
Learn more about auto_vacuum mode here . The auto_vacuum mode does not fragment the database. In this mode, if after adding data, nul-bytes remain after the values of the data themselves, they are deleted.
Thanks for attention. I hope the material can be useful to someone.
Sources:
http://sqlite.org/lang_vacuum.html
http://sqlite.org/wal.html
http://sqlite.org/pragma.html#pragma_auto_vacuum
http://sqlite.org/lang_createtable.html#rowid
I myself am a RubyOnRails developer, so for small projects I try to use the standard configuration that uses SQLite. I see no reason to drag a heavyweight DBMS into a dependency of a simple application.
One day my attention was attracted by the size of the database, the tables of which were cleared a few minutes ago. An interesting fact: when you remove an object from the database from it, free space is left in the database file. Perhaps for experienced specialists this fact is obvious, but the volume of search results on this issue did not exceed my expectations.
Consider the example with which we are dealing.
')
Create a database, and in it - a table with a simple structure: id (INTEGER, AI), phone (TEXT). Next, fill it with arbitrary values:
This database is 850,944 bytes . Number of entries - 41 922.
Run “DELETE FROM mytable”:
Database file size is 850,944 bytes . Number of entries - 0.
After importing the same 41,922 records:
Database file size is 871,424 bytes . Number of entries - 41 922.
In the latter case, volume growth is associated with the types used to store the auto-increment id field. For field values of up to 65,536, an unsigned short appears to be used . After the “delete from” and ai import, the index began with 41 923 and ended with 82 946, therefore, unsigned int or unsigned long is already used to store the values of the id field — I did not count, I don’t know for sure. And maybe not generally unsigned types are used - that's not the point.
The key question is: why after deleting data from a table, the size of the database file remains the same?
At first, I thought that the data was not deleted at all, but simply marked as deleted and then overwritten with new entries. However, this was not the case. Looking into the file after deleting the data, I did not find the previous values. This means that deleting actually deletes the data, only the memory is not released, but is clogged with ASCII 00 values.
What can be done with this? There is a special team - VACUUM.
Run the DELETE FROM mytable again. The table has been cleared, but its size is still 851 KB - everything is as before.
Execute the VACUUM command: the file size has decreased to 3 KB.
During this operation, the contents of our database were copied to a temporary file, which was overwritten by the original database file. Technically, the original file is overwritten using the Write-Ahead Logging method. Read more - here . This means that to perform the VACUUM operation on the hard disk, you must have free space - at least twice the amount of the original database.
Pay attention to the following feature - the vacuum command can change the ROWID values (for more details, click here ) for tables that do not have a primary key.
In general, using the VACUUM operation is appropriate if auto_vacuum mode is not enabled and we need to remove the extra “empty” space, thereby reducing the size of the database file.
Learn more about auto_vacuum mode here . The auto_vacuum mode does not fragment the database. In this mode, if after adding data, nul-bytes remain after the values of the data themselves, they are deleted.
Thanks for attention. I hope the material can be useful to someone.
Sources:
http://sqlite.org/lang_vacuum.html
http://sqlite.org/wal.html
http://sqlite.org/pragma.html#pragma_auto_vacuum
http://sqlite.org/lang_createtable.html#rowid
Source: https://habr.com/ru/post/172085/
All Articles