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 1pHWrp-0002Qb-Ut for pgsql-general@arkaria.postgresql.org; Mon, 16 Jan 2023 21:18: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 1pHWro-00027D-Qw for pgsql-general@arkaria.postgresql.org; Mon, 16 Jan 2023 21:18: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 1pHWro-000274-Em for pgsql-general@lists.postgresql.org; Mon, 16 Jan 2023 21:18:08 +0000 Received: from mail-qt1-x82d.google.com ([2607:f8b0:4864:20::82d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pHWrm-0004CQ-Fx for pgsql-general@lists.postgresql.org; Mon, 16 Jan 2023 21:18:08 +0000 Received: by mail-qt1-x82d.google.com with SMTP id g9so11029891qtu.2 for ; Mon, 16 Jan 2023 13:18:06 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=7lJYEUOYm/xCEukcJ7OzNqkj3zDfvdQdKSQF4x90HWE=; b=O6MmeEdVA9oNyYx5WUlW7M7B2Fd8r3PpzAhsSjOB5EnaTnU3Gm7gvVludNkmvNxT79 5L4WbvSNzygZBHjdNQvthPQGSqrrEx6au96DkPTEg7ykfMKuIYREs1kf4D6CEQocuT1i CMesjhLq9kg4iVlxoMpStjpM1DPtrMJ/A8LX/axgASg8I7NiQYHD/xWqyefTVq2KgyHd 1R98fheDq9NWNQ18Wh5/QjpSAKJBwBtuL2wsGphFXNwX50/XNtJv9mXH4LxJi2xvvwTO x5eXi3qpk7zCxk3elrmJDy0iYFCm0++W7XwlsG+O3A1stamITPxFof4ptmXV5W9gRgJ5 8+RA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=7lJYEUOYm/xCEukcJ7OzNqkj3zDfvdQdKSQF4x90HWE=; b=5ZFkbsddfPe06BQhO3fgd46Hs6KW449XYV27ptHgJrkVzruuQavn0m3P37tzHs5VHh AStgimeDc3CWc4gmXiDhMDxDuaOpqllOYpzh2lz4KH/3L0wYiYuVr9SiBipESINOpQ/9 dyxwcSi1yHCIhln8PCcEgmJJ2vyRlzDL98KR/DRvAPtBEG0Q9LG2okPS12TM5Ok5WFqY 8uTGkJvXO1MhqTABSJp94a5Fh7yCxHYip8lmzfVKZPndK3yOkpMstL7uZHUCzE2A4PsU 03JznbO6y+TjNZSG7kgo9s5ViXotdDzK118QfnWb6CKaBL02lBO0t/io0K6zDKPTcYpJ g4Iw== X-Gm-Message-State: AFqh2kqSHbnIauZ9dJmj2uXnFIXz6Jld4dTWSB1YIuUAfAjlDIJ9Xc0A PO9zid5wE8WKIWsJeyJi2KsTvbdkP1k= X-Google-Smtp-Source: AMrXdXtBSNWC3ijLrb/uW/CabgLEfLIVs9HXEW7Ju8sV9dp+VhOxAdEGRW+N1v4VFwxBH7oozzTPZA== X-Received: by 2002:a05:622a:5a8c:b0:3b6:3165:d887 with SMTP id fz12-20020a05622a5a8c00b003b63165d887mr703034qtb.59.1673903884775; Mon, 16 Jan 2023 13:18:04 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id bv15-20020a05622a0a0f00b003a7e8ab2972sm15049439qtb.23.2023.01.16.13.18.04 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 16 Jan 2023 13:18:04 -0800 (PST) Message-ID: <5be65cbc-b181-f89d-32e4-8efd58d49f73@gmail.com> Date: Mon, 16 Jan 2023 15:18:03 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Why is a Read-only Table Gets Autovacuumed "to prevent wraparound" Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <4cd35a2737cc783662f1df14551c3dcb1bf55916.camel@cybertec.at> From: Ron In-Reply-To: <4cd35a2737cc783662f1df14551c3dcb1bf55916.camel@cybertec.at> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/16/23 07:11, Laurenz Albe wrote: > On Mon, 2023-01-16 at 07:48 -0500, Fred Habash wrote: >> This is a puzzle I have not been able to crack yet. >> >> We have a single-page table with 28 rows that is purely read-only. There isn't a way in postgres to make a table RO, but I say this with confidence because pg_stat_user_tables has always showed 0 >> updates/deletes/inserts. >> >> Furthermore, the schema app developers know, for certain, this table does not get changed at all. >> >> We installed scripts that run every few minutes that do a 'select *' and over a period of days, we have not seen a change. >> >> We disabled autovacuum on this table '{autovacuum_enabled=false}'. But, despite the fact that this table is read-only (by design) and autovac id is disabled, it got autovac'd twice in less than 10 >> days and on both occasions, pg_stat_activity showed the worker with 'to prevent wraparound'. This explains why autovac did not honor the disabled status. >> >> But why is this table autovac'd at all? > For every table PostgreSQL stores the oldest transaction ID in an unfrozen tuple > in "pg_class.relfrozenxid". Once that is more than "autovacuum_freeze_max_age", > the table gets autovacuumed. If the table is already all-frozen, that is a short > operation and will just advance "pg_class.relfrozenxid". So OP should VACUUM FREEZE the table. -- Born in Arizona, moved to Babylonia.