在PostgreSQL中水平分割的好方法是什么 [英] what is a good way to horizontal shard in postgresql

问题描述

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

  1。 pgpool 2 
 2. gridsql 
  

这是使用分片的更好方法

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

如果有人可以共享一个简单的教程或食谱，那将是很棒的设置和使用分片的示例

解决方案

PostgreSQL允许以两种不同方式进行分区。一个是按范围，另一个是按列表。两者都使用表继承来进行分区。

按范围进行分区（通常是日期范围）是最常见的方法，但是如果作为分区的变量是静态的且不偏斜，则按列表进行分区会很有用。 / p>

分区是通过表继承完成的，因此首先要做的是建立新的子表。

  CREATE TABLE测量（
x int不为空，
y日期不为空，
z int 
）； 
 
创建表measurement_y2006（
 CHECK（logdate> = DATE'2006-01-01'并且logdate< DATE'2007-01-01'）
）继承（测量）; 
 
创建表measurement_y2007（
 CHECK（logdate> = DATE'2007-01-01'并且logdate< DATE'2008-01-01'）
）继承（测量）; 
  

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

 创建触发器insert_measurement_trigger 
在插入测量
之前，对每个行执行过程Measurement_insert_trigger（）; 
  

并使用触发函数进行插入

 创建或替换功能measurement_insert_trigger（）
返回触发器为$$ 
开始
如果（NEW.logdate> = DATE'2006-01- 01'
 AND NEW.logdate< DATE'2007-01-01'）然后
插入Measurement_y2006值（NEW。*）; 
 ELSIF（NEW.logdate> = DATE'2007-01-01'
 AND NEW.logdate< DATE'2008-01-01'）然后
插入Measurement_y2006m03值（NEW 。*）; 
 ELSE 
上升例外日期超出范围。 
 END IF; 
返回NULL; 
 END； 
 $$ 
语言plpgsql; 
  

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

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

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

解决方案

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.

这篇关于在PostgreSQL中水平分割的好方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！