PostgreSQL笔记61:分区表核心概念、声明式分区与最佳实践

PostgreSQL - 基础入门 它是开源的,其源代码是免费提供的。它是用PHP编写的PostgreSQL的基于Web的管理工具。PostgreSQL是跨平台的,可以在许多操作系统上运行,如Linux,FreeBSD,OS X,Solaris和Microsoft Windows等。它是一个命令行工具,也是管理PostgreSQL的主要工具。左外连接返回从“ON”条件中指定的左侧表中的所有行,只返回满足条件的另一个表中的行。右外连接返回从“ON”条件中指定的右侧表中的所有行,只返回满足条件的另一个表中的行。右边的空格填充到相等大小的字符。 阅读详情

纲要

  • PostgreSQL 分区表概述
    • 分区表的定义与透明性
    • 适用场景与核心收益
  • 分区表的三大优势
    • VACUUM 并行化
    • 批量加载与删除(DROP / DETACH)
    • 分区裁剪(Partition Pruning)
  • 分区策略类型
    • 范围分区(Range Partitioning)
    • 列表分区(List Partitioning)
    • 哈希分区(Hash Partitioning)
    • 多级分区(Sub-Partitioning)
    • 默认分区(Default Partition)
  • 声明式分区(声明式分区) vs 继承式分区(表继承)
    • PG 10 前后的演进
    • 声明式分区的创建与管理(CREATE TABLE ... PARTITION BY、CREATE TABLE ... PARTITION OF、ALTER TABLE ... ATTACH)
  • 实践演示
    • 范围分区示例
    • 哈希分区示例
    • 查询验证(pg_class 与 tableoid)
  • 分区维护与注意事项

分区表概述

分区表是 PostgreSQL 中一类重要的数据库对象,它将一个逻辑表按既定规则拆分为多个物理子表,对上层业务保持透明。业务端仍操作父表,所有 INSERT / UPDATE / DELETE / SELECT 均自动路由至对应子表,无需修改应用代码。这一特性使得分区表在运维、性能优化和数据生命周期管理方面扮演关键角色。

在 PostgreSQL 中,分区表主要解决以下场景需求:

  • 超大规模表的查询性能瓶颈
  • 数据清理(如按时间淘汰历史数据)效率低下
  • VACUUM 与 ANALYZE 因表体积过大而耗时过长
  • 需要按特定维度(时间、地区、类别)隔离数据

分区表的核心优势

并行 VACUUM 能力

VACUUM 操作在单个表上持有四级锁(ShareUpdateExclusiveLock),同一时刻仅允许一个 VACUUM 进程作用于该对象。当表被拆分为多个子分区后,每个子分区可以独立触发 VACUUM,从而在逻辑上实现并发清理。对于一张亿级大表,分区后可同时运行多个 VACUUM 进程,显著提升清理效率,减轻事务 ID 回卷压力。

批量加载与删除简化

通过分区机制,可以将历史数据整体剥离。若采用时间范围分区,当某月数据不再需要时,可直接 DROP TABLE 该子分区,或使用 ALTER TABLE ... DETACH PARTITION 将其脱离父表,随后作为独立表归档或删除。这相比执行 DELETE 操作,不仅速度极快,而且避免产生大量死元组和事务日志。

分区裁剪(Partition Pruning)

分区裁剪是查询优化器的一种智能决策:当 WHERE 条件包含分区键时,优化器能够排除无关子分区,仅扫描必要的一个或几个子表。例如按月份分区,查询 WHERE order_date = '2025-10-01' 时,优化器直接定位至对应月份分区,跳过其余 11 个月的数据。数据量从数十亿缩减至数千万,查询响应时间可呈数量级下降。但注意,若查询未携带分区键,则优化器会扫描所有子分区,失去裁剪优势。

分区策略类型

PostgreSQL 支持多种分区策略,可根据数据分布特征选择最合适的方案。

策略说明适用场景
范围分区(Range)基于连续区间划分,如日期、数值范围时间序列数据、日志、订单
列表分区(List)基于离散值列表,如地区、状态码按地域、部门、类别隔离
哈希分区(Hash)对分区键计算哈希值,均匀分散至指定数量分区数据分布需均匀,无明确范围或列表值

多级分区(Sub-Partitioning)

支持对子分区再次分区,形成两级或更多层级。例如先按设备 ID 做列表分区,再按时间做范围分区。数据量从亿级逐级缩减至百万级,进一步加速查询和运维。

默认分区(Default Partition)

默认分区用于接收所有不匹配任何现有分区规则的数据。当插入一行,其分区键值未落入任何已定义分区范围时,若未设置默认分区,PostgreSQL 会抛出错误。默认分区扮演“垃圾桶”角色,确保插入不中断。但请注意,默认分区本身也可能膨胀,且查询时会被扫描(除非裁剪逻辑能排除),通常建议配合定时任务将默认分区中的数据转移至正式分区。

注意:PostgreSQL 目前不提供自动创建分区(如 Oracle 的 Interval Partitioning)。如需自动扩展分区,可借助外部定时任务或触发器调用 ALTER TABLE ... ATTACH。

声明式分区 vs 继承式分区

在 PostgreSQL 10 之前,分区功能依赖于表继承(Inheritance)、检查约束(Check Constraint)和触发器(Trigger)。这种方式虽然灵活(可自定义路由逻辑),但维护复杂,易出错,且优化器支持有限。

自 PostgreSQL 10 起,官方引入声明式分区(Declarative Partitioning),专为分区场景设计,语法简洁,优化器对分区裁剪、约束排除等支持更加完善。目前生产环境强烈推荐使用声明式分区,继承式分区已不推荐,仅作为遗留方案参考。

声明式分区的核心特点:

  • 父表定义分区键和策略(PARTITION BY)
  • 子表必须具有与父表完全相同的列结构(继承式可不同,但易引入问题)
  • 支持 RANGE、LIST、HASH 三种策略
  • 可通过 ATTACH / DETACH 动态增减子分区
  • 分区表上可建立索引、外键(部分受限)、触发器

实践演示:声明式分区创建与查询

以下示例基于 PostgreSQL 14+,所有语法兼容 PG 10 及以上版本。

范围分区示例

创建按销售日期范围分区的订单表:

-- 创建父表,指定范围分区键
CREATE TABLE sales (
    id SERIAL,
    order_date DATE NOT NULL,
    amount NUMERIC(10,2)
) PARTITION BY RANGE (order_date);

-- 创建子分区:2025年8月
CREATE TABLE sales_2025_08 PARTITION OF sales
    FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');

-- 创建子分区:2025年9月
CREATE TABLE sales_2025_09 PARTITION OF sales
    FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');

-- 插入测试数据
INSERT INTO sales (order_date, amount) VALUES
    ('2025-08-15', 100.50),
    ('2025-09-10', 200.75);

-- 查询(自动裁剪)
SELECT * FROM sales WHERE order_date = '2025-08-15';

通过 \d+ sales 可查看分区列表。

使用 ATTACH 方式添加分区

若预先创建好独立表,可将其挂载为分区:

-- 创建与父表结构一致的表
CREATE TABLE sales_2025_10 (LIKE sales INCLUDING ALL);

-- 挂载为分区
ALTER TABLE sales ATTACH PARTITION sales_2025_10
    FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');

LIKE ... INCLUDING ALL 会复制列、约束、默认值、索引等,确保结构一致。

哈希分区示例

-- 按哈希分区,共4个分区
CREATE TABLE orders (
    id SERIAL,
    customer_id INT NOT NULL,
    order_date DATE
) 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 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

MODULUS 为分区总数,REMAINDER 为哈希值取模后的余数。

列表分区示例

CREATE TABLE regions (
    id SERIAL,
    region_name TEXT,
    sales_rep TEXT
) PARTITION BY LIST (region_name);

CREATE TABLE regions_emea PARTITION OF regions
    FOR VALUES IN ('EMEA', 'Middle East', 'Africa');
CREATE TABLE regions_apac PARTITION OF regions
    FOR VALUES IN ('APAC', 'India', 'Japan');

查询数据所在子分区

利用系统列 tableoid 可获知每条记录所属子分区:

SELECT
    tableoid::regclass AS partition_name,
    order_date,
    amount
FROM sales;

输出示例:

 partition_name | order_date  | amount
----------------+-------------+--------
 sales_2025_08  | 2025-08-15  | 100.50
 sales_2025_09  | 2025-09-10  | 200.75

API 速览

本节梳理涉及的核心 SQL 命令与系统视图。

CREATE TABLE ... PARTITION BY

定义分区父表,指定分区策略和键。

语法:

CREATE TABLE table_name ( ... )
PARTITION BY { RANGE | LIST | HASH } ( column_name [, ...] );

CREATE TABLE ... PARTITION OF

创建子分区,并绑定到父表。

语法:

CREATE TABLE partition_name PARTITION OF parent_table
    FOR VALUES { FROM (lower_bound) TO (upper_bound)   -- RANGE
               | IN (value_list)                      -- LIST
               | WITH (MODULUS modulus, REMAINDER remainder) -- HASH
               };

ALTER TABLE ... ATTACH PARTITION

将现有表挂载为子分区。

ALTER TABLE parent_table ATTACH PARTITION partition_name
    FOR VALUES { ... };

ALTER TABLE ... DETACH PARTITION

将子分区从父表分离,使其成为独立表。

ALTER TABLE parent_table DETACH PARTITION partition_name;

DROP TABLE

直接删除子分区(需提前 DETACH 或使用 DROP TABLE ... CASCADE 但注意影响)。

系统目录

  • pg_class:存储表、分区等对象信息,relkind 为 'p'(父表)或 'r'(普通表,子分区也为普通表)。
  • pg_partitioned_table:记录分区父表的元数据。
  • tableoid(系统列):每个行标识其所在表的 OID,可转为 regclass 获取表名。

完整 Demo 示例(基于 Node.js)

本示例使用 Node.js + pg 驱动演示分区表的创建、插入与分区裁剪查询。

环境准备

  • Node.js 18+
  • npm install pg

代码

// demo.js
const { Client } = require('pg');

const client = new Client({
    host: 'localhost',
    port: 5432,
    database: 'testdb',
    user: 'postgres',
    password: 'postgres'
});

async function run() {
    await client.connect();

    // 1. 创建父表(范围分区)
    await client.query(`
        CREATE TABLE IF NOT EXISTS sales_demo (
            id SERIAL,
            sale_date DATE NOT NULL,
            amount DECIMAL(10,2)
        ) PARTITION BY RANGE (sale_date);
    `);

    // 2. 创建子分区(2025年8月、9月)
    await client.query(`
        CREATE TABLE IF NOT EXISTS sales_demo_2025_08 PARTITION OF sales_demo
            FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
    `);
    await client.query(`
        CREATE TABLE IF NOT EXISTS sales_demo_2025_09 PARTITION OF sales_demo
            FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');
    `);

    // 3. 插入数据
    await client.query(`
        INSERT INTO sales_demo (sale_date, amount) VALUES
            ('2025-08-15', 100.00),
            ('2025-09-20', 200.00),
            ('2025-08-25', 150.00);
    `);

    // 4. 查询(不带分区键 → 全表扫描)
    const resAll = await client.query('SELECT * FROM sales_demo');
    console.log('所有数据:', resAll.rows);

    // 5. 查询(带分区键 → 裁剪,仅扫描8月分区)
    const resPruned = await client.query(
        'SELECT * FROM sales_demo WHERE sale_date >= $1 AND sale_date < $2',
        ['2025-08-01', '2025-09-01']
    );
    console.log('8月数据(裁剪后):', resPruned.rows);

    // 6. 查看每行所在分区
    const resPart = await client.query(`
        SELECT tableoid::regclass AS partition_name, sale_date, amount
        FROM sales_demo
    `);
    console.log('所在分区:', resPart.rows);

    // 7. 清理(可选)
    // await client.query('DROP TABLE sales_demo CASCADE');

    await client.end();
}

run().catch(console.error);

运行说明

  1. 确保本地 PostgreSQL 已启动,并创建数据库 testdb。
  2. 修改连接参数(用户名、密码等)。
  3. 执行 node demo.js。
  4. 观察控制台输出,可看到查询仅扫描 sales_demo_2025_08 分区。

技术点总结

  • 使用 pg 驱动执行 DDL 与 DML。
  • 分区表对 INSERT 透明,自动路由。
  • 分区裁剪在查询执行计划中生效(可通过 EXPLAIN 验证)。
  • 通过 tableoid 识别物理存储位置。

多语言示例

Go 语言示例

本示例使用 pgx 驱动(github.com/jackc/pgx/v5)实现分区表操作。

代码
// main.go
package main

import (
    "context"
    "fmt"
    "log"

    "github.com/jackc/pgx/v5"
)

func main() {
    ctx := context.Background()

    // 连接数据库
    conn, err := pgx.Connect(ctx, "postgres://postgres:postgres@localhost:5432/testdb")
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close(ctx)

    // 1. 创建父表(范围分区)
    _, err = conn.Exec(ctx, `
        CREATE TABLE IF NOT EXISTS sales_demo (
            id SERIAL,
            sale_date DATE NOT NULL,
            amount DECIMAL(10,2)
        ) PARTITION BY RANGE (sale_date);
    `)
    if err != nil {
        log.Fatal(err)
    }

    // 2. 创建子分区(2025年8月、9月)
    _, err = conn.Exec(ctx, `
        CREATE TABLE IF NOT EXISTS sales_demo_2025_08 PARTITION OF sales_demo
            FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
    `)
    if err != nil {
        log.Fatal(err)
    }
    _, err = conn.Exec(ctx, `
        CREATE TABLE IF NOT EXISTS sales_demo_2025_09 PARTITION OF sales_demo
            FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');
    `)
    if err != nil {
        log.Fatal(err)
    }

    // 3. 插入数据
    _, err = conn.Exec(ctx, `
        INSERT INTO sales_demo (sale_date, amount) VALUES
            ('2025-08-15', 100.00),
            ('2025-09-20', 200.00),
            ('2025-08-25', 150.00);
    `)
    if err != nil {
        log.Fatal(err)
    }

    // 4. 查询全部数据
    rows, err := conn.Query(ctx, `SELECT sale_date, amount FROM sales_demo`)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    fmt.Println("所有数据:")
    for rows.Next() {
        var saleDate string
        var amount float64
        rows.Scan(&saleDate, &amount)
        fmt.Printf("  %s  %.2f\n", saleDate, amount)
    }

    // 5. 分区裁剪查询(仅8月)
    rowsPruned, err := conn.Query(ctx, `
        SELECT sale_date, amount FROM sales_demo
        WHERE sale_date >= $1 AND sale_date < $2
    `, "2025-08-01", "2025-09-01")
    if err != nil {
        log.Fatal(err)
    }
    defer rowsPruned.Close()
    fmt.Println("8月数据(裁剪后):")
    for rowsPruned.Next() {
        var saleDate string
        var amount float64
        rowsPruned.Scan(&saleDate, &amount)
        fmt.Printf("  %s  %.2f\n", saleDate, amount)
    }

    // 6. 查看每行所在分区
    rowsPart, err := conn.Query(ctx, `
        SELECT tableoid::regclass AS partition_name, sale_date, amount
        FROM sales_demo
    `)
    if err != nil {
        log.Fatal(err)
    }
    defer rowsPart.Close()
    fmt.Println("所在分区:")
    for rowsPart.Next() {
        var partName, saleDate string
        var amount float64
        rowsPart.Scan(&partName, &saleDate, &amount)
        fmt.Printf("  %s  %s  %.2f\n", partName, saleDate, amount)
    }
}
运行说明
  1. 安装 Go 1.18+,并初始化模块:go mod init demo && go get github.com/jackc/pgx/v5
  2. 确保本地 PostgreSQL 运行,并创建数据库 testdb。
  3. 修改连接字符串中的用户名、密码、主机、端口(如需要)。
  4. 执行 go run main.go。
代码说明
  • 使用 pgx 的 Connect 方法建立连接,Exec 执行 DDL 和 DML。
  • 使用参数化查询($1、$2)防止 SQL 注入并支持分区裁剪。
  • 通过 tableoid::regclass 获取实际存储的分区名称。
  • 结果集遍历采用 rows.Next() + Scan 的标准模式。
技术点总结
  • Go 语言通过 pgx 驱动与 PostgreSQL 交互,支持上下文传递。
  • 分区表 DDL 与原生 SQL 完全一致,无需特殊 ORM 映射。
  • 分区裁剪自动生效,查询条件携带分区键时性能提升显著。
  • tableoid 系统列可帮助调试分区路由。

Python 语言示例

本示例使用 psycopg2 驱动(psycopg2-binary)实现分区表操作。

代码
# demo.py
import psycopg2

def main():
    # 连接数据库
    conn = psycopg2.connect(
        host="localhost",
        port=5432,
        database="testdb",
        user="postgres",
        password="postgres"
    )
    conn.autocommit = True
    cur = conn.cursor()

    # 1. 创建父表(范围分区)
    cur.execute("""
        CREATE TABLE IF NOT EXISTS sales_demo (
            id SERIAL,
            sale_date DATE NOT NULL,
            amount DECIMAL(10,2)
        ) PARTITION BY RANGE (sale_date);
    """)

    # 2. 创建子分区(2025年8月、9月)
    cur.execute("""
        CREATE TABLE IF NOT EXISTS sales_demo_2025_08 PARTITION OF sales_demo
            FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
    """)
    cur.execute("""
        CREATE TABLE IF NOT EXISTS sales_demo_2025_09 PARTITION OF sales_demo
            FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');
    """)

    # 3. 插入数据
    cur.execute("""
        INSERT INTO sales_demo (sale_date, amount) VALUES
            ('2025-08-15', 100.00),
            ('2025-09-20', 200.00),
            ('2025-08-25', 150.00);
    """)

    # 4. 查询全部数据
    cur.execute("SELECT sale_date, amount FROM sales_demo")
    print("所有数据:")
    for row in cur.fetchall():
        print(f"  {row[0]}  {row[1]:.2f}")

    # 5. 分区裁剪查询(仅8月)
    cur.execute("""
        SELECT sale_date, amount FROM sales_demo
        WHERE sale_date >= %s AND sale_date < %s
    """, ("2025-08-01", "2025-09-01"))
    print("8月数据(裁剪后):")
    for row in cur.fetchall():
        print(f"  {row[0]}  {row[1]:.2f}")

    # 6. 查看每行所在分区
    cur.execute("""
        SELECT tableoid::regclass AS partition_name, sale_date, amount
        FROM sales_demo
    """)
    print("所在分区:")
    for row in cur.fetchall():
        print(f"  {row[0]}  {row[1]}  {row[2]:.2f}")

    cur.close()
    conn.close()

if __name__ == "__main__":
    main()
运行说明
  1. 安装 Python 3.8+,并安装依赖:pip install psycopg2-binary
  2. 确保本地 PostgreSQL 运行,并创建数据库 testdb。
  3. 修改连接参数(用户名、密码等)。
  4. 执行 python demo.py。
代码说明
  • 使用 psycopg2 的 connect 建立连接,设置 autocommit=True 避免显式事务。
  • 使用参数化查询(%s 占位符)传递日期边界。
  • 通过 fetchall() 获取所有结果,遍历输出。
  • 分区裁剪和 tableoid 查询与 SQL 层面完全一致。
技术点总结
  • Python 通过 psycopg2 驱动执行原生 SQL,无 ORM 依赖。
  • 分区表对应用层透明,插入和查询操作与普通表相同。
  • 分区裁剪在数据库端自动优化,应用程序只需正常传参。
  • tableoid 可用于监控和验证数据分布。

Java 语言示例

本示例使用 JDBC 和 PostgreSQL 官方驱动(org.postgresql:postgresql)实现分区表操作。

代码
// Demo.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Demo {
    public static void main(String[] args) throws Exception {
        // 加载驱动(JDBC 4+ 自动加载)
        String url = "jdbc:postgresql://localhost:5432/testdb";
        String user = "postgres";
        String password = "postgres";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement()) {

            // 1. 创建父表(范围分区)
            stmt.execute("""
                CREATE TABLE IF NOT EXISTS sales_demo (
                    id SERIAL,
                    sale_date DATE NOT NULL,
                    amount DECIMAL(10,2)
                ) PARTITION BY RANGE (sale_date);
            """);

            // 2. 创建子分区(2025年8月、9月)
            stmt.execute("""
                CREATE TABLE IF NOT EXISTS sales_demo_2025_08 PARTITION OF sales_demo
                    FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
            """);
            stmt.execute("""
                CREATE TABLE IF NOT EXISTS sales_demo_2025_09 PARTITION OF sales_demo
                    FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');
            """);

            // 3. 插入数据
            stmt.execute("""
                INSERT INTO sales_demo (sale_date, amount) VALUES
                    ('2025-08-15', 100.00),
                    ('2025-09-20', 200.00),
                    ('2025-08-25', 150.00);
            """);

            // 4. 查询全部数据
            try (ResultSet rs = stmt.executeQuery("SELECT sale_date, amount FROM sales_demo")) {
                System.out.println("所有数据:");
                while (rs.next()) {
                    System.out.printf("  %s  %.2f%n", rs.getDate("sale_date"), rs.getDouble("amount"));
                }
            }

            // 5. 分区裁剪查询(仅8月)- 使用 PreparedStatement 防止注入
            String prunedSql = "SELECT sale_date, amount FROM sales_demo WHERE sale_date >= ? AND sale_date < ?";
            try (java.sql.PreparedStatement pstmt = conn.prepareStatement(prunedSql)) {
                pstmt.setDate(1, java.sql.Date.valueOf("2025-08-01"));
                pstmt.setDate(2, java.sql.Date.valueOf("2025-09-01"));
                try (ResultSet rs = pstmt.executeQuery()) {
                    System.out.println("8月数据(裁剪后):");
                    while (rs.next()) {
                        System.out.printf("  %s  %.2f%n", rs.getDate("sale_date"), rs.getDouble("amount"));
                    }
                }
            }

            // 6. 查看每行所在分区
            try (ResultSet rs = stmt.executeQuery("""
                    SELECT tableoid::regclass AS partition_name, sale_date, amount
                    FROM sales_demo
                """)) {
                System.out.println("所在分区:");
                while (rs.next()) {
                    System.out.printf("  %s  %s  %.2f%n",
                        rs.getString("partition_name"),
                        rs.getDate("sale_date"),
                        rs.getDouble("amount"));
                }
            }
        }
    }
}
运行说明
  1. 安装 Java 11+,并配置 Maven 或 Gradle 依赖(见下)。
  2. 添加 PostgreSQL JDBC 驱动依赖(Maven 示例):
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.7.2</version>
    </dependency>
    
  3. 确保本地 PostgreSQL 运行,并创建数据库 testdb。
  4. 编译:javac Demo.java
  5. 运行(需将驱动 jar 置于 classpath):java -cp ".:postgresql-42.7.2.jar" Demo
代码说明
  • 使用 DriverManager 获取连接,通过 Statement 执行 DDL 和简单 DML。
  • 分区裁剪查询使用 PreparedStatement 设置日期参数,提高安全性和执行效率。
  • 使用 try-with-resources 自动释放 Connection、Statement 和 ResultSet。
  • 日期类型使用 java.sql.Date,与 DATE 列匹配。
技术点总结
  • Java 通过 JDBC 驱动连接 PostgreSQL,执行原生 SQL。
  • 分区表对 JDBC 透明,与普通表操作一致。
  • 使用 PreparedStatement 绑定参数可触发分区裁剪。
  • tableoid 系统列同样可在 JDBC 结果集中获取,用于调试。

多语言示例对比

维度Node.js (pg)Go (pgx)Python (psycopg2)Java (JDBC)
驱动/库pgpgx/v5psycopg2-binaryPostgreSQL JDBC 42.7.2
连接方式new Client()pgx.Connect()psycopg2.connect()DriverManager.getConnection()
参数化占位符$1, $2$1, $2%s(需传入元组)?(PreparedStatement)
事务默认非自动提交(需手动end)非自动提交(需Commit)autocommit=True(示例)自动提交(默认 true)
结果集遍历rows.next() 回调 / asyncrows.Next() + Scancur.fetchall() 列表ResultSet.next()
日期处理JavaScript Date 或字符串time.Time 或字符串Python date 或字符串java.sql.Date
分区表特性完全支持,透明完全支持,透明完全支持,透明完全支持,透明
分区裁剪自动生效自动生效自动生效自动生效
tableoid 支持是是是是
依赖管理npmgo modpipMaven/Gradle

所有语言的示例均实现了相同的功能流程:创建分区父表和子分区、插入测试数据、执行全量查询和裁剪查询、通过 tableoid 查看数据所在分区。在实际生产环境中,可根据团队技术栈选择合适的语言驱动,分区表的逻辑完全由数据库层接管,应用程序无需关注物理存储细节。

项目难点与解决方案(基于分区表运维常见挑战)

核心难点

  • 分区键选择不当导致数据倾斜,某些分区过大,部分分区过小,裁剪效率下降。
  • 默认分区膨胀:若大量未预见的值涌入默认分区,将导致该分区极大,且查询无法裁剪。
  • 分区维护自动化缺失:PostgreSQL 不提供自动创建新分区,需外部脚本或定时任务。
  • 跨分区查询性能:若查询条件跨越多个分区,优化器可能生成串行扫描多个分区,需关注 enable_partitionwise_join 等参数。

解决方案

  • 分区键选择:优先选择查询过滤条件中最常使用的字段,且分布均匀(如时间戳、自增ID的哈希)。
  • 监控默认分区:定期检查默认分区大小,若超过阈值则及时创建新分区并将数据迁移。
  • 自动化扩展:使用 pg_cron 或外部调度(如 cron)每月/每季度提前创建未来分区。
  • 并行查询调优:开启 enable_partitionwise_join 和 enable_partitionwise_aggregate 以支持分区间的并行处理(需 PG 11+)。
  • 索引策略:在每个子分区上单独建立索引,而非仅在父表建索引(父表索引不会自动继承)。

广度

涵盖 DDL、DML、查询优化、运维自动化、监控等多个层面。

深度

深入剖析分区裁剪原理(基于约束排除和分区边界),探讨 VACUUM 并行化收益的实现基础。

复杂度

涉及系统表、执行计划分析、外部工具集成,需对 PostgreSQL 内部机制有一定了解。

官方文档与参考链接

官方文档

参考链接

总结

本文系统梳理了 PostgreSQL 分区表的定义、优势、策略类型及声明式分区的使用方法。重点强调分区裁剪对查询性能的提升、VACUUM 并行化对运维的改善,以及 DROP / DETACH 在数据生命周期管理中的便利性。通过范围、列表、哈希分区及多级分区的具体示例,展示了声明式分区的创建与管理方式,并指出默认分区的作用与注意事项。

同时,对比了继承式分区的历史方案,明确声明式分区为当前推荐实践。文中提供的 Node.js 完整 Demo 演示了分区表在应用程序中的透明使用,并给出了分区键选择、自动化维护等常见难点解决方案。掌握分区表技术,是驾驭超大容量 PostgreSQL 数据库的关键能力之一。

PostgreSql简介 PostgreSQL简介 阅读详情

相关推荐

【PostgreSQL】手把手教学PostgreSQL

PostgreSQL 是一个功能强大的开源对象关系数据库系统,它使用和扩展了 SQL 语言,并结合了许多安全存储和扩展最复杂数据工作负载的功能。PostgreSQL 因其经过验证的架构、可靠性、数据完整性、强大的功能集、可扩展性以及软件背后的开源社区致力于始终如一地提供高性能和创新的解决方案而赢得了良好的声誉。数据类型基元:整数、数字、字符串、布尔值结构化:日期/时间、数组、范围/多范围、UUID文档:JSON/JSONB、XML、KEY-VALUE (HSTORE)几何:点、线、圆、多边形。

Fanjufei的博客 5582

PGSQL(PostgreSQL)数据库基础篇:PostgreSQL 的 主要优点 、 劣势 、体系结构 、核心功能 、安装教程。

1.最新版本和历史版本不分离存储,导致清理老旧版本时需要做更多的扫描,代价比较大但一般的数据库都有高峰期,如果合理安排VACUUM,这也不是很大的问题,而且在PostgreSQL9.0中VACUUM进一步被加强了。2.在PostgreSQL中,由于索引完全没有版本信息,不能实现Coverage index scan,即查询只扫描索引,不能直接从索引中返回所需的属性,还需要访问表,而Oracle与Innodb则可以。因为存储过程的存在也避免了在网络上大量原始的SQL语句的传输,这样的优势是显而易见的。

一码归一码@ 1万+

大表查询慢到翻遍整个书架?PostgreSQL分区表教你怎么“分类”才高效

分区表是将逻辑上的大表拆分为物理上的小表的技术。想象你有一个放了10年日记的书架:如果不分类,找某一天的日记要翻遍整个书架;把数据按规则“归类”,让查询/操作只触达必要的数据。分区剪枝(Partition Pruning)是PostgreSQL的自动优化:当查询包含分区键的过滤条件时,数据库会跳过不需要的分区,只扫描必要的分区。比如查询“2023年10月的订单”,数据库会自动跳过(9月)、(11月)等分区,只扫描。

https://blog.cmdragon.cn/ 1838

数据库之PostgreSQL详解

在日常操作中,对于一组相关操作,通常要求要么都成功,要么都失败。在关系型数据库中,称这一组操作为事务。原子性A:事务是一个最小的执行单位,一次事务中的操作要么都成功,要么都失败。一致性C:在事务完成时,所有数据必须保持在一致的状态。(事务完成后吗,最终结果和预期结果是一致的)隔离性:一次事务操作,要么是其他事务操作前的状态,要么是其他事务操作后的状态,不存在中间状态。持久性:事务提交后,数据会落到本地磁盘,修改是永久性的。

励志成为一名优秀的全能型软件开发工程师。 2万+

想让PostgreSQL查询快到飞起?分区表、物化视图、并行查询这三招灵不灵?

分区表是将逻辑上的大表拆分为物理上的小表的技术。查询性能优化:仅扫描相关分区(比如查2023年数据时,跳过2020年的分区);数据维护高效:删除旧数据只需 drop 分区(而非逐条删除);存储分层:将冷数据(如5年前的记录)存到廉价存储,热数据存到高速存储。范围分区(Range):按连续范围划分(如日期、ID);列表分区(List):按离散值划分(如地区、状态);哈希分区(Hash):按哈希值模运算划分(如将用户ID均匀分到10个分区)。

https://blog.cmdragon.cn/ 1352

PostgreSQL:表分区与继承

表分区(Table Partitioning)和表继承(Table Inheritance)作为PostgreSQL应对大数据处理的核心技术手段,正发挥着越来越重要的作用。想象这样一个场景:某电商平台的订单表每天新增百万级记录,三年后将达到惊人的10亿行规模。此时若使用传统单表存储,即使有索引加持,简单的范围查询也可能需要数分钟响应。这正是表分区技术大显身手的时刻——通过将数据物理分割到不同子表,查询性能可提升数十倍。 PostgreSQL的分区演进史本身就是一部技术进化史:从早期的继承表模拟分区(8.1

在技术的广袤天地里,本博客如精准罗盘。剖析前沿科技,深掘代码奥秘,以精炼笔触,带您穿越复杂技术迷宫,速达知识彼岸。 3040

PostgreSQL分区表

分区表数据库分区表将表数据分成更小的物理分片,以此提高性能、可用性、易管理性。分区表是关系型数据库中比较常见的对大表的优化方式,数据库管理系统一般都提供了分区管理,而业务可以直接访问分区表而不需要调整业务架构,当然好的性能需要合理的分区访问方式。分区表是数据库中常见的技术,而PostgreSQL中的分区表有许多专有的特性,比如分区表实现方案多、分区为普通表、分区维护方案、SQL优化还有一些分区表的问题。

Java后端技术栈 2530

PostgreSQL 性能优化:分区表实战

PostgreSQL分区表是应对海量数据的核心解决方案,通过分区剪枝、并行扫描等机制显著提升查询性能。文章详解了三种分区策略(范围、列表、哈希)及适用场景,强调分区键应选择高频过滤、高基数字段。实战演示了创建按月分区的订单表,并给出自动化管理方案。关键优化点包括确保分区剪枝生效、合理设计索引等。分区表特别适合时间序列数据(如日志、订单),能有效解决单表查询慢、维护难等问题,实现高性能、易维护的数据架构。

数据知道的博客 2035

PostgreSQL分区表实战:从触发器到声明式分区的进化之旅

本文深入探讨了PostgreSQL分区表技术的演进历程,从传统的触发器方案到现代声明式分区的跨越。通过物联网时序数据场景的实战测试,对比了两种方案的性能差异,揭示了声明式分区在数据加载速度、查询延迟和并发吞吐量等方面的显著优势。文章还提供了分区策略设计、优化技巧和疑难解决方案,帮助开发者高效管理海量数据。

kkk56的博客 895

eDP、DP与HDMI——现代显示接口的核心解析

eDP、DP和HDMI各有千秋:eDP以嵌入式设备的低功耗见长,DP凭借开放生态和高带宽成为专业领域首选,而HDMI则依靠庞大的消费电子生态稳居家庭市场。未来,随着Type-C接口的普及和超高清内容的爆发,三者的技术边界可能进一步融合,但各自的核心优势仍将长期共存。对于用户而言,选择接口需基于设备需求——游戏玩家青睐HDMI的VRR,设计师依赖DP的多屏扩展,而轻薄本则离不开eDP的紧凑设计。

麟的博客 8439

MQTT阿里云OTA 调试记录

2.验证,这个验证是什么意思呢,就是阿里云会把你包的信息,比如包大小,streamid(相当于包的识别码),版本号发送给你的设备,然后如果你没发送升级成功的包给阿里云,这里会是一直验证中的状态,那你的设备每次重新连接阿里云,发送connect包给阿里云的时候,阿里云就会发送一次这个验证(upgrade)包。提取到了之后就可以发送请求包了,这个请求包比较坑,也可能是我没付费,他的包一次最多只有1024个字节的数据。之前说了,验证状态下,设备发送connect包给阿里云,阿里云就会发一个upgrade包给你。

qq_42585211的博客 1231

PostgreSQL(二十六)分区表管理

范围分区表一般指的一个分区的范围,然后把满足条件的行存放在该分区中。最常见的是以日期做为分区条件,根据时间段分为不同的分区,存放不同时间段的数据。list分区以指定的分区值将数据存放到对应的分区上,然后把满足条件的行存放在该分区中,最常见的是以某列值为分区条件,根据不同的列值存放在不同的分区。hash分区将数据散列存储在各个分区上,以打散热点数据存放到对应的分区上,然后把满足条件的行存放在该分区中,最常见的是平均的把数据放在不同的分区。PG分区下面也可以建立子分区构成联级模式。

shmily_coco的博客 1099

Obsidian+Zotero文献管理终极指南:如何把PDF批注自动同步到笔记库

本文详细介绍了如何通过Obsidian与Zotero的深度集成,实现PDF批注自动同步到笔记库的高效工作流。从工具链配置、批注同步机制到工作流优化,帮助研究者构建智能文献管理系统,提升学术研究效率。特别适合需要处理大量文献的学者和科研人员。

weixin_29306571的博客 597

postgres分区表

表格复制特性声明式分区(推荐)继承分区(旧版)创建方式语法INHERITS+ 约束数据路由自动需触发器/规则维护难度简单复杂性能高(内置优化)较低功能完整(支持子分区等)有限适用场景PostgreSQL 10+ 所有新设计仅兼容旧系统建议:所有新项目都使用声明式分区,它是 PostgreSQL 处理海量数据的标准方案。

leijmdas的专栏 1190

软件测试 | 手把手教你如何使用 Charles 抓包工具(电脑+手机端)

软件测试 | 手把手教你如何使用 Charles 抓包工具(电脑+手机端)一、Charles简介二、Charles下载与安装三、Charles配置(1)Charles-PC端的配置如下:(2)过滤网络请求:(3)Charles抓取HTTPS请求包前置条件:(4)Charles抓取HTTPS双向认证包:关注我,免费领取学习资料 嘿。大家好,我是4U: 做过 APP 测试的同学都会接触抓包工具,通过抓包工具可以获取到客户端与服务端接口的交互数据,在实际测试APP的过程中,我们往往会发现服务端返回 的参数字段错

leboxy的博客 4155

Disk Drill 使用教程

Disk Drill只需单击几下,即可恢复USB闪存驱动器上已删除的数据,包括丢失的照片,文档和其他文件类型。Disk Drill是真正的多合一应用程序,可在Mac上恢复闪存驱动器。Disk Drill将自动应用最佳的笔式驱动器恢复算法来恢复Mac数据。 USB闪存驱动器是一种传输和存储信息的好方法,但是如果错误地丢失或删除了存储在其中的文件,会发生什么呢?当您需要Mac的闪存驱动器数据恢复软件时,Disk Drill是您的最佳选择。Disk Drill将扫描您的USB闪存驱动器(也称为笔式驱动器)并查找

zjj778899的博客 1万+

PostgreSQL 分区表实战——从大表查询 43 秒到 0.3 秒的分区策略迁移

分区键的选择必须从查询模式出发——最重要的判断标准是大多数查询的 WHERE 条件中包含哪个字段的时间范围过滤。在这个系统中,业务查询总是附带created_at时间范围("最近 3 个月"、"本季度"),而很少单独按order_id查询所有历史数据。这使得created_at成为天然的分区键。按月分区的粒度选择是一个权衡。按天分区(365 分区/年)导致分区数过多,PostgreSQL 在规划阶段扫描系统目录(pg_class、pg_inherits)时的开销会增加。

iymei4986533030的博客 8321

从UE4到Unity:双叶高光技术在角色皮肤渲染中的移植全流程

本文详细解析了将UE4中的双叶高光技术移植到Unity引擎的全流程。该技术通过模拟皮肤油脂层和真皮层的不同反射特性,能显著提升角色皮肤渲染的真实感。文章从核心原理、贴图资产处理、Shader代码重构到参数调校,提供了完整的跨引擎移植实战指南,帮助开发者实现高质量的皮肤质感还原。

alice7model的博客 481

Electric分区表同步:Postgres声明式分区支持详解

在处理大规模数据集时,PostgreSQL的声明式分区(Declarative Partitioning)是提升查询性能的关键技术。然而,当需要将分区表数据同步到前端应用或分布式系统时,开发者往往面临三大挑战:**跨分区数据一致性**、**动态分区识别**和**高效变更捕获**。Electric作为专注于PostgreSQL实时数据同步的JavaScript库,通过创新性的分区映射机制和事务重写逻...

gitblog_01065的博客 687

【毕设护航】从0构建YOLOv8犬类识别系统,120种犬类精准检测+可视化界面全流程指南

本文介绍了一个基于YOLOv8的120种犬类目标检测与可视化系统毕业设计项目。该项目使用Stanford Dogs数据集,将图像分类任务转化为目标检测任务。主要内容包括:数据预处理(XML标注转换为YOLO格式)、YOLOv8模型训练与测试(包含训练脚本编写和推理实现),以及基于Tkinter的图形界面开发。该项目技术链条完整,涵盖检测、分割等多任务支持,适合作为计算机视觉方向的毕业设计选题,兼具技术深度和展示效果。

LXY_LOVE36的博客 99

060、模型蒸馏:把扩散模型压进单步的魔法

《端侧文生图模型蒸馏实战:从32步到1步的优化之道》摘要:针对边缘设备部署难题,本文分享了扩散模型蒸馏的核心经验。通过重构损失函数(感知损失+对抗训练+KL散度)、优化采样轨迹截取策略和改造学生模型架构,成功将Stable Diffusion压缩至单步推理。关键发现包括:小批量训练更有效、标签平滑保障生成多样性、双模型策略平衡质量与速度。在Cortex-A53芯片上实现300ms内生成,内存占用控制在200MB以下。特别强调工业部署需权衡压缩率与稳定性,建议保留迭代模式开关以适配不同场景需求。

2601_95651806的博客 152

PostgreSQL分区表在云原生环境中的应用与挑战

本文深入探讨了PostgreSQL分区表在云原生环境中的创新应用与优化策略。通过动态资源调配、智能分层存储和分布式查询优化等核心技术,云原生环境显著提升了分区表的性能与可扩展性。文章结合电商平台等实际案例,展示了分区表在TB级数据处理中的卓越表现,并提供了多级分区、智能剪枝等关键优化方案,助力企业实现高效数据管理。

blue42的博客 853

lwip-2.0.3

lwip是瑞典计算机科学院(SICS)的Adam Dunkels 开发的一个小型开源的TCP/IP协议栈。实现的重点是在保持TCP协议主要功能的基础上减少对RAM 的占用。

PostgreSQL笔记63:分区表最佳实践与避坑指南

技术点说明声明式分区使用定义分区表子分区创建使用分区裁剪验证通过观察是否只扫描目标分区元数据查询通过和查看分区结构技术点说明驱动选择使用pgx作为 PostgreSQL 驱动,支持上下文和原生类型声明式分区与 Node.js 示例相同的 DDL 语句执行计划通过验证分区裁剪元数据查询使用系统表获取分区结构技术点说明驱动选择使用psycopg2作为 PostgreSQL 驱动,成熟稳定事务控制通过简化 DDL 操作执行计划与 Node.js 示例相同的。

Wang的专栏 136

文档:亲测可用解决vnc灰屏,黑屏,鼠标大黑叉情况

主要解决vnc-viewer灰屏,黑屏,鼠标大黑叉等现象,详细见文档。

OV9734 DATASHEET,完整规格书

OV9734完整规格书,包括电气参数,尺寸规格,寄存器配置表等

PostgreSQL - 范围分区与列表分区的实战应用

范围分区与列表分区不是银弹,但在合适场景下,它们能显著提升 PostgreSQL 的性能、可维护性和扩展性。理解业务查询模式合理设计分区键自动化运维流程持续监控与优化通过本文的实战示例和 Java 代码集成,相信你已掌握如何在真实项目中应用这两种分区策略。记住,好的数据库设计始于对数据生命周期的深刻理解。现在,打开你的 PostgreSQL 客户端,尝试为那张臃肿的大表创建第一个分区吧!🎉💬互动话题:你在项目中使用过 PostgreSQL 分区吗?遇到了哪些挑战?欢迎在评论区分享经验!

千淘万漉虽辛苦,吹尽狂沙始到金 2万+

复分析 阿尔福斯(Ahlfors) 第三版答案 英文

答案分为十章,有部分习题没有答案,全英文复分析阿尔福斯(Ahlfors) 第三版答案

pg_partman迁移指南:将现有表转换为分区表的3种终极策略

pg_partman是PostgreSQL的分区管理扩展,它提供了强大的工具来帮助数据库管理员轻松管理分区表。本文将介绍三种将现有表转换为分区表的实用策略,帮助你快速掌握分区表迁移的核心技巧。 ## 策略一:使用CREATE TABLE ... PARTITION BY进行原生声明式分区 PostgreSQL自10版本起支持原生声明式分区,这是创建分区表最直接的方法。通过以下步骤可以将现有表转

gitblog_01122的博客 694
上一篇: PostgreSQL笔记60: 权限与角色管理——从层级体系到最佳实践
下一篇: PostgreSQL笔记62: 分区表维护最佳实践——默认分区、锁策略与性能调优
Wang's Blog
博客等级 码龄12年 7440粉丝 2427原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Wang's Blog

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值