I have added
boolean type column to table with roughly 100 million rows.
Afterwards I have manually executed
UPDATE statements to set the new column value to true/false depending on other column values.
UPDATES were done overnight (took a few hours).
Then I set the column to NOT NULL.
After all these steps table size increased almost twice. ~40GB -> 68GB.
I have used following query for updating in chunks (To not lock the table completely):
WITH cte AS ( SELECT id from results_storage WHERE condition < 100 AND newboolean IS NULL limit 10000 FOR UPDATE SKIP LOCKED ) UPDATE results_storage r SET newboolean = FALSE FROM cte WHERE r.id = cte.id RETURNING r.id;
What could be the cause?
Is there any way to reduce the space taken?
I have expected single boolean column to take very little extra space. (+~100MB for each 100M rows as boolean should take 1 byte storage).