PostgreSQL 的分区 (Partitioning) 是如何实现的？

推荐答案

PostgreSQL 的分区是通过表继承和触发器或声明式分区来实现的。以下是两种主要的分区实现方式：

1. 表继承与触发器：

   • 创建一个父表，然后创建多个子表，每个子表继承父表的结构。
   • 使用触发器将数据插入到父表时，根据某些条件将数据路由到相应的子表。
   • 这种方式需要手动管理分区和触发器。

2. 声明式分区：

   • 使用 CREATE TABLE 语句中的 PARTITION BY 子句来定义分区表。
   • 可以指定分区键和分区策略（如范围分区、列表分区、哈希分区等）。
   • PostgreSQL 会自动管理分区的创建和数据路由。

本题详细解读

表继承与触发器

1. 创建父表：

   CREATE TABLE parent_table (
       id SERIAL PRIMARY KEY,
       created_at TIMESTAMP NOT NULL,
       data TEXT
   );

2. 创建子表：

   CREATE TABLE child_table_2023_01 () INHERITS (parent_table);
   CREATE TABLE child_table_2023_02 () INHERITS (parent_table);

3. 创建触发器函数：

   // www.javascriptcn.com code example
   CREATE OR REPLACE FUNCTION insert_into_child_table()
   RETURNS TRIGGER AS $$
   BEGIN
       IF NEW.created_at >= '2023-01-01' AND NEW.created_at < '2023-02-01' THEN
           INSERT INTO child_table_2023_01 VALUES (NEW.*);
       ELSIF NEW.created_at >= '2023-02-01' AND NEW.created_at < '2023-03-01' THEN
           INSERT INTO child_table_2023_02 VALUES (NEW.*);
       ELSE
           RAISE EXCEPTION 'Date out of range';
       END IF;
       RETURN NULL;
   END;
   $$ LANGUAGE plpgsql;

4. 创建触发器：

   CREATE TRIGGER insert_into_child_table_trigger
   BEFORE INSERT ON parent_table
   FOR EACH ROW EXECUTE FUNCTION insert_into_child_table();

声明式分区

1. 创建分区表：

   CREATE TABLE partitioned_table (
       id SERIAL,
       created_at TIMESTAMP NOT NULL,
       data TEXT
   ) PARTITION BY RANGE (created_at);

2. 创建分区：

   CREATE TABLE partitioned_table_2023_01 PARTITION OF partitioned_table
       FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
   
   CREATE TABLE partitioned_table_2023_02 PARTITION OF partitioned_table
       FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

3. 插入数据：

   INSERT INTO partitioned_table (created_at, data) VALUES ('2023-01-15', 'Data for January');
   INSERT INTO partitioned_table (created_at, data) VALUES ('2023-02-15', 'Data for February');

总结

• 表继承与触发器：需要手动管理分区和触发器，适合复杂的分区逻辑。
• 声明式分区：自动管理分区和数据路由，适合简单的分区需求。