postgresql中水平分片的好方法是什么

发布于 2024-07-24 06:33:23 字数 169 浏览 9 评论 0原文

postgresql 中水平分片的好方法是什么

1. pgpool 2
2. gridsql

这是使用分片的更好方法

是否可以在不更改客户端代码的情况下进行分区

如果有人可以分享如何设置和使用分片的简单教程或食谱示例，那就太好了

原文

what is a good way to horizontal shard in postgresql

1. pgpool 2
2. gridsql

which is a better way to use sharding

also is it possible to paritition without changing client code

It would be great if some one can share a simple tutorial or cookbook example of how to setup and use sharding

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

初懵 2024-07-31 06:33:23

PostgreSQL 允许以两种不同的方式进行分区。一种是按范围，另一种是按列表。两者都使用表继承来进行分区。
按范围（通常是日期范围）分区是最常见的，但如果作为分区的变量是静态且不倾斜的，则按列表分区可能会很有用。

分区是通过表继承完成的，因此要做的第一件事是设置新的子表。

CREATE TABLE measurement (
    x        int not null,
    y        date not null,
    z        int
);

CREATE TABLE measurement_y2006 ( 
    CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2007-01-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2007 (
    CHECK ( logdate >= DATE '2007-01-01' AND logdate < DATE '2008-01-01' ) 
) INHERITS (measurement);

然后需要使用规则或触发器将数据删除到正确的表中。
规则在批量更新时速度更快，在单个更新时触发并且更易于维护。这是一个示例触发器。

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

以及执行插入的触发器函数

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $
BEGIN
    IF ( NEW.logdate >= DATE '2006-01-01' 
         AND NEW.logdate < DATE '2007-01-01' ) THEN
        INSERT INTO measurement_y2006 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2007-01-01' 
            AND NEW.logdate < DATE '2008-01-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.';
    END IF;
    RETURN NULL;
END;
$
LANGUAGE plpgsql;

这些示例是 postgresql 文档的简化版本，以便于阅读。

我不熟悉pgpool2，但gridsql是一个为EnterpriseDB设计的商业产品，EnterpriseDB是一个构建在postgresql之上的商业数据库。他们的产品非常好，但我认为它不适用于标准 postgresl。

PostgreSQL allows partitioning in two different ways. One is by range and the other is by list. Both use table inheritance to do partition.
Partitioning by range, usually a date range, is the most common, but partitioning by list can be useful if the variables that is the partition are static and not skewed.

Partitioning is done with table inheritance so the first thing to do is set up new child tables.

CREATE TABLE measurement (
    x        int not null,
    y        date not null,
    z        int
);

CREATE TABLE measurement_y2006 ( 
    CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2007-01-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2007 (
    CHECK ( logdate >= DATE '2007-01-01' AND logdate < DATE '2008-01-01' ) 
) INHERITS (measurement);

Then either rules or triggers need to be used to drop the data in the correct tables.
Rules are faster on bulk updates, triggers on single updates as well as being easier to maintain. Here is a sample trigger.

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

and the trigger function to do the insert

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $
BEGIN
    IF ( NEW.logdate >= DATE '2006-01-01' 
         AND NEW.logdate < DATE '2007-01-01' ) THEN
        INSERT INTO measurement_y2006 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2007-01-01' 
            AND NEW.logdate < DATE '2008-01-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.';
    END IF;
    RETURN NULL;
END;
$
LANGUAGE plpgsql;

These examples are simplified versions of the postgresql documentation for easier reading.

I am not familiar with pgpool2, but gridsql is a commercial product designed for EnterpriseDB, a commercial database that is built on top of postgresql. Their products are very good, but I do not think that it will work on standard postgresl.

回复收藏 0 原文