pg_squeeze vs. VACUUM FULL CONCURRENTLY

wal_level = logical                         # pg_squeeze 使用 logical decoding
shared_preload_libraries = 'pg_squeeze'     # 用到了共享内存和 BackgroundWorker

-- 1. Register table for regular processing
INSERT INTO squeeze.tables (
    tabschema,
    tabname,
    schedule,
    free_space_extra,
    vacuum_max_age,
    max_retry
)
VALUES (
    'public',
    'bar',
    ('{30}', '{22}', NULL, NULL, '{3, 5}'),
    30,
    '2 hours',
    2
);

-- 2. Ad-hoc processing for any table
SELECT squeeze.squeeze_table('public', 'foo', 'foo_i_idx');

static void
squeeze_table_internal(Name relschema, Name relname, Name indname,
					   Name tbspname, ArrayType *ind_tbsp)
{
    ...
    // 设置 logical decoding 所需的上下文，包括从之前创建好的 replication slot 中反序列化
    // snapshot，创建 decoding context，创建 tuplestore 等
    ctx = setup_decoding(relid_src, tup_desc, &snap_hist);

    // 创建目的表
    relid_dst = create_transient_table(cat_state, tup_desc, tbsp_info->table,
									   rel_src_owner);

    // 打开源表，使用的共享锁，该表依然可以进行 DML 操作
    rel_src = table_open(relid_src, AccessShareLock);

    // 打开目的表，目的表使用的是排他锁
    rel_dst = table_open(relid_dst, AccessExclusiveLock);

    // 根据上面获取的 snapshot 将源表数据插入到目的表，可以根据设置的索引进行重排序
    perform_initial_load(rel_src, relrv_cl_idx, snap_hist, rel_dst, ctx);

    // 在临时的目的表上创建索引
    indexes_dst = build_transient_indexes(rel_dst, rel_src, indexes_src,
										  nindexes, tbsp_info, cat_state,
										  ctx);

    // 构建将在逻辑解码过程中用于查找要更新或删除的行的扫描键
    ident_key = build_identity_key(ident_idx_src, rel_src,
								   &ident_key_nentries);

    // 释放共享锁，后面的 perform_final_merge 会对源表加排他锁
    table_close(rel_src, AccessShareLock);

    // 将到目前为止插入的所有WAL（Write-Ahead Logging）记录刷新到磁盘，
    // 以最小化在持有源表排他锁时需要刷新的数据量
    xlog_insert_ptr = GetInsertRecPtr();
	XLogFlush(xlog_insert_ptr);

    // 解码并回放在 initial load 进行时发生的数据变更。XLOG读取器应该继续从
    // setup_decoding 函数停止的地方开始处理
    process_concurrent_changes(ctx, end_of_wal, cat_state, rel_dst,
							   ident_key, ident_key_nentries, iistate,
							   NoLock, NULL);

    // 尝试对源表的并发数据变更执行最终处理
    source_finalized = false;
	for (i = 0; i < 4; i++)
	{
        // 该函数内部会加排他锁
		if (perform_final_merge(relid_src, indexes_src, nindexes,
								rel_dst, ident_key, ident_key_nentries,
								iistate, cat_state, ctx))
		{
			source_finalized = true;
			break;
		}
		else
			elog(DEBUG1,
				 "pg_squeeze: exclusive lock on table %u had to be released.",
				 relid_src);
	}

    // 清理 logical decoding 上下文
    decoding_cleanup(ctx);

    table_close(rel_dst, AccessExclusiveLock);

    // 在系统表交换源表和目标表相关的文件 oid
    // 包含 relfilenode/reltablespace/reltoastrelid
    swap_relation_files(relid_src, relid_dst);

    // 交换 toast 表名称
    swap_toast_names(relid_src, toastrelid_dst, relid_dst, toastrelid_src);

    // 交换索引相关的 oid
    for (i = 0; i < nindexes; i++)
		swap_relation_files(indexes_src[i], indexes_dst[i]);

    // 删除源表相关的对象（这里删除 relid_dst 是因为上面进行了交换）
    object.classId = RelationRelationId;
	object.objectSubId = 0;
	object.objectId = relid_dst;
	performDeletion(&object, DROP_RESTRICT, PERFORM_DELETION_INTERNAL);
}

FWIW a newer, more modern and more trustworthy alternative to pg_repack
is pg_squeeze, which I discovered almost by random chance, and soon
discovered I liked it much more.

So thinking about your question, I think it might be possible to
integrate a tool that works like pg_squeeze, such that it runs when
VACUUM is invoked -- either under some new option, or just replace the
code under FULL, not sure.  If the Cybertec people allows it, we could
just grab the pg_squeeze code and add it to the things that VACUUM can
run.

Now, pg_squeeze has some additional features, such as periodic
"squeezing" of tables.  In a first attempt, for simplicity, I would
leave that stuff out and just allow it to run from the user invoking it,
and then have the command to do a single run.  (The scheduling features
could be added later, or somehow integrated into autovacuum, or maybe
something else.)