循序渐进MogDB：astore 和空间更新膨胀问题及 Vacuum 解决

MogDB 缺省采用 aStore 存储引擎，行存储表支持多版本元组机制。astore存储格式为追加写优化设计，其多版本元组产生和存储方式如下图所示。

当一个更新操作将v0版本元组更新为v1版本元组之后，如果v0版本元组所在页面仍然有空闲空间，则直接在该页面内插入更新后的v1版本元组，并将v0版本的元组指针指向v1版本的元组指针。在这个过程中，新版本元组以追加写的方式和被更新的老版本元组混合存放。因此，astore存储格式比较适合频繁插入、少量更新的业务场景。

1. 测试 aStore 堆表更新

测试表初始占用约 1174 MB 空间：

MogDB=#select bytes/1024/1024 from dba_segments where segment_name='ALLPEOPLE';
  ?column?   
-------------
 1174.265625
(1 row)

更新该表的 id 字段，用 rownum 替代：

MogDB=#update allpeople set id=rownum;
UPDATE 5372137
MogDB=#select bytes/1024/1024 from dba_segments where segment_name='ALLPEOPLE';
   ?column?   
--------------
 2288.7734375

更新完成之后，该表的空间占用达到了 2288 MB，接近原表的2倍空间。

2. 通过vacuum释放空间

通过 vacuum full 触发一次空间清理：

MogDB=#vacuum full;
NOTICE:  skipping "pg_type" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_client_global_keys_args" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_authid" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_statistic" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_statistic_ext" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_user_mapping" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_subscription" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_class" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_largeobject" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_attribute" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_proc" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_package" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_partition" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_attrdef" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_constraint" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_inherits" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_index" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_operator" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_opfamily" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_opclass" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_am" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_amop" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_amproc" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_language" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_largeobject_metadata" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_aggregate" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_rewrite" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_trigger" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_description" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_cast" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_enum" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_namespace" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_conversion" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_depend" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_database" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_db_role_setting" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_tablespace" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_pltemplate" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_auth_members" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_shdepend" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_shdescription" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_ts_config" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_ts_config_map" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_ts_dict" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_ts_parser" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_ts_template" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_extension" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_obsscaninfo" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_foreign_data_wrapper" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_foreign_server" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pgxc_class" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pgxc_node" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pgxc_group" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_resource_pool" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_workload_group" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_app_workloadgroup_mapping" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_foreign_table" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_rlspolicy" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_default_acl" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_seclabel" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_shseclabel" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_collation" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_range" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_encrypted_columns" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_column_keys" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_column_keys_args" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_client_global_keys" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_encrypted_proc" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_job" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_asp" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_job_proc" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_object" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_synonym" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_obsscaninfo" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_directory" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_hashbucket" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_global_chain" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_global_config" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "streaming_stream" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "streaming_cont_query" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "streaming_reaper_status" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_matview" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_matview_dependency" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pgxc_slice" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_opt_model" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_model_warehouse" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_recyclebin" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_txn_snapshot" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_job_argument" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_job_attribute" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_uid" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_db_privilege" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_replication_origin" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_publication" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_publication_rel" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_user_status" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_auth_history" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "pg_extension_data_source" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_auditing_policy" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_auditing_policy_access" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_auditing_policy_filters" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_auditing_policy_privileges" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_policy_label" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_masking_policy" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_masking_policy_actions" --- use xc_maintenance_mode to VACUUM FULL it
NOTICE:  skipping "gs_masking_policy_filters" --- use xc_maintenance_mode to VACUUM FULL it
VACUUM

3. 空间使用

经过空间回收，空间使用下降到 1115MB：

MogDB=#select bytes/1024/1024 from dba_segments where segment_name='ALLPEOPLE';
   ?column?   
--------------
 1115.6484375
(1 row)

这种情况也是 PostgreSQL 天然带来的，MogDB 在3.0 版本，支持 Ustore 存储，通过UNDO原理，彻底消除了更新膨胀问题。请参考：MogDB 新特性：Ustore 存储引擎 实现原位更新避免空间膨胀