From: Murthy Nunna <mnunna@fnal.gov>
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Vacuumdb on a table
Thread-Topic: Vacuumdb on a table
Thread-Index: AdoB/PzeqJ4SW6LsTCeOSNQJAkSFnw==
Date: Wed, 18 Oct 2023 20:05:53 +0000
Message-ID: 
 <DM8PR09MB66773DD56212C1EEF997ECDBB8D5A@DM8PR09MB6677.namprd09.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?A/QhqteM5EmmxQCLaryV4FI+QdiV5PAQiXjJzdM85YC3e/e8b3NX3WrxNMwf?=
 =?us-ascii?Q?J44YNzrBbeNGE2zgczui4VNPJrLw/8vFTLXhYtv+pr17cyVLqPH03W1aLiBT?=
 =?us-ascii?Q?sJG/mGz+g2pzURZV6WKNPv9RWs2VNMRu111JAba6XDoghljJktcsHSQPRukJ?=
 =?us-ascii?Q?LyocO63Ef6xKb5rPtO2xC2x1k3N1GmCce00zlnSFrdmiGQmK2Iq8Die03uZj?=
 =?us-ascii?Q?kHyLeWNW+sRPCzSUf4KWA5AnRmgDweXYbN4hh/Jx4gbEm27wOHWHWe3fbkbG?=
 =?us-ascii?Q?amt+19YjSQX0kS3midiod9slRL3Yd5X1D7wuwOPbcYLWWoiqZNuk2pNSKwui?=
 =?us-ascii?Q?1Nx/Jbn9UnfE9pwOnlASiB+pEmEHPLVKAzwRLtWdTCBiV8rMyn/HDVwovjw3?=
 =?us-ascii?Q?/Cpmfe2S1WEl5SZks6Wbri0yWpB2mvXYK9IBVRA3fHO10QskHRWDAZxKsOFf?=
 =?us-ascii?Q?283aFXjblplFI03NJh9mfnxI1qPBpJpbHPYXrL7yKjVPhuuLxgURvCC8hwuv?=
 =?us-ascii?Q?S1ul7xgC2hV/KNCu8mExdfrQ8DIjGZ847Sb9cgFWimrmXmN4Ru5jGaRyje03?=
 =?us-ascii?Q?8Xdc2fHU1G1kTX2mKaB2MyG/FuXAgkwyFxXDIT80i03VpQDJy1sR8PP2PMEs?=
 =?us-ascii?Q?F5BfSf85FBPzA9dI6a/JsIr8Nbgy6LzVEqGc++KLhbCzioPSdPyTJOQ4o1m1?=
 =?us-ascii?Q?M16yv2Y4YmSapOHlnO2VZD47AK+KY+LUpMqWNBc4ZHjbmmdrSFcbB/8nGsIi?=
 =?us-ascii?Q?Q0LWDxK5taXaf0AnbVwdg59okgDEb2WfQFdtjiAA5MuQ8wRb9GFVfDgfpz5i?=
 =?us-ascii?Q?Urt2mxiFC1sgBS1j542VPdeJ9oIxWnZdAQBDtGz1vJlMlzJEkvQXtNipgU8A?=
 =?us-ascii?Q?/Mis7JwNY7qGCfnhpA00SSDu++r+sL9R7M3mrvv09AEZoxo5lRHOiOxhhodx?=
 =?us-ascii?Q?PkyuiB7jmpClckyMDQOtgU/ZzNV4nZuUfJt5tCv4fdw4JkVaFeUL7/JZdZWj?=
 =?us-ascii?Q?Rs2dCNMal9hKKL1auPdYt85W4vlvz1oF5vvI9vXL7+3lv4mi1CnZdeOK4IYX?=
 =?us-ascii?Q?zAmZS9cPOHWnvA4IwRelnrihlq+fNtPJ/upgpLaSAtoS74w++M95hJU04QAo?=
 =?us-ascii?Q?nOCEkLKgss8JvPhtDBNijuH1OJD0pibFA5Ea1N9KvBPvwFG3hsmZom3cZaOV?=
 =?us-ascii?Q?HeyXbqjSLW3+zEG7PtsQr02aKJRHWx8Phexq6jh4O30vBDKc8KMMy3G/07kM?=
 =?us-ascii?Q?T//767CuIJTJjWaK1bn5jgOCULABMh8TKASEPoQmr0ZX0ySrWOR7Ri3UlNF5?=
 =?us-ascii?Q?In5wtc1GetXx/lwXH3+4OK/yQpe3Ixr+WV3DVPtsGtPdxYWekB54jpxk/ppO?=
 =?us-ascii?Q?az0s2K2Kq1RuPJ8PUBhAKdXeZe08hTOGopSbsnRNg4c/0GURRctSmOd9/w/y?=
 =?us-ascii?Q?y3285RRizvnxkw3GDtYZHGCcpC8UEAxC6SaPm7Tk5cAyGsZkjOZYY8NreeiM?=
 =?us-ascii?Q?/zm44bL3ZDrYpbAAP2tIEL7YcxnxNVNDeTMLOQGNlNbmrOS3I1fczStV3dCB?=
 =?us-ascii?Q?kYQCr/Z2s1dF8INg6W/kcRH7rJi/9TDuttyD8d50?=
Content-Type: multipart/alternative;
	boundary="_000_DM8PR09MB66773DD56212C1EEF997ECDBB8D5ADM8PR09MB6677namp_"
MIME-Version: 1.0
X-OriginatorOrg: fnal.gov
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: DM8PR09MB6677.namprd09.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 df9c1d7b-898d-40f7-facd-08dbd015a222
X-MS-Exchange-CrossTenant-originalarrivaltime: 18 Oct 2023 20:05:53.5553
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 9d5f83d3-d338-4fd3-b1c9-b7d94d70255a
X-MS-Exchange-Transport-CrossTenantHeadersStamped: SA1PR09MB11753
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/DM8PR09MB66773DD56212C1EEF997ECDBB8D5A%40DM8PR09MB6677.namprd09.prod.outlook.com>
Precedence: bulk

--_000_DM8PR09MB66773DD56212C1EEF997ECDBB8D5ADM8PR09MB6677namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi,

The first table in the following query resulted in age(c.relfrozenxid) =3D =
148795396. But when I manually run vacuumdb command (vacuumdb -d db1 -t tab=
1) on that table it is not lowering the relfrozenxid. There is no indicatio=
n in the pglog that vacuumdb failed.

SELECT c.oid::regclass
    , age(c.relfrozenxid)
    , pg_size_pretty(pg_total_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n on c.relnamespace =3D n.oid
WHERE relkind IN ('r', 't', 'm')
AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC ;

I am wondering why vacuumdb is unable to lower relfrozenxid on this table? =
It seems to work on other tables though.

Thank you!

--_000_DM8PR09MB66773DD56212C1EEF997ECDBB8D5ADM8PR09MB6677namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;
	font-weight:normal;
	font-style:normal;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#0563C1" vlink=3D"#954F72" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">Hi,<o:p></o:p></spa=
n></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">The first table in =
the following query resulted in age(c.relfrozenxid) =3D 148795396. But when=
 I manually run vacuumdb command (vacuumdb -d db1 -t tab1) on that table it=
 is not lowering the relfrozenxid. There
 is no indication in the pglog that vacuumdb failed. <o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-family:&quot;Arial&quot;,sans-se=
rif">SELECT c.oid::regclass<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-family:&quot;Arial&quot;,sans-se=
rif">&nbsp;&nbsp;&nbsp; , age(c.relfrozenxid)<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-family:&quot;Arial&quot;,sans-se=
rif">&nbsp;&nbsp;&nbsp; , pg_size_pretty(pg_total_relation_size(c.oid))<o:p=
></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-family:&quot;Arial&quot;,sans-se=
rif">FROM pg_class c<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-family:&quot;Arial&quot;,sans-se=
rif">JOIN pg_namespace n on c.relnamespace =3D n.oid<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-family:&quot;Arial&quot;,sans-se=
rif">WHERE relkind IN ('r', 't', 'm')<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-family:&quot;Arial&quot;,sans-se=
rif">AND n.nspname NOT IN ('pg_toast')<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-family:&quot;Arial&quot;,sans-se=
rif">ORDER BY 2 DESC ;<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">I am wondering why =
vacuumdb is unable to lower relfrozenxid on this table? It seems to work on=
 other tables though.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">Thank you!<o:p></o:=
p></span></p>
</div>
</body>
</html>

--_000_DM8PR09MB66773DD56212C1EEF997ECDBB8D5ADM8PR09MB6677namp_--