TimescaleDB压缩Chunk触发sequence id overflow错误，求排查与解决

环境信息 #

• TimescaleDB版本：2.7.2
• PostgreSQL版本：12.11（Ubuntu 12.11-1.pgdg20.04+1）
• 运行平台：x86_64-pc-linux-gnu
• 编译环境：gcc 9.4.0

问题描述 #

执行compress_chunk函数手动压缩Chunk，或通过压缩任务自动执行时，部分Chunk触发"sequence id overflow"错误，其余Chunk可正常压缩。相关执行日志如下：

SELECT  'set temp_file_limit =-1; SELECT compress_chunk(''' || chunk_schema|| '.' || chunk_name || ''');'  
 FROM timescaledb_information.chunks
  WHERE   is_compressed =false;
                                         ?column?                                           
-------------------------------------------------------------------------------------------
 set temp_file_limit =-1; SELECT compress_chunk('_timescaledb_internal._hyper_1_2_chunk');
 set temp_file_limit =-1; SELECT compress_chunk('_timescaledb_internal._hyper_1_3_chunk');
 set temp_file_limit =-1; SELECT compress_chunk('_timescaledb_internal._hyper_1_4_chunk');
 set temp_file_limit =-1; SELECT compress_chunk('_timescaledb_internal._hyper_1_5_chunk');
 set temp_file_limit =-1; SELECT compress_chunk('_timescaledb_internal._hyper_1_8_chunk');


SELECT compress_chunk('_timescaledb_internal._hyper_1_2_chunk');
DEBUG:  building index "pg_toast_29929263_index" on table "pg_toast_29929263" serially
DEBUG:  building index "compress_hyper_3_12_chunk__compressed_hypertable_3_ivehicleid__" on table "compress_hyper_3_12_chunk" serially
DEBUG:  building index "compress_hyper_3_12_chunk__compressed_hypertable_3_gid__ts_meta" on table "compress_hyper_3_12_chunk" serially
DEBUG:  building index "compress_hyper_3_12_chunk__compressed_hypertable_3_irowversion_" on table "compress_hyper_3_12_chunk" serially
DEBUG:  building index "compress_hyper_3_12_chunk__compressed_hypertable_3_gdiagnostici" on table "compress_hyper_3_12_chunk" serially
DEBUG:  building index "compress_hyper_3_12_chunk__compressed_hypertable_3_gcontrolleri" on table "compress_hyper_3_12_chunk" serially
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.58", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.86", size 103432192
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.85", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.84", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.83", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.82", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.81", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.80", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.79", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.78", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.77", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.76", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.75", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.74", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.73", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.72", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.71", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.70", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.69", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.68", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.67", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.66", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.65", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.64", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.63", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.62", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.61", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.60", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.59", size 1073741824
ERROR:  sequence id overflow
Time: 1105092.862 ms (18:25.093)



SET client_min_messages TO DEBUG1; CALL run_job(1000);
SET
Time: 1.120 ms
DEBUG:  Executing policy_compression with parameters {"hypertable_id": 1, "compress_after": "28 days"}
DEBUG:  building index "pg_toast_29929276_index" on table "pg_toast_29929276" serially
DEBUG:  building index "compress_hyper_3_13_chunk__compressed_hypertable_3_ivehicleid__" on table "compress_hyper_3_13_chunk" serially
DEBUG:  building index "compress_hyper_3_13_chunk__compressed_hypertable_3_gid__ts_meta" on table "compress_hyper_3_13_chunk" serially
DEBUG:  building index "compress_hyper_3_13_chunk__compressed_hypertable_3_irowversion_" on table "compress_hyper_3_13_chunk" serially
DEBUG:  building index "compress_hyper_3_13_chunk__compressed_hypertable_3_gdiagnostici" on table "compress_hyper_3_13_chunk" serially
DEBUG:  building index "compress_hyper_3_13_chunk__compressed_hypertable_3_gcontrolleri" on table "compress_hyper_3_13_chunk" serially
 LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.87", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.115", size 103432192
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.114", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.113", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.112", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.111", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.110", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.109", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.108", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.107", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.106", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.105", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.104", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.103", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.102", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.101", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.100", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.99", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.98", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.97", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.96", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.95", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.94", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.93", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.92", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.91", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.90", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.89", size 1073741824
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp713.88", size 1073741824
ERROR:  sequence id overflow
CONTEXT:  SQL statement "SELECT public.compress_chunk( chunk_rec.oid )"
PL/pgSQL function _timescaledb_internal.policy_compression_execute(integer,integer,anyelement,integer,boolean,boolean) line 35 at PERFORM
SQL statement "CALL _timescaledb_internal.policy_compression_execute(
        job_id, htid, lag_value::INTERVAL,
        maxchunks, verbose_log, recompress_enabled
      )"
PL/pgSQL function _timescaledb_internal.policy_compression(integer,jsonb) line 51 at CALL
Time: 1113429.153 ms (18:33.429)

错误原因 #

该错误源于TimescaleDB 2.7.2版本中，压缩块的ID使用int32类型存储，其最大值为2^31-1（约21亿）。当待压缩的Chunk数据量极大时，压缩过程中生成的压缩块数量会突破这个上限，触发序列溢出。从日志中大量的临时文件可以判断，出错的Chunk数据量远大于正常Chunk，导致压缩块数量超出int32的范围。

解决方法 #

1. 升级TimescaleDB版本 #

TimescaleDB在2.8.0及后续版本中，已将压缩块ID的类型从int32改为int64，其最大值为2^63-1（约9万亿），从根本上避免了序列溢出问题。升级时需确保PostgreSQL版本兼容（PostgreSQL 12可兼容TimescaleDB 2.8.0及以上版本）。

2. 拆分大Chunk后压缩 #

若暂时无法升级，可先将大Chunk拆分为多个小Chunk，再分别压缩：

-- 按7天间隔拆分Chunk，可根据实际数据量调整间隔
SELECT split_chunk('_timescaledb_internal._hyper_1_2_chunk', INTERVAL '7 days');

拆分后每个子Chunk的压缩块数量会控制在int32上限内，即可正常压缩。

3. 调整压缩任务配置 #

检查压缩任务的maxchunks参数，避免一次处理过多大Chunk；同时确保服务器有足够的临时存储空间，防止IO瓶颈延长压缩时间或引发其他问题。

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