PostgreSQL

苓渊大约 3 分钟

--修改列的数据类型
ALTER TABLE table_name ALTER COLUMN column_name TYPE type_name USING column_name::type_name;

--为表添加注释
comment on table user is 'user table';
--为字段注释
comment on column user.userid is 'user ID';

-- 添加/删除约束
ALTER TABLE public.hk_ccassholdrecdet ADD CONSTRAINT pk_hk_ccassholdrecdet PRIMARY KEY ("id"); 
ALTER TABLE hk_ccassholdrecdet DROP CONSTRAINT pk_hk_ccassholdrecdet;

使用案例

重新发现PG之美

开发规范

INSERT ON conflict

类似于 MySQL 的 replace into，在插入数据时如果数据已存在，则不插入数据且不报错。根据唯一索引判断是否有相同数据。

INSERT INTO hk_ccassholdrecdet VALUES
(562269963791,1000225,TO_DATE('2017-10-24', 'yyyy-mm-dd'),'C00093',635649784.00,0.20890000,TO_DATE('2017-11-23', 'yyyy-mm-dd'),TO_DATE('2017-11-23', 'yyyy-mm-dd'),564790319721),
(562269963791,1000225,TO_DATE('2017-10-24', 'yyyy-mm-dd'),'C00093',635649784.00,0.20890000,TO_DATE('2017-11-23', 'yyyy-mm-dd'),TO_DATE('2017-11-23', 'yyyy-mm-dd'),564790319721)
ON conflict("innercode","holdingdate","partinumber")
DO NOTHING;

BTree优化

PostgreSQL 13: Btree索引优化(引入Deduplication技术)

--表，3亿数据，28GB

--pg12和pg13的发行说明中提到对btree的空间和性能进行了优化，对重复的索引只存储一次

--三列组合索引，每条索引唯一不重复
--local postgresql13 索引大小11GB
--local postgresql11 索引大小11GB
CREATE INDEX idx_hkccass_date_parti_code ON hk_ccassholdrecdet1 USING btree("InnerCode","PartiNumber","HoldingDate")
SELECT pg_size_pretty ( pg_relation_size ( 'idx_hkccass_date_parti_code' ) ) AS SIZE;--11GB

--两列组合索引，索引存在大量重复
--local postgresql13 索引大小2105MB
--local postgresql11 索引大小9033MB
CREATE INDEX idx_hkccassholdrecdet_code_parti ON hk_ccassholdrecdet1 USING btree("InnerCode","PartiNumber");
SELECT pg_size_pretty ( pg_relation_size ( 'idx_hkccassholdrecdet_code_parti' ) ) AS SIZE;--2105MB

--单列组合索引，索引存在大量重复
--local postgresql13 索引大小1987MB
--local postgresql11 索引大小6433MB
CREATE INDEX idx_hkccass_code ON hk_ccassholdrecdet1 USING btree("InnerCode");
SELECT pg_size_pretty ( pg_relation_size ( 'idx_hkccass_code' ) ) AS SIZE;

索引

--查看SQL语句查询计划
explain (analyze,verbose,buffers,costs,timing) 

--抛弃所有会话状态
DISCARD ALL;

-------踩坑---------
--临时空间不足，使用命令修改，单位kb
ALTER ROLE ALL SET temp_file_limit = 1000000000;

--查询所有表大小
SELECT relname,pg_size_pretty ( pg_relation_size ( relid ) ) AS SIZE FROM pg_stat_user_tables;
--查询所有表大小（包括索引）
SELECT relname,pg_size_pretty ( pg_total_relation_size ( relid ) ) AS SIZE FROM pg_stat_user_tables;

-------布隆索引，支持类型：int(4)，text，更适合多条件查询和等值查询---------
--下载扩展插件
CREATE extension bloom;
--创建布隆索引
CREATE INDEX idx_hkccass_code_parti ON hk_ccassholdrecdet USING bloom ("innercode","partinumber");
--查看索引大小
SELECT pg_size_pretty ( pg_relation_size ( 'idx_hkccass_code_parti' ) ) AS SIZE;

-------btree索引，通用---------
CREATE INDEX idx_hkccass_date_code ON hk_ccassholdrecdet USING btree("holdingdate","innercode");

-------brin索引，块级索引---------
CREATE INDEX idx_hkccass_date ON hk_ccassholdrecdet USING brin("holdingdate");


-------唯一索引-----------
CREATE UNIQUE INDEX uk_hkccass_code_parti_date ON hk_ccassholdrecdet USING btree("innercode","partinumber","holdingdate");

-------聚簇索引需要定期手动一次性的聚簇，顺序-------
CREATE INDEX idx_hkccass_cluster_date ON hk_ccassholdrecdet;
CLUSTER hk_ccassholdrecdet USING idx_hkccass_cluster_date;

表解锁

--查询正在运行的进程
SELECT * FROM pg_stat_activity 
--查询是否锁表了
select oid from pg_class where relname='可能锁表了的表'
select pid from pg_locks where relation='上面查出的oid'
--如果查询到了结果，表示该表被锁
--杀掉pid进程
select pg_terminate_backend('pid')