CODEWITHSUNDEEP
amazon-redshift
diemacht
diemacht

Reputation: 2102

Vacuum does not reclaim disk space

I have a fact table with 9.5M records. The table uses distyle=key, and is hosted on a RedShift cluster with 2 "small" nodes.

I made many UPDATE and DELETE operations on the table, and as expected, I see that the "real" number of rows is much above 9.5M.

Hence, I ran vacuum on the table, and to my surprise, after vacuum finished, I still see that the number of "rows" the table allocates did not come back to 9.5M records.

Could you please advice what may be a reason for such a behavior?

What would be the best way to solve it?

A little bit of copy-pastes from my shell:

The fact table I was talking about:

select count(1) from tbl_facts;
9597184

The "real" number of records in the DB:

select * from stv_tbl_perm where id= 332469;
slice | id | name | rows | sorted_rows | temp | db_id | insert_pristine | delete_pristine
-------+--------+--------------------------------------------------------------------------+----------+-------------+------+--------+-----------------+-----------------
0 | 332469 | tbl_facts | 24108360 | 24108360 | 0 | 108411 | 0 | 1
2 | 332469 | tbl_facts | 24307733 | 24307733 | 0 | 108411 | 0 | 1
3 | 332469 | tbl_facts | 24370022 | 24370022 | 0 | 108411 | 0 | 1
1 | 332469 | tbl_facts | 24597685 | 24597685 | 0 | 108411 | 0 | 1
3211 | 332469 | tbl_facts | 0 | 0 | 0 | 108411 | 3 | 0

(All together is almost 100M records).

Thanks a lot!

Upvotes: 1

Views: 1587

Answers (1)

Paladin
Paladin

Reputation: 630

I think you need to run analyze for the particular fact table. Analyze will update the statistics linked to the fact table after you run the vacuum (or any other command where the count of rows changes).

Do let us know if this was the case or not (i do not have a table handy where i can test this out) :-)

Upvotes: 1

Related Questions