postgresで巨大レコード数のtableを扱うなら、パーティショニング

販売管理システムの受注明細のようなデータを1つのtableに対してレコードを追加し続け、巨大になったtableがあるとします。
このようなtableでは、indexを設定していたとしても、indexファイルまで巨大になり、selec等のCRUD処理だけでなく、vacuumの処理速度まで遅くなってしまいます。
postgresでは、そのようなケースに備えて、パーティショニングという機能があります。

次のsqlは、order_rawテーブルをorder_raw_200901とorder_raw_200902に分割する手順を示しています。

-- もとtable
CREATE TABLE order_raw (
order_id         varchar(16),
update_time     timestamp,
val             numeric
);

-- パーティショニング tableの作成
CREATE TABLE order_raw_200901 (
CONSTRAINT order_raw_200901_ins
   CHECK ('2009-1-1' <= update_time AND update_time < '2009-2-1')
) inherits(order_raw);

CREATE TABLE order_raw_200902 (
CONSTRAINT order_raw_200902_ins
   CHECK ('2009-2-1' <= update_time AND update_time < '2009-3-1')
) inherits(order_raw);


-- ルールの作成
CREATE RULE order_raw_200901_ins 
AS ON INSERT TO order_raw
WHERE ('2009-1-1' <= update_time AND update_time < '2009-2-1')
DO INSTEAD INSERT INTO order_raw_200901 (order_id, update_time, val)
VALUES(NEW.order_id, NEW.update_time, NEW.val);

CREATE RULE order_raw_200902_ins 
AS ON INSERT TO order_raw
WHERE ('2009-2-1' <= update_time AND update_time < '2009-3-1')
DO INSTEAD INSERT INTO order_raw_200902 (order_id, update_time, val)
VALUES(NEW.order_id, NEW.update_time, NEW.val);

-- constraint_exclusionの有効化
-- (postgresql.confで constraint_exclusion = on でもよさそう)
SET constraint_exclusion TO on;

constraint_exclusionまで完了すれば、見かけ上、order_rawにinsertしても、それぞれのorder_raw_YYYYMMDDに実態がinsertされます。

# insert into order_raw values('1','2009-01-01',50);
INSERT 0 0
# select * from order_raw_200901;
 order_id |     update_time     | val 
----------+---------------------+-----
 1        | 2009-01-01 00:00:00 |  50
(1 row)
# select * from order_raw;
 order_id |     update_time     | val 
----------+---------------------+-----
 1        | 2009-01-01 00:00:00 |  50
(1 row)