After having created the partitions, now we will create a maintenance function that will help us running queries on every partition.

 
We saw in the first part how to dynamically create the needed partitions.
Just add this function in Postgresql (via psql for instance) :

 CREATE OR REPLACE FUNCTION run_on_partitions(text,text) RETURNS INTEGER AS $$
DECLARE
partition RECORD;
tablename TEXT = $1;
sql TEXT = $2;
sqlReplaced TEXT;
BEGIN
tablename := tablename || '%p';
FOR partition IN SELECT relname::text as rel FROM pg_class WHERE relname::text LIKE tablename AND relkind = 't' ORDER BY relname LOOP
sqlReplaced := replace(sql, '',partition.rel);
RAISE NOTICE 'Executing: %', sqlReplaced;
EXECUTE  sqlReplaced;
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;

 
This function is really useful. To call it, simply do:

 SELECT run_on_partitions('tablename','CREATE INDEX _idx ON  USING btree(name)');

 
The tag <PARTITION> will be replaced by the name of the partition derived from the master table ‘tablename’. We can now create indexes, primary keys on each table…without having to run the query against each table by hand.
Let’s create a full text search index on these items:

SELECT run_on_partitions('tablename','CREATE INDEX _fts_idx ON  USING gin (to_tsvector(name::text || '' ''::text) || description));');

 
The search time has now been greatly improved.
 
Yeepa!