2

I have a .sqlite database:

$db = new PDO("sqlite:db.sqlite");

It's size is 60MB. It has 1 table. One of this table columns (the only heavy one) holds BLOB_TEXT data. I am trying to compress this column values:

$pdo = $db->query("SELECT id, house_plan FROM houses");
$houses = $pdo->fetchAll(PDO::FETCH_ASSOC);
$initial_length = 0;
$compressed_length= 0;

for ($i = 0; $i < count($houses); $i++) {
  $id = $houses[$i]["id"];
  $house_plan = $houses[$i]["house_plan"];
  $compressed = gzcompress($house_plan);

  $initial_length += strlen($house_plan);
  $compressed_length += strlen($compressed);
  flush();

  $query = $db->prepare("UPDATE houses SET house_plan=? WHERE id=" . $id);
  $query->bindParam(1, $compressed, PDO::PARAM_LOB);
  $query->execute();
}

Then printing summed initial BLOB_TEXT data length and compressed data length (which replaced the initial data in the table):

echo "Before: " . $initial_length . ", After: " . $compressed_length;
// Before: 52222553, After: 2577948

And their difference in %:

echo "Difference: " . (100 - round($compressed_length / $initial_length * 100)) . "%";
// Difference: 95%

Afterwards I check the size of the database, and it's still 60MB. BLOB_TEXT data difference was 95%, but the size of the database is not changed.

How that might be? Or what I am doing wrong compressing BLOB_TEXT?
I expect database size reduces as well, because this is the only heavy column I have.

1
  • Ran into this today, very helpful @Nik Commented Apr 11, 2023 at 16:33

1 Answer 1

6

Before the compression, the database was filled to about 100 % with data. After the compression, it contains about 5 % data and about 95 % free space.

Usually, it is a good idea to avoid reducing the file size because the free space will be filled up with new data later anyway. If you want to re-pack the database, run VACUUM.

Sign up to request clarification or add additional context in comments.

2 Comments

+1 VACUUM rebuilds the database, reclaims empty space and reduces the size of the database file.
I will accept the answer if you enrich it with $query = $db->prepare("VACUUM"); $query->execute();, what is definitely going to underline the solution matching my question and the source code in the description.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.