pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: MichaelDBA <MichaelDBA@sqlexec.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Sohel Tamboli <sohel.tamboli0016@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Postgresql | Vacuum information
Date: Wed, 28 Feb 2018 16:04:11 -0500
Message-ID: <5A97194B.30709@sqlexec.com> (raw)
In-Reply-To: <CAKFQuwZ_HPmv6pue1fzMKakUWhaowg2_y5OEjoWaB=LeEb5TRA@mail.gmail.com>
References: <CAAbZosCMH5jo5A+AfP1VAGKh0R3brQbps8OM+oinKDrB_=Pi5g@mail.gmail.com>
	<CAAbZosDKVpWMVCVcQKU=gvonM5rNB0_VmM_nHQ0hKerR410CqQ@mail.gmail.com>
	<CAAbZosA4qoUAGSvj_EKf8tGzkynKZFF1j+hOtBAcJ6xXtxnEjw@mail.gmail.com>
	<CAAbZosCiRR2Khf4CiaH5O_Ra8QNZEN94a8EoHDyDYy2138X5vA@mail.gmail.com>
	<CAAbZosA4aaKwkGj4+4gNEcRKaX2ZUKMoaJy5Pi2dTo53tNFO_A@mail.gmail.com>
	<CAAbZosBy2wfCVAbYf4dXe6DG4n=O3G3jDr=x4gkZiqZOCvhMRA@mail.gmail.com>
	<CAAbZosC5qkNFp4by7NLe9mqyq6JZt95YwwdGPX2+K8xXadtT5g@mail.gmail.com>
	<CAAbZosCyETR=nH-y83uiwWhLaaGDoM7TUTFcXggMHxO1pWVxeg@mail.gmail.com>
	<CAAbZosB8+1aFnxi0y9f1p=LSW-Kh_gfsZQnWUExxkW-9VtBuOA@mail.gmail.com>
	<CAAbZosBB7oAWxoOoexfoxZrBRd7A3GbkzTaK=CdjaV8-E3cKhw@mail.gmail.com>
	<CAAbZosBV=6-kkY+O-zYSRdnh=LMh_Ep3yQG+yirJRaQu5TVn-Q@mail.gmail.com>
	<CAAbZosDrgqBZb2bv5CypDOUckEF7Mx8+xoSJD8W=GNnx+M6GbQ@mail.gmail.com>
	<CAAbZosDAyTWrqzEeMwKWYbgEpieTjZuSoYTODyujC=GJK2fqgw@mail.gmail.com>
	<CAKFQuwZ_HPmv6pue1fzMKakUWhaowg2_y5OEjoWaB=LeEb5TRA@mail.gmail.com>

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 <mailto:david.g.johnston@gmail.com>
> Wednesday, February 28, 2018 1:37 PM
> On Wed, Feb 28, 2018 at 11:31 AM, Sohel Tamboli 
> <sohel.tamboli0016@gmail.com <mailto: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:​
>
> ​ 
> 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 <mailto:sohel.tamboli0016@gmail.com>
> 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

view thread (6+ messages)  latest in thread

Message-ID: <5A97194B.30709@sqlexec.com>
Permalink:  ../5A97194B.30709@sqlexec.com/
Also on:    postgresql.org/message-id/5A97194B.30709@sqlexec.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: MichaelDBA@sqlexec.com, david.g.johnston@gmail.com, sohel.tamboli0016@gmail.com
  Subject: Re: Postgresql | Vacuum information
  In-Reply-To: <5A97194B.30709@sqlexec.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox