如何实现选中行itempointer从1到n连续序列化及多blockid成因解析

ctid是PostgreSQL内置的物理行位置标识，固定格式为(块编号blockid, 块内行偏移itempointer)：

• 单个blockid对应表文件中一个8KB大小的存储数据块，itempointer是行在当前块内的存储位置序号，每个块内的itempointer独立从1开始计数，不存在全局连续的规则。
• 你查询到的结果跨324~327共4个块，本质是筛选出的25条符合条件的数据，物理上没有存储在同一个数据块中，而是分散在4个不同块里：
  • 324号块内只有偏移位10的行满足where筛选条件，块内其他行要么不满足筛选规则，要么是被更新/删除后标记为失效的死元组
  • 单个数据块的存储空间有上限，存满之后新插入的数据会自动写入下一个新块，符合条件的行自然会分散在多个块上
    注意：ctid是随物理存储变化的临时标识，VACUUM清理、数据更新、表重排操作都会修改行的ctid，永远不要把ctid当做业务层面的稳定连续行号使用。

分两种场景对应处理：

仅需要查询结果返回1~25的连续序号（不需要修改物理存储） #

这是绝大多数场景下的正确方案，不需要依赖物理存储位置，直接用窗口函数生成逻辑连续序号即可：

SELECT
  ctid,
  row_number() OVER (ORDER BY ctid) AS continuous_row_index
FROM awanti_grid_cell_data agcd
WHERE selectedsiteid = '202230060950' 
  AND centerPointsOfWindowAsGeoJSONInEPSG4326ForCellsInTreatment IS NOT NULL
  AND centerPointsOfWindowAsGeoJSONInEPSG4326ForCellsInTreatment <> 'None'

查询返回的continuous_row_index字段会从1开始连续计数到25，完全满足连续序号的需求，且不受数据物理存储位置变化的影响。

确实需要让符合条件的行物理存储在同一个blockid下 #

如果有明确的性能调优需求，必须让这些行落在同一个数据块，可以通过物理重排数据实现：

1. 先对表执行VACUUM awanti_grid_cell_data;清理死元组，释放块内闲置空间
2. 在业务低峰期通过事务将符合条件的数据迁移后重插，让新插入的数据尽可能落到同一个块中，参考操作语句：

BEGIN;
-- 将符合条件的数据暂存到临时表
CREATE TEMP TABLE tmp_agcd AS
SELECT * FROM awanti_grid_cell_data agcd
WHERE selectedsiteid = '202230060950' 
  AND centerPointsOfWindowAsGeoJSONInEPSG4326ForCellsInTreatment IS NOT NULL
  AND centerPointsOfWindowAsGeoJSONInEPSG4326ForCellsInTreatment <> 'None';
-- 删除原表中对应的数据
DELETE FROM awanti_grid_cell_data agcd
WHERE selectedsiteid = '202230060950' 
  AND centerPointsOfWindowAsGeoJSONInEPSG4326ForCellsInTreatment IS NOT NULL
  AND centerPointsOfWindowAsGeoJSONInEPSG4326ForCellsInTreatment <> 'None';
-- 将暂存的数据重新插回原表，若单块容量足够，这些行会写入同一个blockid下
INSERT INTO awanti_grid_cell_data SELECT * FROM tmp_agcd;
COMMIT;

注意：物理重排完成后，后续的插入、更新、删除操作依然可能导致数据再次分散到不同块，无法永久保证这些行一直属于同一个blockid。非必要不建议做这类物理重排操作，用逻辑生成的连续序号是通用性、稳定性更好的方案。

内容的提问来源于stack exchange，提问作者Amrmsmb