2022年度最后一期 PostgreSQL 问答
目录
Q1. PG 中能修改字段类型么？
问题描述
PostgreSQL中想要修改表的字段类型，可以直接改么？有什么需要注意的么？
问题解答
可以修改，有一些注意事项：
-
表和索引是否重写
- 由小改大：表是不会重写的，索引不会发生重写。
- 但是注意对于int4到int8这种转化，还是需要重写的，因为底层存储不一样。
- 修改分区表索引列：子表不会重写，所有索引会重写
- 由大改小：表需要重写，索引自然也需要重写。
- 由小改大：表是不会重写的，索引不会发生重写。
-
统计信息：
修改列的类型，包括长度、类型，需要重新收集统计信息
可供参考的文章：
https://mp.weixin.qq.com/s/g8gEDPIfF3kFDf_32CBEmg
https://mp.weixin.qq.com/s/BzLTJhD-o8wiWOzs_-TS-A
Q2. PostgreSQL 必须禁用ipv6吗？
问题描述
PostgreSQL 中“启用 ipv6，统计信息将无法正常工作”，这种说法是否正确么？
问题解答
不对，没有必须禁用一说
一般情况下如果不使用ipv6，我们推荐设置
listen_addresses = '0.0.0.0'
而不是设置为“*”，这样PG不会监听ipv6。
Q3. PostgreSQL 中如何创建一个uuid类型的列？
问题描述
PostgreSQL 中如何创建一个uuid作为索引或者uuid类型的列？
问题解答
PG里直接就有uuid这个类型，可以直接使用
postgres=# create table t_uuid(id uuid);
CREATE TABLE
postgres=# \d t_uuid
Table "public.t_uuid"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
id | uuid | | |
生成uuid函数，从PG13开始可以使用内置的gen_random_uuid()函数，以前的版本可以使用uuid-ossp扩展
postgres=# insert into t_uuid values(gen_random_uuid());
INSERT 0 1
postgres=# select * from t_uuid;
id
--------------------------------------
f9e62887-c6ce-49c0-87e3-bd53d16e8b99
(1 row)
Q4. PostgreSQL 12对于逻辑复制有什么优化建议吗？
问题描述
PostgreSQL 12 版本关于逻辑复制的优化
问题解答
-
方案一：如果cpu负载不高，可以考虑设置wal_compression
WAL的全页写(Full Page Write)发生于检查点完成之后的第一次修改中，通过设置wal_compression为on后，PostgreSQL将采用内置的pglz算法压缩全页写再写入WAL。
详情指路👉：https://www.modb.pro/db/593386
-
方案二：建议升级到14。
PostgreSQL14做了大量性能优化：流式处理大事务、分区表优化、二进制格式传输、大表初始同步优化，还有一些功能和监控视图改进
Q5. PostgreSQL中如何查找所有包含bigint类型字段的表
问题描述
PostgreSQL 中如何查找所有包含bigint类型字段的表，想批量把包含bigint类型字段的表都改成numeric的，只要所有业务用户的表
问题解答
相关表：pg_attribute、pg_class
select pc.relnamespace::regnamespace,pa.attrelid::regclass, pa.attname
from pg_attribute pa
join pg_class pc
on pa.attrelid=pc.oid
where atttypid = 'bigint'::regtype
and pc.relkind=any(array['r','p','m'])
and pc.relnamespace!=to_regnamespace('pg_catalog');
例：
