PostgreSQL Async Views: A Trigger-based Dirty Flag Pattern with pg_cron

The Inherent Bottleneck of Materialized Views

Materialized views in PostgreSQL are a powerful tool for pre-calculating the results of complex and expensive queries, effectively trading storage for query performance. For dashboards, reporting, and analytics endpoints, they can be the difference between a sub-second response and a multi-minute timeout. However, their primary weakness lies in the refresh mechanism. The standard REFRESH MATERIALIZED VIEW command, while simple, presents a significant challenge in production environments:

PostgreSQL introduced REFRESH MATERIALIZED VIEW CONCURRENTLY to address the locking problem. It avoids the EXCLUSIVE LOCK, allowing reads to continue during the refresh process. This is a massive improvement, but it doesn't solve the synchronous execution or resource contention issues. Furthermore, CONCURRENTLY has its own requirements (a UNIQUE index on the view) and can be even more resource-intensive than a standard refresh.

For high-throughput systems where base tables are constantly changing, triggering a synchronous REFRESH after every transaction is infeasible. The performance overhead would cripple the application. The challenge, therefore, is to decouple the refresh process from the application's write path, moving to an asynchronous, eventually consistent model that protects the performance of the primary workload.

This article details a robust, database-centric pattern to achieve exactly that. We will build a system that:

* Uses lightweight triggers on base tables to flag a materialized view as "dirty".

* Leverages a background worker, pg_cron, to periodically check for dirty views.

* Executes the refresh asynchronously and safely, with proper locking to prevent race conditions.

* Is resilient to refresh failures.

The Core Architecture: A Dirty Flag Control Plane

The foundation of our asynchronous system is a simple control table. This table doesn't store business data; it stores metadata about the state of our materialized views. It acts as the single source of truth for our background refresh worker.

Let's define the schema for this control table:

CREATE TABLE public.materialized_view_state (
    view_name text PRIMARY KEY,
    is_dirty boolean NOT NULL DEFAULT false,
    last_refreshed_at timestamptz,
    last_dirtied_at timestamptz
);

COMMENT ON TABLE public.materialized_view_state IS 'Tracks the refresh state of materialized views for the async refresh system.';
COMMENT ON COLUMN public.materialized_view_state.view_name IS 'The name of the materialized view.';
COMMENT ON COLUMN public.materialized_view_state.is_dirty IS 'True if the view needs a refresh, false otherwise.';
COMMENT ON COLUMN public.materialized_view_state.last_refreshed_at IS 'Timestamp of the last successful refresh completion.';
COMMENT ON COLUMN public.materialized_view_state.last_dirtied_at IS 'Timestamp of the last write that marked this view as dirty.';

This table is the central nervous system of our pattern:

* view_name: The primary key, identifying the materialized view we're tracking.

* is_dirty: A simple boolean flag. Our triggers will set this to true. Our refresh worker will set it to false.

* last_refreshed_at: Crucial for monitoring and for application layers to understand the data's freshness.

* last_dirtied_at: Useful for debugging and understanding the latency between a change and its reflection in the view.

Step 1: The Ultra-Lightweight Trigger Function

The most performance-critical part of this pattern is the trigger that fires on every INSERT, UPDATE, or DELETE on the base tables. This trigger must be extraordinarily fast to avoid adding noticeable overhead to application writes.

Our strategy is to make the trigger function do the absolute minimum amount of work possible: set a flag. It will not perform any calculations or complex logic. It will execute a single, highly-optimized INSERT ... ON CONFLICT statement.

Here is the generic trigger function:

CREATE OR REPLACE FUNCTION public.mark_materialized_view_as_dirty()
RETURNS TRIGGER AS $$
DECLARE
    view_name_to_update text := TG_ARGV[0];
BEGIN
    INSERT INTO public.materialized_view_state (view_name, is_dirty, last_dirtied_at)
    VALUES (view_name_to_update, true, now())
    ON CONFLICT (view_name)
    DO UPDATE SET
        is_dirty = true,
        last_dirtied_at = now();

    RETURN NULL; -- The result is ignored for AFTER triggers
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION public.mark_materialized_view_as_dirty() IS 'A generic trigger function to mark a materialized view as dirty in the state table. Expects the view name as the first argument (TG_ARGV[0]).';

Key Design Decisions:

Step 2: The Idempotent Refresh Procedure

Next, we need the logic that our background worker will execute. This logic should be encapsulated in a stored procedure. The procedure will find all dirty views and attempt to refresh them one by one.

This procedure needs to be robust. What if the cron job schedule is every minute, but a refresh takes three minutes? We could have multiple workers trying to refresh the same view simultaneously. To prevent this, we will use PostgreSQL's advisory locks.

CREATE OR REPLACE PROCEDURE public.refresh_dirty_materialized_views()
LANGUAGE plpgsql AS $$
DECLARE
    dirty_view record;
    lock_id bigint;
    lock_acquired boolean;
BEGIN
    -- Loop through all views marked as dirty
    FOR dirty_view IN
        SELECT view_name FROM public.materialized_view_state WHERE is_dirty = true
    LOOP
        -- Generate a unique, stable lock ID for each view name.
        -- hashtext generates a 32-bit integer hash. We cast to bigint to be safe.
        lock_id := hashtext(dirty_view.view_name);

        -- Try to acquire a transaction-level advisory lock. 
        -- pg_try_advisory_xact_lock is non-blocking.
        SELECT pg_try_advisory_xact_lock(lock_id) INTO lock_acquired;

        IF lock_acquired THEN
            RAISE NOTICE 'Acquired lock for view %. Refreshing...', dirty_view.view_name;

            -- Pre-emptively mark as clean. This prevents a race condition where a new write
            -- dirties the flag while we are in the middle of a long refresh.
            UPDATE public.materialized_view_state
            SET is_dirty = false
            WHERE view_name = dirty_view.view_name;

            BEGIN
                -- The core refresh operation.
                EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I', dirty_view.view_name);

                -- On success, update the last_refreshed_at timestamp.
                UPDATE public.materialized_view_state
                SET last_refreshed_at = now()
                WHERE view_name = dirty_view.view_name;
                
                RAISE NOTICE 'Successfully refreshed view %.', dirty_view.view_name;

            EXCEPTION
                WHEN OTHERS THEN
                    -- If the REFRESH fails, mark it as dirty again so we can retry.
                    RAISE WARNING 'Failed to refresh materialized view %: %', dirty_view.view_name, SQLERRM;
                    UPDATE public.materialized_view_state
                    SET is_dirty = true
                    WHERE view_name = dirty_view.view_name;
                    -- The transaction-level lock will be released automatically on commit/rollback.
            END;
            
            -- The lock is released automatically at the end of the transaction.
        ELSE
            RAISE NOTICE 'Could not acquire lock for view %. Another process is likely refreshing it. Skipping.', dirty_view.view_name;
        END IF;
    END LOOP;
END;
$$;

COMMENT ON PROCEDURE public.refresh_dirty_materialized_views() IS 'Scans the materialized_view_state table for dirty views and refreshes them concurrently, using advisory locks to prevent race conditions.';

Critical Production Patterns in this Procedure:

Step 3: Scheduling with `pg_cron`

With the logic in place, the final step is to schedule its execution. pg_cron is a simple cron-based job scheduler that runs as a PostgreSQL extension. It's an excellent choice because it runs inside the database, eliminating the need for external schedulers and managing database connections.

First, ensure pg_cron is enabled in your postgresql.conf:

# postgresql.conf
shared_preload_libraries = 'pg_cron'

Then, connect to your database and create the extension:

CREATE EXTENSION pg_cron;

Now, we can schedule our procedure to run. For example, to run it every minute:

-- The user running this needs to be a superuser or have permissions granted by a superuser.
SELECT cron.schedule(
    'materialized-view-refresher',
    '* * * * *', -- Every minute
    'CALL public.refresh_dirty_materialized_views();'
);

You can verify the job is scheduled by querying the cron.job table.

A Complete, Real-World Example

Let's apply this pattern to a common e-commerce analytics scenario.

1. Schema and Materialized View

-- Base tables
CREATE TABLE products (product_id int PRIMARY KEY, name text, price numeric(10, 2));
CREATE TABLE customers (customer_id int PRIMARY KEY, email text);
CREATE TABLE orders (
    order_id int PRIMARY KEY, 
    customer_id int REFERENCES customers(customer_id), 
    order_date date
);
CREATE TABLE order_items (
    order_id int REFERENCES orders(order_id),
    product_id int REFERENCES products(product_id),
    quantity int,
    PRIMARY KEY (order_id, product_id)
);

-- A complex materialized view for a dashboard
CREATE MATERIALIZED VIEW public.monthly_product_sales AS
SELECT
    p.product_id,
    p.name AS product_name,
    date_trunc('month', o.order_date)::date AS sales_month,
    SUM(oi.quantity) AS total_quantity_sold,
    SUM(oi.quantity * p.price) AS total_revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
GROUP BY p.product_id, p.name, sales_month
ORDER BY sales_month, total_revenue DESC;

-- REFRESH CONCURRENTLY requires a UNIQUE index
CREATE UNIQUE INDEX idx_monthly_product_sales_unique ON monthly_product_sales (sales_month, product_id);

2. Register the View in our State Table

INSERT INTO public.materialized_view_state (view_name) VALUES ('monthly_product_sales');

3. Attach Triggers to all Base Tables

Our view depends on products, orders, and order_items. We need to attach our trigger to all of them.

-- Trigger for order_items (the most frequently changing table)
CREATE TRIGGER trigger_maitain_monthly_product_sales_on_order_items
AFTER INSERT OR UPDATE OR DELETE ON public.order_items
FOR EACH STATEMENT
EXECUTE FUNCTION public.mark_materialized_view_as_dirty('monthly_product_sales');

-- Trigger for orders (changes to order_date affect the view)
CREATE TRIGGER trigger_maitain_monthly_product_sales_on_orders
AFTER INSERT OR UPDATE OR DELETE ON public.orders
FOR EACH STATEMENT
EXECUTE FUNCTION public.mark_materialized_view_as_dirty('monthly_product_sales');

-- Trigger for products (changes to price affect revenue)
CREATE TRIGGER trigger_maitain_monthly_product_sales_on_products
AFTER UPDATE OF price ON public.products -- Only fire if the price changes
FOR EACH STATEMENT
EXECUTE FUNCTION public.mark_materialized_view_as_dirty('monthly_product_sales');

Note the use of FOR EACH STATEMENT. Since our trigger function doesn't depend on row-level data, a statement-level trigger is more efficient, firing only once per statement regardless of how many rows are affected.

4. Simulate Workload and Observe

With the pg_cron job scheduled, you can now simulate a workload:

-- Insert some initial data
INSERT INTO products VALUES (1, 'Super Widget', 99.99);
INSERT INTO customers VALUES (101, '[email protected]');
INSERT INTO orders VALUES (1001, 101, '2023-10-15');
INSERT INTO order_items VALUES (1001, 1, 5);

Immediately after this transaction commits, check the state table:

SELECT * FROM public.materialized_view_state;

-- Result:
-- view_name             | is_dirty | last_refreshed_at | last_dirtied_at
-- -----------------------+----------+-------------------+-------------------------------
-- monthly_product_sales | t        |                   | 2023-10-27 10:30:00.123456+00

Within a minute, the pg_cron job will fire. You can monitor the PostgreSQL logs for the NOTICE messages from our procedure. After it runs, query the state table again:

SELECT * FROM public.materialized_view_state;

-- Result:
-- view_name             | is_dirty | last_refreshed_at               | last_dirtied_at
-- -----------------------+----------+---------------------------------+-------------------------------
-- monthly_product_sales | f        | 2023-10-27 10:31:00.654321+00   | 2023-10-27 10:30:00.123456+00

The system is working. The view has been refreshed asynchronously, with zero impact on the application code that performed the inserts.

Advanced Considerations and Performance Impact

This pattern is robust, but it's not without its trade-offs and edge cases.

* Trigger Overhead: While the trigger is designed to be minimal, it is not free. It adds a small, constant overhead to every write transaction on the tracked tables. For systems with extreme write throughput (tens of thousands of TPS), this overhead must be benchmarked. In most common scenarios, the cost of the single indexed INSERT ... ON CONFLICT is negligible compared to the application's primary write operations.

* Staleness Window: This system is eventually consistent. The maximum data staleness is determined by the pg_cron schedule frequency plus the time it takes for the refresh to complete. If your cron job runs every minute, data can be up to a minute stale (plus refresh time). This must be acceptable to the application and its users. The last_refreshed_at column can be exposed via an API to make this transparent.

* Thundering Herd: If a batch job inserts 1 million rows into order_items, a FOR EACH ROW trigger would fire 1 million times. By using FOR EACH STATEMENT, we ensure it only fires once. This is a critical optimization.

* Initial Setup for Many Views: If you implement this pattern for dozens of views, the refresh procedure will loop through all of them. This is generally fine, as the advisory lock ensures they are processed sequentially and work can be distributed across multiple cron runs if refreshes are long. For very large numbers of views, you might consider sharding the refresh logic into multiple procedures and cron jobs.

* Alternative: pg_notify: An alternative to polling with pg_cron is a listen/notify pattern. The trigger could send a pg_notify message. A dedicated external worker process could LISTEN for these notifications and trigger the refresh. This reduces latency from the polling interval but adds the complexity of maintaining a persistent, external worker process, which is often a larger architectural burden than simply using pg_cron.

Conclusion

The synchronous nature of REFRESH MATERIALIZED VIEW is a common performance cliff for growing PostgreSQL-backed applications. By implementing a trigger-based dirty flag pattern with pg_cron, you can build a resilient, database-centric asynchronous refresh system. This pattern effectively decouples analytical query performance from OLTP write performance, allowing both to scale independently.

The key takeaways are the use of an external state table, an extremely lightweight trigger, an idempotent refresh procedure protected by advisory locks, and robust error handling. This is not a solution for every use case—it introduces eventual consistency—but for the vast majority of dashboarding and reporting workloads, it provides a superior balance of data freshness, performance, and operational simplicity compared to the default synchronous behavior.