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 1tIXN0-00EwrY-I2 for pgsql-general@arkaria.postgresql.org; Tue, 03 Dec 2024 18:11:34 +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 1tIXMy-00BppW-1V for pgsql-general@arkaria.postgresql.org; Tue, 03 Dec 2024 18:11:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tIXMx-00BppN-IV for pgsql-general@lists.postgresql.org; Tue, 03 Dec 2024 18:11:32 +0000 Received: from mx0b-004aa801.pphosted.com ([148.163.140.28]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tIXMu-000rxW-Kw for pgsql-general@lists.postgresql.org; Tue, 03 Dec 2024 18:11:31 +0000 Received: from pps.filterd (m0204361.ppops.net [127.0.0.1]) by mx0b-004aa801.pphosted.com (8.18.1.2/8.18.1.2) with ESMTP id 4B3HZY4o002026; Tue, 3 Dec 2024 12:11:25 -0600 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snapon.com; h= content-type:date:from:in-reply-to:message-id:mime-version :references:subject:to; s=s2048a; bh=ASlJpBgHZIm5FXae+SwoNH24PYS 2L/3XnBq7lSUYy5c=; b=CWYLu2lpTCxku/CC+RzZl2kNnYYtdc71gqQko0SqQTc AdDcEoAglSvwjKRdrhcBuQhgGzn7wYp5p/yMPVhsysJzOno9lZLVwdRpnQwwSBqh 2BHpJVNfBrMdCmRqZaTnodiy1IO7B/N4kbx4I+5miO4uoksUg5SN9TyePrhNonM9 Vkj+mf0hM5ScohITveGJOeB7RFaSaZjTHJfNKVVfebxWiJ/ETIsyHk8LjcPKG5V3 Law3c3Bx4EzhbAGF3nkapv0aKXGUxp8P+Y3kMFXJFFLdDY3tkVsoLk4tQEK788Ug jNfI42fpvmYvIfw91C3G6FsjjysTIhp6FHKfe9BluIA== Received: from nam11-dm6-obe.outbound.protection.outlook.com (mail-dm6nam11lp2173.outbound.protection.outlook.com [104.47.57.173]) by mx0b-004aa801.pphosted.com (PPS) with ESMTPS id 437yd02sas-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT); Tue, 03 Dec 2024 12:11:24 -0600 (CST) ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=xrIyVgz6UxuWgvTQA40poQ+KW7Q6lg2rhFYMu2ogsMPV2G588g8Ca/Ria5wInTCeQdoVOdypayAoOBSPhjk4Wjvv7ocmKX4+UnV29Iu7K+M267j/GNsCkaC69EJXhGuwCqVhdMlUPYSDVJs/YKXsWFoRGF8BOV3SZ+yG78W6PHYPArH16q/wFeD8k5GRtrC+FvZwxfbciF0jzyXvoycVRiEz+YYzg+4+mv83v5VA7qyoCCvw9Xj1yOiLS09Xfky3r1gyZU0nm70a1HFdecAO8TOVh43ZqoiGXs6XbS0q7JPASp0NX3+4gE5F7rQSpIxOVonlJ0WK1Mtw+VzZc7haOQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=BiEfqmNw9BG5HEmEOdA651R8fTsy64rHfa7QlmRnQW4=; b=KnbLrRyi5kVMHUxH6HdHLuHSPbRWrAUKc9/AXbziP2aLpLfWZHDdNq8nk9ytd1BGjbXOZetIIpqZXGgX+64SOyPFPygqjrtGhEVkVRGAfdu6DhwpsNh/2Shv9JqMRnHo3jKIXDzeT3Hzt/m1NyEwv0aM6j1kmWf14FN0LL7iVfO+sQzv2QIbVEHYTja+J4la1l/VnQqynJncbQpjqK8aTWN0Ec/6RUmzOFCtN8MUV3n5OqpYmpgg8jD+/l8v4tFcvXLiz8SLGFBZDQ4oKxrYC0ECtTkC9nfn0xIAKeaZsPRQQSssmb4/WeBbZ/gjRb+gHcQtePuFQU6cPifwZgfq5g== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=snapon.com; dmarc=pass action=none header.from=snapon.com; dkim=pass header.d=snapon.com; arc=none Received: from BN8PR04MB6289.namprd04.prod.outlook.com (2603:10b6:408:d9::7) by SJ0PR04MB7549.namprd04.prod.outlook.com (2603:10b6:a03:318::12) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8207.20; Tue, 3 Dec 2024 18:11:22 +0000 Received: from BN8PR04MB6289.namprd04.prod.outlook.com ([fe80::63ff:16cf:cb10:259f]) by BN8PR04MB6289.namprd04.prod.outlook.com ([fe80::63ff:16cf:cb10:259f%4]) with mapi id 15.20.8207.014; Tue, 3 Dec 2024 18:11:21 +0000 From: "Tefft, Michael J" To: Adrian Klaver , "pgsql-general@lists.postgresql.org" Subject: RE: Autovacuum and visibility maps Thread-Topic: Autovacuum and visibility maps Thread-Index: AdtFnZrPZttyZEAwRIqDe+1fxv6vqQABtMwAAAJ39AA= Date: Tue, 3 Dec 2024 18:11:21 +0000 Message-ID: References: <341dd184-9096-4d1f-a74c-7db02cd1004c@aklaver.com> In-Reply-To: <341dd184-9096-4d1f-a74c-7db02cd1004c@aklaver.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-dg-rorf: true x-dg-ref: PG1ldGE+PGF0IGFpPSIwIiBubT0iYm9keS5odG1sIiBwPSJjOlx1c2Vyc1xuajM0MTdcYXBwZGF0YVxyb2FtaW5nXDA5ZDg0OWI2LTMyZDMtNGE0MC04NWVlLTZiODRiYTI5ZTM1Ylxtc2dzXG1zZy1mZWM4NDRhZS1iMWExLTExZWYtOWQ1Zi03NDNhZjQ2Yjg3OGVcYW1lLXRlc3RcZmVjODQ0YjAtYjFhMS0xMWVmLTlkNWYtNzQzYWY0NmI4NzhlYm9keS5odG1sIiBzej0iMzE0MDIiIHQ9IjEzMzc3NzIzMDgwNDI4MzM2OCIgaD0iUGVFb3h4djFuS3hYTnE2Ymw2NmVoNjVURFZjPSIgaWQ9IiIgYmw9IjAiIGJvPSIxIi8+PC9tZXRhPg== x-ms-publictraffictype: Email x-ms-traffictypediagnostic: BN8PR04MB6289:EE_|SJ0PR04MB7549:EE_ x-ms-office365-filtering-correlation-id: 913dcf59-0a23-4a33-a911-08dd13c5e452 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|366016|376014|1800799024|7053199007|8096899003|38070700018; x-microsoft-antispam-message-info: =?utf-8?B?RDY4Y3h4dTFzSmhQRmJOaVFIL2xURWhKVTBYWFljekQ2VXpjZGcyM3RMS0k2?= =?utf-8?B?Wjc0VC9kcXA5cUcwT3FJaHJJNFNrOWh1aHJFT1RXdVMzYkJwTmtPSHlMazZJ?= =?utf-8?B?R242TGZ2L2loelY5SWFzMW9yNG5Ub09hTGFQc0h5VW50ZGtsOU10VU1Gcjgw?= =?utf-8?B?YklJVHEwVXdzcWFvZ1hNUzZDWFFxSDR5ckU4bHIzTGFoRzBjVFBZNFBpRm5Y?= =?utf-8?B?b25jejdiNTIzWFFWK0FhSVpVVDkyaDZpMkROdXp5V1VNaUdJTFpDMllKR3Zm?= =?utf-8?B?NEZrN2Fja2drc08yZlJ2U1F2NU8wczJNUkZuWERkNmxJZ21ONGhVU01mMnBk?= =?utf-8?B?dmp4YjV4WlBEV0x2U2dBb0h5TzJvTzUxcUR6VGdzcmdBc0gzYndXeVZwa05u?= =?utf-8?B?Q3F0MXMxT0FHNmh3U1dzZU0vVHZCZmJodGUySWpmbXBNRFJobnQzeTlweENh?= =?utf-8?B?Ymtxb1Vtb1ZHb1U5R1ArRVpRdTFSYVNlRUN2TXlLWGo5T0RUZGp0Nkd5N2Jh?= =?utf-8?B?aXNud0YzelhXeldkc3grUUJWNWRQazMrbituVzBhV29vZnN5aVRxUm1JS1Iy?= =?utf-8?B?SUR4bHdkLzRXUU5rRmRZcDVpcnJsa3NaSEZUUFdGd3krTFhQbEJyaEdsaXV4?= =?utf-8?B?blV6dm54NVpONGN6RnVTRmRlb3pvL2hwV3VkUFZ3SWdjNS9HUUE2dVFmSnl5?= =?utf-8?B?WVR3TmpNUG5DNzMwRkhGWHBLRm1QaW1QYVBWWThFalg0b2J0UmFMT1FONDB6?= =?utf-8?B?Y1U0d2xnallQbDdJT1VlUWdVTjNjbEhnVU9CaUhrbzVKQ1djNTluMVFHdCt6?= =?utf-8?B?N0tHQU5oL1AvQmxkVXcyOXV3T3BTYjhDdzcyQUp1SGlTUmt6N0VTU21QL2tU?= =?utf-8?B?dVU2cUFPdzVZT0laT3U2L0pTczg0dGhXZjBDR0FzbnNQamN0UTBmVENpNmxl?= =?utf-8?B?NUhuRjBLV1ZvdlVtOXBsS01KdUUzcGdscFE3VzBDenhOTU91TFdGTFZSbUFC?= =?utf-8?B?TWo2a3FKSjNXQjZ4OTVrU1BRczV6WUs1UVlvQ0tqL2lGTHFZUHJNcDNJSnNX?= =?utf-8?B?WGo0STVEd1lqOVJRYjd2OGs5R3NDY1hUQU1XWWY1UU52dm9aNmRvbTlGMHlC?= =?utf-8?B?RDByZ1NmL1RHTzdqSC9RQ3dlVU1LZ0psKytEQTRFYVVHYWNJd2tLdndiVURN?= =?utf-8?B?V09CZ0VDSnErTkZyallQQ1JiUURPaStGbE1MY2IwaEw3S1M1NThqaUJNdFh3?= =?utf-8?B?cVpaWXZzR3dZOVpxOW45ZWw5WXJMWmM4MnJlSFRTS1JNbjVqVGExdFE1L2Ja?= =?utf-8?B?Vkd2ZkZvekVoRncrb1FTdHV0ck0yeVdSb2NJc1dGbFhCZGpCSSsxOCt3elJ4?= =?utf-8?B?Umt3NVlGcmVScytPcUFtVlRySFhMWlF0ckFhdFlaM3BQZkhKUXRSd0FPRHFI?= =?utf-8?B?QS9yTUxyVjZzbWZkQmdOTVB6U0syUFFwSEQraW5RMEhPUDNuc1lFakhwMmZN?= =?utf-8?B?cFJySjV5cnFNWjg2VUtWQzNibmVqbDlCNVJOZUFtd3MvWVlIcm1IQkozQmxV?= =?utf-8?B?aE1odExFbytZaTMxNGZYSzZaUkZROWQreCtUZXRGOXZrQ2xrWHU4am5jbktX?= =?utf-8?B?SzJwakdIOWRITE91YUZGZGh3YXc2bU1DUGFBaTN0Z3g1UWJUOGRkV0NXMEY2?= =?utf-8?B?UDZTbW94bUR4UUNORjEyUWVsc25mRWJKQU1WSExZY1EwK2Iwby9sTDh1T0xK?= =?utf-8?B?YlNSczFUUGdBRGozaDJyYmtTMWN5R2NvWVdZWmZFRlJXbzB4YmROckhtRU85?= =?utf-8?B?Z0VOWDhhZ09QU052Q21EZmtXVTY1cjZtZjMxV1V2VFVjdXpYYXNVNGN5Rlgw?= =?utf-8?B?MzZiN0lneUJHaHhvdGRLd2FJQy94UWcxWlc1ZEZ6bXlDYzJBYTlPKzRzQXJr?= =?utf-8?Q?4VZ6RomM3QM=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:BN8PR04MB6289.namprd04.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(366016)(376014)(1800799024)(7053199007)(8096899003)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?utf-8?B?VExSQzZvck1XdWNqcko1eSt1SnBMckNVWnN2NHo3U2poSjIrejJlWi9tWDBm?= =?utf-8?B?QnNaU3Q4eEVTNHZSNlB5NDhFc0k5UlQrUWNMSVBzNjlNTzBJenpITzBPZzY5?= =?utf-8?B?ckhtVm9wNmdxc3ZVWnpjMGJzcnFFZFUvaVhaY0ZZbis4UFFJNE1oSGhZbzRS?= =?utf-8?B?YzFGd25QaUVsbnZ6ZEQxUWw3ZnZldkhlWW52Wk5vSHpJM05ERzRpdHRhZlR5?= =?utf-8?B?aGM3THFFZkhvbEI3eXpXZmx4K0M3YlhlSjNqd2VqNzFWNnVacVFxS3RHZVdo?= =?utf-8?B?SDdMZ0pyeHBmcXNJeUZtWEtFc1RQT3FSNy9wa05lNVVjZ1B2Tlc2cGFIRUg2?= =?utf-8?B?OVJGbkN2aXgrbGJjTkVlU0hicGVRRzhvRU9HRklsUU5xMk5GQXpOU2VSbEhk?= =?utf-8?B?NHcyU3h4TzdSTzFmRlFXaGI4S2NtSXg5WldRSWtodDlkN2VzK01hVlZIVmgy?= =?utf-8?B?WDVYRUNEQnk2dkd2MEE4WWF3UDhNNFYyWDVjaUtpQ0ZsNW51OXphbElPWUc1?= =?utf-8?B?eG40MmYyQjlMTm9RM3VNS1RMVUV5bFRudkdzWjlqVzdIdjVIZE1SeW51OU5l?= =?utf-8?B?VENMTFRVYytyY1VIaVEzTHBrZlJyM0s1RmtyTmtVUG5pTHpIUEVJV3oxNHNF?= =?utf-8?B?b2FJa3BwOGZjbWcxR0RtSlhscWNFSlJNZUh1MmZ0L3ZuWTErVVJRUWxaOUFa?= =?utf-8?B?aTY1T0VWd0lES0dUUWFlSmY5SUIyYllZZVVzQTJMTHA4eXgyK3ViVFRDMnN2?= =?utf-8?B?blI4cVAra2JlenVCaXRNSElJWWx1cjh4ZDhkOWpFajBWc3NEMGZFNFdMYy94?= =?utf-8?B?Ritnamx4VCs3Rk5vR2lUMHA4Q3hnWFRkRjZOSFJ1alRJR2w2NHFkL1JGN0xU?= =?utf-8?B?Qm0xMHNyelFVN0lLS1FLSEpaLzAvSmFpTFM2Ynk5Z3ZsRHhYTTRmZzUwb0pC?= =?utf-8?B?dCtyVTRhVlhLWTVVbGNJMTQvRDE0VUc4MFhrdWNiTllwTkpXVmlQUGlKbWlu?= =?utf-8?B?Tmg4ak5Zd3dIWFlLQWFwMTZ5MGVieWwwdEpxZGhmZm1GQjF4TEtWUUhYaFBo?= =?utf-8?B?aFFEWU1yZmorQi83Q3hvaGdXRmxYUk1qVU1xaGhyUVJISVRKVFNJTTBaNm1Q?= =?utf-8?B?enZQTDY5V2RCaWRVajdrZEJ2ZG1lUFJkOEpIelNKODJFYk9VMGl2TDVuN3d6?= =?utf-8?B?OUh2c0pyMnhRNW9QN1J0RGtidHZ2UW9Eb2gxbG9ZNDQ5Sk53S1pya3N3Vmtx?= =?utf-8?B?bm1LSHRoVitHeG9CRFhmckhteFlMSVovUlZFdm1odHNrVHZ5OWpMcTRQWHNZ?= =?utf-8?B?VmlLV2ZBQStMQkwycFNzYy9sMVE4bWlyTTF5Q0hmalZBZnFoLzZFYnYvNHQz?= =?utf-8?B?c0VQelF5Y1BtU1hUSGE1VklIb1lvUnZWTVhMSDJQYlpaYVN6cHU4Sm1MN3BP?= =?utf-8?B?YWhOcldWVzU2Qmg2NEZSdVJUWVFPWUEwQm13bTR6ZWJ2Q09oQ1VOQUJhak5M?= =?utf-8?B?ZVdYYWZNd3RUZFc5YmFjTGx3MTB4dVltamFLOXd4RnhCd05OV1VsZU1BSEV2?= =?utf-8?B?Z1ViYXNjWVVFdisvUS9kZy9GSUE0WjRlM2tLYklwMGJqU1hTSXp4VGVKRE5H?= =?utf-8?B?d0JValAzK1JOSStCb1FLNjN3WW14a0dzcm8rRXkyYzErQ0plaldab3huRUFi?= =?utf-8?B?OTlrcGo3Y1BVT0kvUFRyV0RGcVQ5aEx2Nk9zbWJTeXlRU1RsS0hGTjYyc3Yw?= =?utf-8?B?VzZwU2cyRWdqTWp4N3VLd1RsaVY4VlNQc1E3dmNvT1dvQnptRGdxVm5jeDBO?= =?utf-8?B?THBiaDRYMGdia2xlQWVHazJIUzNXZmVYOGxFbm0xUGp0UVRHZ2dXUWduM3BE?= =?utf-8?B?U3l2aE9ZRTV4VUNKQmphdGRlK3lnbWU5R2Z1U1VXT3dXSFVMYmFRb2tTbnpT?= =?utf-8?B?L2w5UzdBemVkM2lweUp5RE1OVFJWUmRFWUpVQkZRVWRIdGNSWUx3TzVqRncw?= =?utf-8?B?b1oyM1lhTCtQb3JXNWg3R3F6aXJ5dWtXc3VIUUlhS0MyekJQeG1FQ2VxV1ha?= =?utf-8?B?MUxPbUdEVlgxYTN6eEtoQXE1Q21IdUFCWHhmbWgyRUY0Q1BrVEZMaHBpc0lv?= =?utf-8?Q?zFZPoh+siwpJxegp7PMta10kW?= Content-Type: multipart/alternative; boundary="_000_BN8PR04MB62890EE42B888F3A0B455F76D0362BN8PR04MB6289namp_" MIME-Version: 1.0 X-OriginatorOrg: snapon.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: BN8PR04MB6289.namprd04.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 913dcf59-0a23-4a33-a911-08dd13c5e452 X-MS-Exchange-CrossTenant-originalarrivaltime: 03 Dec 2024 18:11:21.5994 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 9cbb18c7-7b64-4d6a-953b-09ec023c8c08 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: kgG7vDraUxWeIkqq5S6hBKRMGJifBv2zIRGQy+iGUAyX2D+idNH4GbhxwCVBV1roH09EcrEblvq3i3BFJxDtyF8RwvLQM8GFjQJt0ghaqgY= X-MS-Exchange-Transport-CrossTenantHeadersStamped: SJ0PR04MB7549 X-Proofpoint-GUID: Re8FG6rGdtW66iiRff-olIa7qLv8McK9 X-Proofpoint-ORIG-GUID: Re8FG6rGdtW66iiRff-olIa7qLv8McK9 X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1039,Hydra:6.0.680,FMLib:17.12.60.29 definitions=2024-09-06_09,2024-09-06_01,2024-09-02_01 X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 bulkscore=0 suspectscore=0 impostorscore=0 clxscore=1031 mlxlogscore=999 spamscore=0 lowpriorityscore=0 mlxscore=0 phishscore=0 malwarescore=0 adultscore=0 priorityscore=1501 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.19.0-2411120000 definitions=main-2412030152 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_BN8PR04MB62890EE42B888F3A0B455F76D0362BN8PR04MB6289namp_ Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Thanks for the point about truncates versus deletes. But most of these partitions have over 100k rows, all inserted at once. We = have the default setting: #autovacuum_vacuum_insert_threshold =3D 1000 # min number of row inser= ts So I thought we should be triggering by inserts. Mike From: Adrian Klaver Sent: Tuesday, December 3, 2024 11:57 AM To: Tefft, Michael J ; pgsql-general@lists.post= gresql.org Subject: Re: Autovacuum and visibility maps On 12/3/24 08:=E2=80=8A32, Tefft, Michael J wrote: > We have some batch que= ries that had occasionally having degraded > runtimes: from 2 hours degradi= ng to 16 hours, etc. > > Comparing plans from good and bad runs, we saw tha= t the good plans On 12/3/24 08:32, Tefft, Michael J wrote: > We have some batch queries that had occasionally having degraded > runtimes: from 2 hours degrading to 16 hours, etc. > > Comparing plans from good and bad runs, we saw that the good plans used > index-only scans on table =E2=80=9Cx=E2=80=9D, while the bad plans used i= ndex scans. > > Using the pg_visibility utility, we found that all of the 83 partitions > of table =E2=80=9Cx=E2=80=9D were showing zero blocks where all tuples we= re visible. We > ran a VACUUM on the table; the visibility maps are now clean and the > good plans came back. > > Our question is: why did autovacuum not spare us from this? > > We are using default autovacuum parameters for all except > log_autovacuum_min_duration=3D5000. These partitions are populated by > processes that do a truncate + a single insert-select. > > We see autovacuum failure (failed to get lock) messages, followed by a > success message, in the log for one of these partitions (the biggest > one) but even that partition showed zero blocks with all tuples visible. > > Are we wrong to expect autovacuum to clean up the visibility map? I have to believe it is due to this: https://urldefense.com/v3/__https://www.postgresql.org/docs/current/routine= -vacuuming.html*VACUUM-FOR-SPACE-RECOVERY__;Iw!!Lf_9VycLqA!mGufXaOdGX6PdXSp= HcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_jyxJnNx3YBvbxQOracZSxz= vbw$ "If you have a table whose entire contents are deleted on a periodic basis, consider doing it with TRUNCATE rather than using DELETE followed by VACUUM. TRUNCATE removes the entire content of the table immediately, without requiring a subsequent VACUUM or VACUUM FULL to reclaim the now-unused disk space. The disadvantage is that strict MVCC semantics are violated." Combined with this: https://urldefense.com/v3/__https://www.postgresql.org/docs/current/runtime= -config-autovacuum.html*GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD__;Iw!!Lf_9Vy= cLqA!mGufXaOdGX6PdXSpHcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_j= yxJnNx3YBvbxQOraeerEd0yw$ "autovacuum_vacuum_threshold Specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. ... " I'm going to say the TRUNCATE itself does not trigger an autovacuum. I would suggest throwing a manual VACUUM in the table population script. > > postgres=3D# select version(); > > version > > -------------------------------------------------------------------------= --------------------------------- > > PostgreSQL 14.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 > 20210514 (Red Hat 8.5.0-22), 64-bit > > Thank you, > > Mike Tefft > -- Adrian Klaver adrian.klaver@aklaver.com --_000_BN8PR04MB62890EE42B888F3A0B455F76D0362BN8PR04MB6289namp_ Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Thanks for the poin= t about truncates versus deletes.

But most of these p= artitions have over 100k rows, all inserted at once. We have the default se= tting:

#autovacuum_vacuum_= insert_threshold =3D 1000      # min number of row= inserts

 

So I thought we sho= uld be triggering by inserts.

 

Mike

 

From: Adrian Klaver <adrian.klave= r@aklaver.com>
Sent: Tuesday, December 3, 2024 11:57 AM
To: Tefft, Michael J <Michael.J.Tefft@snapon.com>; pgsql-gener= al@lists.postgresql.org
Subject: Re: Autovacuum and visibility maps

 

On 12/3/24 08:=E2=80=8A= 32, Tefft, Michael J wrote: > We have some batch queries that had occasionally having degr= aded > runtimes: from 2 hours degrading to 16 hours, etc. > > Comp= aring plans from good and bad runs, we saw that the good plans

On 12/3/24 08:32, Tefft, Michael J wrote=
:
> We have some batch queries that had occasionally having degraded <=
o:p>
> runtimes: from 2 hours degrading to 16 hours, etc.
> 
> Comparing plans from good and bad runs, we saw that the good plans=
 used 
> index-only scans on table =E2=80=9Cx=E2=80=9D, while the bad plans=
 used index scans.
> 
> Using the pg_visibility utility, we found that all of the 83 parti=
tions 
> of table =E2=80=9Cx=E2=80=9D were showing zero blocks where all tu=
ples were visible. We 
> ran a VACUUM on the table; the visibility maps are now clean and t=
he 
> good plans came back.
> 
> Our question is: why did autovacuum not spare us from this?
> 
> We are using default autovacuum parameters for all except 
> log_autovacuum_min_duration=3D5000. These partitions are populated=
 by 
> processes that do a truncate + a single insert-select.<=
/span>
> 
> We see autovacuum failure (failed to get lock) messages, followed =
by a 
> success message, in the log for one of these partitions (the bigge=
st 
> one) but even that partition showed zero blocks with all tuples vi=
sible.
> 
> Are we wrong to expect autovacuum to clean up the visibility map?<=
o:p>
 
I have to believe it is due to this:
 
https://urldefense.com/v3/__https://www.postgresql.org/=
docs/current/routine-vacuuming.html*VACUUM-FOR-SPACE-RECOVERY__;Iw!!Lf_9Vyc=
LqA!mGufXaOdGX6PdXSpHcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_jy=
xJnNx3YBvbxQOracZSxzvbw$
 
"If you have a table whose entire contents are deleted on a period=
ic 
basis, consider doing it with TRUNCATE rather than using DELETE followe=
d 
by VACUUM. TRUNCATE removes the entire content of the table immediately=
, 
without requiring a subsequent VACUUM or VACUUM FULL to reclaim the 
now-unused disk space. The disadvantage is that strict MVCC semantics <=
o:p>
are violated."
 
Combined with this:
 
https://urldefense.com/v3/__https:=
//www.postgresql.org/docs/current/runtime-config-autovacuum.html*GUC-AUTOVA=
CUUM-VACUUM-INSERT-THRESHOLD__;Iw!!Lf_9VycLqA!mGufXaOdGX6PdXSpHcIUnIF1pe8ev=
FpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_jyxJnNx3YBvbxQOraeerEd0yw$=

 
"autovacuum_vacuum_threshold
 
Specifies the minimum number of updated or deleted tuples needed to 
trigger a VACUUM in any one table. ...
 
"
 
I'm going to say the TRUNCATE itself does not trigger an autovacuum. I =

would suggest throwing a manual VACUUM in the table population script.<=
o:p>
 
> 
> postgres=3D# select version();
> 
>            =
            &nb=
sp;            =
            &nb=
sp; version
> 
> ------------------------------------------------------------------=
----------------------------------------
> 
> PostgreSQL 14.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5=
.0 
> 20210514 (Red Hat 8.5.0-22), 64-bit
> 
> Thank you,
> 
> Mike Tefft
> 
 
-- 
Adrian Klaver
adrian.klaver@aklaver.com<=
/a>
 
--_000_BN8PR04MB62890EE42B888F3A0B455F76D0362BN8PR04MB6289namp_--