快速上手

苓渊大约 3 分钟

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

--为表添加注释
comment on table user is '用户表';
--为字段注释
comment on column user.userid is '用户唯一标识';

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

PostgreSQL 数据类型

索引

--查看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;

INSERT ON conflict DO

类似 MySQL 中的 replace into，在插入数据时如果数据已存在，则不插入数据且不报错。

PgSQL 中没有 replace into，而是用 INSERT ON conflict 实现相似功能。

-- 使用的如下命令，根据三列的唯一索引判断是否有相同数据，自定义操作方式。
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;
-- NOTHING 时什么都不做，也可以做些什么，用关键字 EXCLUDED. 获取本次要插入的值
-- DO UPDATE SET money = EXCLUDED.money;

BTree优化

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

-- 表，3亿数据，28GB

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

-- 三列组合索引，每条索引唯一不重复
-- postgresql13 索引大小11GB
-- 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

-- 两列组合索引，索引存在大量重复
-- postgresql13 索引大小2105MB
-- 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

-- 单列组合索引，索引存在大量重复
-- postgresql13 索引大小1987MB
-- 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;

表解锁

-- 查询正在运行的进程
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')

常见问题

PostgreSQL的SQL语句参数上限 An I/O error occurred while sending to the backend

PostgreSQL 连接失败问题

快速上手

下载安装

规范

实用场景

数据表操作

索引

INSERT ON conflict DO

BTree优化

表解锁

常见问题