REFRESH MATERIALIZED VIEW in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the REFRESH MATERIALIZED VIEW command do in PostgreSQL?

It updates the data stored in a materialized view to match the current data in the underlying tables.

beginner

Why do we need to refresh a materialized view?

Because a materialized view stores a snapshot of data, it can become outdated when the original tables change. Refreshing updates it to show the latest data.

intermediate

What is the difference between REFRESH MATERIALIZED VIEW and a regular view?

A regular view always shows current data by running its query each time. A materialized view stores data physically and needs to be refreshed to update.

intermediate

What does the CONCURRENTLY option do in REFRESH MATERIALIZED VIEW?

It refreshes the materialized view without locking it for selects, allowing users to query it during the refresh process.

advanced

Can you refresh a materialized view if it has no unique index when using CONCURRENTLY?

No, the materialized view must have a unique index to use the CONCURRENTLY option.

What happens when you run REFRESH MATERIALIZED VIEW my_view;?

A The materialized view data is updated to match the current data.

B The materialized view is deleted.

C The underlying tables are refreshed.

D Nothing happens; it only checks the view.

Refreshing updates the stored data in the materialized view to reflect current data.

Which option allows querying a materialized view during refresh?

A NOWAIT

B IMMEDIATE

C CONCURRENTLY

D LOCKED

The CONCURRENTLY option refreshes without locking the view for selects.

What must a materialized view have to use REFRESH MATERIALIZED VIEW CONCURRENTLY?

A No special requirement

B A foreign key

C A trigger

D A unique index

A unique index is required for concurrent refresh to track rows.

How often does a materialized view update automatically?

A Every time the underlying tables change

B Only when manually refreshed

C Every minute by default

D When the database restarts

Materialized views update only when you run REFRESH MATERIALIZED VIEW.

Which statement is true about regular views compared to materialized views?

A Regular views run their query each time they are accessed.

B Materialized views always show live data without refresh.

C Materialized views cannot be refreshed.

D Regular views store data physically.

Regular views run their query on demand, showing current data.

Explain what a materialized view is and why you need to refresh it.

Think about how a photo snapshot differs from a live video.

Describe the purpose and requirements of the CONCURRENTLY option in REFRESH MATERIALIZED VIEW.

It helps users keep using the view while it updates.