PostgreSQL中int类型达到上限的一些处理方案

使用int类型作为表的主键在pg中是很常见的情况，但是pg中int类型的范围在-2147483648到2147483647，最大只有21亿，这个在一些大表中很容易就会达到上限。一旦达到上限，那么表中便没办法在插入数据了，这个将会是很严重的问题。

如何监控？

对于此类情况，我们可以考虑将序列使用情况加入到监控中，防止达到最大值后表中无法插入数据的情况发生。

可以使用下面SQL去查询库中序列的使用情况：

SELECT
    seqs.relname AS sequence,
    format_type(s.seqtypid, NULL) sequence_datatype,
    CONCAT(tbls.relname, '.', attrs.attname) AS owned_by,
    format_type(attrs.atttypid, atttypmod) AS column_datatype,
    pg_sequence_last_value(seqs.oid::regclass) AS last_sequence_value,
    TO_CHAR((
        CASE WHEN format_type(s.seqtypid, NULL) = 'smallint' THEN
            (pg_sequence_last_value(seqs.relname::regclass) / 32767::float)
        WHEN format_type(s.seqtypid, NULL) = 'integer' THEN
            (pg_sequence_last_value(seqs.relname::regclass) / 2147483647::float)
        WHEN format_type(s.seqtypid, NULL) = 'bigint' THEN
            (pg_sequence_last_value(seqs.relname::regclass) / 9223372036854775807::float)
        END) * 100, 'fm9999999999999999999990D00%') AS sequence_percent,
    TO_CHAR((
        CASE WHEN format_type(attrs.atttypid, NULL) = 'smallint' THEN
            (pg_sequence_last_value(seqs.relname::regclass) / 32767::float)
        WHEN format_type(attrs.atttypid, NULL) = 'integer' THEN
            (pg_sequence_last_value(seqs.relname::regclass) / 2147483647::float)
        WHEN format_type(attrs.atttypid, NULL) = 'bigint' THEN
            (pg_sequence_last_value(seqs.relname::regclass) / 9223372036854775807::float)
        END) * 100, 'fm9999999999999999999990D00%') AS column_percent
FROM
    pg_depend d
    JOIN pg_class AS seqs ON seqs.relkind = 'S'
        AND seqs.oid = d.objid
    JOIN pg_class AS tbls ON tbls.relkind = 'r'
        AND tbls.oid = d.refobjid
    JOIN pg_attribute AS attrs ON attrs.attrelid = d.refobjid
        AND attrs.attnum = d.refobjsubid
    JOIN pg_sequence s ON s.seqrelid = seqs.oid
WHERE
    d.deptype = 'a'
    AND d.classid = 1259;

查询出的结果类似这样：

解决方案

1、修改序列为负数

因为pg中int类型是包含负数的，所以如果序列从0开始递增即将达到最大值，那么可以考虑切换到负数排序。将序列的起始值设置为-1然后降序来递增。

alter sequence test_id_seq no minvalue start with -1 increment -1 restart;

这种方式不需要修改表的结构，可以很快的解决问题。但是这种方案的前提是主键列只是单纯用来做递增的唯一约束用的，可以接受使用负数才行。

而且这也只能将可用数据范围翻倍，只能短期解决问题，如果后续负数用完了那就没办法了，只能去修改字段类型了。

2、修改序列cycle属性（分区表）

如果你的表是分区表的话，还可以考虑直接修改序列的属性为cycle。因为在pg中，主键并不是全局性的约束，而只是针对单个分区的。

即分区1和分区2中都可以出现主键id相同的数据。当然，这种方案仅限于分区表的场景。

alter sequence test_id_seq cycle;

3、修改字段类型为bigint

如果上面俩种方案都没法解决的话，那最终只能选择修改字段类型为bigint的方式了。不过肯定也不能直接去这样修改：

alter table xxx alter id type bigint;

毕竟一般int类型达到上限的表都很大了，修改int为bigint是会重写表的，需要长时间持有独占锁，这个对业务来说是难以接受的。

比较推荐的方案就是新增一个bigint列，然后用其去替换原先的int列。

alter table test add column id_new bigint;
CREATE UNIQUE INDEX CONCURRENTLY test_id_new ON test (id_new);

紧接着再创建对应的bigint的序列。

CREATE SEQUENCE test_id_new_seq START 2147483776 AS bigint;
ALTER TABLE test ALTER COLUMN id_new SET DEFAULT nextval ('test_id_new_seq');
alter sequence test_id_new_seq owned by test.id_new;

现在旧的int类型和新的bigint类型的列就都在自增了。

bill=# select * from test;
     id     | value |   id_new
------------+-------+------------
 2000000009 |       |
 2000000010 |       |
 2000000011 |       | 2147483776
 2000000012 |       | 2147483777

然后我们就可以将id列和id_new列进行重命名了，这一步需要放在同一个事务中去处理。

BEGIN;
ALTER TABLE test DROP CONSTRAINT test_pkey;
ALTER TABLE test ALTER COLUMN id DROP DEFAULT;
ALTER TABLE test RENAME COLUMN id TO id_old;
ALTER TABLE test RENAME COLUMN id_new TO id;
ALTER TABLE test ALTER COLUMN id_old DROP NOT NULL;
ALTER TABLE test ADD CONSTRAINT id_not_null CHECK (id IS NOT NULL) NOT VALID;
COMMIT;

由于之前添加id_new列中有大量null值，因此需要将约束设置为NOT VALID，但是我们需要将该列变为主键的话，之前的null值还是需要去处理的，这里使用批量update的方式去进行更新。

WITH unset_values AS (
    SELECT
        id_old
    FROM
        test
    WHERE
        id IS NULL
    LIMIT 1000)
UPDATE
    test
SET
    id = unset_values.id_old
FROM
    unset_values
WHERE
    unset_values.id_old = test.id_old;

null的数据处理完之后，我们便可以将新的id列更改为主键了。

ALTER TABLE test VALIDATE CONSTRAINT id_not_null;
ALTER TABLE test ADD CONSTRAINT test_pkey PRIMARY KEY USING INDEX test_id_new;
ALTER TABLE test DROP CONSTRAINT id_not_null;

最后我们便可以将旧的id列删除了，记得删除完之后重新收集下表的统计信息。

bill=# ALTER table test drop column id_old;
ALTER TABLE
bill=# analyze t1;
ANALYZE
bill=# \d test
                              Table "public.test"
 Column |  Type   | Collation | Nullable |               Default
--------+---------+-----------+----------+--------------------------------------
 value  | integer |           |          |
 id     | bigint  |           | not null | nextval('test_id_new_seq'::regclass)
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

总的来说这种方式也是比较麻烦的，其中在线创建索引和批量update都是比较耗时的操作。

因此对于应用来说还是应该尽可能的避免出现这种情况，大表在设计的阶段就应该考虑使用bigint或者bigserial来代替int类型，不要在int类型快要达到最大值再开始去救火。