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 1tNLOm-00AMNx-7F for pgsql-general@arkaria.postgresql.org; Tue, 17 Dec 2024 00:25:16 +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 1tNLOj-00CzQ2-Ow for pgsql-general@arkaria.postgresql.org; Tue, 17 Dec 2024 00:25:14 +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 1tNLOj-00CzPQ-AB for pgsql-general@lists.postgresql.org; Tue, 17 Dec 2024 00:25:14 +0000 Received: from mail-pf1-x42c.google.com ([2607:f8b0:4864:20::42c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tNLOh-0003pk-Nd for pgsql-general@lists.postgresql.org; Tue, 17 Dec 2024 00:25:13 +0000 Received: by mail-pf1-x42c.google.com with SMTP id d2e1a72fcca58-725f4623df7so4058667b3a.2 for ; Mon, 16 Dec 2024 16:25:11 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734395109; x=1734999909; darn=lists.postgresql.org; h=content-disposition:mime-version:message-id:subject:to:from:date :sender:from:to:cc:subject:date:message-id:reply-to; bh=4TqcXGPPoblCeECIB7v3JhsmCknFCT1DMlHqpP0vCck=; b=PCx2IsMTNnBOoXkY7I4wDZTurzMM2tJYMx1NgDeXob8YW71xGIl3HwJkTQObTP1Db8 R1XqbYe8YMN2WDf7Oxww46J6gnunNb6txCZrcercY90eGV7LeEo+PIe+j3tps24STwZf aD9XgpsRRRg4MfP9AtpX7FxW6v3GlHw7ByniaU/ah3lhkOEOSbkRF7WCuL6hRvRzM9C3 GGxKXxOP+hgWoofdkx5D0VpqU2ApPf9K20+pjs5PjSRL2ghJhsYi5xtQoVyTKrwAzvBw dMFDx8yrGqxWOIjkIfppCANYOVlis/UmEUTR+PRZ5d/523fbFSEmeKNaVMHn+YfMyrJC zWzg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734395109; x=1734999909; h=content-disposition:mime-version:message-id:subject:to:from:date :sender:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=4TqcXGPPoblCeECIB7v3JhsmCknFCT1DMlHqpP0vCck=; b=BVmv3qQqcurtyA15UGQjs+ua492s9WmIM5AcBby6MVSv7SRi8c4343G+uXAVqhkYHS RY1SRTR7TsSinRSOi97keEcJc0+5eh73Oylilt3d6sEErKdJTvyUA6eH8/n1YGMzvHzw uIC0aaArUcOisWmRBCyheORdCPQoNg360QmhqudgI1WeoXt8pSIfoblTOXbpCpNZaE89 9SHHe/NHxzVDuMV4Q+y2mdTrYRtZwMkqk0q2LKAMVE9i+rfLUmcT+HhQK/6HRvaWsZwb 56pYSF7WhxwF8rVshq9NLZAaiS6wYP9aQ9GdBJR7tbdxVlZyyRXeAcVEvPed5ZTIUWe7 vTiQ== X-Gm-Message-State: AOJu0YxHHjhOY+keqFmRJsX4H1gEO6YJduL+/gwZHCyqOmphWWfLgnVg cyGaihaiZLtw0K3gOY+s6yArDfQcF4vZIDJdqRxcAB4z/ahzMV+84U5r4A== X-Gm-Gg: ASbGnctguD2hpB33nTLnBdDMIX+gJiL61fV6krrYvwFxXazAjAZuPpWEnP9TyF6v7E8 T3Y/RLDvPu4dPTCLcMDmkrynUHxZqlfprwOugnkGWYcf7YJpnJ1FL1gcuhNGrxkctsdxigkLjyA tATSUM3qcmh7BEgN/0OSigzGsLgbNas9r7ZFtRLJnxKmohuGP5H/0RtqR+OMUCQsyLSZ/Gkfe3a f96ghewuOSWtZKhQrdHZB9HPdrS1ssQIBuxlqqnSQiorIPbirhQ8TzxS+zfr0dKi0QP+jbUzjcB jxQcQaw= X-Google-Smtp-Source: AGHT+IHjPmYVrYzK3CIMKgXOyx6RP0NCovadFQ817kiSq9WAn6SCHI0smXWq5eREGwMaUNBrwVwsFw== X-Received: by 2002:a05:6a00:2e1e:b0:71e:4930:162c with SMTP id d2e1a72fcca58-7290c12714amr21452082b3a.6.1734395108867; Mon, 16 Dec 2024 16:25:08 -0800 (PST) Received: from dev.null (d23-16-179-220.bchsia.telus.net. [23.16.179.220]) by smtp.gmail.com with ESMTPSA id d2e1a72fcca58-72918ad778asm5374161b3a.78.2024.12.16.16.25.07 for (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 16 Dec 2024 16:25:08 -0800 (PST) Sender: Will Storey Date: Mon, 16 Dec 2024 16:25:06 -0800 From: Will Storey To: pgsql-general@lists.postgresql.org Subject: Disabling vacuum truncate for autovacuum Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi! I would like to disable vacuum's truncate behaviour for autovacuum. Previously I had an outage due to its access exclusive lock when it was replicated to a hot standby. When that outage happened it was from a VACUUM call in a cronjob rather than autovacuum. I now run such VACUUMs with TRUNCATE false which avoids the issue for these. However I've realized that autovacuum could cause this as well. This is of interest to me because I'm looking at tuning autovacuum and getting rid of the cronjob, but I've realized relying on autovacuum could be dangerous because of the truncates. I believe the only way to disable this for autovacuum is by changing the vacuum_truncate storage parameters on tables. (Ignoring the now removed old_snapshot_threshold option). I'm thinking of altering all my tables to turn it off. Is this a horrible idea? I expect I would need to monitor tables for problematic growth, but that might be better than a surprise outage. I suppose the growth could cause an outage too, but I'm thinking it would be more controllable. Would I need to disable the settings on catalog tables too? (To rule out any possibility of it happening). Are there any other things I might be missing? I am also wondering if having an autovacuum setting to control it would be a good idea for a feature. That would be simpler for me than altering all my tables and help me avoid missing any (e.g. catalogs, new tables). I might be worrying needlessly about this as maybe it is unlikely to happen. I suppose it is workload dependent. Thank you! Will