Refresh PostgreSQL Materialized View on a Schedule
PostgreSQL materialized views store the result of a query on disk, which makes reads fast but means the data goes stale over time. Refreshing with CONCURRENTLY lets readers continue using the view without any locking while the refresh runs, making it safe to run on a busy production database. A scheduled job paired with a heartbeat monitor ensures the refresh actually completes on time and you get alerted if it ever silently fails.
Schedule
0 * * * *Every hour at the top of the hour
Setup
- 1
Create a unique index on the materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY requires at least one unique index on the view. Run: CREATE UNIQUE INDEX CONCURRENTLY ON your_schema.your_view (id); — replace id with a column or combination of columns that is unique per row. Without this index, PostgreSQL will reject the CONCURRENTLY option.
- 2
Create the refresh script on your server
Save the shell script (shown below) to a path such as /opt/scripts/refresh_matview.sh. Make it executable with: chmod +x /opt/scripts/refresh_matview.sh. Store your database credentials in a .pgpass file or a dedicated PostgreSQL role with minimal privileges rather than hardcoding them in the script.
- 3
Set up a CronJobPro heartbeat monitor
In CronJobPro, create a new Heartbeat monitor. Set the expected period to match your schedule (1 hour) and add a grace period of 5 to 10 minutes to allow for the refresh duration. Copy the unique ping URL you receive — it will look like https://cronjobpro.com/ping/<token>. Paste this URL into the HEARTBEAT_URL variable in your script.
- 4
Add the cron entry
Open your crontab with: crontab -e — and add the line shown in the schedule section. Redirect output to a log file so you can review past runs: 0 * * * * /opt/scripts/refresh_matview.sh >> /var/log/refresh_matview.log 2>&1
- 5
Verify the first run
Wait for the next scheduled run or trigger the script manually. Check /var/log/refresh_matview.log to confirm it exited cleanly. In CronJobPro, open the heartbeat monitor and confirm the last ping timestamp updated. Query pg_stat_user_tables or pg_matviews to verify the last_refresh timestamp advanced.
The script
bash
#!/usr/bin/env bash
set -euo pipefail
# Configuration — adjust these variables for your environment
DB_HOST="localhost"
DB_PORT="5432"
DB_NAME="your_database"
DB_USER="your_user"
MATVIEW="your_schema.your_view"
HEARTBEAT_URL="https://cronjobpro.com/ping/YOUR_TOKEN_HERE"
log() {
echo "[$(date -u '+%Y-%m-%dT%H:%M:%SZ')] $*"
}
log "Starting refresh of materialized view: ${MATVIEW}"
# Run the refresh; psql exits non-zero on SQL errors when using -v ON_ERROR_STOP=1
if psql \
--host="${DB_HOST}" \
--port="${DB_PORT}" \
--username="${DB_USER}" \
--dbname="${DB_NAME}" \
-v ON_ERROR_STOP=1 \
-c "REFRESH MATERIALIZED VIEW CONCURRENTLY ${MATVIEW};"; then
log "Refresh completed successfully."
# Ping the heartbeat URL so CronJobPro knows the job succeeded
curl --silent --max-time 10 --retry 3 --retry-delay 5 \
--output /dev/null \
"${HEARTBEAT_URL}"
log "Heartbeat ping sent."
exit 0
else
EXIT_CODE=$?
log "Refresh FAILED with exit code ${EXIT_CODE}."
# Report the failure to CronJobPro
curl --silent --max-time 10 --retry 3 --retry-delay 5 \
--output /dev/null \
"${HEARTBEAT_URL}/exitcode/${EXIT_CODE}"
exit "${EXIT_CODE}"
fi
Monitor it
Create a Heartbeat monitor in CronJobPro and set its period to 1 hour with a grace period of 5 to 10 minutes. The script calls the /ping/token URL only when the refresh exits cleanly, and calls /ping/token/exitcode/N when psql returns an error. This means CronJobPro receives a signal only when the database operation itself succeeded — not just when the cron entry fired. If the ping does not arrive within the period plus grace, CronJobPro sends an alert to whichever channels you configure: email, Slack, Discord, Teams, PagerDuty, Opsgenie, or a generic webhook. Common situations the heartbeat catches that cron alone cannot: the database was down, psql authentication failed, the unique index was dropped so CONCURRENTLY was rejected, or the server ran out of disk and the script was killed mid-run. Check the heartbeat history log in CronJobPro alongside your /var/log/refresh_matview.log to correlate timing when diagnosing slow refreshes.
Frequently asked questions
Why does REFRESH MATERIALIZED VIEW CONCURRENTLY require a unique index?
PostgreSQL uses the unique index to identify which rows have changed between the old and new snapshot so it can apply updates incrementally without replacing the entire view in one locked operation. Without a unique index, the database has no reliable way to match old rows to new rows, so the CONCURRENTLY option is rejected. You must create the index before you can use this form of refresh.
How do I avoid locking reads during the refresh?
Using REFRESH MATERIALIZED VIEW CONCURRENTLY is the right approach. Without CONCURRENTLY, PostgreSQL takes an exclusive lock on the view for the entire duration of the refresh, blocking all reads. With CONCURRENTLY, it builds a new snapshot in a separate transaction and then swaps in only the changed rows, so readers see the old data until the swap completes and are never blocked.
What PostgreSQL user permissions are needed to run the refresh?
The database user running the script must be the owner of the materialized view, or have been explicitly granted the privilege. A minimal approach is to create a dedicated role (for example refresh_worker), grant it ownership or REFRESH privilege on the specific view, and configure .pgpass or a PostgreSQL service file (pg_service.conf) so the script authenticates without storing a plaintext password in the cron line.
How do I handle a refresh that takes longer than the scheduled interval?
If the refresh duration approaches or exceeds the schedule interval, two instances of the script could run concurrently and contend on the view. A simple guard is to use a file-based lock at the top of the script with flock: ( flock -n 9 || exit 1; ... ) 9>/var/lock/refresh_matview.lock. This causes a second instance to exit immediately rather than queue up. If refreshes routinely exceed the interval, consider lengthening the schedule or optimizing the underlying query.