百度360必应搜狗淘宝本站头条
nodejsbufferorg.jsoupsetnocountoncv2.bitwise_nottime.strptime
当前位置：网站首页 > 编程字典 > 正文

PostgreSQL分区表（postgresql）

toyiye 2024-07-15 01:14 12 浏览 0 评论

PostgreSQL是通过表继承来实现分区表的。表分区就是把逻辑上的一个大表分割成物理上的几个小块。分区可以提供若干好处:

  • 删除历史数据时更快，如果是按时间分区的，在删除历史数据时，直接删除历史分区就可以了，如果没有分区，通过DELETE删除历史数据时会很慢，还容易导致VACUUM超载；
  • 某些类型的查询性能可以得到极大提升，特别时表中访问率较高的行位于一个单独分区或少数几个分区上的情况下。如果在按时间分布的表中，大多数查询发生在时间最近的一个分区或几个 分区中，而较早时间分区比较少查询。那么，在建分区表后，各个分区表会有各自的索引，使用率较高的分区表的索引就可能完全缓存在内存中，这样效率就会高很多；
  • 当查询或更新一个分区的大部分记录时，连续扫描那个分区而不是使用索引离散地访问整个表，可以获得巨大的性能提升；
  • 很少用到的历史数据可以使用表空间的技术移动到便宜一些的慢速存储介质上。因为使用分区表可以将不同的分区安置在不同的物理介质上。

多大数据该使用分区表？一般取决于具体的应用，不过也有个基本的简单原则，即表的大小超过了数据库服务器的物理内存大小则应该使用。

在使用继承实现的分区表时，一般会让父表为空，数据都存储在子表中。

建分区表的步骤如下：

1.创建父表，所有分区都从它继承。这个表中没有数据，不要在这个表上定义任何检查约束，除非你希望约束所有分区。同意，在其上定义任何索引或唯一约束也没有意义。

2.创建几个子表，每个表都是从主表上继承。通常，这些表不会增加任何字段。我们把子表称作分区，实际上他们就是普通的PostgreSQL表。

3.给分区表增加约束，定义每个分区的键值。

4.对于每个分区，在关键字字段上创建一个索引，也可创建其他你想创建的索引。严格来说，关键字字段索引并非必须的，但是在大多数情况下它是很有帮助的。如果你希望关键字值是唯一的，那么应该总是给每个分区创建一个唯一约束或主键约束。

5.定义一个规则或者触发器，把对主表的数据插入重定向到合适的分区表。

6.确保constraint_exclusion里的配置参数postgresql.conf是打开的。打开后，如果查询中WHERE子句过滤条件与分区的约束条件匹配，那么这个查询会智能地只查询这个分区，而不会查询其他分区。

实现分区表的例子

假设有一张销售明细表，定义如下:
create table sales_detail(
    product_id int not null,  -- 产品编号
    price      numeric(12,2), -- 单价
    amount     int not null,  -- 数量
    sale_date  date not null, -- 销售日期
    buyer      varchar(40),   -- 买家名称
    buyer_contact text        -- 买家联系方式
);

先建主表，主表就是上面的sales_detail表。现在按销售日期进行分区，每个月一个分区，建各个分区的语句如下:
create table sales_detail_y2021m01(CHECK (sale_date >= DATE '2021-01-01' AND sale_date < DATE '2021-02-01')) INHERITS(sales_detail);

create table sales_detail_y2021m02(CHECK (sale_date >= DATE '2021-02-01' AND sale_date < DATE '2021-03-01')) INHERITS(sales_detail);

create table sales_detail_y2021m03(CHECK (sale_date >= DATE '2021-03-01' AND sale_date < DATE '2021-04-01')) INHERITS(sales_detail);

create table sales_detail_y2021m04(CHECK (sale_date >= DATE '2021-04-01' AND sale_date < DATE '2021-05-01')) INHERITS(sales_detail);

每个分区实际上都是一张完整的表，只不过他们是从sales_detail表继承定义的。父表sales_detail中实际是不存数据的。以后若要删除旧数据，只需要删除最早月份的表即可。不知大家是否注意到了每个分区表中都加了一个约束，这表示只允许插入本月内的数据。

一般情况下，还可以在分区键"sale_date"上建索引:
CREATE INDEX sale_detail_y2021m01_sale_date ON sales_detail_y2021m01 (sale_date);
CREATE INDEX sale_detail_y2021m02_sale_date ON sales_detail_y2021m02 (sale_date);
CREATE INDEX sale_detail_y2021m03_sale_date ON sales_detail_y2021m03 (sale_date);
CREATE INDEX sale_detail_y2021m04_sale_date ON sales_detail_y2021m04 (sale_date);

当然如果有需要，还可以在其他字段上建索引。
目前还有一个插入数据的问题没有解决，在往sale_detail表中插入数据时，怎么能自动且正确地把数据插入到正确的分区呢？可能有人已经想到了，使用触发器，那么，接下来就建一个触发器：
CREATE OR REPLACE FUNCTION sale_detail_insert_trigger()
RETURNS TRIGGER AS $
BEGIN
    IF (NEW.sale_date >= DATE '2021-01-01' AND NEW.sale_date < DATE '2021-02-01') THEN
        INSERT INTO sales_detail_y2021m01 VALUES(NEW.*);
    ELSIF (NEW.sale_date >= DATE '2021-02-01' AND NEW.sale_date < DATE '2021-03-01') THEN
        INSERT INTO sales_detail_y2021m02 VALUES(NEW.*);
    ELSIF (NEW.sale_date >= DATE '2021-03-01' AND NEW.sale_date < DATE '2021-04-01') THEN
        INSERT INTO sales_detail_y2021m03 VALUES(NEW.*);
    ELSIF (NEW.sale_date >= DATE '2021-04-01' AND NEW.sale_date < DATE '2021-05-01') THEN
        INSERT INTO sales_detail_y2021m04 VALUES(NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range. Fix the sale_detail_insert_trigger () function!';
        END IF;
        RETURN NULL;
END;
$
LANGUAGE plpgsql;

CREATE TRIGGER insert_sale_detail_trigger
    BEFORE INSERT ON sales_detail
    FOR EACH ROW EXECUTE PROCEDURE sale_detail_insert_trigger();

至此分区表就建好了。

试试插入数据
insert into sales_detail values(1,43.12,1,date '2021-01-02','塞尔达','海拉鲁');
insert into sales_detail values(2,8.88,1,date '2021-03-07','林克','海拉鲁保安部');
insert into sales_detail values(3,28.88,1,date '2021-04-15','达尔克尔','咚鼓族');
testdb=# select * from sales_detail;
 product_id | price | amount | sale_date  |  buyer   | buyer_contact
------------+-------+--------+------------+----------+---------------
          1 | 43.12 |      1 | 2021-01-02 | 塞尔达   | 海拉鲁
          2 |  8.88 |      1 | 2021-03-07 | 林克     | 海拉鲁保安部
          3 | 28.88 |      1 | 2021-04-15 | 达尔克尔 | 咚鼓族
          
testdb=# insert into sales_detail values(4,7.99,1,date '2021-05-02','米法','卓尔领地');
ERROR:  Date out of range. Fix the sale_detail_insert_trigger () function!
CONTEXT:  PL/pgSQL function sale_detail_insert_trigger() line 12 at RAISE

不过需要注意的是，上面的分区表只包括2021年1月到4月的分区表，如果日期到达2021年5月1日，再往表sale_detail中插入数据，就会报错，所以在2021年5月之前，就应该把所有新的分区表加上去，同时还要改调触发器中的内容。在PostgreSQL中，删除表sales_detail_y2021m01并不会导致触发器函数失效。
验证如下:
drop table sales_detail_y2021m01;
insert into sales_detail values(4,7.99,1,date '2021-04-08','米法','卓尔领地');
可以看出，分区表是使用触发器来把插入的数据重新定位到相应的分区中，实际上可以使用PostgreSQL中的"规则”来实现上面的函数触发器功能。
连接一个新库chinese，然后创建主表sales_detail，以及子表sales_detail_y2021m01 sales_detail_y2021m02 sales_detail_y2021m03等。

CREATE RULE sales_detail_insert_y2021m01 AS ON INSERT TO sales_detail WHERE ( sale_date >= DATE '2021-01-01' AND  sale_date < DATE '2021-02-01') DO INSTEAD INSERT INTO sales_detail_y2021m01 VALUES(NEW.*);

CREATE RULE sales_detail_insert_y2021m02 AS ON INSERT TO sales_detail WHERE ( sale_date >= DATE '2021-02-01' AND  sale_date < DATE '2021-03-01') DO INSTEAD INSERT INTO sales_detail_y2021m02 VALUES(NEW.*);

CREATE RULE sales_detail_insert_y2021m03 AS ON INSERT TO sales_detail WHERE ( sale_date >= DATE '2021-03-01' AND  sale_date < DATE '2021-04-01') DO INSTEAD INSERT INTO sales_detail_y2021m03 VALUES(NEW.*);

CREATE RULE sales_detail_insert_y2021m04 AS ON INSERT TO sales_detail WHERE ( sale_date >= DATE '2021-04-01' AND  sale_date < DATE '2021-05-01') DO INSTEAD INSERT INTO sales_detail_y2021m04 VALUES(NEW.*);

但该规则有如下的缺点:
1.相比于触发器，该规则有显著的开销，而且每次检查时都会有此开销。不过，批量插入时只会有一次开销，所以再批量插入的情况下，其相对于触发器更有优势。然而再更多的情况下，触发器的方法更好一些；
2.如果想用COPY插入数据，由于COPY不会触发"规则"，因此先得把要复制得数据直接COPY到分区表(而不是主表)。不过，COPY时回触发触发器得，所以用触发器得方法就可以正常使用；
3.如果插入数据时在规则设置范围之外得，比如2021年5月1日之后得数据，那么将会插入到主表中。如果此时希望直接报错，而不是把数据插入到主表中，使用规则时无法实现的。

insert into sales_detail values(1,43.12,1,date '2021-01-02','塞尔达','海拉鲁');
insert into sales_detail values(2,8.88,1,date '2021-03-07','林克','海拉鲁保安部');
insert into sales_detail values(3,28.88,1,date '2021-04-15','达尔克尔','咚鼓族');
chinese=# select * from sales_detail;
 product_id | price | amount | sale_date  |  buyer   | buyer_contact
------------+-------+--------+------------+----------+---------------
          1 | 43.12 |      1 | 2021-01-02 | 塞尔达   | 海拉鲁
          2 |  8.88 |      1 | 2021-03-07 | 林克     | 海拉鲁保安部
          3 | 28.88 |      1 | 2021-04-15 | 达尔克尔 | 咚鼓族
          

insert into sales_detail values(4,7.99,1,date '2021-05-02','米法','卓尔领地');
chinese=# select * from sales_detail;
 product_id | price | amount | sale_date  |  buyer   | buyer_contact
------------+-------+--------+------------+----------+---------------
          4 |  7.99 |      1 | 2021-05-02 | 米法     | 卓尔领地
          1 | 43.12 |      1 | 2021-01-02 | 塞尔达   | 海拉鲁
          2 |  8.88 |      1 | 2021-03-07 | 林克     | 海拉鲁保安部
          3 | 28.88 |      1 | 2021-04-15 | 达尔克尔 | 咚鼓族
          
chinese=# select * from sales_detail_y2021m01;
 product_id | price | amount | sale_date  | buyer  | buyer_contact
------------+-------+--------+------------+--------+---------------
          1 | 43.12 |      1 | 2021-01-02 | 塞尔达 | 海拉鲁
(1 row)

chinese=# select * from sales_detail_y2021m02;
 product_id | price | amount | sale_date | buyer | buyer_contact
------------+-------+--------+-----------+-------+---------------
(0 rows)

chinese=# select * from sales_detail_y2021m03;
 product_id | price | amount | sale_date  | buyer | buyer_contact
------------+-------+--------+------------+-------+---------------
          2 |  8.88 |      1 | 2021-03-07 | 林克  | 海拉鲁保安部
(1 row)

chinese=# select * from sales_detail_y2021m04;
 product_id | price | amount | sale_date  |  buyer   | buyer_contact
------------+-------+--------+------------+----------+---------------
          3 | 28.88 |      1 | 2021-04-15 | 达尔克尔 | 咚鼓族
(1 row)
查看主表和子表，可以看出5月份的数据，实际是插入到了主表里。

分区优化的技巧

打开约束排除(constraint_exclusion)是一种查询优化技巧，它改进了用上面的方法定义的表分区的性能。在PostgreSQL9.2.4中，参数"constraint_exclusion"默认就是"partition",如果采用默认值，在SQL查询中将WHERE语句的过滤条件与表上的CHECK条件进行对比，就可得知不需要扫描的分区，从而跳过相应的分区表，性能也就得到了提高。

testdb=# explain select count(*) from sales_detail where sale_date >= DATE '2021-03-01';
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=32.90..32.91 rows=1 width=8)
   ->  Append  (cost=0.00..32.19 rows=287 width=0)
         ->  Seq Scan on sales_detail sales_detail_1  (cost=0.00..0.00 rows=1 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
         ->  Seq Scan on sales_detail_y2021m03 sales_detail_2  (cost=0.00..15.38 rows=143 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
         ->  Seq Scan on sales_detail_y2021m04 sales_detail_3  (cost=0.00..15.38 rows=143 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
(8 rows)
这里将WHERE条件中的"sale_date >= DATE '2021-03-01'"与各个分区子表上的CHECK条件进行对比，就知道了只需要扫描主表和sales_detail_y2021m03 sales_detail_y2021m04，而不需要扫描其他的分区子表。

如果把参数"constraint_exclusion"设置成off,则会扫描每张分区子表，如下:
testdb=# set constraint_exclusion='off';
SET
testdb=# explain select count(*) from sales_detail where sale_date >= DATE '2021-03-01';
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=67.43..67.44 rows=1 width=8)
   ->  Append  (cost=0.00..65.97 rows=586 width=0)
         ->  Seq Scan on sales_detail sales_detail_1  (cost=0.00..1.54 rows=14 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
         ->  Seq Scan on sales_detail_y2021m01 sales_detail_2  (cost=0.00..15.38 rows=143 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
         ->  Seq Scan on sales_detail_y2021m02 sales_detail_3  (cost=0.00..15.38 rows=143 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
         ->  Seq Scan on sales_detail_y2021m03 sales_detail_4  (cost=0.00..15.38 rows=143 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
         ->  Seq Scan on sales_detail_y2021m04 sales_detail_5  (cost=0.00..15.38 rows=143 width=0)
               Filter: (sale_date >= '2021-03-01'::date)
(12 rows)

相关推荐

Asterisk-ARI对通道中的DTMF事件处理

Asterisk通道中关于DTMF处理是一个非常重要的功能。通过DTMF可以实现很多的业务处理。现在我们介绍一下关于ARI对通道中的DTMF处理，我们通过自动话务员实例来说明Asterisk如何创建一...

PyQt5 初次使用（pyqt5下载官网）

本篇文章默认已安装Python3，本篇文章默认使用虚拟环境。安装pipinstallPyQt5PyQt一些图形界面开发工具QtDesigner、国际化翻译工具Liguist需要另外...

Qt开发，使用Qt for Python还是Qt C++ Qt开发，使用Qt for

Qt开发使用QtforPython还是QtC++？1.早些年写过一个PyQt5的项目，最近几年重构成QtC++了，其中有个人原因，如早期代码写得烂，...

最简单方法!!用python生成动态条形图

最近非常流行动态条形图，在B站等视频网站上，此类视频经常会有上百万的播放量，今天我们通过第三方库：bar_chart_race（0.2版本）来实现动态条形图的生成；生成的效果如图：问题：...

Asterisk通道和ARI接口的通信（aau通道数）

Asterisk通道和ARI详解什么是通道Asterisk中，通道是介于终端和Asterisk自己本身的一个通信媒介。它包含了所有相关信息传递到终端，或者从终端传递到Asterisk服务器端。这些信...

Python GUI-长链转短链（长链接转化成短链接java）

当我们要分享某一个链接给别人，或是要把某个链接放入帖子中时，如果链接太长，则会占用大量空间，而且很不美观。这时候，我们可以结束长链转短链工具进行转换。当然可以直接搜索在线的网站进行转换，但我们可以借此...

Python 的hash 函数（python的hash函数）

今天在看python的hash函数源码的时候，发现针对不同的数据类型python实现了不同的hash函数，今天简单介绍源码中提到的hash函数。（https://github.com/pyth...

8款Python GUI开源框架，谁才是你的菜?

作为Python开发者，你迟早都会用到图形用户界面来开发应用。本文千锋武汉Python培训小编将推荐一些PythonGUI框架，希望对你有所帮助。1、Python的UI开发工具包Kivy...

python适合开发桌面软件吗?（python可不可以开发桌面应用软件）

其实Python/Java/PHP都不适合用来做桌面开发，Java还是有几个比较成熟的产品的，比如大名鼎鼎的Java集成开发环境IntelliJIDEA、Eclipse就是用Java开发的，不过PH...

CryptoChat:一款功能强大的纯Python消息加密安全传输工具

关于CryptoChatCryptoChat是一款功能强大的纯Python消息加密安全传输工具，该工具专为安全研究专家、渗透测试人员和红蓝队专家设计，该工具可以完全保证数据传输中的隐私安全。该工具建立...

为什么都说Python简单，但我觉得难?

Python普遍被大家认为是编程语言中比较简单的一种，但有一位电子信息的学生说自己已经学了C语言，但仍然觉得Python挺难的，感觉有很多疑问，像迭代器、装饰器什么的……所以他提出疑问：Python真...

蓝牙电话-关联FreeSwitch中继SIP账号通过Rest接口

蓝牙电话-关联FreeSwitch中继SIP账号通过Rest接口前言上一篇章《蓝牙电话-与FreeSwitch服务器和UA坐席的通话.docx》中，我们使用开源的B2B-UA当中经典的FreeSWIT...

技术分享|Sip与WebRTC互通-SRProxy开源库讲解

SRProxy介绍目前WebRTC协议跟SIP协议互通场景主要运用在企业呼叫中心、企业内部通信、电话会议（PSTN）、智能门禁等场景，要想让WebRTC与SIP互通，要解决两个层面的...

全网第N篇SIP协议之GB28181注册 JAVA版本

鉴于网上大部分关于SIP注册服务器编写都是C/C++/python，故开此贴，JAVA实现也贴出分享GB28181定义了了基于SIP架构的视频监控互联规范，而对于多数私有协议实现的监控系统...

「linux专栏」top命令用法详解，再也不怕看不懂top了

在linux系统中，我们经常使用到的一个命令就是top，它主要是用来显示系统运行中所有的进程和进程对应资源的使用等信息，所有的用户都可以使用top命令。top命令内容量丰富，可令使用者头疼的是无法全部...

取消回复欢迎 你 发表评论:

请填写验证码
一周热门
最近发表
标签列表