• 售前

  • 售后

热门帖子
入门百科

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

[复制链接]
forregistuse 显示全部楼层 发表于 2021-10-26 12:34:54 |阅读模式 打印 上一主题 下一主题
文章目次

PosgtreSQL 11 支持为分区表创建一个默认(DEFAULT)的分区,用于存储无法匹配其他任何分区的数据。显然,只有 RANGE 分区表和 LIST 分区表需要默认分区。
  1. CREATE TABLE measurement (
  2.   city_id     int not null,
  3.   logdate     date not null,
  4.   peaktemp    int,
  5.   unitsales    int
  6. ) PARTITION BY RANGE (logdate);
  7. CREATE TABLE measurement_y2018 PARTITION OF measurement
  8.   FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
复制代码
以上示例只创建了 2018 年的分区,如果插入 2017 年的数据,系统将会无法找到相应的分区:
  1. INSERT INTO measurement(city_id,logdate,peaktemp,unitsales)
  2. VALUES (1, '2017-10-01', 50, 200);
  3. ERROR: no partition of relation "measurement" found for row
  4. DETAIL: Partition key of the failing row contains (logdate) = (2017-10-01).
复制代码
使用默认分区可以解决这类标题。创建默认分区时使用 DEFAULT 子句替代 FOR VALUES 子句。
  1. CREATE TABLE measurement_default PARTITION OF measurement DEFAULT;
  2. \d+ measurement
  3.                  Table "public.measurement"
  4. Column  | Type  | Collation | Nullable | Default | Storage | Stats target | Description
  5. -----------+---------+-----------+----------+---------+---------+--------------+-------------
  6. city_id  | integer |      | not null |     | plain  |       |
  7. logdate  | date  |      | not null |     | plain  |       |
  8. peaktemp | integer |      |     |     | plain  |       |
  9. unitsales | integer |      |     |     | plain  |       |
  10. Partition key: RANGE (logdate)
  11. Partitions: measurement_y2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
  12.       measurement_default DEFAULT
复制代码
有了默认分区之后,未定义分区的数据将会插入到默认分区中:
  1. INSERT INTO measurement(city_id,logdate,peaktemp,unitsales)
  2. VALUES (1, '2017-10-01', 50, 200);
  3. INSERT 0 1
  4. select * from measurement_default;
  5. city_id | logdate  | peaktemp | unitsales
  6. ---------+------------+----------+-----------
  7.     1 | 2017-10-01 |    50 |    200
  8. (1 row)
复制代码
默认分区存在以下限制:

一个分区表只能拥有一个 DEFAULT 分区;
对于已经存储在 DEFAULT 分区中的数据,不能再创建相应的分区;参见下文示例;
如果将已有的表挂载为 DEFAULT 分区,将会检查该表中的全部数据;如果在已有的分区中存在雷同的数据,将会产生一个错误;
哈希分区表不支持 DEFAULT 分区,现实上也不需要支持。
使用默认分区也可能导致一些不可预见的标题。例如,往 measurement 表中插入一条 2019 年的数据,由于没有创建相应的分区,该记载同样会分配到默认分区:
  1. INSERT INTO measurement(city_id,logdate,peaktemp,unitsales)
  2. VALUES (1, '2019-03-25', 66, 100);
  3. INSERT 0 1
  4. select * from measurement_default;
  5. city_id | logdate  | peaktemp | unitsales
  6. ---------+------------+----------+-----------
  7.     1 | 2017-10-01 |    50 |    200
  8.     1 | 2019-03-25 |    66 |    100
  9. (2 rows)
复制代码
此时,如果再创建 2019 年的分区,操纵将会失败。由于添加新的分区需要修改默认分区的范围(不再包罗 2019 年的数据),但是默认分区中已经存在 2019 年的数据。
  1. CREATE TABLE measurement_y2019 PARTITION OF measurement
  2.   FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
  3. ERROR: updated partition constraint for default partition "measurement_default" would be violated by some row
复制代码
为相识决这个标题,可以先将默认分区从分区表中卸载(DETACH PARTITION),创建新的分区,将默认分区中的相应的数据移动到新的分区,末了重新挂载默认分区。
  1. ALTER TABLE measurement DETACH PARTITION measurement_default;
  2. CREATE TABLE measurement_y2019 PARTITION OF measurement
  3.   FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
  4. INSERT INTO measurement_y2019
  5. SELECT * FROM measurement_default WHERE logdate >= '2019-01-01' AND logdate < '2020-01-01';
  6. INSERT 0 1
  7. DELETE FROM measurement_default WHERE logdate >= '2019-01-01' AND logdate < '2020-01-01';
  8. DELETE 1
  9. ALTER TABLE measurement ATTACH PARTITION measurement_default DEFAULT;
  10. CREATE TABLE measurement_y2020 PARTITION OF measurement
  11.   FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
  12. \d+ measurement
  13.                  Table "public.measurement"
  14. Column  | Type  | Collation | Nullable | Default | Storage | Stats target | Description
  15. -----------+---------+-----------+----------+---------+---------+--------------+-------------
  16. city_id  | integer |      | not null |     | plain  |       |
  17. logdate  | date  |      | not null |     | plain  |       |
  18. peaktemp | integer |      |     |     | plain  |       |
  19. unitsales | integer |      |     |     | plain  |       |
  20. Partition key: RANGE (logdate)
  21. Partitions: measurement_y2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
  22.       measurement_y2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
  23.       measurement_y2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
  24.       measurement_default DEFAULT
复制代码
官方文档:Table Partitioning
增补:postgresql10以上的自动分区分表功能
一.列分表

1.起首创建主分区表:
  1. create table fenbiao(
  2. id int,
  3. year varchar
  4. ) partition by list(year)
复制代码
这里设置的是根据year列进行数据分表;创建后使用navicat是看不到的;
2.创建分表:
  1. create table fenbiao_2017 partition of fenbiao for values in ('2017')
  2. create table fenbiao_2018 partition of fenbiao for values in ('2018')
复制代码
如许这两天数据会依赖规则插入到不同分表中,如果插入一条不符合规则的数据,则会报错误:no partition of relation "fenbiao" found for row.

二.范围分表

1.以year列为范围进行分表
  1. create table fenbiao2(
  2. id int,
  3. year varchar
  4. ) partition by range(year)
复制代码
2.创建分表
  1. create table fenbiao2_2018_2020 partition of fenbiao2 for values from ('2018') to ('2020')
  2. create table fenbiao2_2020_2030 partition of fenbiao2 for values from ('2020') to ('2030')
复制代码
留意:此时插入year=2020会插入到下面的表;如下面表范围为2021到2030,则会报错;同时插入2030也会报错;范围相当于时a<=year<b;
以上为个人经验,盼望能给大家一个参考,也盼望大家多多支持脚本之家。如有错误或未考虑完全的地方,望不吝见教。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作