Difference Between Views and Materialized Views in Databases

database-recommendationMySQLpostgresqlrdbms

What is the difference between PostgreSQL's materialized views and MySQL's views?

Best Answer

A VIEW is a stored query. When you select from it, you essentially run the query. 

CREATE VIEW foo
AS
  SELECT col1,col2
  FROM bar;

Is essentially 

SELECT foo.*
FROM (
  SELECT col1,col2
  FROM bar
) AS foo;

A materialized view is more or less a view that is cached (or materialized) to disk,

CREATE IF NOT EXISTS TABLE foo
AS
  SELECT col1,col2
  FROM bar;

With the added bonus that, at least in PostgresSQL, there is a command REFRESH MATERIALIZED VIEW that you can feed to it the name of the view, and it'll regenerate it from scratch: REFRESH MATERIALIZED VIEW foo;

Related Question