PostgreSQL 中的表分区如何工作？

Question

IT Interview Practice · Accepted Answer

**分区** 根据列的值将大表分成较小的物理块（**分区**），但在逻辑上仍然是一个表。PostgreSQL 的 **declarative partitioning**（Postgres 10+）使其变得简洁。它为非常大的表改进了性能和可管理性。

## Declarative partitioning（现代方式）

```sql
-- create a partitioned table, partitioned by RANGE on a date
CREATE TABLE orders (id INT, order_date DATE, amount NUMERIC)
PARTITION BY RANGE (order_date);

-- create partitions for each range
CREATE TABLE orders_2023 PARTITION OF orders
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
```

`orders` 在逻辑上是一个表，在物理上按年份分布在分区中。插入和查询会自动路由到正确的分区。

## 分区剪枝（分区修剪）— 关键性能优势

```sql
-- a query filtered on the partition key scans ONLY relevant partitions
SELECT * FROM orders WHERE order_date >= '2024-06-01';
-- → Postgres skips orders_2023 entirely ("partition pruning") — scans only orders_2024
```

**分区剪枝** 是主要优势：在分区键上过滤的查询仅触及相关分区，极大地减少在超大表上扫描的数据。

## 分区策略

```text
PARTITION BY RANGE → value ranges (dates, numbers) — common for time-series
PARTITION BY LIST  → discrete values (region, status)
PARTITION BY HASH  → hash of the key (even distribution across N partitions)
```

## 维护优势

```sql
-- drop old data instantly by dropping a whole partition (vs slow DELETE)
DROP TABLE orders_2020;              -- removes all 2020 data instantly
-- vs DELETE FROM orders WHERE year = 2020 → slow, creates bloat

-- detach/attach partitions; automate with pg_partman extension
```

删除整个分区以归档/删除旧数据是即时的 — 相比对大数据集的 `DELETE` 是一个重大的运维优势。

## 为什么这很重要

表分区是管理和查询 **非常大的表** 的重要 PostgreSQL 技术，理解它对于大规模使用 Postgres 很有价值。

随着表增长到数亿或数十亿行，分区通过将表拆分成较小的物理块来解决性能和可管理性问题。

Postgres 的 **declarative partitioning**（Postgres 10+）使其变得简洁，是需要了解的现代标准。

关键性能优势是 **分区剪枝** — 在分区键上过滤的查询仅扫描相关分区（跳过其余分区），极大地减少超大表扫描的数据（对于按日期过滤的时间序列数据特别有价值，这是一种非常普遍的情况）。

除了性能外，分区还提供重大的 **维护优势**：归档或删除旧数据变成通过删除整个分区即时完成（相比于数百万行的缓慢、产生膨胀的 `DELETE` — 对于具有保留策略的数据（如日志和事件）的重大运维优势）。

理解 **策略**（RANGE 用于日期/数字范围 — 对时间序列常见；LIST 用于离散值；HASH 用于均匀分布）和 **何时使用分区**（具有自然分区键的非常大的表，尤其是具有定期归档的时间序列 — 索引足以的小表不适用）反映了合理的判断。

由于在大规模 PostgreSQL 系统中管理非常大的表是真正的挑战，而且 declarative partitioning（具有分区剪枝和高效的分区删除维护）是标准工具 — 补充索引 — 理解 PostgreSQL 表分区是有价值的、实际相关的数据库可扩展性知识，对于大规模、高容量或时间序列应用特别重要，是演示大规模管理 Postgres 能力的常见主题。

Declarative partitioning（现代方式）

分区剪枝（分区修剪）— 关键性能优势

分区策略

维护优势

为什么这很重要

相关问题