PostgreSQL Plus Advanced Server一括作成パーティション表書き込み億レベルデータインスタンス

現在の状況:大きなテーブルのデータ量はすでに2億件に近い私の解決構想:n*100個のパーティションテーブルを作成し、各パーティションテーブルを異なるtablespaceに置くという利点:1、まずこのレベルのデータテーブルの性能が向上する2、データ管理がより科学的である3、生産メンテナンスプロセスのトラブルシューティング便利な実験環境VM Ware ESXiRedhat 6.3 64 bitPostgreSQL 9.2/9.3メモリ:2 G実装方法(Oracleにも適用)
1、試験表の作成

 1 /**

 2   300    ，     5   

 3 */

 4 -- -------------------- step1 : CREATE TABLE--------------------

 5 DROP TABLE parttest;

 6 CREATE TABLE parttest (some_test TEXT,some_tstimestamptz TEXT,random_value INT4, p_condition DATE)

 7 PARTITION BY RANGE(p_condition)

 8 (

 9     PARTITION p_2013_09_02_and_early VALUES LESS THAN ('2013-09-03')

10 );

 
2、時間が既にパーティション条件で、300個のパーティションテーブルを作成するプロセス

 1 -- -------------------- step2 : CREATE PARTITION PROCEDUCE --------------------

 2 CREATE OR REPLACE PROCEDURE create_partition_withindex() AS

 3 

 4 DECLARE

 5    tblname varchar := 'parttest';

 6    tblname_prefix varchar := 'p_condition';   

 7    part_tbl_name varchar;

 8    current_part_name varchar;

 9    sql_addpartition_cmd varchar;

10    

11 BEGIN

12     -- POPULATE SQL COMMAND

13     -- PARTITION TABLE'S NAME IS LIKE "TABLENAME_p_2019_12_31"

14     FOR j IN 1..300 LOOP

15     current_part_name := to_char(current_date + j,'YYYY_MM_DD');

16     part_tbl_name := tblname_prefix || '_p_' || current_part_name;

17 

18     -- POPULATE SQL COMMAND STRING

19         sql_addpartition_cmd = 'ALTER TABLE ' || tblname || ' ADD PARTITION p_' || current_part_name || ' VALUES LESS THAN (''' || (current_date + j + 1) || ''');';

20     DBMS_OUTPUT.PUT_LINE(sql_addpartition_cmd);

21         EXECUTE IMMEDIATE sql_addpartition_cmd;

22 

23     IF ((j % 30) = 0) THEN

24         COMMIT;

25     END IF;

26         

27     END LOOP;

28 

29     EXECUTE IMMEDIATE 'ALTER TABLE ' || tblname || ' ADD PARTITION p_' || to_char(current_date + 300 + 1,'YYYY_MM_DD') || '_and_later VALUES LESS THAN (''' || (current_date + 300 + 2) || ''');';

30 END;

3、パーティション表の作成

-- -------------------- step3 CREATE PARTITION --------------------

EXEC create_partition_withindex;

DEMOテスト1、書き込みデータの作成手順

 1 -- -------------------- step4 CREATE TEST DATA PROCEDUCE --------------------

 2 

 3 CREATE OR REPLACE PROCEDURE create_partition_testdata() AS

 4 

 5 DECLARE

 6 

 7 BEGIN

 8     FOR i IN 0..300 LOOP

 9     INSERT INTO parttest (some_test, some_tstimestamptz, random_value,p_condition)

10     SELECT

11         'user #' || cast(floor(random() * 10) as int4),    

12     now() - '1 year'::INTERVAL * random(),

13     cast(random() * 100000000 as INT4),

14     (current_date + i)

15     FROM

16     generate_series(1,500000);

17     COMMIT;

18     END LOOP;

19 END;

2、書き込みデータの保存プロセスを実行する

-- -------------------- step5 CREATE TEST DATA --------------------

EXEC create_partition_testdata;

3、書き込みデータのデータ量をチェックする

SELECT COUNT(*) FROM parttest;

必要に応じて、パーティションテーブルの作成やデータの書き込みの時間データを記録できます.