Views and Materialized Views in PostgreSQL: Updatable Views, security_invoker, REFRESH CONCURRENTLY and Scheduling with pg_cron

Version note: The examples in this post were run on PostgreSQL 18.7 and pg_cron 1.6.8 (a container built with the Dockerfile from post 13, which adds pg_cron from the PGDG package to the official postgres:18 image), right after loading the Bookstore schema; the .NET example was built and run with .NET 10, Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 and EF Core 10.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, uses a view that returns books together with the author's name for the catalog screen. Emre took the shortcut and wrote the view with b.*. Some time later a column is added to books, someone wants to drop a column from the view, and the type of title is changed:

CREATE VIEW v_book_list AS
SELECT b.*, a.name AS author_name
FROM books AS b
JOIN authors AS a ON a.author_id = b.author_id;
ALTER TABLE books ADD COLUMN subtitle text;
SELECT * FROM v_book_list WHERE book_id = 1;
CREATE OR REPLACE VIEW v_book_list AS
SELECT b.book_id, b.isbn, b.title, a.name AS author_name
FROM books AS b
JOIN authors AS a ON a.author_id = b.author_id;
ALTER TABLE books ALTER COLUMN title TYPE varchar(300);

Does the new subtitle column show up in the view? Does CREATE OR REPLACE replace the view with fewer columns? Does the type change go through?

 book_id |     isbn      |  title  | author_id | category | price | published_year | author_name
---------+---------------+---------+-----------+----------+-------+----------------+-------------
       1 | 9780000000001 | Emma #1 |      1818 | Classic  | 32.77 |           1885 | Ken Turing

ERROR:  cannot drop columns from view

ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view v_book_list depends on column "title"

The answer to all three is "no". * is expanded to the column list at the moment the view is created, and the view is stored that way; subtitle, added later, does not get into the view. CREATE OR REPLACE VIEW only allows adding columns; it cannot drop an existing column, rename it or change its type. And the type of a column used by a view cannot be changed while the view exists. In SQL Server, views with SELECT * also stay on the old column list (you need sp_refreshview), but a type change is not blocked; PostgreSQL keeps the dependency strict. In this post we look at these rules of views, updatable views, how views relate to security, materialized views, and scheduling them with pg_cron.

Limits of Views: CREATE OR REPLACE and Dependencies

The real definition of a view and the views that depend on a table can be read from the catalog:

ALTER TABLE books DROP COLUMN subtitle;
SELECT pg_get_viewdef('v_book_list');
SELECT DISTINCT v.relname AS dependent_view
FROM pg_depend AS d
JOIN pg_rewrite AS r ON r.oid = d.objid
JOIN pg_class AS v ON v.oid = r.ev_class
WHERE d.refobjid = 'books'::regclass AND v.oid <> 'books'::regclass;

ALTER TABLE

                     pg_get_viewdef
--------------------------------------------------------
  SELECT b.book_id,                                    +
     b.isbn,                                           +
     b.title,                                          +
     b.author_id,                                      +
     b.category,                                       +
     b.price,                                          +
     b.published_year,                                 +
     a.name AS author_name                             +
    FROM (books b                                      +
      JOIN authors a ON ((a.author_id = b.author_id)));

 dependent_view
----------------
 v_book_list

subtitle, which the view does not use, was dropped without trouble. The stored definition has no *; the seven columns are written out one by one. PostgreSQL stores a view as a rewrite rule (_RETURN) and keeps a dependency in pg_depend between the view and every column it uses; that is where the "rule _RETURN" in the error message comes from. The query above shows which views will be affected before you touch a table.

In practice a migration that changes a column type is written in this order: drop the dependent views with DROP VIEW, change the table, recreate the views; all in one transaction. In PostgreSQL DDL runs inside transactions, so this is safe: if one step fails, everything is rolled back. DROP ... CASCADE looks like a shortcut, but it also silently drops other views built on top of these views; do not use it without knowing the list. Not writing SELECT * in views is a good habit for the same reason: when the column list is spelled out, what the view depends on can be read from its definition.

Updatable Views and WITH CHECK OPTION

A view that reads from a single table and contains no GROUP BY, DISTINCT, aggregates, UNION or similar constructs is automatically updatable in PostgreSQL: INSERT, UPDATE and DELETE on it are redirected to the underlying table. A view for the team that manages science fiction books:

CREATE VIEW v_scifi_books AS
SELECT book_id, isbn, title, author_id, category, price, published_year
FROM books
WHERE category = 'SciFi';
UPDATE v_scifi_books SET price = price + 1 WHERE book_id = 5 RETURNING book_id, title, price;
INSERT INTO v_scifi_books (isbn, title, author_id, category, price, published_year)
VALUES ('9780000040001', 'The Hobbit #40001', 1, 'Fantasy', 19.90, 1937)
RETURNING book_id;
SELECT count(*) AS visible_in_view FROM v_scifi_books WHERE book_id = 40001;

 book_id |     title     | price
---------+---------------+-------
       5 | Foundation #5 | 16.34

 book_id
---------
   40001

 visible_in_view
-----------------
               0

The UPDATE worked as expected. But adding a Fantasy book through the SciFi view did not fail either: the row was written to the table and immediately disappeared from the view. If the view is used as a permission boundary (the team can only write to the view), this is a hole. WITH CHECK OPTION applies the view's WHERE condition to the rows being written as well:

CREATE OR REPLACE VIEW v_scifi_books AS
SELECT book_id, isbn, title, author_id, category, price, published_year
FROM books
WHERE category = 'SciFi'
WITH LOCAL CHECK OPTION;
INSERT INTO v_scifi_books (isbn, title, author_id, category, price, published_year)
VALUES ('9780000040002', 'The Hobbit #40002', 1, 'Fantasy', 19.90, 1937);
UPDATE v_scifi_books SET category = 'Fantasy' WHERE book_id = 5;
CREATE VIEW v_category_stats AS
SELECT category, count(*) AS books, avg(price) AS avg_price FROM books GROUP BY category;
UPDATE v_category_stats SET books = 0 WHERE category = 'SciFi';

ERROR:  new row violates check option for view "v_scifi_books"
DETAIL:  Failing row contains (40002, 9780000040002, The Hobbit #40002, 1, Fantasy, 19.90, 1937).

ERROR:  new row violates check option for view "v_scifi_books"
DETAIL:  Failing row contains (5, 9780000000005, Foundation #5, 2061, Fantasy, 16.34, 1938).

ERROR:  cannot update view "v_category_stats"
DETAIL:  Views containing GROUP BY are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

Now both adding a row outside the view and an update that would move an existing row out of the view are rejected. LOCAL checks only this view's condition; CASCADED (the default) also checks the conditions of the views it is built on. A view with GROUP BY is not automatically updatable; the error message names two ways out: an INSTEAD OF trigger or a rule. We will see INSTEAD OF triggers in post 23; the rule system is old and full of surprises, so it is not recommended for new code. is_updatable in information_schema.columns shows which columns of a view can be written.

Views and Security: security_invoker, security_barrier and RLS

Similar to ownership chaining in SQL Server, a view in PostgreSQL accesses the underlying tables with the privileges of the view's owner by default. That makes it possible to grant a user access to the view only, not to the table; but combined with RLS (post 14) it produces a surprise. A small table for customer notes, a policy for the application role and two views:

CREATE TABLE customer_notes
(
    note_id     int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id int  NOT NULL REFERENCES customers (customer_id),
    note        text NOT NULL
);
INSERT INTO customer_notes (customer_id, note)
SELECT 1 + g % 5, 'note ' || g FROM generate_series(1, 50) AS g;
ALTER TABLE customer_notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY customer_notes_own ON customer_notes FOR SELECT TO bookstore_app
    USING (customer_id = current_setting('app.customer_id')::int);
CREATE VIEW v_customer_notes AS SELECT customer_id, note FROM customer_notes;
CREATE VIEW v_customer_notes_invoker WITH (security_invoker = true) AS
    SELECT customer_id, note FROM customer_notes;

We ran the same query as bookstore_app, with customer 3 selected in the session, against the table and both views:

SET app.customer_id = '3';
SELECT 'table' AS source, count(*) AS rows, count(DISTINCT customer_id) AS customers FROM customer_notes
UNION ALL
SELECT 'view (owner)', count(*), count(DISTINCT customer_id) FROM v_customer_notes
UNION ALL
SELECT 'view (security_invoker)', count(*), count(DISTINCT customer_id) FROM v_customer_notes_invoker;

         source          | rows | customers
-------------------------+------+-----------
 table                   |   10 |         1
 view (owner)            |   50 |         5
 view (security_invoker) |   10 |         1

When the table was queried directly, the policy applied: only the 10 notes of customer 3. The default view returned all 50 notes of five customers: the view accesses the table with the privileges of its owner (bookstore_owner), and the table's owner is not subject to RLS. security_invoker = true, added in PostgreSQL 15, runs the view with the privileges and policies of the user who queries it; the result is 10 rows again. If you write views in a schema that uses RLS, this option is almost always the right one. Multi-tenancy design with RLS is covered in post 13 of the Çözüm Desenleri series.

security_barrier solves a different problem. Even if the view's WHERE restricts the rows, the planner may run a cheap function from the user's query before the view's condition; if that function writes its argument into an error message or a RAISE NOTICE, rows the view is supposed to hide leak out. In a view marked with security_barrier, functions not marked LEAKPROOF run after the view's condition. The price is fewer plan options; turn it on only if you really use the view as a security boundary.

Materialized Views: a View That Stores Its Result

A normal view is only a query definition; the query runs again on every read. A materialized view writes the result to disk like a table and is updated only when it is explicitly refreshed with REFRESH. That is the difference from SQL Server's indexed views: SQL Server keeps an indexed view up to date synchronously on every INSERT/UPDATE, while PostgreSQL does not update it at all and leaves freshness to you. In return there are no restrictions: LIMIT, window functions, outer joins, anything you like. For "the top 100 best-selling books of the last 30 days" on Bookstore's home page:

CREATE MATERIALIZED VIEW mv_top_books AS
WITH window_start AS (
    SELECT max(created_at) - interval '30 days' AS since FROM orders
)
SELECT rank() OVER (ORDER BY sum(oi.quantity) DESC, oi.book_id) AS rank,
       oi.book_id, b.title, b.category,
       sum(oi.quantity) AS units,
       sum(oi.quantity * oi.unit_price) AS revenue
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN books AS b ON b.book_id = oi.book_id
WHERE o.created_at >= (SELECT since FROM window_start)
  AND o.status <> 'Cancelled'
GROUP BY oi.book_id, b.title, b.category
ORDER BY units DESC, oi.book_id
LIMIT 100
WITH NO DATA;
SELECT * FROM mv_top_books LIMIT 3;
REFRESH MATERIALIZED VIEW mv_top_books;
SELECT rank, book_id, title, category, units, revenue FROM mv_top_books ORDER BY rank LIMIT 5;
SELECT pg_size_pretty(pg_total_relation_size('mv_top_books')) AS size, ispopulated
FROM pg_matviews WHERE matviewname = 'mv_top_books';

CREATE MATERIALIZED VIEW
Time: 4.025 ms
ERROR:  materialized view "mv_top_books" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.
REFRESH MATERIALIZED VIEW
Time: 158.855 ms

 rank | book_id |      title      | category | units | revenue
------+---------+-----------------+----------+-------+---------
    1 |       1 | Emma #1         | Classic  |   285 | 9339.45
    2 |       2 | The Hobbit #2   | Fantasy  |   123 | 4797.00
    3 |       4 | Frankenstein #4 | Classic  |    90 | 3852.00
    4 |       3 | Middlemarch #3  | Classic  |    83 | 2962.27
    5 |       5 | Foundation #5   | SciFi    |    76 | 1165.84
Time: 0.187 ms

 size  | ispopulated
-------+-------------
 32 kB | t

Since the lab data ends on 20 October, we computed "the last 30 days" from the newest order rather than now(); in a real system you write now() - interval '30 days'. A materialized view created WITH NO DATA is an empty shell: reading it raises an error. Populating it took 159 ms (in this lab it scanned and grouped the lines of about 21,000 orders); reading it takes 0.2 ms. Every time the home page opens, a ready-made 100-row table is read instead of the grouping query.

REFRESH and REFRESH CONCURRENTLY

A plain REFRESH computes the result from scratch, replaces the whole content of the materialized view, and takes an ACCESS EXCLUSIVE lock while doing so: nobody can read the view until the refresh is done. CONCURRENTLY computes the new result separately, compares it with the old one and applies only the difference; it does not block reads. Its requirement is a unique index:

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_top_books;
CREATE UNIQUE INDEX ux_mv_top_books_book_id ON mv_top_books (book_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_top_books;
REFRESH MATERIALIZED VIEW mv_top_books;

ERROR:  cannot refresh materialized view "public.mv_top_books" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.
Time: 0.100 ms
CREATE INDEX
Time: 0.466 ms
REFRESH MATERIALIZED VIEW
Time: 115.788 ms
REFRESH MATERIALIZED VIEW
Time: 100.569 ms

Without a unique index CONCURRENTLY was rejected; the index is the key for matching old and new rows. With this small result the two refreshes take about the same time; because CONCURRENTLY computes a difference, it is slower than a plain refresh when many rows change, and the better choice when few rows change and read traffic is high. We saw the difference with two sessions. Session A starts the refresh in a transaction and waits four seconds; session B, a second later, looks at the locks being held and tries to read the view (lock_timeout = '2s'):

BEGIN;
REFRESH MATERIALIZED VIEW mv_top_books;
SELECT pg_sleep(4);
COMMIT;

SET lock_timeout = '2s';
SELECT l.mode, l.granted, a.query
FROM pg_locks AS l
JOIN pg_stat_activity AS a ON a.pid = l.pid
WHERE l.relation = 'mv_top_books'::regclass AND a.pid <> pg_backend_pid();
SELECT count(*) AS rows FROM mv_top_books;

What B sees during a plain REFRESH:

        mode         | granted |        query
---------------------+---------+---------------------
 AccessExclusiveLock | t       | SELECT pg_sleep(4);
 ExclusiveLock       | t       | SELECT pg_sleep(4);
 ShareLock           | t       | SELECT pg_sleep(4);

ERROR:  canceling statement due to lock timeout
LINE 1: SELECT count(*) AS rows FROM mv_top_books;
                                     ^

The same experiment with REFRESH MATERIALIZED VIEW CONCURRENTLY in A:

       mode       | granted |        query
------------------+---------+---------------------
 ExclusiveLock    | t       | SELECT pg_sleep(4);
 RowExclusiveLock | t       | SELECT pg_sleep(4);
 AccessShareLock  | t       | SELECT pg_sleep(4);

 rows
------
  100

With the plain refresh, the read waited two seconds and was cancelled; in a real application that means the home page hangs for the duration of the refresh. With CONCURRENTLY, the strongest lock A holds is ExclusiveLock: it blocks another refresh and writes, but not reads; B read the 100 rows immediately. We covered lock modes and pg_locks in detail in post 09.

Periodic Refresh with pg_cron

Who refreshes the materialized view and when is your decision. One solution that stays inside the database is pg_cron, which we set up in post 13. We installed the extension once as postgres and gave the view's owner the right to use the cron schema (GRANT USAGE ON SCHEMA cron TO bookstore_owner); the owner defines the job, because REFRESH can be run by the owner of the materialized view (or, since PostgreSQL 17, by a role granted the MAINTAIN privilege), and pg_cron runs a job as the user who defined it:

SELECT cron.schedule('refresh-top-books', '*/10 * * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_top_books$$) AS jobid;
SELECT jobid, jobname, schedule, command, username, active FROM cron.job;

 jobid
-------
     1

 jobid |      jobname      |   schedule   |                       command                       |    username     | active
-------+-------------------+--------------+-----------------------------------------------------+-----------------+--------
     1 | refresh-top-books | */10 * * * * | REFRESH MATERIALIZED VIEW CONCURRENTLY mv_top_books | bookstore_owner | t

This time we also loaded the roles from post 02 and the Bookstore schema into the bookstore-cron container from post 13; the steps are at the top of 21-cron.sql in the snapshot. After 00:30 (UTC) we looked at the job's run history:

SELECT jobid, status, return_message, end_time - start_time AS duration
FROM cron.job_run_details
ORDER BY runid DESC
LIMIT 3;

 jobid |  status   |      return_message       |    duration
-------+-----------+---------------------------+-----------------
     1 | succeeded | REFRESH MATERIALIZED VIEW | 00:00:00.464489

The job ran in the next 10-minute slot and finished successfully in under half a second. Had the refresh failed, status would be failed and return_message would hold the error message. cron.job_run_details grows by one row per run; a 10-minute job means 52,000 rows a year, so clean this table up too, with a job that deletes old records.

PostgreSQL does not record when a materialized view was last refreshed; pg_matviews has no such column. If you use pg_cron, cron.job_run_details is the source of that information; with another scheduler, writing the time into a small table after the refresh does the job. If you want to show "the data is at most 10 minutes old" on screen, you need that time. The status of pg_cron on managed services is in post 13: Azure Database for PostgreSQL and Amazon RDS support it, but it needs preloading in the parameter group. Where pg_cron is not available, a background job in the application does the same work; the only difference from the schedulers in the Background Job'lar series is that the job is defined outside the database.

Mapping a View as a Keyless Entity in EF Core

On the application side, a materialized view is read like an ordinary table. In EF Core, read-only results like this that have no primary key are defined as keyless entities; the Npgsql provider from post 12 works the same here:

public sealed class TopBook
{
    public long Rank { get; set; }
    public int BookId { get; set; }
    public string Title { get; set; } = "";
    public string Category { get; set; } = "";
    public long Units { get; set; }
    public decimal Revenue { get; set; }
}

// in ReportingContext.OnModelCreating
modelBuilder.Entity<TopBook>().HasNoKey().ToView("mv_top_books");

IQueryable<TopBook> query = db.TopBooks
    .Where(t => t.Category == "Fantasy")
    .OrderBy(t => t.Rank)
    .Take(3);

-- @p='3'
SELECT m.book_id, m.category, m.rank, m.revenue, m.title, m.units
FROM mv_top_books AS m
WHERE m.category = 'Fantasy'
ORDER BY m.rank
LIMIT @p

  2      2 The Hobbit #2         123   4797.00
 17     17 The Silmarillion #17   34   1333.82
 19     42 The Hobbit #42         34    529.38
tracked: 0
saved: 0

Property types must match the types SQL returns: rank() and sum over a smallint return bigint, which is why Rank and Units are long. A keyless entity does not enter the change tracker; when we changed the first book's Units and called SaveChanges, nothing was written. ToView tells EF Core migrations "this is not a table, do not create it"; you have to create the materialized view itself in a migration with migrationBuilder.Sql(...). Because the ReportingContext in the lab does the snake_case conversion by hand, we defined the view mapping before the conversion loop; the EFCore.NamingConventions package from post 12 handles this by itself.

When a Materialized View, When a Read Model Table?

The limit of a materialized view is its freshness model: every refresh runs the whole query again. If the query takes 159 ms, a refresh every 10 minutes is a negligible load; if the query takes 10 minutes, the next refresh is due before the current one finishes. PostgreSQL core has no incremental materialized view (one that processes only the changed rows); for that you need either an extension or your own table.

Your own read model table updates only the affected rows when something changes (through a trigger, an outbox message or an event); it is fresher and cheaper on large data, but the synchronization code is your responsibility. The "Ayrı Read Model" post of the CQRS ve Event Sourcing series discusses this choice and living with eventual consistency. Roughly: if the result is small, a delay of a few minutes is acceptable and the query is fast, use a materialized view; if the data must be current within seconds or the refresh is expensive, use a read model table.

In Production

Plan the CONCURRENTLY refresh so that only one runs at a time for the same view. pg_cron does not start a job while its previous run is still going, but a manually triggered refresh can collide with it; setting lock_timeout limits the wait.

A refresh leaves dead tuples in the materialized view as well, especially CONCURRENTLY, which applies the difference by deleting and inserting rows one by one. Check in pg_stat_user_tables that autovacuum cleans it up too (post 08).

Privileges: the application role needs GRANT SELECT to read the materialized view; the ALTER DEFAULT PRIVILEGES from post 02 covers materialized views as well. A refresh, however, can only be run by the owner or a role with MAINTAIN; when the application role tried, it got permission denied for materialized view mv_top_books. Instead of giving the application role that privilege, let the owner's job do the refresh.

Common Pitfalls

Forgetting the unique index for CONCURRENTLY: the refresh job fails every 10 minutes, and if nobody looks at cron.job_run_details, the data stays stale for days. Connect the job's last status to your monitoring system.

Being unable to change a column because of dependent objects: when writing the migration, find the dependent views in pg_depend, drop and recreate them in the same transaction. Do not rely on CASCADE.

Blocking reads with a plain REFRESH: a refresh of a few seconds that nobody notices in the test environment turns into the home page waiting on every refresh in production.

Writing a default view over a table with RLS: the view runs with the owner's privileges, so the policy is bypassed; use security_invoker = true.

A Small Experiment

★☆☆ Using CREATE OR REPLACE VIEW, try separately to add a new column to v_book_list after published_year and to swap the positions of two existing columns. Which one is accepted?

★★☆ Add 500 new orders dated 2026-10-20 with their lines to orders, then refresh mv_top_books with CONCURRENTLY. Compare n_tup_ins, n_tup_upd and n_tup_del in pg_stat_user_tables before and after; how many rows actually changed?

★★★ Instead of a materialized view, design a read model table called top_books for "the top 100 best-selling books" and a job that updates it: it should recompute the numbers only for books that received orders in the last 10 minutes. Compare the refresh time and the difference on the read side for the two solutions.

In the next post we move on to functions and procedures: LANGUAGE sql versus PL/pgSQL, RETURNS TABLE, the volatility categories, SECURITY DEFINER and transaction control with CALL.