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.