postgresql数据库创建表分区和分区分区查询失效问题

一、前言

在postgresql数据库单库单表数据量比较大的场景，会导致查询性能比较慢，所有这种场景单库单表的优化，就是创建分库分表，这种修改比较简单，不涉及业务层的变更，又能提高性能。

二、分区表创建

表分区:
表分区是指在逻辑上将一个大表拆分为较小的物理部分。分区可以带来几个好处：

• 在某些情况下，查询性能可以显著提高，尤其是当表的大多数大量访问的行都放在单个分区或少量分区中时。分区取代了索引的前导列，减小了索引大小，使索引中大量使用的部分更可能适合内存。
• 当查询或更新访问单个分区的很大一部分时，可以通过利用该分区的顺序扫描来提高性能，而不是使用分散在整个表中的索引和随机访问读取。
• 如果分区设计中计划了分区，则可以通过添加或删除分区来完成批量加载和删除。使用执行或删除单个分区比批量操作快得多。
• 很少使用的数据可以迁移到更便宜、更慢的存储介质。
• 只有当一个表会很大时，这些好处通常才是值得的。表将从分区中受益的确切点取决于应用程序，尽管经验法则是表的大小应超过数据库服务器的物理内存。

PostgreSQL从10版本支持通过表继承来实现表的分区。父表是普通表并且正常情况下并不存储任何数据，它的存在只是为了代表整个数据集。
从11版本开始PostgreSQL可实现如下3种表分区:

• 范围分区　每个分区表包含一个或多个字段组合的一部分，并且每个分区表的范围互不重叠。比如可近日期范围分区
• 列表分区　分区表显示列出其所包含的列值
• 哈希分区
  PostgreSQL11版本引入，可以根据自定义的hash规则，通过为每个分区指定模数和余数来对表进行分区。每个分区将保存分区键的哈希值除以指定的模数将生成指定余数的行。

我们主要通过这三种方式实现分区分表：

1、范围分区（Range Partitioning）

根据表中某一列的值范围将表分割成若干个分区。例如，可以按照时间字段（如日期）创建按年、月、日的范围分区。

CREATE TABLE person_record(
   id int4,
	 dev_code VARCHAR,
	 pass_time TIMESTAMP,
	 person_id VARCHAR,
	 remark TEXT
)PARTITION BY RANGE(pass_time);

CREATE TABLE person_record_time_20240831 PARTITION OF person_record FOR VALUES FROM ('2024-08-31 00:00:00') TO ('2024-08-31 23:59:59');
CREATE TABLE person_record_time_20240901 PARTITION OF person_record FOR VALUES FROM ('2024-09-01 00:00:00') TO ('2024-09-01 23:59:59');

查询结果：
分区查询：

查询所有：

可以使用系统调度器，如 Crontab (Linux, Unix, etc.) 和 Task Scheduler (Windows)周期性执行创建分表的sql语句；

2、列表分区（List Partitioning）

在列表分区中，数据根据已指定的离散值进行分区。当需要对地区、部门等离散数据进行任意值分组时，这种方法效果很好。例如职位名称、按地区划分等。
我们以区域地点作为列分区：

CREATE TABLE person_record_list(
   id int4,
	 dev_code VARCHAR,
	 pass_time TIMESTAMP,
	 region VARCHAR,
	 person_id VARCHAR,
	 remark TEXT
)PARTITION BY list(region);

CREATE TABLE shandong PARTITION OF person_record_list FOR VALUES IN ('shandong');
CREATE TABLE beijing PARTITION OF person_record_list FOR VALUES IN ('beijing');

执行结果：

分区查询：

查询所有：

3、hash分区（hash Partitioning）

基于哈希hash的分区表通常用于将数据均匀地分布到多个分区中，以便实现更好的负载均衡和查询性能。

CREATE TABLE person_record_hash(
   id int4,
	 dev_code VARCHAR,
	 pass_time TIMESTAMP,
	 type_key int4,
	 person_id VARCHAR,
	 remark TEXT
)PARTITION BY HASH(type_key);

CREATE TABLE table_1 PARTITION OF person_record_hash FOR VALUES WITH (MODULUS 2,REMAINDER 0);
CREATE TABLE table_2 PARTITION OF person_record_hash FOR VALUES WITH (MODULUS 2,REMAINDER 1);

分区查询：

查询全部：

三、表分区查询失效问题

时间类型不匹配导致的分表查询失效

失效查询实例：

EXPLAIN SELECT
	* 
FROM
	person_record_time 
WHERE
	1 = 1 
	AND pass_time BETWEEN CEILING ( EXTRACT ( epoch FROM CURRENT_TIMESTAMP - INTERVAL '1day 5 hours' ) * 1000 ) 
	AND CEILING ( EXTRACT ( epoch FROM CURRENT_TIMESTAMP ) * 1000 )

执行结果：

解决方式：
把时间类型强转成bigint类型，如下：

EXPLAIN SELECT
	* 
FROM
	person_record_time 
WHERE
	1 = 1 
	AND pass_time BETWEEN CEILING ( EXTRACT ( epoch FROM CURRENT_TIMESTAMP - INTERVAL '1day 5 hours' ) * 1000 ) ::BIGINT
	AND CEILING ( EXTRACT ( epoch FROM CURRENT_TIMESTAMP ) * 1000 )::BIGINT

执行结果：