User-Agent: Cyrus-JMAP/3.1.6-728-g133121a-fmstable-20190620v1
Mime-Version: 1.0
Message-Id: <9887eca2-9316-4f76-bc7b-545d7ccf516d@www.fastmail.com>
In-Reply-To: <29887.1561126102@sss.pgh.pa.us>
References: <15eddc60-6441-e55e-f742-bd7ea37f1873@quadratum-braccas.com>
 <31037.1560988222@sss.pgh.pa.us>
 <053c95f2-1425-23ac-0308-ca22887db156@quadratum-braccas.com>
 <CAHw75vuMizA6F0B+DTbbyG09GYfc8X2d5E+QKt75uXot4bquiA@mail.gmail.com>
 <02C3BE5C-ED36-4B36-85C3-90C774FA528B@crazybean.net>
 <ef2d3985-cbb4-4d42-a06a-0c8163dc85a7@www.fastmail.com>
 <29887.1561126102@sss.pgh.pa.us>
Date: Fri, 21 Jun 2019 10:11:52 -0400
From: SBob <sbob@quadratum-braccas.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Vacuum not cleaning up rows.
Content-Type: multipart/alternative;
 boundary=e5b9d476ad2c4f429f0b2d1064f46f98
Precedence: bulk

--e5b9d476ad2c4f429f0b2d1064f46f98
Content-Type: text/plain

Got it. can someone point me to more reading on snapshots and snapshot-horizon?

Thanks!


On Fri, Jun 21, 2019, at 10:08 AM, Tom Lane wrote:
> SBob <sbob@quadratum-braccas.com> writes:
> > I do see older backend_start values (from around 6 hours ago). However I am confused as to how a connection to one database in a cluster can prevenr vacuum from cleaning up row versions in a table within another database. How is that possible since onle one database has access to this table?
> 
> Because the snapshot-horizon calculations are done system-wide.
> We can't just lobotomize them to only consider the current DB,
> because that would result in wrong row-cleanup decisions for shared
> catalogs.
> 
> There are some attempts to refine that when vacuuming a non-shared table,
> but that can only go so far given the limited amount of state that
> backends expose about this. Doing better would require expending
> more effort in transaction start/transaction stop/snapshot creation,
> which are hot spots so it hasn't seemed like it'd be a good tradeoff.
> 
> regards, tom lane
> 
> 
> 

--e5b9d476ad2c4f429f0b2d1064f46f98
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>Got it. can som=
eone point me to more reading on snapshots&nbsp; and snapshot-horizon?<b=
r></div><div><br></div><div>Thanks!<br></div><div><br></div><div><br></d=
iv><div><br></div><div>On Fri, Jun 21, 2019, at 10:08 AM, Tom Lane wrote=
:<br></div><blockquote type=3D"cite" id=3D"qt"><div>SBob &lt;sbob@quadra=
tum-braccas.com&gt; writes:<br></div><div>&gt; I do see older backend_st=
art values (from around 6 hours ago). However I am confused as to how a =
connection to one database in a cluster can prevenr vacuum from cleaning=
 up row versions in a table within another database. How is that possibl=
e since onle one database has access to this table?<br></div><div><br></=
div><div>Because the snapshot-horizon calculations are done system-wide.=
<br></div><div>We can't just lobotomize them to only consider the curren=
t DB,<br></div><div>because that would result in wrong row-cleanup decis=
ions for shared<br></div><div>catalogs.<br></div><div><br></div><div>The=
re are some attempts to refine that when vacuuming a non-shared table,<b=
r></div><div>but that can only go so far given the limited amount of sta=
te that<br></div><div>backends expose about this.&nbsp; Doing better wou=
ld require expending<br></div><div>more effort in transaction start/tran=
saction stop/snapshot creation,<br></div><div>which are hot spots so it =
hasn't seemed like it'd be a good tradeoff.<br></div><div><br></div><div=
>regards, tom lane<br></div><div><br></div><div><br></div><div><br></div=
></blockquote><div><br></div></body></html>
--e5b9d476ad2c4f429f0b2d1064f46f98--