postgresql分割的简单介绍
postgresql 怎么自动创建分区
在数据库日渐庞大的今天,为了方便对数据库数据的管理,比如按时间,按地区去统计一些数据时,基数过于庞大,多有不便。很多商业数据库都提供分区的概念,按不同的维度去存放数据,便于后期的管理,PostgreSQL也不例外。
创新互联专注于中大型企业的成都网站建设、网站制作和网站改版、网站营销服务,追求商业策划与数据分析、创意艺术与技术开发的融合,累计客户千余家,服务满意度达97%。帮助广大客户顺利对接上互联网浪潮,准确优选出符合自己需要的互联网运用,我们将一直专注成都品牌网站建设和互联网程序开发,在前进的路上,与客户一起成长!
PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升,关键的是,它能带来管理和维护上的方便。
分区的具体好处是:
某些类型的查询性能可以得到极大提升。
更新的性能也可以得到提升,因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里,那么在索引上的读和写都会产生更多的磁盘访问。
批量删除可以用简单的删除某个分区来实现。
可以将很少用的数据移动到便宜的、转速慢的存储介质上。
在PG里表分区是通过表继承来实现的,一般都是建立一个主表,里面是空,然后每个分区都去继承它。无论何时,都应保证主表里面是空的。
小表分区不实际,表在多大情况下才考虑分区呢?PostgresSQL官方给出的建议是:当表本身大小超过了机器物理内存的实际大小时(the size of the table should exceed the physical memory of the database server),可以考虑分区。
PG目前(9.2.2)仅支持范围分区和列表分区,尚未支持散列分区。
二、环境
系统环境:CentOS release 6.3 (Final)
PostgreSQL版本:PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit
三、实现分区
3.1 创建主表
david=# create table tbl_partition (
david(# id integer,
david(# name varchar(20),
david(# gender boolean,
david(# join_date date,
david(# dept char(4));
CREATE TABLE
david=#
3.2 创建分区表
david=# create table tbl_partition_201211 (
check ( join_date = DATE '2012-11-01' AND join_date DATE '2012-12-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201212 (
check ( join_date = DATE '2012-12-01' AND join_date DATE '2013-01-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201301 (
check ( join_date = DATE '2013-01-01' AND join_date DATE '2013-02-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201302 (
check ( join_date = DATE '2013-02-01' AND join_date DATE '2013-03-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201303 (
check ( join_date = DATE '2013-03-01' AND join_date DATE '2013-04-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201304 (
check ( join_date = DATE '2013-04-01' AND join_date DATE '2013-05-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201305 (
check ( join_date = DATE '2013-05-01' AND join_date DATE '2013-06-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=#
3.3 分区键上建索引
david=# create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);
CREATE INDEX
david=# create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date);
CREATE INDEX
david=# create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);
CREATE INDEX
david=# create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date);
CREATE INDEX
david=# create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date);
CREATE INDEX
david=# create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date);
CREATE INDEX
david=# create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date);
CREATE INDEX
david=#
对于开发人员来说,希望数据库是透明的,只管 insert into tbl_partition。对于数据插向哪个分区,则希望由DB决定。这点,ORACLE实现了,但是PG不行,需要前期人工处理下。
3.4 创建触发器函数
david=# CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.join_date = DATE '2012-11-01' AND
NEW.join_date DATE '2012-12-01' ) THEN
INSERT INTO tbl_partition_201211 VALUES (NEW.*);
ELSIF ( NEW.join_date = DATE '2012-12-01' AND
NEW.join_date DATE '2013-01-01' ) THEN
INSERT INTO tbl_partition_201212 VALUES (NEW.*);
ELSIF ( NEW.join_date = DATE '2013-01-01' AND
NEW.join_date DATE '2013-02-01' ) THEN
INSERT INTO tbl_partition_201301 VALUES (NEW.*);
ELSIF ( NEW.join_date = DATE '2013-02-01' AND
NEW.join_date DATE '2013-03-01' ) THEN
INSERT INTO tbl_partition_201302 VALUES (NEW.*);
ELSIF ( NEW.join_date = DATE '2013-03-01' AND
NEW.join_date DATE '2013-04-01' ) THEN
INSERT INTO tbl_partition_201303 VALUES (NEW.*);
ELSIF ( NEW.join_date = DATE '2013-04-01' AND
NEW.join_date DATE '2013-05-01' ) THEN
INSERT INTO tbl_partition_201304 VALUES (NEW.*);
ELSIF ( NEW.join_date = DATE '2013-05-01' AND
NEW.join_date DATE '2013-06-01' ) THEN
INSERT INTO tbl_partition_201305 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION
david=#
说明:如果不想丢失数据,上面的ELSE 条件可以改成 INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同时需要创建一张结构和tbl_partition 一样的表tbl_partition_error_join_date,这样,错误的join_date 数据就可以插入到这张表中而不是报错了。
附上出处链接:
kettle提取postgresql数据
kettle提取postgresql数据步骤如下
1 遍历区域和像素
其中xingzheng是行政区域数据表,thematic是专题像素分类表
2 影像分割
通过查询某个区域和影像的交,达到影像分割的目的,
查询区域gid=24,波段为1,像素值为4的像素个数的sql语句为:
其中函数ST_Intersects查询两个几何的交集,示例如下
3 栅格统计与数据提取
通过提取函数ST_ValueCount,查询分割后的栅格、指定波段和像素的像素总个数,并乘以单个像元值大小,实现数据面积提取。
其中28.15132773 * 28.15132773为单个像元值面积大小,具体使用albers投影(双标准纬线投影)计算得出,0.000001单位换算成平方公里。
区域gid和像素类型作为输入,实现影像分割和栅格面积统计,完整的sql如下:
4 kettle数据提取转换
kettle数据提取转换过程如下:
kettle遍历区域和像素,如kettle数据转换图所示“表输入2”,sql见步骤1;
kettle影像分割和栅格统计,如kettle数据转换图所示“表输入”,sql见步骤3;
将提取出的数据保存到文件中。
postgresql自定义函数语法
最近在写postgres的函数,整理下常用语法备忘
regexp_split_to_table 字符串分割为表格
regexp_split_to_array 字符串分割为数组
定义内部变量
执行动态sql,并传入参数
执行动态sql,取出结果
打印变量
字符串拼接
本文名称:postgresql分割的简单介绍
文章转载:http://pwwzsj.com/article/dsdhsdp.html