如何在Postgres中处理海量数据以及使用分区

这篇文章将为大家详细讲解有关如何在Postgres中处理海量数据以及使用分区，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

成都创新互联成都网站建设按需定制制作，是成都网站制作公司,为成都阳台护栏提供网站建设服务,有成熟的网站定制合作流程，提供网站定制设计服务：原型图制作、网站创意设计、前端HTML5制作、后台程序开发等。成都网站制作热线：13518219792

我们发现个一个有趣的模式就是，在Postgres数据库集群中会存在一、两个表以较快的速度增长, 以量化的方式表示就是数GB或者数TB级别的大表。

通常情况下，这些表中存储的数据通常是应用程序中的事件跟踪数据，或者是应用程序日志数据

这种规模的表在数据存储上不是问题,但是存在其他方面的问题：

查询性能的降低，更新索引变得缓慢。
维护时间变长，VACUUM(表空间整理)
你需要配置数据在应用中的使用

在数据量随着时间增长时, 通过使用Postgres表分区可以保证较高的查询性能, 并且不需要将数据拆分保存在不同的数据存储区域。

在我们的平台中我们使用了pg_partman来维护表分区, (广告:)Heroku 平台拥有Heroku Postgres，Heroku redis，Heroku Kafka 存储编排服务

在我们的控制平台中，我们拥有一个表，表中存储的数据是每个人的数据存储的状态变化记录,在几个星期以后,我们不需要使用这些信息,

这时，我们使用表分区来完成这样的动作,在两星期以后，我们就可以快速删除这些表，并且，在此期间其他查询语句的速度不会受到影响。

要了解在大数据量的情况下,Postgre如何保证较高的性能, 我们需要Postgres 内部是如何如何使用继承, 如何手动设置表分区，学会使用 Postgres 扩展模块，pg_partman, 你可以学到更多Postgre分区设置和维护方法。

第一, 继承

Postgres 具有对表分区通过表继承的基本支持。 Postgres表继承和面向对象中继承的概念一样。表据说是从另一个继承，当它保持相同的数据定义和接口。 Postgres 中实现表继承已经很长时间了,这一功能也比较成熟，

看一下在我们的案例中一个表继承是如何实现的:

CREATE TABLE products (
    id BIGSERIAL,
    price INTEGER
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ
);

CREATE TABLE books (
    isbn TEXT,
    author TEXT,
    title TEXT
) INHERITS (products);

CREATE TABLE albums (
    artist TEXT,
    length INTEGER,
    number_of_songs INTEGER
) INHERITS (products);

在此示例中，产品衍生出书籍和唱片, 这意味着如果一个记录被插入到书表，它将有所有相同特性的产品表，再加上那书表。如果对产品表发出的查询，该查询将参考产品表，再加上它的所有子信息。

对于此示例，该查询将参考产品、书籍和唱片。

这是在 Postgres 的默认行为。但是，您也可以发出对里面每个子表去做单独的查询

第二部设置手动分区

现在，我们有一个把握上继承在 Postgres，我们将设置手动分区。

分区的基本前提条件是主表存在从继承的所有其他的孩子。

我们将使用短语子表和分区交替整个安装过程的其余部分。

活数据不应该存储在主表上. 相反，当数据往主表写入的时候，数据需要重定向到适当的子分区表中去, 此重定向操作通常是使用Postgres触发器来实现的。最重要的是，检查约束放在每一个子表，这样，如果直接在子表中插入恰当的数据,则将插入成功。如果数据不属于分区,那么就不会存入该分区表中去。

做表分区,你需要选择一个键来决定如何区分分区信息，让我们将我们的 Postgres 数据库中一个非常大型的活动表分区的过程。对于一个事件表，时间是确定如何拆分出信息的关键。让我们假定，我们事件表获取 1000 万插入在任何给定的天完成，下面是我们原先的事件表架构︰

CREATE TABLE events (
    uuid text,
    name text,
    user_id bigint,
    account_id bigint,
    created_at timestamptz
);

让我们做几个更多的假设来证明该示例。针对事件表运行的聚合查询仅有每一天的时间框架。这意味着我们聚合分手小时为任何给定的一天。我们使用事件表中的数据只有跨越了几天。之后那个时候，我们不要再查询数据。最重要的是，我们有 1000 万的事件生成的一天。

鉴于这些存在的假设，我们有理由来创建日常分区。我们使用表中数据的创建时间作为键值来对数据进行分区(例如 created_at)

CREATE TABLE events (
    uuid text,
    name text,
    user_id bigint,
    account_id bigint,
    created_at timestamptz
);

CREATE TABLE events_20160801 ( 
    CHECK (created_at >= '2016-08-01 00:00:00' AND created_at < '2016-08-02 00:00:00')  
) INHERITS (events);

CREATE TABLE events_20160802 ( 
    CHECK (created_at >= '2016-08-02 00:00:00' AND created_at < '2016-08-03 00:00:00')   
) INHERITS (events);

我们的主表定义为事件表，有两个子表用来存储接受到的数据, events_20160801 和 events_20160802。

我们也把它们以确保唯一数据的那一天结束在该分区上的 CHECK 约束。现在我们需要创建一个触发器，以确保在主表中输入任何数据都能够去寻找正确的分区︰

CREATE OR REPLACE FUNCTION event_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.created_at >= '2016-08-01 00:00:00'AND
         NEW.created_at < '2016-08-02 00:00:00' ) THEN
        INSERT INTO events_20160801 VALUES (NEW.*);
    ELSIF ( NEW.created_at >= '2016-08-02 00:00:00'AND
         NEW.created_at < '2016-08-03 00:00:00' ) THEN
        INSERT INTO events_20160802 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the event_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_event_trigger
    BEFORE INSERT ON event
    FOR EACH ROW EXECUTE PROCEDURE event_insert_trigger();

太好了！分区创建,触发功能定义,并且触发器已添加到事件表。

在这一点上，我的应用程序可以插入事件表上的数据和数据可以定向到适当的分区。

那么问题来了,利用手工操作表分区,会存在很多的失误, 需要我们自己每次都去手动更新分区,创建触发器，

我们还没谈论到尚未从数据库中删除旧数据。这样引入了pg_partman .

第三部使用pg_partman来实现

Postgres 使用 pg_partman，使使得表分区的管理更加简便(相较于手动创建分区)　让我们通过一个实例，这样做从零开始运行︰

首先，让我们来加载扩展和创建我们的事件表。如果你已经有一个大的表定义，pg_partman 文件具有指导意义，如何将该表格转换成一种使用表分区。

$ heroku pg:psql -a sushi
sushi::DATABASE=> CREATE EXTENSION pg_partman;
sushi::DATABASE=> CREATE TABLE events (
  id bigint,
  name text,
  properities jsonb,
  created_at timestamptz
);

继续使用我们的假设，我们早些时候所作的事件数据。我们每天产生1000万的事件，我们的查询聚合是以天为单位的。鉴于此，我们要创建按天来创建分区。

sushi::DATABASE=> SELECT create_parent('public.events', 'created\_at', 'time', 'daily');

此命令告诉 pg_partman，使用数据表的create_at列作为键来创建分区，

到这里存在的另一个问题是,这一创建分区的命令需要手动执行。

目前尚未实现数据库的定期分区维护,创建新的表分区，转移相关数据。

sushi::DATABASE=> SELECT run_maintenance();

run_maintenance() 命令将指示 pg_partman 仔细看看所有被分区的表，并确定是否需要创建新的分区和摧毁旧分区。无论应销毁一个分区或保留选项是否确定。这个命令都将在终端命令行执行。

我们需要设置定时任务,使用Heroku调度程序来完成这项任务,可以达到目的。此命令将运行每小时检查一次数据库表分区,检查分区和分区创建都在命令中，

Heroku 调度程序是一个很高效率的服务, 每小时一次的执行频率并不会对数据库产生明显的性能影响.

就是这个,我们已经在Postgres中配置了表分区,它将只是在后端做很少的维护动作,

pg_partman的安装过程(我们目前所做的)只是表象。

想要了解pg_partman 的更多细节的话,可以查看该拓展模块的相关文档.

###第四部分重要的疑问:我需要使用表分区吗?

表分区允许你打破了一个非常大的表成许多较小的表，来获得较高的性能提升.

正如在 '手动设置分区部分章节' 指出，许多挑战存在时试图创建并使用表分区自己

使用　pg_partman 可以减轻这种业务负担。

尽管如此，表分区不是你解决一切问题的首选方案. 应该问一些其他问题来确定使用表分区解决该是否合理︰

你有一个足够大的数据集存储在一个表中, 他随着事件显著增长么?
数据是不可变的么?不可变是指,将它首次插入之后没有更新操作么?
你在索引方面是否进行过优化?
经过一段时间以后的数据还有价值么?
存在小范围的数据查询么?
可以将大规模的数据归档到一种廉价的存储介质上去么? 或者旧的数据需要做"聚合"或"汇总"计算么？

如果你对所有这些问题的回答是，你可以使用表分区。总的来说,表分区需要你评估如何使用你的数据, 从大架构的设计角度使用和优化去考虑它表分区的使用需要你提前规划,并考虑您的使用模式。只要你考虑到了这些因素,使用表分区会对你的应用性能提升带来极大的帮助.

关于如何在Postgres中处理海量数据以及使用分区就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

文章标题：如何在Postgres中处理海量数据以及使用分区
网页地址：http://myzitong.com/article/gshjjc.html

如何在Postgres中处理海量数据以及使用分区

第一, 继承

第二部 设置手动分区

第三部 使用pg_partman来实现

其他资讯

第二部设置手动分区

第三部使用pg_partman来实现