Materialized views

Jump to: navigation, search

Materializing a view is the process of taking a view (which is a sort of virtual table) and turning it into a real table that can be indexed for better performance. While PostgreSQL doesn't support maintenance of materialized views, we have written a command line tool that will handle the creation and maintenance of materialized views in Chado running on PostgreSQL.

The perl script (which resides in schema/chado/bin, and gets installed in your path when you do sudo make install) has several options for creating and maintaining materialized views:


       Guides the user through a series of prompts to create a new material‐
       ized view.

       -u|--update_view viewname

       Updates the data in a materialized view by first deleting the data in
       the table and then running the query that defines the data to repopu‐
       late it.


       Automatically updates all of the MVs that are currently marked out of
       date according to the update frequency that was specified when the MV
       was created.  This option is very useful in a cron job to update MVs on
       a regular basis.

       --dematerialize viewname

       Takes a MV and turns into a standard view.  This might be done if the
       database administrator desides that the downsides of the MV scheme is
       not working for a given view, if for example, the data in the underly‐
       ing tables is changing to frequently or the MV is taking up too much
       disk space.


       The name of the DB profile to use for database connectivity.  These
       profiles are kept in $GMOD_ROOT/conf (typically /usr/local/gmod/conf)
       and contain information like the database name, user name and password.
       The default value is ’default’ which was created when the Chado
       database was created.


       Gives a list of current MVs.


       Gives the status of all MVs, including whether they are considered cur‐
       rent or out of date.


       Prints documetation and quits.

Creating a materialized view example -c
Viewing 'chado' database on host localhost

Creating a new materialized view!

Give your materialized view a name (word characters only):all_feature_names

Where will this MV be located? (schemaname.tablename):public.all_feature_names

A view with this name already exists; do you want to replace it
with a materialized view? [y|n] y

How often, in seconds, should the MV be refreshed?
You can also type 'daily', 'weekly', 'monthly' (30 days), or 'yearly' (365 days):daily
Using refresh_time of 86400 seconds

Enter specifications for the materialized view, OR provide a file in which
the specs are written ('? for help):feature_id integer,name varchar(255)

Enter the SQL query for the materialized view,
or a file containing only the query:SELECT feature_id,uniquename FROM feature UNION SELECT feature_id, name FROM feature UNION SELECT fs.feature_id, FROM feature_synonym fs, synonym s WHERE fs.synonym_id = s.synonym_id

Enter a comma separated list of fields to index (or return for none):feature_id,name

Enter the SQL queries for special indexes,
or a file containing only the query (or return for none):create index all_feature_names_lower_name on all_feature_names (lower(name))

Confirm that the following is correct:
Name: all_feature_names
Location: public.all_feature_names
Refresh Time (sec): 86400
MV creation query: CREATE TABLE public.test_view ( feature_id integer,name varchar(255) )
Query: SELECT feature_id,uniquename FROM feature UNION SELECT feature_id, name FROM feature UNION SELECT fs.feature_id, FROM feature_synonym fs, synonym s WHERE fs.synonym_id = s.synonym_id
Indexes on: feature_id,name
Special index query: create index all_feature_names_lower_name on all_feature_names (lower(name))

Enter 'y' to confirm, 'n' to re-enter data: y

And it goes on to create the table and populate it, giving real time estimates of completion time.

Example crontab entry to maintain materialized views

Cron is a unix tool for executing commands on a regular specified interval. To create a new crontab entry, execute the command crontab -e, and enter the special syntax and the command to be run. For example, if you wanted the materialized view tool to run every night at 1:16AM, you would enter this:

 16 1 * * * /usr/bin/ -a

Where the stars mean 'every day of the month', 'every month of the year' and 'every day of the week' respectively.


The script described in this page was originally written by Chris Carpita and modified by Scott Cain