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

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

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
Greya
Guest
Greya

SELECT * FROM hashvalue_PT_InsertRandomRecords(20000000);

Errr… actually: 20_000_000

Greya
Guest
Greya

ops sorry, 20m x 10month