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

2024-02-28 13:20

本文主要是介绍PostgreSQL中int类型达到上限的一些处理方案,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

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

如何监控?

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

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

SELECTseqs.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
FROMpg_depend dJOIN pg_class AS seqs ON seqs.relkind = 'S'AND seqs.oid = d.objidJOIN pg_class AS tbls ON tbls.relkind = 'r'AND tbls.oid = d.refobjidJOIN pg_attribute AS attrs ON attrs.attrelid = d.refobjidAND attrs.attnum = d.refobjsubidJOIN pg_sequence s ON s.seqrelid = seqs.oid
WHEREd.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 |       | 21474837762000000012 |       | 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 (SELECTid_oldFROMtestWHEREid IS NULLLIMIT 1000)
UPDATEtest
SETid = unset_values.id_old
FROMunset_values
WHEREunset_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 testTable "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类型快要达到最大值再开始去救火。

这篇关于PostgreSQL中int类型达到上限的一些处理方案的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/755622

相关文章

Python使用vllm处理多模态数据的预处理技巧

《Python使用vllm处理多模态数据的预处理技巧》本文深入探讨了在Python环境下使用vLLM处理多模态数据的预处理技巧,我们将从基础概念出发,详细讲解文本、图像、音频等多模态数据的预处理方法,... 目录1. 背景介绍1.1 目的和范围1.2 预期读者1.3 文档结构概述1.4 术语表1.4.1 核

Knife4j+Axios+Redis前后端分离架构下的 API 管理与会话方案(最新推荐)

《Knife4j+Axios+Redis前后端分离架构下的API管理与会话方案(最新推荐)》本文主要介绍了Swagger与Knife4j的配置要点、前后端对接方法以及分布式Session实现原理,... 目录一、Swagger 与 Knife4j 的深度理解及配置要点Knife4j 配置关键要点1.Spri

PostgreSQL的扩展dict_int应用案例解析

《PostgreSQL的扩展dict_int应用案例解析》dict_int扩展为PostgreSQL提供了专业的整数文本处理能力,特别适合需要精确处理数字内容的搜索场景,本文给大家介绍PostgreS... 目录PostgreSQL的扩展dict_int一、扩展概述二、核心功能三、安装与启用四、字典配置方法

Spring Boot @RestControllerAdvice全局异常处理最佳实践

《SpringBoot@RestControllerAdvice全局异常处理最佳实践》本文详解SpringBoot中通过@RestControllerAdvice实现全局异常处理,强调代码复用、统... 目录前言一、为什么要使用全局异常处理?二、核心注解解析1. @RestControllerAdvice2

SQLite3 在嵌入式C环境中存储音频/视频文件的最优方案

《SQLite3在嵌入式C环境中存储音频/视频文件的最优方案》本文探讨了SQLite3在嵌入式C环境中存储音视频文件的优化方案,推荐采用文件路径存储结合元数据管理,兼顾效率与资源限制,小文件可使用B... 目录SQLite3 在嵌入式C环境中存储音频/视频文件的专业方案一、存储策略选择1. 直接存储 vs

postgresql数据库基本操作及命令详解

《postgresql数据库基本操作及命令详解》本文介绍了PostgreSQL数据库的基础操作,包括连接、创建、查看数据库,表的增删改查、索引管理、备份恢复及退出命令,适用于数据库管理和开发实践,感兴... 目录1. 连接 PostgreSQL 数据库2. 创建数据库3. 查看当前数据库4. 查看所有数据库

PostgreSQL数据库密码被遗忘时的操作步骤

《PostgreSQL数据库密码被遗忘时的操作步骤》密码遗忘是常见的用户问题,因此提供一种安全的遗忘密码找回机制是十分必要的,:本文主要介绍PostgreSQL数据库密码被遗忘时的操作步骤的相关资... 目录前言一、背景知识二、Windows环境下的解决步骤1. 找到PostgreSQL安装目录2. 修改p

PostgreSQL 默认隔离级别的设置

《PostgreSQL默认隔离级别的设置》PostgreSQL的默认事务隔离级别是读已提交,这是其事务处理系统的基础行为模式,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价... 目录一 默认隔离级别概述1.1 默认设置1.2 各版本一致性二 读已提交的特性2.1 行为特征2.2

PostgreSQL中MVCC 机制的实现

《PostgreSQL中MVCC机制的实现》本文主要介绍了PostgreSQL中MVCC机制的实现,通过多版本数据存储、快照隔离和事务ID管理实现高并发读写,具有一定的参考价值,感兴趣的可以了解一下... 目录一 MVCC 基本原理python1.1 MVCC 核心概念1.2 与传统锁机制对比二 Postg

电脑提示xlstat4.dll丢失怎么修复? xlstat4.dll文件丢失处理办法

《电脑提示xlstat4.dll丢失怎么修复?xlstat4.dll文件丢失处理办法》长时间使用电脑,大家多少都会遇到类似dll文件丢失的情况,不过,解决这一问题其实并不复杂,下面我们就来看看xls... 在Windows操作系统中,xlstat4.dll是一个重要的动态链接库文件,通常用于支持各种应用程序