Partition Table In PostgreSQL (Simulate Millions Data) – Part 2

By mkyong | Updated: May 31, 2010

Viewed: 418

Tags: performance postgresql

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.

 

--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.

 
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