My database table had a size of 38GB, which I decided to reduce. I reviewed some data in a certain backup_data column (TEXT column( and decided to reduce the data there.
On average, I removed about 60% of the size in the backup_data column for selected rows. How do I know it was 60%? -> I copied the text of the backup_data column to a text file and saved it. Then I removed the data that I didn’t considered important and saved it again. This file now had 40% of the original size.
In theory, this sounded promising. So I applied the same process for all data in the database table that I wanted to reduce.
However, my surprise was that the size of this database table went from 38GB to 41GB. I thought that I might have been saving some extra data to the column (like concatenating of the old version and new version of the data), but that was not the case.
In the database table is saved the right data, which is 60% smaller than the previous data, but instead of reducing the size of the database table, it went up.
How could this be possible? Am I missing anything obvious? I am using a managed PostgreSQL database on DigitalOcean.
EDIT: I ran the VACUUM command and got the following output:
VACUUM
INFO: vacuuming "db_name.public.users_raw_data"
INFO: finished vacuuming "db_name.public.users_raw_data": index scans: 0
pages: 0 removed, 51631 remain, 13759 scanned (26.65% of total)
tuples: 805 removed, 2044904 remain, 0 are dead but not yet removable
removable cutoff: 75327337, which was 0 XIDs old when operation ended
frozen: 57 pages from table (0.11% of total) had 2081 tuples frozen
index scan bypassed: 846 pages from table (1.64% of total) have 9598 dead item identifiers
avg read rate: 72.554 MB/s, avg write rate: 43.789 MB/s
buffer usage: 14081 hits, 12642 misses, 7630 dirtied
WAL usage: 7735 records, 7630 full page images, 57005828 bytes
system usage: CPU: user: 0.16 s, system: 0.23 s, elapsed: 1.36 s
INFO: vacuuming "db_name.pg_toast.pg_toast_52515"
INFO: finished vacuuming "db_name.pg_toast.pg_toast_52515": index scans: 1
pages: 0 removed, 5226736 remain, 1504608 scanned (28.79% of total)
tuples: 2241408 removed, 19483927 remain, 0 are dead but not yet removable
removable cutoff: 75327337, which was 93 XIDs old when operation ended
frozen: 898673 pages from table (17.19% of total) had 3637393 tuples frozen
index scan needed: 576864 pages from table (11.04% of total) had 2318261 dead item identifiers removed
index "pg_toast_52515_index": pages: 60198 in total, 6357 newly deleted, 6357 currently deleted, 1793 reusable
avg read rate: 31.957 MB/s, avg write rate: 31.645 MB/s
buffer usage: 1621246 hits, 2079416 misses, 2059113 dirtied
WAL usage: 5022458 records, 2059118 full page images, 12166056017 bytes
system usage: CPU: user: 26.60 s, system: 51.76 s, elapsed: 508.36 s
INFO: analyzing "public.users_raw_data"
INFO: "users_raw_data": scanned 30000 of 51631 pages, containing 1116811 live rows and 5800 dead rows; 30000 rows in sample, 1922069 estimated total rows
12