Mysql – Are databases only growing in size


Databases use the file system to store the data.
As far as I know it is not possible to delete a record from a random access file.
So does that mean that when we do a DELETE FROM TABLE the size of the table i.e. the file that stores the table never decreases?
So databases essentially keep growing and never reduce in size?

Best Answer

It is certainly possible to delete data from a random access file.

Realistically, though, virtually any database will do a soft delete and mark data as deleted rather than physically deleting data. At some point, something else will then reuse the space that the deleted row had been using (what operations reuse the space will depend on the database among other things). And different databases may provide tools to automatically or manually reclaim that space.

Practically, this is basically the same thing that the operating system does when you delete a file. The operating system removes the file system's pointer to the file just like the database removes the reference to the row from the index. The actual data for the file still exists on the drive. At some point, some other file system operation will eventually reuse that space. Some operating systems provide a "defrag" utility that will go through and make all the free space contiguous and move the allocated data to the "beginning" of the drive.