PostgreSQL介绍

PostgreSQL 是一个功能强大的开源关系型数据库系统，它使用并扩展了 SQL 语言，并结合了许多功能，可以安全地存储和扩展复杂的数据工作

PostgreSQL 因其经过验证的架构、可靠性、数据完整性、强大的功能集、可扩展性以及软件背后的开源社区始终如一地提供高性能和创新解决方案的奉献精神而赢得了良好的声誉。PostgreSQL 可在所有主要操作系统上运行，自 2001 年以来一直符合ACID，并且具有强大的附加组件，例如流行的PostGIS地理空间数据库扩展器。

为什么使用 PostgreSQL？

PostgreSQL 附带了许多功能，旨在帮助开发人员构建应用程序、管理员保护数据完整性和构建容错环境，并帮助您管理数据，无论数据集有多大或多小。除了免费和开源之外，PostgreSQL 还具有高度可扩展性。例如，可以定义自己的数据类型、构建自定义函数，甚至可以用不同的编程语言编写代码，而无需重新编译数据库！

PostgreSQL社区是纯社区，没有被商业公司控制，让很多用户云厂商愿意贡献核心代码，让PostgreSQL得到了快速的版本迭代，丰富的应用插件。

为什么要学习PostgreSQL？

中国目前在全面实行国产化替代项目，从军工、政府、金融、医疗、教育到企业逐步替换掉非国产化的东西，比如说计算机服务器，军工，软件等，软件中数据库是重要的一环。国内很多数据软件，以下列举几个常用的国产重构的关系型集中式架构数据库。

国外数据库使用情况 来源：https://db-engines.com/en/ranking/relational+dbms

国内数据库使用情况 来源：https://www.modb.pro/dbRank

可以看到排行前几的底层技术基本使用PostgreSQL

为什么要国产数据库大多数底层都基于PostgreSQL而非MYSQL？

主要是PostgreSQL与MySQL的版权区别

PostgreSQL许可

PostgreSQL许可是一种自由的开源许可，BSD开源协议是一个给于使用者很大自由的协议。可以自由的使用，修改源代码，也可以将修改后的代码作为开源或者专有软件再发布，被誉为开源许可的“活雷锋”。

BSD 代码鼓励代码共享，但需要尊重代码作者的著作权。BSD由于允许使用者修改和重新发布代码，也允许使用或在BSD代码上开发商业软件发布和销售，因此是对商业集成很友好的协议。所以很多公司企业在选开源产品的时都首选BSD协议，因为可以完全控制这些第三方的代码，在必要的时候可以修改或者二次开发。

PostgreSQL许可描述：https://www.postgresql.org/about/licence/

MySQL许可

众所周知，MySQL被Oracle所控制，MySQL同时使用了GPL和一种双重许可

GPL(General Public license)是公共许可，遵循了GPL的软件是公共的。如果某软件使用了GPL软件，那么该软件也需要开源，如果不开源，就不能使用GPL软件，这和是否把该软件商用与否是没关系的。具体约束：

• 不允许对在MySQL上作出的修改申请专利；
• MySQL上的修改需要公开，且所有权归Oracle所有；
• 出于纯学术目的、练习目的源码修改也是符合GPL的；
• Oracle的MySQL企业版或高级功能会涉及费用，并且Oracle公司不允许其它基于MySQL的闭源产品。

可以理解为：商业许可是控制MySQL的公司留给自己的特权。

与mysql区别

PostgreSQL的优势

1. 在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨。
2. 对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强。
3. PostgreSQL主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
4. PostgreSQL的主从复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能影响更小
5. PostgreSQL支持JSON，XML和其他NoSQL功能，还支持索引JSON数据以加快访问速度
6. PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，相反MySQL现在是被Oracle公司控制

MySQL的优势

1. innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触发VACUUM,会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀。
2. MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束。
3. MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作。
4. MySQL相对于PostgreSQL在国内的流行度更高，PostgreSQL在国内显得就有些落寞了。
5. MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。

从应用场景来说，PG更加适合严格的企业应用场景( 比如金融、电信、ERP、CRM )，但不仅仅限制于此，PostgreSQL的json,jsonb,hstore等数据格式，特别适用于一些大数据格式的分析；

而MySQL更加适合业务逻辑相对简单、数据可靠性要求较低的互联网场景(比如google、facebook、alibaba)，当然现在MySQL的在innodb引擎的大力发展，功能表现良好。

对比维度	MySQL	PostgreSQL
基础架构	多线程架构（共享进程资源），适合高并发简单查询	多进程架构（独立进程处理连接），资源隔离性更好
SQL标准支持	支持SQL:2003核心规范，对窗口函数等高级特性支持较晚（5.8+版本）	严格遵循SQL:2016标准，支持窗口函数、CTE、递归查询等复杂语法
事务与并发控制	InnoDB支持MVCC，默认隔离级别为可重复读（REPEATABLE READ）	全功能MVCC，支持可序列化（SERIALIZABLE）隔离级别，无锁读设计更优
存储引擎	多引擎可选（InnoDB/MyISAM/Memory），MyISAM适合读密集型场景	单一存储引擎但支持自定义索引（GIN/GiST/BRIN等）和扩展（如PostGIS）
数据类型	基础类型（INT/VARCHAR/TEXT），5.7+支持JSON	50+种内置类型（数组/JSONB/范围/几何类型），支持自定义类型
索引能力	B+树为主，支持全文索引	支持B-Tree/GiST/GIN/SP-GiST/BRIN/Bloom等多种索引类型
扩展性	插件式存储引擎扩展，用户自定义功能有限	支持自定义函数/运算符/聚合函数，可集成TimescaleDB、Citus等扩展
高可用方案	InnoDB Cluster + MySQL Router，主从复制延迟较低	流复制 + Patroni集群，支持同步/异步/级联复制
性能特性	简单查询响应快（主键查询0.0001秒级），高并发写入吞吐量高（TPC-C 1000并发达96k tpmC）	复杂查询优化强（窗口函数+并行查询），高并发下延迟更低（TPC-C 1000并发延迟89ms vs MySQL 132ms）
GIS支持	需依赖第三方工具（如MySQL Spatial）	内置PostGIS扩展，支持空间数据索引和GIS函数
全文搜索	基于N-gram分词，支持简单全文检索	支持多语言分词（TSVector/TSQuery），加权搜索和短语匹配更优
开发友好度	语法宽松（如允许错误日期插入），适合快速迭代	语法严格（强制日期合法性检查），适合企业级严谨开发
许可证	GPL协议，商业用途需购买授权	BSD协议，允许闭源和商业二次开发
典型适用场景	电商秒杀、社交应用、中小型CMS	金融系统、地理信息、数据分析、ERP/CRM

PostgreSQL下载安装

官网地址：https://www.postgresql.org/download/

直接选择对应的版本就行 https://ftp.postgresql.org/pub/source/v16.6/

数据库基础操作

1. 创建数据库和用户

使用createdb命令可以创建新的数据库，而createuser命令则用于创建新的数据库用户。在创建数据库和用户之前，确保你已经安装了PostgreSQL并正确配置了系统环境。

# 创建数据库
createdb mydatabase# 创建用户
createuser myuser --pwprompt    # pwprompt选项设置密码

完整

CREATE DATABASE name[ WITH ] [ OWNER [=] user_name ][ TEMPLATE [=] template ][ ENCODING [=] encoding ][ STRATEGY [=] strategy ] ][ LOCALE [=] locale ][ LC_COLLATE [=] lc_collate ][ LC_CTYPE [=] lc_ctype ][ ICU_LOCALE [=] icu_locale ][ ICU_RULES [=] icu_rules ][ LOCALE_PROVIDER [=] locale_provider ][ COLLATION_VERSION = collation_version ][ TABLESPACE [=] tablespace_name ][ ALLOW_CONNECTIONS [=] allowconn ][ CONNECTION LIMIT [=] connlimit ][ IS_TEMPLATE [=] istemplate ][ OID [=] oid ]

2. 连接数据库

使用psql命令行工具可以连接到PostgreSQL数据库。需要提供数据库名称、用户名和密码作为连接参数。

# 连接到数据库
psql -U myuser -d mydatabase

3. 数据类型与表操作

PostgreSQL支持丰富的数据类型，包括数值、字符、日期/时间、二进制数据等。你可以使用CREATE TABLE语句来创建表，并指定列的数据类型。同时，还可以使用INSERT、UPDATE和DELETE语句来操作表中的数据。

-- 创建表
CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,age INTEGER,hire_date DATE
);-- 插入数据
INSERT INTO employees (name, age, hire_date) VALUES ('Alice', 30, '2025-01-01');-- 更新数据
UPDATE employees SET age = 31 WHERE name = 'Alice';-- 删除数据
DELETE FROM employees WHERE id = 1;

4. 查询数据

使用SELECT语句可以从表中检索数据。可以使用各种条件、排序和聚合函数来定制查询结果。

-- 查询所有数据
SELECT * FROM employees;-- 查询特定列的数据
SELECT name, age FROM employees;-- 带条件的查询
SELECT * FROM employees WHERE age > 30;-- 排序查询结果
SELECT * FROM employees ORDER BY age DESC;-- 使用聚合函数
SELECT COUNT(*) FROM employees;

这些查询语句将分别返回employees表中的所有数据、特定列的数据、满足年龄大于30岁的员工数据、按年龄降序排列的员工数据，以及员工的总数。

5. 索引与性能优化

为了提高查询性能，可以为表的某些列创建索引。加速数据的检索速度，但也会占用额外的磁盘空间并可能增加插入、更新和删除操作的开销。

-- 创建索引
CREATE INDEX idx_employees_name ON employees(name);-- 删除索引
DROP INDEX idx_employees_name;

6. 备份与恢复

为了数据安全，定期备份数据库是非常重要的。PostgreSQL提供了多种备份和恢复工具，如pg_dump和pg_restore。

# 备份数据库
pg_dump -U myuser -d mydatabase > backup.sql# 恢复数据库
pg_restore -U myuser -d mydatabase < backup.sql

7.扩展操作

在PGSQL中，写SQL语句时，单引号用来标识实际的值。双引号用来标识一个关键字，比如表名，字段名。

-- 单引号写具体的值，双引号类似MySQL的``标记，用来填充关键字
-- 下面的葡萄牙会报错，因为葡萄牙不是关键字
select 1.414,'卡塔尔',"葡萄牙";

数据类型转换

-- 第一种方式：只需要在值的前面，添加上具体的数据类型即可
-- 将字符串转成位图类型
select bit '010101010101001';-- 第二种方式：也可以在具体值的后面，添加上 ::类型 ，来指定
-- 数据类型
select '2011-11-11'::date;
select '101010101001'::bit(20);
select '13'::int;-- 第三种方式：使用CAST函数
-- 类型转换的完整写法
select CAST(varchar '100' as int);

九大类型

布尔类型

布尔类型简单的丫批，可以存储三个值，true，false，null

-- 布尔类型的约束没有那么强，true，false大小写随意，他会给你转，同时yes，no这种他也认识，但是需要转换
select true,false,'yes'::boolean,boolean 'no',True,FaLse,NULL::boolean;

数值类型

整型

整型比较简单，主要就是三个：

• smallint、int2：2字节
• integer、int、int4：4字节
• bigint、int8：8字节

正常没啥事就integer，如果要存主键，比如雪花算法，那就bigint。空间要节约，根据情况smallint

浮点型

浮点类型就关注2个（其实是一个）

• decimal(n,m)：本质就是numeric，PGSQL会帮你转换
• numeric(n,m)：PGSQL本质的浮点类型

针对浮点类型的数据，就使用 numeric

序列

序列大多数的应用，是用作表的主键自增效果。默认情况下，seqeunce的起始值是0，每次nextval递增1，最大值9223372036854775807

MySQL中的主键自增，是基于auto_increment去实现。MySQL里没有序列的对象。

PGSQL和Oracle十分相似，支持序列：sequence。

PGSQL可没有auto_increment。序列的正常构建方式：

create sequence laozheng.table_id_seq;
-- 查询下一个值
select nextval('laozheng.table_id_seq');
-- 查询当前值
select currval('laozheng.table_id_seq');

数值的常见操作

针对数值咱们可以实现加减乘除取余这5个操作，另外还有

字符串类型

字符串类型用的是最多的一种，在PGSQL里，主要支持三种：

• character（就是MySQL的char类型），定长字符串。（最大可以存储1G）
• character varying（varchar），可变长度的字符串。（最大可以存储1G）
• text（跟MySQL异常）长度特别长的字符串。

操作没什么说的，但是字符串常见的函数特别多。

字符串的拼接一要要使用||来拼接。其他的函数，可以查看 http://www.postgres.cn/docs/12/functions-string.html

日期类型

在PGSQL中，核心的时间类型，就三个。

• timestamp（时间戳，覆盖 年月日时分秒）

• date（年月日）

• time（时分秒）

在PGSQL中，声明时间的方式：只需要使用字符串正常的编写 yyyy-MM-dd HH:mm:ss就可以转换为时间类型。直接在字符串位置使用之前讲到的数据类型转换就可以了。

当前系统时间 ：

• 可以使用now作为当前系统时间（没有时区的概念）

  -- 直接查询now，没有时区的概念
  select timestamp 'now';
  -- 也可以使用current_timestamp的方式获取（推荐，默认东八区）
  select time with time zone 'now' at time zone '08:00:00'
  

日期类型的运算

• 正常对date类型做+，-操作，默认单位就是天~

-- date + time = timestamp~~~
select date '2011-11-11' + time '12:12:12' ;-- 可以针对timestamp使用interval的方式进行 +，-操作，在查询以时间范围为条件的内容时，可以使用
select timestamp '2011-11-11 12:12:12' + interval '1day' + interval '1minute' + interval '1month';

枚举类型

枚举类型MySQL也支持，只是没怎么用，PGSQL同样支持这种数据类型，可以声明枚举类型作为表中的字段类型，这样可以无形的给表字段追加诡异的规范。

-- 声明一个星期的枚举，值自然只有周一~周日。
create type week as enum ('Mon','Tues','Sun');
-- 声明一张表，表中的某个字段的类型是上面声明的枚举。
drop table test;
create table test(id bigserial ,weekday week
);
insert into test (weekday) values ('Mon');
insert into test (weekday) values ('Fri');

IP类型

PGSQL支持IP类型的存储，支持IPv4，IPv6这种，甚至Mac内种诡异类型也支持，这种IP类型，可以在存储IP时，帮助做校验，其次也可以针对IP做范围查找。

JSON&JSONB类型

JSON在MySQL8.x中也做了支持，但是MySQL支持的不好，因为JSON类型做查询时，基本无法给JSON字段做索引。

PGSQL支持JSON类型以及JSONB类型。JSON和JSONB的使用基本没区别。

本质上JSON格式就是一个字符串，比如MySQL5.7不支持JSON的情况的下，使用text也可以，但是字符串类型无法校验JSON的格式，其次单独的字符串没有办法只获取JSON中某个key对应的value。

JSON和JSONB的区别：

• JSON类型无法构建索引，JSONB类型可以创建索引。
• JSON类型的数据中多余的空格会被存储下来。JSONB会自动取消多余的空格。
• JSON类型甚至可以存储重复的key，以最后一个为准。JSONB不会保留多余的重复key（保留最后一个）。
• JSON会保留存储时key的顺序，JSONB不会保留原有顺序。

JSON中key对应的value的数据类型

[{"name": "张三"},{"name": {"info": "xxx"}}
]

操作JSON

select '9'::JSON,'null'::JSON,'"laozheng"'::JSON,'true'::json;
select '9'::JSONB,'null'::JSONB,'"laozheng"'::JSONB,'true'::JSONB;-- JSON数组
select '[9,true,null,"我是字符串"]'::JSON;-- JSON对象
select '{"name": "张三","age": 23,"birthday": "2011-11-11","gender": null}'::json;
select '{"name": "张三","age": 23,"birthday": "2011-11-11","gender": null}'::jsonb;-- 构建表存储JSON
create table test(id bigserial,info json,infob jsonb
);
insert into test(info,infob) values ('{"name":"张三","age": 23,"birthday": "2011-11-11","gender": null}','{"name":"张三","age": 23,"birthday": "2011-11-11","gender": null}')
select * from test;create index json_index on test(info);
create index jsonb_index on test(infob);

JSON还支持很多函数。可以直接查看 http://www.postgres.cn/docs/12/functions-json.html 函数太多了，不分析了。

复合类型

复合类型就像Java中的一个对象，Java中有一个User，User和表做了一个映射，User中有个人信息对象。可以基于符合类型对映射上个人信息

public class User{private Integer id;private Info info;
}class Info{private String name;private Integer age;
}

按照上面的情况，将Info构建成一个复合类型

-- 构建复合类型，映射上Info
create type info_type as (name varchar(32),age int);
-- 构建表，映射User
create table tb_user(id serial,info info_type
);
-- 添加数据
insert into tb_user (info) values (('张三',23));
insert into tb_user (info) values (('露丝',233));
insert into tb_user (info) values (('jack',33));
insert into tb_user (info) values (('李四',24));
select * from tb_user;

数组类型

数组还是要依赖其他类型，比如在设置住址，住址可能有多个住址，可以采用数组类型去修饰字符串。

PGSQL中，指定数组的方式就是[]，可以指定一维数组，也支持二维甚至更多维数组。

构建数组的方式：

drop table test;
create table test(id serial,col1 int[],col2 int[2],col3 int[][]
);
-- 构建表指定数组长度后，并不是说数组内容只有2的长度，可以插入更多数据
-- 甚至在你插入数据，如果将二维数组结构的数组扔到一维数组上，也可以存储。
-- 数组编写方式
select '{{how,are},{are,you}}'::varchar[];
select array[[1,2],[3,4]];
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}','{7,8,9}');
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}',array[[1,2],[3,4]]);
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}','{{1,2},{3,4}}');
select * from test;

如果现在要存储字符串数组，如果存储的数组中有双引号怎么办，有大括号怎么办。

-- 如果存储的数组中的值，有单引号怎么办？
-- 使用两个单引号，作为一个单引号使用
select '{''how''}'::varchar[];
-- 如果存储的数组中的值，有逗号怎么办？(PGSQL中的数组索引从1开始算，写0也是从1开始算。)
-- 用双引号将数组的数据包起来~
select ('{"how,are"}'::varchar[])[2];
-- 如果存储的数组中的值，有双引号怎么办？
-- 如果要添加双引号，记得转义。
select ('{"\"how\",are"}'::varchar[])[1];

数组的比较方式

-- 包含
select array[1,2] @> array[1];
-- 被包含
select array[1,2] <@ array[1,2,4];
-- 是否有相同元素
select array[2,4,4,45,1] && array[1];

【总结】

1. 数字类型

   • smallint: ±32767，小整数，int2。适用于范围有限的整数场景。
   • integer: ±2147483647，整数，int4。适用于大多数整数场景。
   • bigint: ±9223372036854775807，大整数，int8。适用于需要更大范围的整数场景。

2. 字符串型

   • char(n): 固定长度字符，如国家代码。注意：存储过短或过长的字符串可能导致不必要的空间浪费或截断错误。
   • varchar(n): 可变长度文本，如姓名或地址。推荐为多语言或特殊字符需求指定UTF-8字符集和编码。
   • text: 较长文本，禁止用于存储附件。注意：在某些查询中性能可能不如varchar。

3. 日期/时间类型

   • date: 日期。适用于只需要日期不需要时间的场景。
   • datetime: 日期时间。适用于需要日期和时间的场景，精度到秒。
   • time: 时间（精确到1微秒）。
   • time(0): 时间（精确到1秒）。
   • timestamp: 时间戳（精确到1微秒）。适用于需要时间戳的场景。
   • timestamp(0): 时间戳（精确到1秒）。
   • timestamptz: 带时区的日期和时间。注意：时区处理需要根据实际应用范围考虑。

4. 货币类型

   • decimal(p, s): 精确财务场景，如decimal(15, 6)，精度s。推荐用于需要高度精确的财务计算。
   • money: 固定两位小数精度货币金额，±2233720368547758.07。注意：适用于不需要自定义精度的货币存储。

5. 逻辑类型

   • boolean: 状态/开关/条件判断，如true、false、unknown。

6. JSON类型

   • json: 用于存储JSON对象或数组，文本格式。注意：可能导致存储膨胀和查询复杂性增加。
   • jsonb: 用于检索，支持索引和高效查询，二进制格式。注意：虽然查询性能高，但也同样面临存储膨胀的风险。

7. 其他类型

   • inet: IPv4或IPv6地址。
   • cidr: 网络地址。
   • macaddr: MAC地址。
   • set: 多选字符集集合。
   • xml: 格式化XML数据，最大2G。注意：存储大量XML数据可能影响性能。
   • table: 结果集。
   • bytea: 用于存储大型二进制对象，如图形。注意：应考虑存储效率和访问速度。
   • real: 单精度浮点数。
   • double precision: 双精度浮点数。
   • enum: 枚举类型，增强安全性及明确语义。注意：变更枚举类型可能需要修改表结构，谨慎操作。

高级特性

索引进阶

-- 创建索引
CREATE INDEX index_name ON table_name (column_name);-- 删除索引
DROP INDEX index_name;-- 查看索引
SELECT indexname, indexdef FROM pg_indexes WHERE tablename='table_name';-- 重建索引
REINDEX INDEX index_name;

PostgreSQL 支持多种类型的索引，如 B-tree、Hash索引等。默认的 B-tree 索引适用于大多数情况，有些特殊应用场景可能需要其他类型的索引

CREATE INDEX index_name ON table_name USING gin (column_name);-- 复合索引 多个列创建
CREATE INDEX index_name ON table_name (column1, column2);-- 部分索引 基于条件创建索引
CREATE INDEX index_name ON table_name (column_name) WHERE condition;-- 并行索引
CREATE INDEX index_name ON table_name USING method (column_name) WITH (parallel_workers = 4);

模式（Schema）

模式是PostgreSQL中用于组织数据库对象（如表、视图、索引等）的逻辑容器。一个数据库可以包含多个模式，每个模式可以包含多个对象。模式的主要作用包括：

• 命名空间管理：避免对象名称冲突，例如在不同模式中可以存在同名的表。
• 权限控制：可以为模式设置独立的权限，控制用户对模式内对象的访问。
• 逻辑分组：将相关的对象组织在一起，便于管理和维护。

创建和使用模式的示例：

CREATE SCHEMA my_schema;
CREATE TABLE my_schema.my_table (id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL
);

在查询时，可以通过模式名限定对象：

SELECT * FROM my_schema.my_table;

表分区

什么是分区表，何时需要分区？

表分区（Partitioning）指的是将大表拆分成多个小的物理分块，每个小表称为一个“分区”。减少数据库对表的查询时间及物理资源消耗

当表的大小应超过数据库服务器的物理内存时，就需要考虑将表进行分区。

优点

• 查询性能高

  • 频繁查询的数据将会放在单个分区或者在少量的分区中。
  • 当表分区后，频繁使用的索引将会存放在内存中。
  • 当查询或更新单个分区的大量数据时，将会使用分区的顺序扫描而不是使用索引，从而提高性能

• 可以通过添加或移除分区来完成批量加载和删除。

• 减少批量 DELETE ，避免 VACUUM。

• 通过使用DROP TABLE或ALTER TABLE DETACH PARTITION删除单个分区比批量操作要快得多。

• 查询脚本可保持不变，对业务开发来说是透明的。

• 具有更高的可用性，比如一个分区的的数据页面损坏，不会影响其它分区。

分区类型

PostgreSQL内置支持以下几种分区方法：

声明式分区策略	常用场景
范围分区	适合时间序列数据等有顺序特征的数据。
列表分区	适用于有离散值的数据，如分类或状态。
哈希分区	适合均匀分布数据。
复合分区	结合两种或多种分区方式。可以先按范围分区，再在每个范围内按列表或哈希分区。

💡注意：选择分区方式时，要考虑数据是否有明显的范围、离散值或均匀性；常用的查询条件是什么，能否利用分区来提高性能；不同的分区策略在维护和管理上复杂度不同。综合以上来选择最适合的分区方式。

下面将演示如何创建与使用范围分区、列表分区、哈希分区。前提已安装好 postgresql ，并服务能正常运行。如未安装 postgresql 数据库可参考以下文章：

https://www.modb.pro/db/1846154339788881920

范围分区

💡范围分区基于某个键列或列集合，将表数据划分为不重叠的范围。例如，按日期范围划分，或按业务对象的ID范围划分。范围分区的边界是下限包含、上限排除的方式。例如，一个分区的范围为1至10，下一个分区为10至20，那么值10归属于第二个分区。

CREATE TABLE sales (sale_id serial,sale_date date,amount numeric
) PARTITION BY RANGE (sale_date);CREATE TABLE sales_2022 PARTITION OF salesFOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

列表分区

列表分区通过明确指定每个分区中包含的键值进行划分，适合数据量不大且需要区分度高的情况，例如按地域或类别分区。

CREATE TABLE employees (emp_id serial,emp_country text
) PARTITION BY LIST (emp_country);CREATE TABLE employees_usa PARTITION OF employeesFOR VALUES IN ('USA');

哈希分区

哈希分区通过给定模数（modulus）和余数（remainder）划分表数据。PostgreSQL会根据分区键的哈希值对模数取余，将数据分配到相应的分区，适合负载均衡的场景。

CREATE TABLE logs (log_id serial,log_date date
) PARTITION BY HASH (log_id);CREATE TABLE logs_part_1 PARTITION OF logsFOR VALUES WITH (MODULUS 4, REMAINDER 0);

分区表总结

• 选择合适的分区策略：根据具体的业务场景和条件查询频率，选择合适的分区方式和分区键。

• 分区数控制：分区数量并不是越多越好，通过实际业务查询测试来确定分区的合理数量。

• 索引优化

  • 在分区键上创建索引，确保查询可以快速定位到具体的分区。
  • 创建局部索引。

• 避免跨分区查询。

• 自动化分区维护：使用触发器（Trigger）或第三方工具（如 pg_partman）来自动创建新的分区表。

• 定期运行 VACUUM 清理死锁数据。

• 对特定分区进行 ANALYZE，提高查询计划的准确性。

• 使用并行查询：设置 max_parallel_workers_per_gather 等参数。

分区选择与设计原则

选择分区方法时应考虑以下因素：

• 数据访问模式：例如，如果数据随时间累积，则范围分区是一个适合的选择。
• 查询性能：如果应用程序主要查询少数特定值，列表分区可以显著提高性能。
• 负载均衡需求：若需均衡数据分布，避免单一分区的数据量过大，可以选择哈希分区。

若内置分区无法满足需求，可考虑使用继承（Inheritance）和UNION ALL视图来手动实现分区。虽然此方法灵活，但会失去部分性能优势。

总结

PostgreSQL表分区为数据库优化带来了极大的灵活性。通过合理选择分区类型，您可以有效提升查询性能并简化数据管理流程。掌握分区的原理和使用场景后，在设计大数据表时能够充分利用分区的优势，实现数据库性能的最大化。