Does VACUUM (not FULL) in PostgreSQL make it easier to cache into memory?

Asked 2 years ago, Updated 2 years ago, 56 views

According to the video below from 6:00, it will be difficult to cache into memory if unnecessary areas increase.
On the other hand, according to 7:30~, non-FULL VACUUM only registers unnecessary areas with FSM.
Even after VACUUM, if half of the disk is not needed, wouldn't it be possible to cache only 0.5GB of records even if there is 1GB of cache memory?

Learn more now VACUUM Tomoaki Sato (YouTube)

postgresql

2022-09-30 19:29

2 Answers

According to the video below, 6:00~, it will be difficult to cache more unnecessary space into memory.

The cache here is managed by PostgreSQL, which seems to hold data read from disk in memory.
Unnecessary areas are old data at UPDATE and data in DELETE rows, and it seems that logical deletion is enough to be flagged internally.Cached 8KB pages also contain unnecessary space, thus reducing the amount of valid (non-unnecessary) data in the cache.

On the other hand, according to 7:30~, non-FULL VACUUM only registers unnecessary areas with FSM.
Even after VACUUM, if half of the disk is not needed, wouldn't it be possible to cache only 0.5GB of records even if there is 1GB of cache memory?

After VACUUM, which is not full, the unnecessary area is not reused, so I think the situation will remain the same.Subsequent UPDATE and INSERT reuse unnecessary space, increasing the percentage of valid cache data.


2022-09-30 19:29

I don't know what the phrase "unnecessary space" is, but if you're talking about a database file that doesn't have to be read or written at this moment, you don't use cache memory because it doesn't read or write.


2022-09-30 19:29

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.