Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUK51-002AUp-Oy for pgsql-general@arkaria.postgresql.org; Thu, 18 Jul 2024 05:53:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sUK4z-00AZFO-Nn for pgsql-general@arkaria.postgresql.org; Thu, 18 Jul 2024 05:53:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUK4z-00AZEJ-Cb for pgsql-general@lists.postgresql.org; Thu, 18 Jul 2024 05:53:25 +0000 Received: from mail-oo1-xc36.google.com ([2607:f8b0:4864:20::c36]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sUK4w-0008jQ-T2 for pgsql-general@lists.postgresql.org; Thu, 18 Jul 2024 05:53:24 +0000 Received: by mail-oo1-xc36.google.com with SMTP id 006d021491bc7-5cebd3bd468so191408eaf.1 for ; Wed, 17 Jul 2024 22:53:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721282002; x=1721886802; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=iAJCPUPoj5fVFrQDV26yTgWUzuZjAKBMbQPVav55iUQ=; b=Xgt651aBhd98EnrBYmGg4QC9LdvIcwW0eKbWZeutbzZ2NlFg5wSTj1tohpJ6N7Hz5Q VoX2tIZg+vT9/JZGOPKNy4xIa5AvaFYniKepqGpzKbCXiRJDKb6kApF62XewsinYXlu0 jezcuKp8mZMkltNrom+/2by9uK8SfzkQcl72DWGOSTn9vDD2BvvdaznpQq/WORCk8usq zBwKt9as0GRnB+R9J6ggjrArydfWMm1DdZ3T+OwZTlS5A7JzAo1+/9EN5Fx9AcVPUdhI 4qXkrWNWm3VYv9flL7hEwmQHqB61ELf4Bvd+LcpTzrCNVdSeW0MrkaK3yBZPb+jqFQxC qapQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721282002; x=1721886802; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=iAJCPUPoj5fVFrQDV26yTgWUzuZjAKBMbQPVav55iUQ=; b=omvurT1r7BpajLoZuWppAIMUpzC3IQ2mJ7jqwXyXoeJbelbCRXQd/NDfGvbwFYagdK gHXcl/i57URW1SbXfY87C0r7r3mdGtWWVJ/PSTNThdOPvjsrxw4FuGtaAM1wCEnmsAhn 3746er3wwxMQsl7nbH1vJQnh7uIKpQIC31RPnkUIkwnBKiaJW0eDT2aS+tEa5T4GdNFZ mmwApuvCs9tRa21uNRA75KQTtciV9ZmtY9B0HsmcyRpxjdIO/84pVVhUZmzUm8wQIHG3 Uh9shqS9aP4jw3Em6dQ6Y//R4DMRhwOTwkD/72k+qkT5ovbnTUz3zRuvWfWwDnsTu37F OxUA== X-Gm-Message-State: AOJu0Yz/wIvqEKkqCG8l96ScT6afqusAG3gZIPdimlFALdLui2awfRqe L8Im/GZnxssWH9EQ+cOMi1AItnYO8Ojkz1Qogn2UoOmuHxti0dh8bXEBO3T6mj27YAS4F4ROpPp unq2kwiRXGci0GBMVpiXhnbnBAWE= X-Google-Smtp-Source: AGHT+IG/lnqse8elbMmjLSNymQbqToqfexwuAx2RMhzfj/Pb4zObQauEZeG26sabnesFzqG9s6Ydie53CkDboTFLkG4= X-Received: by 2002:a05:6820:260e:b0:5c4:10df:c479 with SMTP id 006d021491bc7-5d41933c237mr4470595eaf.2.1721282002277; Wed, 17 Jul 2024 22:53:22 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a05:6802:29a5:b0:539:aa10:6c7 with HTTP; Wed, 17 Jul 2024 22:53:21 -0700 (PDT) In-Reply-To: References: From: "David G. Johnston" Date: Wed, 17 Jul 2024 22:53:21 -0700 Message-ID: Subject: Re: Regarding vacuum freeze locking mechanism To: Durgamahesh Manne Cc: "pgsql-general@lists.postgresql.org" , "andrian.klaver@aklaver.com" Content-Type: multipart/alternative; boundary="0000000000004ddf15061d7f32eb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004ddf15061d7f32eb Content-Type: text/plain; charset="UTF-8" On Wednesday, July 17, 2024, Durgamahesh Manne wrote: > when autovacuum runs , it will freeze the transaction ID (TXID) of the > table it's working on. > This statement is incorrect. A table as a whole does not have a txid. Freezing makes it so individual tuples get assigned an always-in-the-past txid. Then, the table can recompute how far away its furthest back txid is from being considered in-the-future. That gap should increase since the furthest away txids were the ones being frozen. Inserts might get delayed a brief moment if it just happens the page they want to insert onto is presently being worked on. But the odds there seem low. David J. --0000000000004ddf15061d7f32eb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wednesday, July 17, 2024, Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
when autovacuum runs , it will freeze= the transaction=C2=A0ID (TXID) of the table it's working on.

This statement is incorrect.=C2=A0 A table as = a whole does not have a txid.=C2=A0 Freezing makes it so individual tuples = get assigned an always-in-the-past txid.=C2=A0 Then, the table can recomput= e how far away its furthest back txid is from being considered in-the-futur= e.=C2=A0 That gap should increase since the furthest away txids were the on= es being frozen.

Inserts might get delayed a brief= moment if it just happens the page they want to insert onto is presently b= eing worked on.=C2=A0 But the odds there seem low.

David J.

--0000000000004ddf15061d7f32eb--