Postgresql中进行水平分区的正确步骤是什么？

Question

我们有一个带有Postgresql 9.1数据库的电子商务门户网站。一个非常重要的表目前拥有3200万条记录。如果我们想要提供所有项目，该表将增长到3.2亿条记录，其中大部分是日期。这将过于庞大。

因此，我们考虑水平分区/分片。我们可以将该表中的项目分为12个水平块（每月1个）。最佳步骤和技术是什么？在数据库内进行水平分区是否足够好，还是我们必须开始考虑分片？

- Brambo76

Answer 1

虽然三亿两千万并不少，但也不算非常大。这主要取决于你在表上运行的查询。如果你总是在查询中包含分区键，则“常规”分区可能适用。

PostgreSQL wiki中有一个示例：
http://wiki.postgresql.org/wiki/Month_based_partitioning

手册还解释了一些分区的注意事项：
http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html

如果您正在考虑数据分片，可以了解Instagram是如何实现该功能的（它使用的是PostgreSQL）：
http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram

如果您主要使用读取查询，另一个选择可能是使用流复制来设置多个服务器，并通过连接到热备份进行读取访问并连接到主服务器进行写访问。我认为pg-pool II可以在某种程度上自动完成这项工作。这可以与分区结合使用，以进一步减少查询运行时间。

如果您大胆探险，并且没有非常迫切的需求，请考虑Postgres-XC，它承诺支持透明水平扩展：
http://postgres-xc.sourceforge.net/

尚未发布最终版本，但看起来这不会太久。

- user330315

---

仅作为一个数据点，我们的商店在最频繁访问的一张表中有超过3亿行数据，没有进行分区或分片，但一切都运行良好。重新陈述上面的一些内容，使分区变得有价值的关键因素是要有一个常用于限制查询感兴趣行的分区键，并希望定期删除整个分区。（删除一个分区比删除你数据的1/12要快得多。） - kgrittn

Answer 2

如果您不介意升级到PostgreSQL 9.4，那么您可以使用pg_shard扩展程序，它可以让您在许多计算机上透明地分片PostgreSQL表。每个分片都存储为另一个PostgreSQL服务器上的常规PostgreSQL表，并复制到其他服务器。它使用哈希分区来决定哪些分片用于给定查询。如果您的查询具有自然的分区维度（例如，客户ID），则pg_shard将非常有效。

更多信息：https://github.com/citusdata/pg_shard

- Marco Slot

Answer 3

这是我用于分区的示例代码： t_master 是你的应用程序中要选择/插入/更新/删除的视图 t_1 和 t_2 是实际存储数据的底层表。

create or replace view t_master(id, col1)
as 
select id, col1 from t_1
union all
select id, col1 from t_2


CREATE TABLE t_1
(
  id bigint PRIMARY KEY,
  col1 text
);

CREATE TABLE t_2
(
  id bigint PRIMARY KEY,
  col1 text
);



CREATE OR REPLACE FUNCTION t_insert_partition_function()
returns TRIGGER AS $$
begin
raise notice '%s', 'hello';
    execute 'insert into t_'
        || ( mod(NEW.id, 2)+ 1 )
        || ' values ( $1, $2 )' USING NEW.id, NEW.col1 ;
    RETURN NULL;
end;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION t_update_partition_function()
returns TRIGGER AS $$
begin
    raise notice '%s', 'hello';
    execute 'update t_'
        || ( mod(NEW.id, 2)+ 1 )
        || ' set id = $1, col1 = $2 where id = $1' 
        USING NEW.id, NEW.col1 ;
    RETURN NULL;
end;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION t_delete_partition_function()
returns TRIGGER AS $$
begin
    raise notice '%s', 'hello';
    execute 'delete from t_'
        || ( mod(OLD.id, 2)+ 1 )
        || ' where id = $1' 
        USING OLD.id;
    RETURN NULL;
end;
$$
LANGUAGE plpgsql;



CREATE TRIGGER t_insert_partition_trigger instead of INSERT
ON t_master FOR each row 
execute procedure t_insert_partition_function();

CREATE TRIGGER t_update_partition_trigger instead of update
ON t_master FOR each row 
execute procedure t_update_partition_function();

CREATE TRIGGER t_delete_partition_trigger instead of delete
ON t_master FOR each row 
execute procedure t_delete_partition_function();

- TritonHo