wee bit further elaboration...
MVCC for updates is DELETE followed by INSERT. That is what the docs are talking about when mentioning "new rows", not the type of "new row" that is done with an INSERT statement which is ALWAYS appended to end of table. I actually had to ask about this one in PostgreSQL slack chat because I wasn't 100% positive that index clustering order does not influence INSERT location, but apparently even clustering order does not influence a location for INSERTs other than the end of the table.
We all learned something here, lol
Regards,
Michael Vitale
Wednesday, February 28, 2018 4:04 PM
Actually, David, that reference to the docs about "new rows" in
https://www.postgresql.org/docs/10/static/routine-vacuuming.html#VACUUM-BASICS, recovering disk space, relates to UPDATEs and DELETEs, not INSERTs. The main gain for reusing dead space is where UPDATES are concerned where it will try to insert on the same page if possible.
Regards,
Michael Vitale
Wednesday, February 28, 2018 1:37 PM
Wednesday, February 28, 2018 1:31 PM
Hi,
I need some information on vacuum in postgresql. I know that "vacuum full" recreate full table and releases space to OS. Only "vacuum" clears the dead tuples and free the space but does not returns free space to OS, indeed it keeps free space as a part of table.
My question is, after running only "vacuum", how does new data or insert is written to the table? I need to know that Does new data gets inserted in free space available in between of live tuples or gets inserted at the end of table everytime.
Appreciate your quick response!!
Thanks,
Sohel