Greenplum shows "WARNING: skipping "gp_persistent_relation_node" --- cannot vacuum indexes, views, external tables, or special system tables"

Products

• VMware Tanzu Greenplum

Issue/Introduction

The following warning message shows up in all versions of Greenplum:

database=# select * from gp_toolkit.gp_bloat_diag where bdinspname = 'pg_catalog' and bdirelname ilike 'gp%'; bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag ----------+------------+-----------------------------+-------------+-------------+------------------------------------5090 | pg_catalog | gp_persistent_relation_node | 4711 | 41 | significant amount of bloat suspected 
5094 | pg_catalog | gp_relation_node | 640 | 30 | significant amount of bloat suspected (2 rows) 
database=# vacuum pg_catalog.gp_persistent_relation_node; 
VACUUM 
database=# select * from gp_toolkit.gp_bloat_diag where bdinspname = 'pg_catalog' and bdirelname ilike 'gp%'; 
bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag ----------+------------+-----------------------------+-------------+-------------+--------------------------------------- 
5090 | pg_catalog | gp_persistent_relation_node | 4711 | 30 | significant amount of bloat suspected 
5094 | pg_catalog | gp_relation_node | 640 | 30 | significant amount of bloat suspected (2 rows) 
database=# vacuum full pg_catalog.gp_persistent_relation_node; 
WARNING: skipping "gp_persistent_relation_node" --- cannot vacuum indexes, views, external tables, or special system tables VACUUM 
database=#

Environment

Product Version: 5.22

Resolution

For GPDB 4：

You cannot perform a VACUUM on the pg_catalog.gp_persistent_relation_node table and this is by design.

Persistent tables use their own free lists to re-use dead rows, therefore VACUUM or VACUUM FULL is not needed. These tables have no impact on the database age and warnings are not getting generated because of these tables. The above theory applies to all persistent and global catalog tables.

For GPDB 5: 

Free list handling for PT was changed to leverage the regular heap mechanism and hence regular VACUUM works and is required to manage freespace. GPDB 4 PT had their own version of free list management and poses unnecessary challenges and complexity in code. Due to that, a need for vacuum didn’t exist in GPDB 4. 

Note: VACUUM FULL is still not enabled for PT and we can only use the PT rebuild to reduce the size of it.