文档章节

在Postgres中处理海量数据,使用分区[翻译]

MtrS
 MtrS
发布于 2016/09/15 16:27
字数 2578
阅读 88
收藏 0

2016/13/09 Silkaitis@Heroku 翻译: 小次郎@飞象

原文链接

我们发现个一个有趣的模式就是,在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列作为键来创建分区,

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

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

sushi::DATABASE=> SELECT run_maintenance();

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

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

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

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

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

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

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

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

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

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

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

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

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

译者注: 表分区模块

© 著作权归作者所有

MtrS
粉丝 36
博文 699
码字总数 419371
作品 0
榆林
私信 提问
数据库案例集锦 - 开发者的《如来神掌》

标签 PostgreSQL , PG DBA cookbook , PG Oracle兼容性 , PG 架构师 cookbook , PG 开发者 cookbook , PG 应用案例 背景 「剑魔独孤求败,纵横江湖三十馀载,杀尽仇寇,败尽英雄,天下更无抗...

德哥
2017/06/09
0
0
PostgreSQL pgbench tpcb 海量数据库测试 - 分区表测试优化

标签 PostgreSQL , pgbench , tpcb 背景 pgbench是PG的一款测试工具,内置的测试CASE为tpcb测试。同时支持用户自己写测试CASE。 大量自定义CASE参考 https://github.com/digoal/blog/blob/ma...

德哥
04/14
0
0
PostgreSQL 类微博FEED系统 - 设计与性能指标

标签 PostgreSQL , feed , 微博 , 推送 , 分区 , 分片 , UDF , 挖掘 , 文本挖掘 背景 类微博系统,最频繁用到的功能: 之前写过一篇《三体高可用PCC大赛 - facebook微博 like场景 - 数据库设...

德哥
2018/04/18
0
0
PostgreSQL 生成空间热力图

标签 PostgreSQL , 热力图 , 空间切割 , 并行计算 , parallel safe 背景 结合空间数据,计算基于地理位置信息的热力图,在空间数据可视化场景中是一个非常常见的需求。 结合流计算,可以实现...

德哥
2018/10/05
0
0
PostgreSQL 12 正式发布

PostgreSQL 12 已经发布,该版本在各方面都得到了加强,包括显著地提升查询性能,特别是对大数据集,总的空间利用率方面。 这个版本为应用程序开发人员提供了更多的功能,比如对 SQL/JSON 路...

afterer
10/04
13.8K
65

没有更多内容

加载失败,请刷新页面

加载更多

Mybatis Plus删除

/** @author beth @data 2019-10-17 00:30 */ @RunWith(SpringRunner.class) @SpringBootTest public class DeleteTest { @Autowired private UserInfoMapper userInfoMapper; /** 根据id删除......

一个yuanbeth
今天
4
0
总结

一、设计模式 简单工厂:一个简单而且比较杂的工厂,可以创建任何对象给你 复杂工厂:先创建一种基础类型的工厂接口,然后各自集成实现这个接口,但是每个工厂都是这个基础类的扩展分类,spr...

BobwithB
今天
5
0
java内存模型

前言 Java作为一种面向对象的,跨平台语言,其对象、内存等一直是比较难的知识点。而且很多概念的名称看起来又那么相似,很多人会傻傻分不清楚。比如本文我们要讨论的JVM内存结构、Java内存模...

ls_cherish
今天
4
0
友元函数强制转换

友元函数强制转换 p522

天王盖地虎626
昨天
5
0
js中实现页面跳转(返回前一页、后一页)

本文转载于:专业的前端网站➸js中实现页面跳转(返回前一页、后一页) 一:JS 重载页面,本地刷新,返回上一页 复制代码代码如下: <a href="javascript:history.go(-1)">返回上一页</a> <a h...

前端老手
昨天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部