ruk·si

🐘 PostgreSQL
Materialized Views

Updated at 2015-11-18 23:52

Like regular views but they are cached in the file system and can thus be indexed. They are only refreshed when specified.

-- DROP MATERIALIZED VIEW aggregate_user_name_length;
CREATE MATERIALIZED VIEW aggregate_user_name_length (count)
AS (
    SELECT user_id, length(name)
    FROM users
);

SELECT * FROM aggregate_user_name_length;

UPDATE users SET name = 'Conan Barbara' WHERE name = 'Conan Barb';

-- Notice that the view doesn't update.
SELECT * FROM aggregate_user_name_length;

-- To update the view, use REFRESH
REFRESH MATERIALIZED VIEW aggregate_user_name_length;
SELECT * FROM aggregate_user_name_length;

Sources