分库分表

分库分表都用来处理大型数据集，以提高数据库的性能和可扩展性。

分库（Sharding）

将数据水平拆分到多个数据库实例中，每个实例只存储一部分数据。这样可以减轻单个数据库实例的负载，提升读写性能。
PostgreSQL 本身不直接支持分库，但可以通过一些工具和扩展来实现，如：

Citus：
- Citus 是一个 PostgreSQL 的扩展，可以将数据水平分片（Sharding）到多个 PostgreSQL 节点。Citus 会自动处理数据的分片和查询的路由。
- Citus 官方文档
pg_shardman：
- 另一个用于实现 sharding 的 PostgreSQL 扩展。

水平拆分（Sharding）是指将数据按行拆分成多个部分，每个部分存储在不同的数据库实例中。具体来说，有以下两种方式：

每个库存储特定的几张表（按表水平拆分）：
- 在这种方式下，不同的数据库实例存储不同的表。例如，数据库实例 A 存储用户表（users），数据库实例 B 存储订单表（orders）。
- 优点是每个实例处理的表数量减少，管理和维护较为简单。
- 缺点是如果一个查询需要访问多个表（如 JOIN 操作），可能会涉及多个实例，增加了复杂性和延迟。
每个库存储所有表但每个表里存储特定的数据（按数据水平拆分）：
- 在这种方式下，每个数据库实例包含相同的表结构，但每个表的数据被分割存储。例如，数据库实例 A 存储用户 ID 为 1-1000 的用户数据，数据库实例 B 存储用户 ID 为 1001-2000 的用户数据。
- 优点是查询单个表的数据时可以并行处理，分担了负载。
- 缺点是需要有路由机制来确定查询应发送到哪个数据库实例。

查询路由

要知道查询哪个库，需要一个路由层。路由层的主要任务是根据查询条件，将查询请求路由到正确的数据库实例。实现查询路由的常见方法包括：

应用程序逻辑：
- 在应用程序代码中实现路由逻辑，根据查询条件（如用户 ID、订单 ID 等）计算应该访问哪个数据库实例。
- 例如，如果用户 ID 为 123 的数据应该在实例 B 中，应用程序会将查询路由到实例 B。
中间件：
- 使用专门的中间件（如 Proxy 层）来处理查询路由。这些中间件可以根据查询条件自动将请求转发到合适的数据库实例。
- 常见的中间件有 ProxySQL、Citus（用于 PostgreSQL）等。
数据库扩展：
- 一些数据库扩展或插件提供了内置的分片和路由功能。例如，Citus 是一个 PostgreSQL 的扩展，提供了分片和路由的能力。

分表（Partitioning）

将一张大表拆分成多个小表，这些小表可以存储在同一个数据库实例中。分表可以按行划分（水平分表）或按列划分（垂直分表）。PostgreSQL 主要支持水平分表。按经验看，如果表的尺寸超过了服务器的内存，那么适合划分表
优点

提升查询性能
取代索引的主导列，减少索引压力，分区合理时只访问分区表数据来避免整表索引访问
提升批量操作性能
比如删除分区表的性能要比批量删除快得多，也可以将一个常规表或者别的分区表作为分区加入到另一个分区表里面，也可以将分区表移出来作为普通表
支持指定物理位置
比如访问特别少的分区表可以放到机械盘上，访问多的放到固态上
最佳实践：

我们正在为一个大型的冰淇凌公司构建数据库。该公司每天测量最高温度以及每个区域的冰激凌销售情况。概念上，我们想要一个这样的表：

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
我们知道大部分查询只会访问上周的、上月的或者上季度的数据，因为这个表的主要用途是为管理层准备在线报告。为了减少需要被存放的旧数据量，我们决定只保留最近3年的数据。在每个月的开始我们将去除掉最早的那个月的数据。

创建分区表
创建分区，指定最近日期的分区存储在好的硬盘上（TABLESPACE fasttablespace）
根据日期创建索引
定时删除不需要的分区

PostgreSQL 支持多种分表方式，包括范围分区（Range Partitioning）、列表分区（List Partitioning）和哈希分区（Hash Partitioning）。以下是一些常用的分表方法：

范围分区（Range Partitioning）：

按照数据的范围进行分区，例如按日期、ID 等。

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL
) 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');

列表分区（List Partitioning）：

按照特定的值进行分区，例如按地区、类别等。

CREATE TABLE customers (
    customer_id INT,
    name TEXT,
    region TEXT
) PARTITION BY LIST (region);

CREATE TABLE customers_us PARTITION OF customers
    FOR VALUES IN ('US');

CREATE TABLE customers_eu PARTITION OF customers
    FOR VALUES IN ('EU');

哈希分区（Hash Partitioning）：

通过哈希函数将数据分散到不同的分区中。

CREATE TABLE logs (
    log_id SERIAL,
    message TEXT,
    log_time TIMESTAMP
) PARTITION BY HASH (log_id);

CREATE TABLE logs_part1 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE logs_part2 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE logs_part3 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE logs_part4 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

对已有数据分表

对一个已有的大表进行水平分表操作时，需要执行一些步骤将数据从原表迁移到分区表中，并确保查询能够正确访问这些分区表。
1.创建父表及分区表
2.迁移数据
3.删除旧表数据

假设你有一个大表 orders，其中已经存储了数据。你希望对这个表按日期进行水平分表。

1. 创建父表和分区表

首先，创建一个分区表结构：

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL
) 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');

2. 将已有数据插入到分区表中

自动插入：

INSERT INTO orders
SELECT * FROM orders；

手动插入：
对于已经存在于 orders 表中的数据，你需要将其移动到合适的分区中。可以使用 INSERT INTO ... SELECT ... 语句来实现：

INSERT INTO orders_2023
SELECT * FROM orders
WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

INSERT INTO orders_2024
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

3. 删除原始数据

在确保数据已经正确地移动到分区表之后，可以删除原始表中的数据：

DELETE FROM orders
WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

DELETE FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

4. 确保查询正确路由

在使用分区表后，查询将自动根据分区策略路由到正确的分区表中。你可以像以前一样查询 orders 表：

SELECT * FROM orders WHERE order_date = '2023-06-15';

PostgreSQL 将自动路由这个查询到 orders_2023 分区。
插入，删除，修改也是一样的

注意事项

触发器：如果你是自定义的分区，那么可以使用触发器，来自动将数据插入正确的分区，而不需要手动插入和删除。例如，可以创建一个 BEFORE INSERT 触发器，在插入新数据时自动路由到正确的分区。

CREATE OR REPLACE FUNCTION orders_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF (NEW.order_date >= '2023-01-01' AND NEW.order_date < '2024-01-01') THEN
        INSERT INTO orders_2023 VALUES (NEW.*);
    ELSIF (NEW.order_date >= '2024-01-01' AND NEW.order_date < '2025-01-01') THEN
        INSERT INTO orders_2024 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range. Fix the orders_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_orders_trigger
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION orders_insert_trigger();

性能：在分区表创建后，查询性能会显著提升，尤其是涉及到分区键的查询，因为数据库只需要扫描相关的分区表。
维护：定期维护分区表，例如添加新的分区和归档旧的分区，是分区表管理的重要部分。