PostgreSQL10分区表详解及性能测试报告
作者简介：
中国⽐较早的postgresql使⽤者,2001年就开始使⽤postgresql,⾃2003年底⾄2014年⼀直担任PGSQL中国社区论坛PostgreSQL的论坛板块版主、管理员，参与Postgresql讨论和发表专题⽂章7000多贴.拥有15年的erp设计，开发和实施经验，开源mrp系统PostMRP就是我的作品，该应⽤软件是⼀套基于Postgresql专业的制造业管理软件系统.⽬前任职于--中国第⼀物流控股有限公司/运⼒宝（北京）科技有限公司，为公司的研发部经理
⼀、测试环境
操作系统：CentOS 6.4
Postgresql版本号：10.0
CPU：Intel(R) Xeon(R) CPU E5-2407 v2 @ 2.40GHz 4核⼼ 4线程
内存：32G
硬盘：2T SAS 7200
⼆、编译安装PostgreSQL 10
－－编译安装及初始化
[root@ad source]# git clone git:///git/postgresql.git
[root@ad source]# cd postgresql
[root@ad source]# ./configure --prefix=/usr/local/pgsql10
[root@ad postgresql]# gmake -j 4
[root@ad postgresql]# gmake install
[root@ad postgresql]# su postgres
[postgres@ad postgresql]# /usr/local/pgsql10/bin/initdb --no-locale -E utf8 -D /home/postgres/data10/ -U postgres
－－修改⼀些参数
listen_addresses = '*'
port = 10000
shared_buffers = 8096MB
maintenance_work_mem = 512MB
effective_cache_size = 30GB
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_checkpoints = off
log_connections = off
log_disconnections = off
log_duration = off
log_line_prefix = '%m %h %a %u %d %x [%p] '
log_statement = 'none'
log_timezone = 'PRC'
track_activity_query_size = 4096
max_wal_size = 32GB
min_wal_size = 2GB
checkpoint_completion_target = 0.5
f增加许可条⽬
host all all 192.168.1.0/24 trust
－－启动服务
[postgres@ad data10]$ /usr/local/pgsql10/bin/pg_ctl start -D /home/postgres/data10/
－－连接数据库
[postgres@ad data10]$ /usr/local/pgsql10/bin/psql -p 10000 -U postgres -h 127.0.0.1 -d postgres
psql (10devel)
Type "help"for help.
postgres=#
三、分区表介绍
PostgreSQL的分区表跟先前版本⼀样，也要先建⽴主表，然后再建⽴⼦表，使⽤继承的特性，但不需要⼿⼯写规则了，这个⽐较赞阿。⽬前⽀持range、list分区，10正式版本发布时不知会不会⽀持其它⽅法。
1、分区主表
create table order_range(id bigserial not null,userid integer,product text, createdate date) partition by range ( createdate );
分区主表不能建⽴全局约束，使⽤partition by range(xxx)说明分区的⽅式，xxx可以是多个字段，表达式……，具体见
2、分区⼦表
create table order_range(id bigserial not null,userid integer,product text,
createdate date not null) partition by range ( createdate );
create table order_range_201701 partition of order_range(id primary key,userid,product,
createdate) for values from ('2017-01-01') to ('2017-02-01');
create table order_range_201702 partition of order_range(id primary key,userid,product,
createdate) for values from ('2017-02-01') to ('2017-03-01');
说明：
建⽴分区表时必需指定主表。
分区表和主表的列数量，定义必须完全⼀致。
分区表的列可以单独增加Default值，或约束。
当⽤户向主表插⼊数据库时，系统⾃动路由到对应的分区，如果没有到对应分区，则抛出错误。
指定分区约束的值（范围，LIST值），范围，LIST不能重叠，重叠的路由会卡壳。
指定分区的列必需设置成not null，如建⽴主表时没设置系统会⾃动加上。
Range分区范围为 >=最⼩值 and <；最⼤值……
不⽀持通过更新的⽅法把数据从⼀个区移动到另外⼀个区，这样做会报错。如果要这样做的话需要删除原来的记录，再INSERT⼀条新的记录。
修改主表的字段名，字段类型时，会⾃动同时修改所有的分区。
TRUNCATE 主表时，会清除所有继承表分区的记录，如果要清除单个分区，请对分区进⾏操作。
DROP主表时会把所有⼦表⼀起给DROP掉，如果drop单个分区，请对分区进⾏操作。
使⽤psql能查看分区表的详细定义。
postgres=# \d+ order_range
Table "der_range"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+---------+-----------+----------+-----------------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('order_range_id_seq'::regclass) | plain | |
userid | integer | | | | plain | |
product | text | | | | extended | |
createdate | date | | not null | | plain | |
Partition key: RANGE (createdate)
Partitions: order_range_201701 FOR VALUES FROM ('2017-01-01') TO ('2017-02-01'),
order_range_201702 FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
postgres=#
1、分区主表
create table order_list(id bigserial not null,userid integer,product text,area text, createdate date) partition by list( area );
2、分区⼦表
create table order_list_gd partition of order_list(id primary key,userid,product,area,createdate) for values in ('⼴东');
create table order_list_bj partition of order_list(id primary key,userid,product,area,createdate) for value
s in ('北京');
先按地区分区，再按⽇期分区
1、主表
create table order_range_list(id bigserial not null,userid integer,product text,area text, createdate date) partition by list ( area );
2、⼀级分区表
create table order_range_list_gd partition of order_range_list for values in ('⼴东') partition by range(createdate);
create table order_range_list_bj partition of order_range_list for values in ('北京') partition by range(createdate);
3、⼆级分区表
create table order_range_list_gd_201701 partition of order_range_list_gd(id primary
key,userid,product,area,createdate) for values from ('2017-01-01') to ('2017-02-01');
create table order_range_list_gd_201702 partition of order_range_list_gd(id primary
key,userid,product,area,createdate) for values from ('2017-02-01') to ('2017-03-01');
create table order_range_list_bj_201701 partition of order_range_list_bj(id primary
key,userid,product,area,createdate) for values from ('2017-01-01') to ('2017-02-01');
create table order_range_list_bj_201702 partition of order_range_list_bj(id primary
key,userid,product,area,createdate) for values from ('2017-02-01') to ('2017-03-01');
postgres=# insert into order_range_201702 (id,userid,product,createdate) values(1,
(random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01'));
ERROR: new row for relation "order_range_201702" violates partition constraint
DETAIL: Failing row contains (1, 322345, 51a9357a78416d11a018949a42dd2f8d, 2017-01-01).
INSERT提⽰违反了分区约束
postgres=# update order_range_201701 set createdate='2017-02-01' where createdate='2017-01-17';
ERROR: new row for relation "order_range_201701" violates partition constraint
DETAIL: Failing row contains (1, 163357, 7e8fbe7b632a54ba1ec401d969f3259a, 2017-02-01).
UPDATE提⽰违反了分区约束
如果分区表是外部表，则约束失效，后⾯有介绍
1、移除分区
录⼊2条测试数据
postgres=# insert into order_range (userid,product,createdate)
values((random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01'::date+
(random()*31)::integer));
INSERT 01
Time: 25.006 ms
postgres=# insert into order_range (userid,product,createdate)
values((random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01'::date+
(random()*31)::integer));
INSERT 01
Time: 7.601 ms
postgres=# select * from order_range;
id | userid | product | createdate
----+--------+----------------------------------+------------
1 | 163357 | 7e8fbe7b632a54ba1ec401d969f3259a | 2017-01-17
2 | 349759 | 8095c9036295d3c800dace9069f9c102 | 2017-01-27
(2 rows)
删除分区
postgres=# alter table order_range detach partition order_range_201701;
ALTER TABLE
Time: 14.129 ms
查看确认分区没了
id | bigint | | not null | nextval('order_range_id_seq'::regclass) | plain | |
userid | integer | | | | plain | |
product | text | | | | extended | |
createdate | date | | not null | | plain | |
Partition key: RANGE (createdate)
Partitions: order_range_201702 FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
postgres=#
数据也查不出来了
postgres=# select * from order_range;
id | userid | product | createdate
----+--------+---------+------------
(0 rows)
Time: 0.505 ms
但分区表还在
postgres=# select * from order_range_201701;
id | userid | product | createdate
----+--------+----------------------------------+------------
1 | 163357 | 7e8fbe7b632a54ba1ec401d969f3259a | 2017-01-17
2 | 349759 | 8095c9036295d3c800dace9069f9c102 | 2017-01-27
(2 rows)
Time: 0.727 ms
2、添加分区
postgres=# alter table order_range attach partition order_range_201701 FOR VALUES FROM ('2017-
01-01') TO ('2017-02-01'); ERROR: column "createdate"in child table must be marked NOT NULL
Time: 0.564 ms
增加⼦表⾥，约束需要与主表⼀致
postgres=# alter table order_range_201701 alter column createdate set not null;
ALTER TABLE
Time: 17.345 ms
postgres=# alter table order_range attach partition order_range_201701 FOR VALUES FROM ('2017-01-01') TO ('2017-01-15'); ERROR: partition constraint is violated by some row
Time: 1.276 ms
加回来时可以修改其约束范围，但数据必需在约束的规则范围内
postgres=# alter table order_range attach partition order_range_201701 FOR VALUES FROM
('2017-01-01') TO ('2017-02-01');
ALTER TABLE
Time: 18.407 ms
分区表⼜加回来了
id | bigint | | not null | nextval('order_range_id_seq'::regclass) | plain | |
userid | integer | | | | plain | |
product | text | | | | extended | |
createdate | date | | not null | | plain | |
Partition key: RANGE (createdate)
Partitions: order_range_201701 FOR VALUES FROM ('2017-01-01') TO ('2017-02-01'),
order_range_201702 FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
postgres=# select * from order_range;
id | userid | product | createdate
----+--------+----------------------------------+------------
1 | 163357 | 7e8fbe7b632a54ba1ec401d969f3259a | 2017-01-17
2 | 349759 | 8095c9036295d3c800dace9069f9c102 | 2017-01-27
(2 rows)
Time: 0.627 ms
－－增加⼀个新库，建⽴需要的外部表
[postgres@ad root]$ /usr/local/pgsql10/bin/psql -p 10000 -U postgres -h 127.0.0.1 -d postgres
psql (10devel)
Type "help"for help.
#建⽴数据库
postgres=# create database postgres_fdw;
CREATE DATABASE
postgres_fdw=# create table order_range_fdw(id bigserial not null,userid integer,product text, createdate date not null); CREATE TABLE
postgres_fdw=#
#录⼊⼀条测试数据
postgres_fdw=# insert into order_range_fdw (userid,product,createdate)
values((random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01'::date-
(random()*31)::integer));
INSERT 01
postgres_fdw=# select * from order_range_fdw;
id | userid | product | createdate
----+--------+----------------------------------+------------
2 | 300686 | 55956a07742d6aebdef7ebb78c2400d7 | 2016-12-22
(1 row)
－－在postgres库中增加外部表order_range_fdw
[postgres@ad root]$ /usr/local/pgsql10/bin/psql -p 10000 -U postgres -h 127.0.0.1 -d postgres
psql (10devel)
Type "help"for help.
#增加postgres_fdw模块
postgres=# create extension postgres_fdw;
CREATE EXTENSION
#建⽴外部服务器
postgres=# CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.1.10', port '10000', dbname 'postgres_fdw');
CREATE SERVER
#建⽴外部服务器⽤户标识
postgres=# CREATE USER MAPPING FOR postgres
tabletimepostgres-# SERVER foreign_server
postgres-# OPTIONS (user 'postgres', password '');
CREATE USER MAPPING
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论