Message-ID:
 <SN4P221MB0683A34E144BD9A66BBCB566F7EA2@SN4P221MB0683.NAMP221.PROD.OUTLOOK.COM>
Date: Tue, 21 May 2024 14:46:15 -0700
User-Agent: Mozilla Thunderbird
Content-Language: en-US
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
From: Senor Cervesa <frio_cervesa@hotmail.com>
Subject: vacuum an all frozen table
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
X-MS-PublicTrafficType: Email
X-MS-TrafficTypeDiagnostic: SN4P221MB0683:EE_|CH0P221MB0377:EE_
X-MS-Office365-Filtering-Correlation-Id: efca4b2e-883d-4077-d9ad-08dc79df71b8
X-Microsoft-Antispam:
	BCL:0;ARA:14566002|461199019|440099019|3412199016|3430499023;
X-Microsoft-Antispam-Message-Info:
	mTixwTVIDlykbaEu6HrZA8aw0Ls3CVVjkudyJFZb3biUCXXR7LNQyRfwdpKldlawDb3cx2UgtGjZnlUxI1y0/VMRTnVvomP9W7L6hoXtNebyhMcaSQSEeqe5rZRKJPX3/xBrL2s0cpOrA0c3e9VledJ0eMZDibJgDlLtN78J/XOVVjw4KwoIqmGZGXfta2GMpZ6mhvdNOJYZtYhyykTw5dqZW3HC6QWxkQUe3xb0o9hAoIph/nUPIgJfj514C5Ehc4UzZWjOr4j6lkNxOH4EJmJCmaFb/hPYbpT9AwxDAIJUVNR8lMqZWeZzPLdKi4p9nn2LMzv+jps4pqtaLnMgu8D+mg569DyfVMqMg79bIGOqjEh5Xs+K4TrsVvubVYy4Z5f7LSBgBgrU0bG/XT1f39Uxlq6Db4qHRd1WbqgOp3fQCNkjFGsrjiW8d/73a8FYeB6sFQrgCHL0UjxL0eeQF+sPYfowc88Q8VS0QtcD1LkRsfObmpHCuVGZof2ZWatvQhyQ+x7COlIElO8keOc99fZ/E8MQELcPLe8KOL6Va/XqPFqhXaK9IFqmw60aZOq/
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0:
	=?utf-8?B?Ly9WZ1huSkRveTZDNVVObm4ybEVUNTY0RHFZSENrcjNHWi9lQlNwN2grc3ly?=
 =?utf-8?B?YWFyNlY1UmhUcS9mbExBbXoraGFnUGJlKzlOdkJBU2pndlhKeS9EMU5CcU1C?=
 =?utf-8?B?Zkc2NHA0NmtweEgwUzBSSk1DRUtjdDZLMzZxeE01RjgxWkR6c2tKVC9IR05u?=
 =?utf-8?B?NkJ0NWRuTVA1Uk1Kd0VSZ01CbzZtUzVNa0xVNW5rcmJWM1lXTFozVlNlOWgx?=
 =?utf-8?B?V0VXdFBtZUY2VXdMM0tsd1YrWmpjMFFxbWY1dmFMcmt2bXNydUVNeHhVSTB5?=
 =?utf-8?B?TG1LT3VGNlZOaGZTOWZPMmdZT1RHbFIrRURXRmgxMTBWcnZTT0hQK1ladmh3?=
 =?utf-8?B?a0M3d3RMa25EZWtkeWNxeitKancwS3VsSHlqV0FXaGJQL1pVL2lzckJiSEJT?=
 =?utf-8?B?NG00MC8zdm01YktPY29RZXU5bnVFU2xQdU12NURpTVh3ZDUyNG9pWFRleE1X?=
 =?utf-8?B?L0J5SG1mU1dGOHVlWWpzVHBmRHR6Sjd1SEVLNTNQUTBtVkRzazZwakpzZnY4?=
 =?utf-8?B?RVJNTEQ5NDdqdDZBNU14SHJtUWsvMzdsakNoMUxYOEdQbkNEQngrN1Q0dnFL?=
 =?utf-8?B?bHRWZ0FsMGwyL3pOUTZMVXBUOVZ3emxGUnJCTjVDYVVPRzEvNjlFN0lBY1d2?=
 =?utf-8?B?TmhYRTdlZnFFaVY1b0tHL0V1L2hSaFJwbWg1YzJyQWFoK09lSXpKV2FqeFEr?=
 =?utf-8?B?R1YzYTR6WEUvbHdBKzJFNTYwWUF1dmkrcEtxZ3dZUDBIcmMzUVFlVCtpUGtK?=
 =?utf-8?B?SWpNNTFvTll2bEZPWUdqZ0FwUGFMOXR0eWNDR1lSNDZhYTh5Q3FVbFNQVXBu?=
 =?utf-8?B?aG1DeE5LTkRYSCtBUFpHRWoyQ05ZN1BaVnkzaFg5VmMrM0FuWE9ZM2dDNVVZ?=
 =?utf-8?B?TEM4eHFSUyt2aEU1MkdEaFNib3J4Z05HRjg1aXJBdnFBaFVqQUVkMk13cEJq?=
 =?utf-8?B?ZnVHOXZSUHBKTGlyd2tOVkIyY2VMald1WDY2M0tsUFVrYzR4RndFUDg5Z2tk?=
 =?utf-8?B?REx0WEUwWGc3RnN2MTNLT01yWWMzU1pmMjhqZDFLS25nQjd0T2hucUZXMG5W?=
 =?utf-8?B?UkZRUlo1VVdIdXFsR240RlFvTnJ4Rk9KbEwwTmE5QlliT3p3T2Rnc05MOHRm?=
 =?utf-8?B?MGh0NzlubHI4dC9yNVl5dHR0Y2VQMXpmTXZLM29pQ1E4QUlhbDR1eXFTRHFT?=
 =?utf-8?B?UG1Eb2JJM2FJTlc0YnR4NElFeHlpZGdxckc3OEk0QU1jbndza3loQUlScUZk?=
 =?utf-8?B?S2lyTUhQU0k5UEJzVk0ybzVETjlhTEZMU2IvWjVkME9tK2M4NmwyZWlvSkNQ?=
 =?utf-8?B?L0FsUitKWldweWpIbWNZblRoOTdDMTJzZU9zcnVPVm1JTUV6THJCd1IrZVFC?=
 =?utf-8?B?UVF4VFo4bUI0dWNnYWIvNnFGRTlDZ2hiMFJGZnhyclhUOTl0cXBSb0EzQWVU?=
 =?utf-8?B?a2xrWEJxSEV0YkZGbFAweFB5WTFuZzdJczZlUWZObWNzdHh6c1lRNGNxK2c1?=
 =?utf-8?B?UUJPL2F4bU1sb1JEVkc1ZW9iYlBDa2FhU3BZR2N0Uk1WR2VrQWZQclMzYVVh?=
 =?utf-8?B?MVhNZ3g1WWtzbEtFdGs2QTAzNTBGMzZnejFQdHdkcFI2YXhEblFUMHltVVJD?=
 =?utf-8?B?cW5nY2lQWDRmUDNGYlhDcnQzMGppVG9mVGdURk5jVHQ4WERqeFhrNmpjVU8r?=
 =?utf-8?Q?+F+ddND+jst4B/UGNe6O?=
X-OriginatorOrg: sct-15-20-4755-11-msonline-outlook-f5d03.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: efca4b2e-883d-4077-d9ad-08dc79df71b8
X-MS-Exchange-CrossTenant-AuthSource: SN4P221MB0683.NAMP221.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 21 May 2024 21:46:17.5086
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg:
	00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CH0P221MB0377
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/SN4P221MB0683A34E144BD9A66BBCB566F7EA2%40SN4P221MB0683.NAMP221.PROD.OUTLOOK.COM>
Precedence: bulk

Hi All,

I'd like to understand what's happening here and whether there is 
anything I can do to improve the situation.

PostgreSQL v11.22 (yeah, I know. Needs upgrade)

The following 3 autovacuum log entries show a vacuum of an append only 
table that has not had any changes since the end of 5/10/2024. There is 
only 1 page not skipped in each instance yet it takes over 1100 seconds 
to complete. Visibility map shows all frozen. The associated TOAST table 
is similar in numbers except that it completes in sub-second times.

I understand that the vacuum is occurring due to age of 
pg_class.relfrozenxid for the table but what exactly is it referring to 
in these cases? Can that also be frozen or similar? Should I add 
autovacuum_freeze_max_age=400000000 or higher to relopts do reduce 
vacuum frequency.


2024-05-17 09:56:57.167 GMT "" "" LOG:  automatic aggressive vacuum of 
table "workdb1.public.log_entry_20240510": index scans: 0
         pages: 0 removed, 53551748 remain, 0 skipped due to pins, 
53551747 skipped frozen
         tuples: 0 removed, 242384013 remain, 0 are dead but not yet 
removable, oldest xmin: 3245896267
         buffer usage: 107117593 hits, 123159244 misses, 3 dirtied
         avg read rate: 856.853 MB/s, avg write rate: 0.000 MB/s
         system usage: CPU: user: 151.07 s, system: 638.29 s, elapsed: 
1122.92 s

2024-05-18 23:20:37.900 GMT "" "" LOG:  automatic aggressive vacuum of 
table "workdb1.public.log_entry_20240510": index scans: 0
         pages: 0 removed, 53551748 remain, 0 skipped due to pins, 
53551747 skipped frozen
         tuples: 0 removed, 242384013 remain, 0 are dead but not yet 
removable, oldest xmin: 3445717402
         buffer usage: 107105521 hits, 123171316 misses, 3 dirtied
         avg read rate: 839.611 MB/s, avg write rate: 0.000 MB/s
         system usage: CPU: user: 166.46 s, system: 611.40 s, elapsed: 
1146.09 s

2024-05-20 19:11:29.519 GMT "" "" LOG:  automatic aggressive vacuum of 
table "workdb1.public.log_entry_20240510": index scans: 0
         pages: 0 removed, 53551748 remain, 0 skipped due to pins, 
53551747 skipped frozen
         tuples: 0 removed, 242384013 remain, 0 are dead but not yet 
removable, oldest xmin: 3645738812
         buffer usage: 107095847 hits, 123180990 misses, 2 dirtied
         avg read rate: 744.513 MB/s, avg write rate: 0.000 MB/s
         system usage: CPU: user: 195.13 s, system: 694.13 s, elapsed: 
1292.59 s

workdb1=# SELECT * from pg_visibility('log_entry_20240510'::regclass) where
all_visible IS NOT true
OR all_frozen IS NOT true
OR pd_all_visible IS NOT true;
  blkno | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
(0 rows)


Thank you for any insights,

Senor