PostgreSQL Materialized views

Materialized Views in PostgreSQL are a powerful tool that can significantly enhance query performance by physically storing the result of a complex query. Unlike standard views, which compute their result set every time they are queried, materialized views store the query result and can be refreshed periodically. This makes them ideal for scenarios where fast data access is required.

Creating materialized views

To create a materialized view, you use the CREATE MATERIALIZED VIEW statement as follows:

CREATE MATERIALIZED VIEW [IF NOT EXISTS] view_name AS query WITH [NO] DATA;

How it works.

First, specify the view_name after the CREATE MATERIALIZED VIEW clause
Second, add the query that retrieves data from the underlying tables after the AS keyword.
Third, if you want to load data into the materialized view at the creation time, use the WITH DATA option; otherwise, you use WITH NO DATA option. If you use the WITH NO DATA option, the view is flagged as unreadable. It means that you cannot query data from the view until you load data into it.
Finally, use the IF NOT EXISTS option to conditionally create a view only if it does not exist.

Refreshing data for materialized views

To load data into a materialized view, you use the REFRESH MATERIALIZED VIEW statement:

REFRESH MATERIALIZED VIEW view_name;

When you refresh data for a materialized view, PostgreSQL locks the underlying tables. Consequently, you will not be able to retrieve data from underlying tables while data is loading into the view.

To avoid this, you can use the CONCURRENTLY option.

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

With the CONCURRENTLY option, PostgreSQL creates a temporary updated version of the materialized view, compares two versions, and performs INSERT and UPDATE only the differences.

PostgreSQL allows you to retrieve data from a materialized view while it is being updated. One requirement for using CONCURRENTLY option is that the materialized view must have a UNIQUE index.

Notice that CONCURRENTLY option is only available in PostgreSQL 9.4 or later.

Removing materialized views

To remove a materialized view, you use the DROP MATERIALIZED VIEW statement:

DROP MATERIALIZED VIEW view_name;

In this syntax, you specify the name of the materialized view that you want to drop after the DROP MATERIALIZED VIEW keywords.

Scheduled Refresh Using pg_cron (Database-Level)

If you prefer managing the schedule within PostgreSQL, you can use the pg_cron extension.

Install pg_cron:

sudo apt install postgresql-<version>-cron

Enable it in the postgresql.conf:

shared_preload_libraries = 'pg_cron'

Create the extension in the database

CREATE EXTENSION IF NOT EXISTS pg_cron;

Schedule the refresh:

SELECT cron.schedule('0 * * * *', $$REFRESH MATERIALIZED VIEW your_mat_view$$);

This example refreshes the materialized view every hour.

4. Optional: View or manage scheduled jobs

Check all scheduled jobs:

SELECT * FROM cron.job;

Delete a job:

SELECT cron.unschedule(jobid);

See Job run details:

SELECT * FROM cron.job_run_details ORDER BY start_time DESC;

Cron failed and error message is "connection failed"

Set the authentication method in pg_hba.conf to trust

instead of

# TYPE  DATABASE        USER            ADDRESS                 METHOD

local   all             all                                     peer
# IPv4 local connections:
host    all             all             0.0.0.0/0               peer

to:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

local   all             all                                     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0               trust

in the above change peer to trust