| From: | Philip Semanchuk <philip(at)americanefficient(dot)com> |
|---|---|
| To: | Pavlos Kallis <pkallis(at)yourhero(dot)com> |
| Cc: | postgres performance list <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: Slow query in table where many rows were deleted. VACUUM FULL fixes it |
| Date: | 2024-01-30 20:08:40 |
| Message-ID: | E73D6BCC-388B-4029-83AF-9A48A3FF86AD@americanefficient.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
> On Jan 30, 2024, at 4:40 AM, Pavlos Kallis <pkallis(at)yourhero(dot)com> wrote:
>
> Shouldn't VACUUM ANALYZE reclaim the disk space?
Hi Pavlos,
The short answer to this is “no”. That’s an important difference between VACUUM (also known as “plain” VACUUM) and VACUUM FULL. In some special cases plain VACUUM can reclaim disk space, but I think both the circumstances under which it can do so and the amount it can reclaim are pretty limited. An oversimplified but "mostly correct" way to think about it is that plain VACUUM can't reclaim disk space, whereas VACUUM FULL can.
This is covered in the 4th paragraph of the doc of the VACUUM command --
https://www.postgresql.org/docs/current/sql-vacuum.html
So in your case those 5m rows that you deleted were probably still clogging up your table until you ran VACUUM FULL.
Hope this helps
Philip
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David Rowley | 2024-01-30 20:38:36 | Re: Slow query in table where many rows were deleted. VACUUM FULL fixes it |
| Previous Message | Laurenz Albe | 2024-01-30 15:37:09 | Re: Slow query in table where many rows were deleted. VACUUM FULL fixes it |