If you do not know how to do table partition in PostgreSQL, please study Partition Table In PostgreSQL (Create Partition) – Part 1

Here i will provide a simple function to demonstrate how to insert million of data into partition table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

 
--create sequence for testing 
CREATE SEQUENCE hashvalue_PT_serial START 1;
 
--Generate Dynamic data for testing
CREATE OR REPLACE FUNCTION hashvalue_PT_InsertRandomRecords(IN a_no_of_records integer) RETURNS integer AS $$
DECLARE
    v_counter integer;
    vhash varchar(255);
    v_date varchar(15);
BEGIN
 
    v_counter := 1;
 
    RAISE NOTICE 'No of records insert : %', a_no_of_records;
 
    WHILE (v_counter <= a_no_of_records) LOOP
 
	IF( v_counter % 10000 =0) THEN
	    RAISE NOTICE 'Counter here is %', v_counter;
	END IF;
 
 
	v_date := trunc(random() * 27) +1;
	vhash := '00' || NEXTVAL('hashvalue_PT_serial');
 
	--insert into partiton table
	INSERT INTO hashvalue_pt(
		    hash, hashtime)
			VALUES (E'\\003\\002\\001\\0151'::bytea || vhash::bytea , date ('2008-01-' || v_date));
	INSERT INTO hashvalue_pt(
		    hash, hashtime)
			VALUES (E'\\003\\002\\001\\0151'::bytea || vhash::bytea, date ('2008-02-' || v_date));
	INSERT INTO hashvalue_pt(
		    hash, hashtime)
			VALUES (E'\\003\\002\\001\\0151'::bytea || vhash::bytea, date ('2008-03-' || v_date));
	INSERT INTO hashvalue_pt(
		    hash, hashtime)
			VALUES (E'\\003\\002\\001\\0151'::bytea || vhash::bytea, date ('2008-04-' || v_date));
	INSERT INTO hashvalue_pt(
		    hash, hashtime)
			VALUES (E'\\003\\002\\001\\0151'::bytea || vhash::bytea, date ('2008-05-' || v_date));
	INSERT INTO hashvalue_pt(
		    hash, hashtime)
			VALUES (E'\\003\\002\\001\\0151'::bytea || vhash::bytea , date ('2008-06-' || v_date));
	INSERT INTO hashvalue_pt(
		    hash, hashtime)
			VALUES (E'\\003\\002\\001\\0151'::bytea || vhash::bytea, date ('2008-07-' || v_date));
	INSERT INTO hashvalue_pt(
		    hash, hashtime)
			VALUES (E'\\003\\002\\001\\0151'::bytea || vhash::bytea, date ('2008-08-' || v_date));
	INSERT INTO hashvalue_pt(
		    hash, hashtime)
			VALUES (E'\\003\\002\\001\\0151'::bytea || vhash::bytea, date ('2008-09-' || v_date));
	INSERT INTO hashvalue_pt(
		    hash, hashtime)
			VALUES (E'\\003\\002\\001\\0151'::bytea || vhash::bytea, date ('2008-10-' || v_date));
 
 
    v_counter := v_counter + 1;
 
    END LOOP;
 
    RETURN 0;
END;
$$ LANGUAGE plpgsql;

I created a simple “hashvalue_PT_InsertRandomRecords” function to simulate data for performance testing on partition table.

1

SELECT * FROM hashvalue_PT_InsertRandomRecords(20000000);

Haha.. one shot insert 200 millions data. Ok, now i have 200 millions of data in 10 months, next session i will start to do some performance testing between partition and non-partition table, please visit Performance Testing on Partition Table In PostgreSQL – Part 3

Oracle Magazine (Free)
Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and more. Oracle (NASDAQ: ORCL) is the world\'s largest enterprise software company.
Publisher : Oracle Corporation