跳到主要内容
PostgreSQL 哈希与复合分区实践 | 极客日志
SQL java
PostgreSQL 哈希与复合分区实践 探讨 PostgreSQL 哈希分区和复合分区在订单表上的实际应用。哈希分区能将数据均匀散落,适合作高并行写入与等值查询,但范围查询效率低。复合分区结合时间范围和用户哈希,可同时利用两种剪枝,适合多维查询场景。文章给出 SQL 建表语句、Java 分区管理工具类及日常优化陷阱,强调要根据查询模式设计分区键顺序,并控制分区总数在合理范围内。
接口猎人 发布于 2026/6/27 更新于 2026/7/3 2 浏览PostgreSQL 哈希与复合分区实践
在处理单表数亿行的订单数据时,简单的范围分区已经不够用——写入热点、用户维度的查询效率低下这些问题会慢慢浮现。我曾在电商系统里试过哈希和复合分区,这里记录一些关键做法,希望能帮你少踩坑。
哈希分区
哈希分区不关心数据分布怎么样,直接把行均匀打散到 N 个分区里。PostgreSQL 的做法是对分区键调用内部哈希函数,然后用结果模分区数决定落在哪个子表。
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY ,
customer_id INT NOT NULL ,
order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
total_amount DECIMAL (12 ,2 ) NOT NULL ,
status VARCHAR (20 ) NOT NULL
) PARTITION BY HASH (customer_id);
CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 4 , REMAINDER 0 );
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 4 , REMAINDER 1 );
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (MODULUS 4 , REMAINDER 2 );
CREATE TABLE orders_p3 orders (MODULUS , REMAINDER );
PARTITION
OF
FOR
VALUES
WITH
4
3
优点很明确:写入负载自动均衡,等值查询能直接定位到唯一分区,不会像范围分区那样可能出现某些时间段的数据挤在一起。但缺点也摆在那儿:范围查询(例如查某个月的订单)需要扫所有分区,分区剪枝使不上劲;而且分区数量一开始就得定好,后面很难动态调整。
复合分区 当查询既有时间范围又有用户 ID 时,单靠一种分区就不够用了。复合分区把两种策略叠加起来,比如先用范围按年月拆开,再在每个时间分区内做哈希。这样同一个月的用户数据会按哈希散开,而时间过滤又能去掉一大部分分区。
CREATE TABLE orders_composite (
id BIGSERIAL,
customer_id INT NOT NULL ,
order_date DATE NOT NULL ,
total_amount DECIMAL (12 ,2 ) NOT NULL ,
status VARCHAR (20 ) NOT NULL
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2023 PARTITION OF orders_composite
FOR VALUES FROM ('2023-01-01' ) TO ('2024-01-01' ) PARTITION BY HASH (customer_id);
CREATE TABLE orders_2023_p0 PARTITION OF orders_2023 FOR VALUES WITH (MODULUS 4 , REMAINDER 0 );
CREATE TABLE orders_2023_p1 PARTITION OF orders_2023 FOR VALUES WITH (MODULUS 4 , REMAINDER 1 );
CREATE TABLE orders_2023_p2 PARTITION OF orders_2023 FOR VALUES WITH (MODULUS 4 , REMAINDER 2 );
CREATE TABLE orders_2023_p3 PARTITION OF orders_2023 FOR VALUES WITH (MODULUS 4 , REMAINDER 3 );
CREATE TABLE orders_2024 PARTITION OF orders_composite
FOR VALUES FROM ('2024-01-01' ) TO ('2025-01-01' ) PARTITION BY HASH (customer_id);
CREATE TABLE orders_2024_p0 PARTITION OF orders_2024 FOR VALUES WITH (MODULUS 4 , REMAINDER 0 );
CREATE TABLE orders_2024_p1 PARTITION OF orders_2024 FOR VALUES WITH (MODULUS 4 , REMAINDER 1 );
CREATE TABLE orders_2024_p2 PARTITION OF orders_2024 FOR VALUES WITH (MODULUS 4 , REMAINDER 2 );
CREATE TABLE orders_2024_p3 PARTITION OF orders_2024 FOR VALUES WITH (MODULUS 4 , REMAINDER 3 );
查询时,PostgreSQL 会先根据时间条件剪掉不需要的年份分区,再在剩余分区里用 customer_id 的哈希值定位到具体的哈希子分区。像下面这个查询就非常高效:
SELECT * FROM orders_composite
WHERE order_date BETWEEN '2023-06-01' AND '2023-06-30' AND customer_id = 12345 ;
我的经验是把时间维度放在第一级比较实用,因为大部分业务都有按日期清理老数据的需求,而第二级用哈希可以避免单个时间段内的数据倾斜。
用 Java 管理分区 分区是透明的,普通的 JPA 查询不需要任何特殊处理。但定期创建新分区、删除旧分区还是得手动操作。我一般会在应用里写一个简单的工具类来做这件事。
<dependency >
<groupId > org.postgresql</groupId >
<artifactId > postgresql</artifactId >
<version > 42.6.0</version >
</dependency >
<dependency >
<groupId > org.springframework.boot</groupId >
<artifactId > spring-boot-starter-data-jpa</artifactId >
<version > 3.1.0</version >
</dependency >
@Entity
@Table(name = "orders")
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "customer_id", nullable = false)
private Integer customerId;
@Column(name = "order_date", nullable = false)
private LocalDateTime orderDate;
@Column(name = "total_amount", nullable = false, precision = 12, scale = 2)
private BigDecimal totalAmount;
@Column(name = "status", nullable = false, length = 20)
private String status;
}
分区管理工具类,用 JdbcTemplate 执行 DDL:
@Component
public class PartitionManager {
@Autowired
private JdbcTemplate jdbcTemplate;
public void createYearlyCompositePartition (int year) {
String yearStr = String.valueOf(year);
String nextYearStr = String.valueOf(year + 1 );
String rangePartitionSql = String.format(
"CREATE TABLE IF NOT EXISTS orders_%s " +
"PARTITION OF orders_composite " +
"FOR VALUES FROM ('%s-01-01') TO ('%s-01-01') " +
"PARTITION BY HASH (customer_id)" ,
yearStr, yearStr, nextYearStr
);
jdbcTemplate.execute(rangePartitionSql);
for (int i = 0 ; i < 4 ; i++) {
String hashPartitionSql = String.format(
"CREATE TABLE IF NOT EXISTS orders_%s_p%d " +
"PARTITION OF orders_%s " +
"FOR VALUES WITH (MODULUS 4, REMAINDER %d)" ,
yearStr, i, yearStr, i
);
jdbcTemplate.execute(hashPartitionSql);
}
}
public void dropYearlyPartition (int year) {
String sql = "DROP TABLE IF EXISTS orders_" + year + " CASCADE" ;
jdbcTemplate.execute(sql);
}
}
还可以用 @Scheduled 定时创建未来几个月或下一年的分区,同时清理超过保留期的旧数据。
查询方面,利用哈希分区的等值查询优势和复合分区的双重剪枝:
public interface OrderRepository extends JpaRepository <Order, Long> {
List<Order> findByCustomerId (Integer customerId) ;
@Query("SELECT o FROM Order o WHERE o.customerId = :customerId " +
"AND o.orderDate BETWEEN :startDate AND :endDate")
List<Order> findOrdersByCustomerAndDate (@Param("customerId") Integer customerId,
@Param("startDate") LocalDateTime startDate,
@Param("endDate") LocalDateTime endDate) ;
}
优化与陷阱 分区数量 哈希分区 4~16 个比较合适,再多元数据开销会变大,太少又利用不上并行。对于复合分区,总子表数量最好控制在 1000 个以内。
查询条件 只有包含分区键的 where 条件才能触发分区剪枝。如果只查 status = 'completed',不管什么分区都会扫全表。另外别在分区键上套函数,比如 EXTRACT(YEAR FROM order_date) 会导致剪枝失效,换成范围比较 >= '2023-01-01' AND < '2024-01-01' 才行。
监控执行计划 用 EXPLAIN (ANALYZE, BUFFERS) 看看查询实际扫了几个分区,确认剪枝生效。
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders_composite
WHERE order_date BETWEEN '2023-06-01' AND '2023-06-30' AND customer_id = 12345 ;
维护操作 尽量针对单个子分区执行 VACUUM 或 REINDEX,而不是整个父表。
VACUUM ANALYZE orders_2023_p0;
分区表不是万能药,小数据量下反而会因为额外元数据降低性能,通常单表超过千万行才值得考虑。
一个电商案例 以一个日均 50 万单、保留 3 年数据的订单表为例,数据量在 5 亿以上。查询模式主要有三种:
按用户 ID 查历史订单
按时间范围统计
同时按用户和时间过滤
我用的是范围(按天或按月)+ 哈希的组合:第一级按 order_date 分年或分月,第二级按 user_id 哈希成 4 个区。这样既能快速删除老分区,又能确保用户数据均匀分布。复合查询的剪枝效果很明显,近一个月内一个用户的订单只会扫到一个哈希子表。
CREATE TABLE ecommerce_orders (
id BIGSERIAL,
user_id BIGINT NOT NULL ,
order_date TIMESTAMP NOT NULL ,
total_amount DECIMAL (12 ,2 ) NOT NULL ,
status VARCHAR (20 ) NOT NULL
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2023_12 PARTITION OF ecommerce_orders
FOR VALUES FROM ('2023-12-01' ) TO ('2024-01-01' ) PARTITION BY HASH (user_id);
CREATE TABLE orders_2023_12_p0 PARTITION OF orders_2023_12 FOR VALUES WITH (MODULUS 8 , REMAINDER 0 );
记住一个原则:你的第一级分区键应该是出现频率最高、效果最明显的过滤条件。大部分业务系统里,时间几乎总是第一优先级。
最后 哈希分区解决数据均匀性问题,复合分区让多维查询也能走索引分区剪枝。设计时先想清楚最频繁的查询长什么样,再决定哪一列做主分区键、哪一列做子分区键。分区不是越细越好,不要为了分区而分区。监控磁盘用量和执行计划,及时调整,才能真正让分区表物有所值。
相关免费在线工具 Keycode 信息 查找任何按下的键的javascript键代码、代码、位置和修饰符。 在线工具,Keycode 信息在线工具,online
Escape 与 Native 编解码 JavaScript 字符串转义/反转义;Java 风格 \uXXXX(Native2Ascii)编码与解码。 在线工具,Escape 与 Native 编解码在线工具,online
JavaScript / HTML 格式化 使用 Prettier 在浏览器内格式化 JavaScript 或 HTML 片段。 在线工具,JavaScript / HTML 格式化在线工具,online
JavaScript 压缩与混淆 Terser 压缩、变量名混淆,或 javascript-obfuscator 高强度混淆(体积会增大)。 在线工具,JavaScript 压缩与混淆在线工具,online
SQL 美化和格式化 在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
SQL转CSV/JSON/XML 解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL转CSV/JSON/XML在线工具,online