Re: Slow query in table where many rows were deleted. VACUUM FULL fixes it - Mailing list pgsql-performance

> On Jan 30, 2024, at 4:40 AM, Pavlos Kallis <pkallis@yourhero.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
VACUUMFULL. In some special cases plain VACUUM can reclaim disk space, but I think both the circumstances under which
itcan do so and the amount it can reclaim are pretty limited. An oversimplified but "mostly correct" way to think about
itis 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