Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ppkQH-0001np-A6 for pgsql-general@arkaria.postgresql.org; Fri, 21 Apr 2023 06:39:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ppkQG-0002vv-30 for pgsql-general@arkaria.postgresql.org; Fri, 21 Apr 2023 06:39:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ppkQF-0002uu-MO for pgsql-general@lists.postgresql.org; Fri, 21 Apr 2023 06:39:07 +0000 Received: from mail-ed1-x535.google.com ([2a00:1450:4864:20::535]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1ppkQD-004BM4-PR for pgsql-general@lists.postgresql.org; Fri, 21 Apr 2023 06:39:07 +0000 Received: by mail-ed1-x535.google.com with SMTP id 4fb4d7f45d1cf-5067736607fso2180025a12.0 for ; Thu, 20 Apr 2023 23:39:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20221208.gappssmtp.com; s=20221208; t=1682059143; x=1684651143; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=EVPnPhu3ufxxepohI+DL4VRyS3bFLzxS/Zut0qTnEwQ=; b=YOtFzZKVTy3KJEZPr9QYYCo+ysETlLwYAXWlXiSC8+dah8/E94Gk3WBxwNSJcRakpv OZb/nUgGibQKg+Cvw/KRiFueUZDAYFrmL40Lho1iANi4QUZ2Kztit4H4xQJsPk5XHXD+ 9qaaFqvzP75E/6Subm269B12j6JZm+oycAazfm470rl1/qToYXRFlE1XJqvB+Uxza8bJ H7J+UdDwDZjvLOm3PaDAkixD0cxo5iAdkdk7yyBGMQlfC5shB4KALZZ6v0hZH1iK7z4H w2Uv3Iga90+Yah7bOjPcKvq/VjOw38ZoQ6CmDgxc16DZgd3ifqPV4J7a00EA83xGbDkH qrYg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1682059143; x=1684651143; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=EVPnPhu3ufxxepohI+DL4VRyS3bFLzxS/Zut0qTnEwQ=; b=YwZ1Mhd3diLMq4wK17qwDNUHpxQj6E9m6zPGkFVjzdK9BDIN1Y1VOlI+IFuouNmgp8 Ht7Ox3nQE72ruCmwZwuJODSnlkAgOG80/meWIKdqPfYl2Dh1ORyEh4Oab/weOriK61qk 7uIb3izN7oUPdTn+I4cNMJb8fa0boWhFWi/vCFalFSTQtVUAwzOvMrR1WjO0p+3I/nTK LOn03jdSkT3hkyBE8AYxMUgI7wn65YkaWF1Q8kNZeCXeDN0fxqjH6SAzr9xcRadjKZX9 OAeXS3GSEs4PDuNv1WqkdMDzi2LSvzg+lJGas8FnuM7bTDa9gBpnvW+kO48ApTLgRfKr tjxA== X-Gm-Message-State: AAQBX9fEjdOSIZ+0iUUvTmxVulb9cjHb8DoJ46qXM32+oi292sY0kAZf Sh0yAzzaxUZFh2KHSbMveilauw== X-Google-Smtp-Source: AKy350be7gwMOEWcqonvph6DdVPG7n4na7h0Fm0xP0pfgDu4iGinnKPRvdCYJQBf0Q59gvBd1UVsgQ== X-Received: by 2002:aa7:d9d0:0:b0:504:8014:1554 with SMTP id v16-20020aa7d9d0000000b0050480141554mr3324265eds.28.1682059143380; Thu, 20 Apr 2023 23:39:03 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:b9eb:1859:8a46:452:70c4]) by smtp.gmail.com with ESMTPSA id f12-20020a50ee8c000000b00504ae3a5adfsm1580077edr.2.2023.04.20.23.39.02 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 20 Apr 2023 23:39:03 -0700 (PDT) Message-ID: <1d84ecfca8a7c58e143d7d64c66d61bfb694d9e3.camel@cybertec.at> Subject: Re: vacuum TOAST tables From: Laurenz Albe To: senor , "pgsql-general@lists.postgresql.org" Date: Fri, 21 Apr 2023 08:39:02 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 2023-04-21 at 04:37 +0000, senor wrote: > PostgreSQL v11.19-1PGDG.rhel7 (upgrading to 12+ is so far only talk) > CentOS 7.9 >=20 > If I understand correctly, autovacuum handles tables and their associated= TOAST tables separately > but a manual vacuum will also vacuum the TOAST. That is correct. > When manually vacuuming does it matter whether it's the main table or TOA= ST? It makes a difference. As superuser you can directly VACUUM a toast table,= and that will no VACUUM the table it belongs to. However, if you VACUUM the main table, = both tables will be vacuumed, which is more work. So if you only need VACUUM on the to= ast table, doing that directly will be cheaper. > I've posted before about these same systems. It'll get to=C2=A0age(datfro= zenxid) > 2,000,000,000 and > is not able to keep up until I get it back down to under ~600000000. Then= it starts humming along > as if I "cleared" something. That sounds unsavory. Did you set "autovacuum_freeze_max_age" to an extrem= ely high value? Yours, Laurenz Albe --=20 Cybertec | https://www.cybertec-postgresql.com