Amazon Aurora: Using Materialized Views

Why use Materialized Views?

Materialized Views are common in Oracle databases. They are especially useful when some set of data is repeatedly needed—such as in an oft-run report. These views can also be defined in Aurora, but there are important differences.

The sytax for creating the MV is the same as in Oracle.

Create Materialize View MVNEW as Select from ...

Key Differences in Aurora

Although the syntax to create the MV is like Oracle, there are important differences. Firstly, the command to refresh is different (and simpler)

REFRESH MATERIALIZED VIEW mvnew;

A Crucial Difference

In Aurora, refresh will always be Complete. Some Aurora documentation recommends creating a trigger to automatically command a refresh after any update to the underlying table. This option will not be appropriate in many cases. You can see how running a complete refresh would be a disaster in many cases. Instead, one should schedule the refresh. This is accomplished using the database extension, pg_cron.

Pg_Cron Setup

There are some preliminary steps before this scheduler can be used. First, pg_cron needs to be added to the parameter, shared_preload_libraries. Then, administrator should restart the database instance. From a database account with superuser privilege, create the extension: CREATE EXTENSION pg_cron. All of the pg_cron objects run in the database called postgres. Users who need to use pg_cron can be granted privilege using command, GRANT USER ON SCHEMA cron to user;  (That user also needs to have permission on the underlying tables.)

Scheduling the MV Refresh

Use the function, cron.schedule to inititate a job in the default postgres database. The return value is the job_id. The syntax is:

cron.schedule (job_name, schedule, command);

The schedule entry uses the same cron syntax as normally used in Unix. Note that job_name is optional.

The cron entry uses the format: Minute Hour Day Month Day-of-Week. Thus, the following command schedules a refresh of our MV “ABC” at noon every day:

SELECT cron.schedule ('REFRESHMV',0 12 * * *, 'REFRESH MATERIALIZED VIEW ABC');

Removing a scheduled job uses the function, cron.unschedule().

Other Notes

The status of run jobs can be found in the table, cron.job_run_details.

The following are key Parameters related to pg_cron:

cron.host: The hostname to connect to PostgreSQL. 

cron.log_run: Log each job in job_run_details. 

cron.log_statement: Log all cron statements 

cron.max_running_jobs: Maximum number of concurrent jobs.

cron.use_background_workers Use background workers instead of client sessions. 

Display current settings of above parameters using:

SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%';