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.96) (envelope-from ) id 1vpPBC-007b04-1L for pgsql-general@arkaria.postgresql.org; Mon, 09 Feb 2026 11:11:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vpPBA-00AFHr-2N for pgsql-general@arkaria.postgresql.org; Mon, 09 Feb 2026 11:11:44 +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.96) (envelope-from ) id 1vpPBA-00AFHc-17 for pgsql-general@lists.postgresql.org; Mon, 09 Feb 2026 11:11:44 +0000 Received: from mail-wr1-x443.google.com ([2a00:1450:4864:20::443]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vpPB8-00000001nq6-07od for pgsql-general@lists.postgresql.org; Mon, 09 Feb 2026 11:11:43 +0000 Received: by mail-wr1-x443.google.com with SMTP id ffacd0b85a97d-4362507f0bcso2552878f8f.0 for ; Mon, 09 Feb 2026 03:11:41 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec.at; s=google; t=1770635500; x=1771240300; darn=lists.postgresql.org; 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=zx0KbKrw3XYWbq/E8Z3IFq+W7WJNG2S1rbmzIntsGoo=; b=IktFrXD3MHyTeK90M/6L5xFYO/MGy8NW3CRXDtX+4w3W5BgfNa3TJk53Ik8a+JLDGs U6V9CoFGgYJ2sDLeOKjouOHOpP6NTihBZnRe02MQB4hFoJ8vlkpV0qPqP6WphQFsPUH0 wn3IIpn0VEJsrbAAGvEIbg9bxGgXNbo9sYid/7sptxr8pvp9JPgGr6XIOM8QyojZ4Jex 9AvHuIoVxoYxLNrxeFHHKG7lDiGIESeW7WZiDsLtDJrfBr+q1hu4xAu7HCrEBlsJXDN7 sbsqo950mX8raW6e6B+hlinHx3/4aBIlsZRRCDT9Ig6rvbAekhCdBwaQp/TEEw+L0jpK oe+w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770635500; x=1771240300; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=zx0KbKrw3XYWbq/E8Z3IFq+W7WJNG2S1rbmzIntsGoo=; b=N6pPKh2Q5R4s/5Hbpdk/MnT4vB07GFRTDcVxBGPZQZklrnfo+dik9hWNSZnvzbR9oK fW72v0nM5X8mVWE7SQF0zH2xoA/3D882cWx0czA2ehW4UPTVBJgGKNexNA0j2npLhyRe 6+PSNh+9Sa0TPrf0YP8H8QIyN2UankmhZNTlOnIoPcnHUQotPdGhpkO0rXk1yUJSF0/S uGpxy9+cfiEgjHGaMx5dwR439pzTp0mmzTx45mo4aUYvG5nv1EsscB2jHjIXqdFEKMoj UJj/uk0OvLp0hSEYDtrI/PbnLgD8lauvOC6GIDS7pezsjpl5lr+31gav/L7IxrBGGnBQ zNmg== X-Forwarded-Encrypted: i=1; AJvYcCW5DihFsYmVekRaXqU0gdg+Jb67GlRyOg7X19/er/3A5P7VKi6hdOXiMBSJXC1aEx97tA7B/0cqzjjX8vvD@lists.postgresql.org X-Gm-Message-State: AOJu0YxdBz04NquLxYZOVR+RgC5mSVK4wVer9kwGTV1Wp+6QQkw7fS6m yQK/DnFb4HYlF4rG1bRahkkTAmwr7ktt1+XoDN+0GdPD99A630c8k8OL4jVGRuw+HWE= X-Gm-Gg: AZuq6aKkK23eYq/j0o4feO5irDpdfonRD4/yOoND2nXsJYIB2NcfeogMyX8n7kaOhPU d99ZhsbtAuN0RAqSrhaRkkT8gIjDy7eMoSIJ8nIR0uBuPeunXL4YsabZW0vXOGenH5XTB4y1BFI /LMMelimaEYyASiAWutODx31utzsC6Qr6zYhcjkDws3pfAALIGJ4NjLPsr9bVgtRaAJO9JSDkGp LVfCIkAzc4R+JXIVgIMrfaT8aUIp+cmP8Ozcr3pl5CibPJzkkrjvkEu+RkNgFrxUu6ONtcn2CWh h/xuiCRBOklEh31v9cFmk7cwJ2RYVi4ekE69ruUXHavN+8XQUnB2wRKioZmenjjsn1gdToVuAiI 6ZPZiXwyvK9ft1vGHP8Ko4RtKJnZmNSEepFMqCLGNA+NSUJsExtdktIwzjpnmD6/aOmpB0ICZxY TQq6reZlafvQ0= X-Received: by 2002:a05:6000:2902:b0:435:9e81:105e with SMTP id ffacd0b85a97d-4362933ed0fmr16602795f8f.19.1770635499950; Mon, 09 Feb 2026 03:11:39 -0800 (PST) Received: from laurenz.albe-K4N0CV00F97414D ([213.208.157.35]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-4376ab6e4c0sm11134734f8f.0.2026.02.09.03.11.38 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 09 Feb 2026 03:11:39 -0800 (PST) Message-ID: Subject: Re: REFRESH MATERIALIZED VIEW CONCURRENTLY is blocking autovacuum on table From: Laurenz Albe To: Dirk Krautschick , pgsql-general@lists.postgresql.org Date: Mon, 09 Feb 2026 12:11:37 +0100 In-Reply-To: <918deb54-9d01-4a9c-abd3-d8412c9e6c80@gmx.de> References: <918deb54-9d01-4a9c-abd3-d8412c9e6c80@gmx.de> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.58.2 (3.58.2-1.fc43) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, 2026-02-09 at 02:17 +0100, Dirk Krautschick wrote: > I have a situation here where a frequent=C2=A0 >=20 > =C2=A0REFRESH MATERIALIZED VIEW CONCURRENTLY >=20 > is preventing autovacuum on some tables kind of running into a circle of = growing. I'm not sure I understand you correctly. REFRESH MATERIALIZED VIEW CONCURRENTLY will block VACUUM on the materialize= d view, because the statements take conflicting locks. But the statement won't block VACUUM on the underlying tables. However, if REFRESH MATERIALIZED VIEW CONCURRENTLY is running for a long ti= me, you will have a long-running transaction, and VACUUM won't be able to clean up tuples that became dead later. > Why is this acutally blocking autovacuum and what is the best practice to= prevent=C2=A0 > this excelt just not refreshing the materialized view frequently? :-) I also have trouble understanding that sentence. Yours, Laurenz Albe