浅谈PostgreSQL 11 新特性之默认分区

数据库 2024/11/5 佚名

3 2 1

文章目录

PosgtreSQL 11 支持为分区表创建一个默认（DEFAULT）的分区，用于存储无法匹配其他任何分区的数据。显然，只有 RANGE 分区表和 LIST 分区表需要默认分区。

CREATE TABLE measurement (
  city_id     int not null,
  logdate     date not null,
  peaktemp    int,
  unitsales    int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2018 PARTITION OF measurement
  FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');

以上示例只创建了 2018 年的分区，如果插入 2017 年的数据，系统将会无法找到相应的分区：

INSERT INTO measurement(city_id,logdate,peaktemp,unitsales)
VALUES (1, '2017-10-01', 50, 200);
ERROR: no partition of relation "measurement" found for row
DETAIL: Partition key of the failing row contains (logdate) = (2017-10-01).

使用默认分区可以解决这类问题。创建默认分区时使用 DEFAULT 子句替代 FOR VALUES 子句。

CREATE TABLE measurement_default PARTITION OF measurement DEFAULT;
\d+ measurement
                 Table "public.measurement"
 Column  | Type  | Collation | Nullable | Default | Storage | Stats target | Description 
-----------+---------+-----------+----------+---------+---------+--------------+-------------
 city_id  | integer |      | not null |     | plain  |       | 
 logdate  | date  |      | not null |     | plain  |       | 
 peaktemp | integer |      |     |     | plain  |       | 
 unitsales | integer |      |     |     | plain  |       | 
Partition key: RANGE (logdate)
Partitions: measurement_y2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
      measurement_default DEFAULT

有了默认分区之后，未定义分区的数据将会插入到默认分区中：

INSERT INTO measurement(city_id,logdate,peaktemp,unitsales)
VALUES (1, '2017-10-01', 50, 200);
INSERT 0 1
select * from measurement_default;
 city_id | logdate  | peaktemp | unitsales 
---------+------------+----------+-----------
    1 | 2017-10-01 |    50 |    200
(1 row)

默认分区存在以下限制：

一个分区表只能拥有一个 DEFAULT 分区；

对于已经存储在 DEFAULT 分区中的数据，不能再创建相应的分区；参见下文示例；

如果将已有的表挂载为 DEFAULT 分区，将会检查该表中的所有数据；如果在已有的分区中存在相同的数据，将会产生一个错误；

哈希分区表不支持 DEFAULT 分区，实际上也不需要支持。

使用默认分区也可能导致一些不可预见的问题。例如，往 measurement 表中插入一条 2019 年的数据，由于没有创建相应的分区，该记录同样会分配到默认分区：

INSERT INTO measurement(city_id,logdate,peaktemp,unitsales)
VALUES (1, '2019-03-25', 66, 100);
INSERT 0 1
select * from measurement_default;
 city_id | logdate  | peaktemp | unitsales 
---------+------------+----------+-----------
    1 | 2017-10-01 |    50 |    200
    1 | 2019-03-25 |    66 |    100
(2 rows)

此时，如果再创建 2019 年的分区，操作将会失败。因为添加新的分区需要修改默认分区的范围（不再包含 2019 年的数据），但是默认分区中已经存在 2019 年的数据。

CREATE TABLE measurement_y2019 PARTITION OF measurement
  FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
ERROR: updated partition constraint for default partition "measurement_default" would be violated by some row

为了解决这个问题，可以先将默认分区从分区表中卸载（DETACH PARTITION），创建新的分区，将默认分区中的相应的数据移动到新的分区，最后重新挂载默认分区。

ALTER TABLE measurement DETACH PARTITION measurement_default;
CREATE TABLE measurement_y2019 PARTITION OF measurement
  FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
INSERT INTO measurement_y2019
SELECT * FROM measurement_default WHERE logdate >= '2019-01-01' AND logdate < '2020-01-01';
INSERT 0 1
DELETE FROM measurement_default WHERE logdate >= '2019-01-01' AND logdate < '2020-01-01';
DELETE 1
ALTER TABLE measurement ATTACH PARTITION measurement_default DEFAULT;
CREATE TABLE measurement_y2020 PARTITION OF measurement
  FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
\d+ measurement
                 Table "public.measurement"
 Column  | Type  | Collation | Nullable | Default | Storage | Stats target | Description 
-----------+---------+-----------+----------+---------+---------+--------------+-------------
 city_id  | integer |      | not null |     | plain  |       | 
 logdate  | date  |      | not null |     | plain  |       | 
 peaktemp | integer |      |     |     | plain  |       | 
 unitsales | integer |      |     |     | plain  |       | 
Partition key: RANGE (logdate)
Partitions: measurement_y2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
      measurement_y2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
      measurement_y2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
      measurement_default DEFAULT

官方文档：Table Partitioning

补充：postgresql10以上的自动分区分表功能

一.列分表

1.首先创建主分区表:

create table fenbiao( 
id int, 
year varchar 
) partition by list(year)

这里设置的是根据year列进行数据分表;创建后使用navicat是看不到的;

2.创建分表:

create table fenbiao_2017 partition of fenbiao for values in ('2017')

create table fenbiao_2018 partition of fenbiao for values in ('2018')

这样这两天数据会依靠规则插入到不同分表中,如果插入一条不符合规则的数据,则会报错误:no partition of relation "fenbiao" found for row.

二.范围分表

1.以year列为范围进行分表

create table fenbiao2( 
id int, 
year varchar 
) partition by range(year)

2.创建分表

create table fenbiao2_2018_2020 partition of fenbiao2 for values from ('2018') to ('2020')

create table fenbiao2_2020_2030 partition of fenbiao2 for values from ('2020') to ('2030')

注意:此时插入year=2020会插入到下面的表;如下面表范围为2021到2030,则会报错;同时插入2030也会报错;范围相当于时a<=year<b;

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

PostgreSQL11,新特性,默认分区

标签：

PostgreSQL11,新特性,默认分区

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

白云城资源网 Copyright www.dyhadc.com

评论“浅谈PostgreSQL 11 新特性之默认分区”

浅谈PostgreSQL 11 新特性之默认分区

暂无“浅谈PostgreSQL 11 新特性之默认分区”评论...

www.dyhadc.com 白云城资源网

129,905影音资源

244,626技术资源

111,817软件资源

578,645站长资源

浅谈PostgreSQL 11 新特性之默认分区

文章目录

默认分区存在以下限制：

一.列分表

二.范围分表

PostgreSQL11,新特性,默认分区

PostgreSQL 重复数据处理的操作方法

PostgreSQL去掉表中所有不可见字符的操作

评论“浅谈PostgreSQL 11 新特性之默认分区”

稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！

更新日志

友情链接