目录

pgsql-20 分区表技术

xingliuhua 收录于笔记

2026-03-16 约 199 字预计阅读 1 分钟

目录

20 - 分区表技术

1. 📖 概述

分区表将大表拆分为多个物理分区,提高查询性能和维护效率。

2. 🔧 声明式分区

2.1 范围分区

CREATE TABLE orders (
    id SERIAL,
    order_date DATE NOT NULL,
    amount NUMERIC(10,2)
) PARTITION BY RANGE (order_date);

-- 创建分区
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');

-- 默认分区
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

2.2 列表分区

CREATE TABLE sales (
    id SERIAL,
    region VARCHAR(50),
    amount NUMERIC(10,2)
) PARTITION BY LIST (region);

CREATE TABLE sales_north PARTITION OF sales
    FOR VALUES IN ('US', 'Canada');

CREATE TABLE sales_asia PARTITION OF sales
    FOR VALUES IN ('China', 'Japan', 'India');

2.3 哈希分区

CREATE TABLE users_partitioned (
    id SERIAL,
    username VARCHAR(50)
) PARTITION BY HASH (id);

CREATE TABLE users_p0 PARTITION OF users_partitioned
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

3. 📚 下一步

学习查询优化与执行计划