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
Excellent script to injest data!
Thanks for wonderful explanation.
thanks
SELECT * FROM hashvalue_PT_InsertRandomRecords(20000000);
Errr… actually: 20_000_000
ops sorry, 20m x 10month