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 1v6t51-00BLpZ-Gu for pgsql-hackers@arkaria.postgresql.org; Thu, 09 Oct 2025 16:01:23 +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 1v6t4z-006vLT-7e for pgsql-hackers@arkaria.postgresql.org; Thu, 09 Oct 2025 16:01:22 +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 1v6t4y-006vLK-U7 for pgsql-hackers@lists.postgresql.org; Thu, 09 Oct 2025 16:01:21 +0000 Received: from mail-io1-xd2d.google.com ([2607:f8b0:4864:20::d2d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v6t4w-000sxv-1c for pgsql-hackers@postgresql.org; Thu, 09 Oct 2025 16:01:20 +0000 Received: by mail-io1-xd2d.google.com with SMTP id ca18e2360f4ac-938bf212b72so40108339f.1 for ; Thu, 09 Oct 2025 09:01:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1760025679; x=1760630479; darn=postgresql.org; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=G/AYCMrR9IUWtcu1KY+9SpY1gOv1WKNMyh0OvKzxukw=; b=cXawhqZT2c76ZOHzhj6Ko5h+yxEWuN5s8cWBRp8mqIVa2I9T7VLPVpf/H4Wi9yBYHp o1BRL8gdbFYj58HATBVxkbkzIakgNfuI1zuljGY6LO+1Fr/YEZoxmeeja1+w02EPhM29 5nvsUX3ynlMSxGoJPa7xqZIiLC/JXIGeGhLUSGa11Zj/yt+iQ/8gk4FPXUZE+6sH1wJm fADihyUiiOS3DZOmbLSDS1yxVRrl5LRNzt3ihd5BE4XD6I4JzV4jPQfDSEb4BwR0iERg BRONV7rhRSZ2+YpFCAD2qI5Rw6Nl6mGEIlEacG+tVE2JGIfwRx/FZeCQKcBAvhm2mKAI N6pA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1760025679; x=1760630479; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=G/AYCMrR9IUWtcu1KY+9SpY1gOv1WKNMyh0OvKzxukw=; b=BszvE4h0ZkxC1qp4O0Kg5FccRytpwypZ7GDYTmsDNoH395h7LrB7XaLfoq5uqvsoCB n2+iHo0fMd+OsA7fXEVNRfMBtqNO7aOe7g6Ey1GPrNLrchBihJZG2r9/sAq1jfE0BdZz eSnQWSna3Q1kqbYfrR8tux+IttPPLEk7W7Sqvic+OlGlqSrFeNk66g5qOhec1zICAcb8 lhV/N1SsNMRFb+oGaVOeDelcOF3NS8dNwrwgINlv26WULL3ZoSiPg6rAp+PTpn2n1zvB y0sjOcmefXbaUVfxohtSDOzLcCzny+m3zdKHbCxH1HhhzVNcg0sP53UTaIsraKIMuYp9 RaoA== X-Gm-Message-State: AOJu0Yy10l4hAdmwURZSnZF+tvn8hYmkJVPBN0fz+9SnI4sHUpokOU98 51OM/Ea3QraNZc2Wm1UFOu/KCJxv0WCgxkvPWCtQh/smeRX7clE8Rjs5 X-Gm-Gg: ASbGnctiTT8RVLB8mcLdIT0mYdzRGusjQVir6qHe+de0uc+DMGMzh51U2YX/u+YS642 PaeJSKwaexqJ9VMao3+A1c9rtsSslCOZC/WDrmLBHXZPfvFzZzkKftowLCbCT9mhXf/Qabus8fk NJcsat2CrDk3yUPoQ//FizaQB0Hgo/bA6V22nf5ZKbFnrsT0M3JVsri7YgGJWpyh1UoZ6HJjMFZ yTF5Ky5txYmghOHwC9DQIsnEuSJH3xCfkRZbGqJ3q6k4vhwRB2lZfcL1cjXPa3v6Ogi9TXAkQZb KI7JeGQ9VNuCU9PO05Nt7vysB+xCmAV3dpWdvnLDbu0D+YtVwtFx4DaNezV9BlMUn25rNWwgzHv yfP6YMmEZoIlzCpM0xAVK7ZKRfxSJn2Pr0Uw9m0fTUjreCJRFE+G6CL8ul3kCMzQohin6s5qh69 JCuM6TaJvrTRlAaf+v6AhsUxDtVi8Rnr5nsLVGCv5vZTAGp+cikcLEmIq8ww== X-Google-Smtp-Source: AGHT+IG+LooblcorvW8DWIDCZdNA4lCzFhB9zKNuo6hg0dOSA6kzuyIDzq12PRgxi5o6brQjaNCHlA== X-Received: by 2002:a05:6e02:12c3:b0:42e:7a9d:f5ff with SMTP id e9e14a558f8ab-42f8737025amr72603835ab.11.1760025678221; Thu, 09 Oct 2025 09:01:18 -0700 (PDT) Received: from nathan (162-195-168-172.lightspeed.stlsmo.sbcglobal.net. [162.195.168.172]) by smtp.gmail.com with ESMTPSA id 8926c6da1cb9f-57b5ea2dd8asm8194288173.22.2025.10.09.09.01.17 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 09 Oct 2025 09:01:17 -0700 (PDT) Date: Thu, 9 Oct 2025 11:01:16 -0500 From: Nathan Bossart To: Andres Freund Cc: pgsql-hackers@postgresql.org Subject: Re: another autovacuum scheduling thread Message-ID: References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, Oct 08, 2025 at 01:37:22PM -0400, Andres Freund wrote: > On 2025-10-08 10:18:17 -0500, Nathan Bossart wrote: >> The attached patch works by storing the maximum of the XID age and the MXID >> age in the list with the OIDs and sorting it prior to processing. > > I think it may be worth trying to avoid reliably using the same order - > otherwise e.g. a corrupt index on the first scheduled table can cause > autovacuum to reliably fail on the same relation, never allowing it to > progress past that point. Hm. What if we kept a short array of "failed" tables in shared memory? Each worker would consult this table before processing. If the table is there, it would remove it from the shared table and skip processing it. Then the next worker would try processing the table again. I also wonder how hard it would be to gracefully catch the error and let the worker continue with the rest of its list... -- nathan