为满足每日 大数据量空间 查询。打算使用使用表分区 。
本博文关注,分区在硬盘文件使用上的细节。
为管理方便先设置 新的表空间
--查询现有表空间
SELECT * FROM pg_tablespace;
--创建 新的表空间
CREATE TABLESPACE click_space LOCATION '/opt/data/postgre_db/tablespace/click_space';
SET default_tablespace = click_space;
--创建分区父表
create table partitioned(
id SERIAL primary key,
dt date,
pt geometry
);
-- 按照时间分区 子表 ( 表空间中 下面这两个表会对应两个 oid 表文件)
create table p20090101 (
check(dt >= DATE '2009-01-01' AND dt < DATE '2009-01-02')
) inherits(partitioned);
create table p20090102 (
check(dt >= DATE '2009-01-02' AND dt < DATE '2009-01-03')
) inherits(partitioned);
-- 创建规则
CREATE OR REPLACE RULE partitioned_p20090101 AS
ON INSERT TO partitioned WHERE
(dt >= DATE '2009-01-01' AND dt < DATE '2009-01-02')
DO INSTEAD
INSERT INTO p20090101 VALUES (NEW.*);
CREATE OR REPLACE RULE partitioned_p20090102 AS
ON INSERT TO partitioned WHERE
(dt >= DATE '2009-01-02' AND dt < DATE '2009-01-03')
DO INSTEAD
INSERT INTO p20090102 VALUES (NEW.*);
--创建 表分区中的 索引 ( 表空间中 下面这几个索引都会对应各个 oid 文件)
CREATE INDEX index_partitioned_p20090101 ON p20090101 (dt);
CREATE INDEX index_partitioned_p20090101_pt ON p20090101 using gist (pt);
CREATE INDEX index_partitioned_p20090102 ON p20090102 (dt);
CREATE INDEX index_partitioned_p20090102_pt ON p20090102 using gist (pt);
--查询 对应 OID
select oid from pg_class where relname ='partitioned';
select oid from pg_class where relname ='p20090101';
select oid from pg_class where relname ='p20090102';
select oid from pg_class where relname ='index_partitioned_p20090102';
select oid from pg_class where relname ='index_partitioned_p20090101';
select oid from pg_class where relname ='index_partitioned_p20090101_pt';
select oid from pg_class where relname ='index_partitioned_p20090102_pt';
-- 在 postgresql 硬盘上,可以看到 对应 OID 的文件
-- 文件大小都是初始,没数据
ls -la tablespace/click_space/PG_9.1_201105231/*
-- 导入数据
insert into partitioned values (1,'2009-01-01', 'POINT(0 0)');
insert into partitioned values (2,'2009-01-02','POINT(31.5 60.87)');
insert into partitioned values (3,'2009-01-01','POINT(10.77 85.902)');
-- 再在 postgresql 硬盘上,查看到 对应 OID 的文件
-- 文件变化 :
-- partitioned 表对应的 OID 文件没有变化,数据并没真实导入到这张表里
-- p20090101 表对应的 OID 文件有变化,数据导入到这张表里!
-- p20090102 表对应的 OID 文件有变化,数据导入到这张表里!
-- 相对应相关索引文件 OID 也都有变化 !
ls -la tablespace/click_space/PG_9.1_201105231/*
-- 在大数据量下 ,需要定时去清理 硬盘使用
drop index index_partitioned_p20090101;
drop index index_partitioned_p20090102;
drop index index_partitioned_p20090102_pt;
drop index index_partitioned_p20090101_pt;
DROP RULE partitioned_p20090101 on partitioned;
DROP RULE partitioned_p20090102 on partitioned;
drop table p20090101 ;
drop table p20090102 ;
drop table partitioned ;
-- 真正物理磁盘清除
VACUUM FULL ;
-- 文件大小都回到 0
ls -la tablespace/click_space/PG_9.1_201105231/*
-- 重启 postgresql
-- 为0 的 OID文件 都清除
阅读(2080) | 评论(2) | 转发(1) |