Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1er9AR-0004Dt-FD for pgsql-admin@arkaria.postgresql.org; Wed, 28 Feb 2018 21:21:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1er9AQ-0005hi-Fl for pgsql-admin@arkaria.postgresql.org; Wed, 28 Feb 2018 21:21:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1er9AQ-0005hL-6k for pgsql-admin@lists.postgresql.org; Wed, 28 Feb 2018 21:21:38 +0000 Received: from mout.perfora.net ([74.208.4.197]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1er9AM-0002XM-Id for pgsql-admin@postgresql.org; Wed, 28 Feb 2018 21:21:37 +0000 Received: from [192.168.1.101] ([98.169.215.140]) by mrelay.perfora.net (mreueus003 [74.208.5.2]) with ESMTPSA (Nemesis) id 0Mb9hN-1fABJh3qIt-00KdCF; Wed, 28 Feb 2018 22:21:31 +0100 Message-ID: <5A971D59.5050000@sqlexec.com> Date: Wed, 28 Feb 2018 16:21:29 -0500 From: MichaelDBA User-Agent: Postbox 5.0.22 (Windows/20171208) MIME-Version: 1.0 To: "David G. Johnston" CC: Sohel Tamboli , pgsql-admin@postgresql.org Subject: Re: Postgresql | Vacuum information References: <5A97194B.30709@sqlexec.com> In-Reply-To: <5A97194B.30709@sqlexec.com> Content-Type: multipart/alternative; boundary="------------010000050100010105040303" X-Provags-ID: V03:K0:kz40sFEeGYTG8Uk+hl2/34XrW0cIxlhfMe4IL21c0lRvw1gS46m 95X3mZHTZ1u19DcpeVwMqcHviHStaRs0t4EOQkgyLG0gnvvyuN7p39XtuK/Ngdl1An5b2/4 0xb1JMiBLNOI2QqUvGIfQtlLIxjzDR6FvyaiRciAhncdw7pH07NVhvR3xhIMbxKc6ASFkg1 5QOXp4Dh6nAGtPNgyEY0g== X-UI-Out-Filterresults: notjunk:1;V01:K0:L2b88/L+nB4=:ggaPANL6MUn0GKKRul9y2D Vz/NkGD4N4MJ496KBVrGecjt1BCEKJROlQgpO3eyJ57No/L5+BqgVZUlM1uLyiLF/jzVViV/0 zhmxB4oDHXncfhVjWumLEhB8YMzpr7lXxv7ln9u2Kp5dsE2yjaMjYj1PzAcED2V22by+/fJqm ElIHZzkDDg70k/vBWhFlCs3pmy/by20izwAg7qG+Rpn11EdvfkhyFaKEgOTctNrhP2POvDZBf 2mGBcDewnr1rKS2ks/e/pduFrBoxRfkZ+QNAumD7Rif7OAHssDN0xQeBezIuKzRSjZsfu3xqF +T087NGKrUFuAL2eUiV3Imk6cJGJUzmYvL69XTfnmtCKHSjbp2gH+MhaXNTAdw9j8CMa3HBtZ SRsH+pouTS6prQD+mLAtb8hPqf7Q6NYr5mR2ygeKYsobe/y8XPHiySpSa749hCPxBiOxyn9gM Z9haHev5kOHsuldKooMRCusz0hLEMyUcBFLp/e/ezf69JKunUNkcLAhH94XKRgpwS5M2GQNqA 1zCV0l3AnMXcsoDQBsHtpCHigC+tkCH2gLdAfU62uzSRCq/jRC4Ipt980x+mxHKhuAtAtPNe9 D8Vn0rwgKlEyAPi6+9o7CUASUJ9PO1nMwzijnZbuutAbqpoGTN+p2A1rcG8j0mZge7YtLC7ep SZ2G22Z9Q23TI2OedbziqMELEuzFlpmQcX0hkM6MorgMQJcciT5dDvUTX8ZLSqgbZS8Q2MMjt PZF/jwd/NH/F0KuDFAXmzyhu+luKwa92R/BKRaGW4TGUIhYEm/Wy2c8fEt0= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------010000050100010105040303 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > MichaelDBA > 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 > > David G. Johnston > Wednesday, February 28, 2018 1:37 PM > On Wed, Feb 28, 2018 at 11:31 AM, Sohel Tamboli > >wrote: > > 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. > > > There would be no point to non-full vacuuming if "new data [was] > inserted ... at the end of the table everytime"...​ > > ​ This logic is also documented:​ > > ​ > https://www.postgresql.org/docs/10/static/routine-vacuuming.html#VACUUM-BASICS > ​ > > ​" > The space it occupies must then be reclaimed for reuse by new rows, to > avoid unbounded growth of disk space requirements. This is done by > running VACUUM. > ​"​ > > ​ David J. > > Sohel Tamboli > 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 --------------010000050100010105040303 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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
On Wed, Feb 28, 2018 at 11:31 AM, Sohel Tamboli <sohel.tamboli0016@gmail.com> wrote:
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. 


There would be no point to non-full vacuuming if "new data [was] inserted ... at the end of the table everytime"...​

​ This logic is also documented:​

​"
The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM.
​"​

​ David J.

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

--------------010000050100010105040303--