lets continue from the last post, ..vacuum in postgressql..
now as we know vacuum is an utility to reclaim unused space from dead rows .
now let's discuss more.
vacuum types.
1. vacuum broadly can be done either plain vacuum operation or full vacuum operation.
each operation having its pros and cons.
we will discuss more on those .
plain vacuum only mark the dead rows, free for further use, so in case of future insert or update , postgressql can use the space, instead on consuming new space
Full vacuum - it releases the dead row occupied space from the tulle and added the space to os.
but the issue with full vacuum is it creates a new tuple only with the required rows (removing the dead rows) and then postgres renames the new tuple to older one(having dead rows) and after rename completed, it drops the old table, and reclaims the space to os.
but we have certain limitations with full vacuum operation.
like,
1. as it creates a temporary table to move data, so for the operation to complete we need almost double size (of the candidate tuple for which we are expecting vacuum operation) , so we should have sufficient space in is for the operation.
2. it needs an exclusive lock on table till the full vacuum operation complete, so that lead to application unavailability and downtime.
and it's also a slower operation as compared to plain vacuum, .
now I know ur mind is crazy to learn, when and how frequently I will do the vacuum, and what I need to check after my vacuum operation, ok, so we will discuss those too..
Till the time as you go through this post you can share me your experience with vacuum operation in your environment, any issues, any amazing achievement by doing vacuum on your db. ( I know how satisfying it is to see db running faster and getting appreciation from client/ application team for the same..😁)
stay tuned, ..😊#vacuum #postgresql