欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

postgreSQL表分区使用方法介绍

程序员文章站 2022-06-28 18:07:33
分区的意思是把逻辑上的一个大表分割成物理上的几块。分区可以提供若干好处: 1 提高查询性能。 原因如下: ① 通过创建分区,索引所占的空间减少,因此可以将高使用率的索引放入内存中,减少因磁盘读写索引...

分区的意思是把逻辑上的一个大表分割成物理上的几块。分区可以提供若干好处:

1 提高查询性能。

原因如下:

① 通过创建分区,索引所占的空间减少,因此可以将高使用率的索引放入内存中,减少因磁盘读写索引造成的开销;

② 可以将表中访问率较高的行存放到单独分区或少数几个分区上,极大提升查询性能;

③ 通过创建分区,在查询或更新时,只需扫描数据所在的那个分区上的数据即可,避免整表扫描及使用索引离散的访问整个表造成的开销,提升性能;

④ 提升更新和删除数据的性能:

alter table比操作大量的数据要快的多。它同时还可以避免由于大量delete导致的vacuum超载。那么可以通过直接读取或删除那个分区以获得巨大的性能提升

2 节约存储设备成本

通过创建分区,可以将很少使用的数据存储到慢速存储介质上。

前提条件:表分区的优势通常只有在表可能会变得非常大的情况下才有价值。多大的表会从分区中收益取决于具体的应用,但通常情况下,在表的大小超过了服务器的物理内存大小时需要进行表分区。

3 postgresql的表分区技术目前,postgresql支持通过表继承进行分区。每个分区必须做为单独一个父表的子表进行创建。父表自身通常是空的,它的存在只是为了代表整个数据集。

具体来讲,postgresql可以实现下面形式的分区:

① 范围分区(按行分区)

表被一个或者多个关键字段分区成"范围",这些范围在不同的分区里没有重叠。比如,我们可以为特定的商业对象根据数据范围分区,或者根据标识符范围分区。

②列表分区(按列分区)

明确地列出每个分区里应该出现那些关键字值实现。

4 postgresql的表分区操作流程

要设置一个分区的表,做下面的步骤:

创建"主表",所有分区都从它继承。这个表中没有数据,不要在这个表上定义任何检查约束,除非你希望约束同样也适用于所有分区。同样,在其上定义任何索引或者唯一约束也没有意义。

创建几个"子表",每个都从主表上继承。通常,这些表不会增加任何字段。我们将把子表称作分区,尽管它们就是普通的postgresql表。

给分区表增加约束,定义每个分区允许的健值。5 示例① 创建主表

create table users ( uid int not null primary key, name varchar(20));

② 创建分区表(必须继承上面的主表)

create table users_0 ( check (uid >= 0 and uid< 100) ) inherits (users);

create table users_1 ( check (uid >= 100)) inherits (users);

③ 在分区表上建立索引,其实这步可以省略的哦

create index users_0_uidindex on users_0(uid);

create index users_1_uidindex on users_1(uid);

④ 创建规则rule

create rule users_insert_0 as

on insert to users where

(uid >= 0 and uid < 100)

do instead

insert into users_0 values (new.uid,new.name);

create rule users_insert_1 as

on insert to users where

(uid >= 100)

do instead

insert into users_1 values (new.uid,new.name);