PostgreSQL上,想做表分区处理大容量数据库表。我看到资料说需要先建立一个空表,然后字表继承。具体的细节不是很清楚,请熟悉的大侠讲讲分区表的建立过程吧,谢谢!没查到详细的资料讲解这个东东。我的想法是每天当中每个小时产生一个分区。此外这种分区对于应用应该是透明的吧?比如从应用层并为感觉到分区的存在?比如表结构为:
Create Table DATA_TABLE (ID int, Name varchar(100),Value int, Description varchar(4000))
Create Table DATA_TABLE (ID int, Name varchar(100),Value int, Description varchar(4000))
2、对于update、insert、delete. 必需在主表上建立trigger进行转向。postgresql官方文档有很详细的例子
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);创建分区
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 (
CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 (
CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 (
CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);创建必要的索引
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);