free_tool
Postgres view vs materialized view
Three of the four things under a schema render an almost identical grid. What separates them is where the rows live and when they were computed. Change the table below and watch the view follow instantly while the materialized view falls behind, write by write, until you refresh it.
- Table
The rows themselves, sitting on disk.
What you actually wrote. Everything else on this page is built by reading a table. If someone saves something and expects it to still be there tomorrow, it belongs in one.
- View
A saved SELECT, run fresh every time you read it.
It stores no data at all — only the query text. Reading it costs whatever the query costs, and the answer is never out of date. Think of it as a named shortcut for a query you were going to write anyway.
- Materialized view
A saved SELECT whose answer is stored on disk.
Postgres runs the query once, keeps the result rows, and hands you those rows on every read. Fast like a table, but frozen at the moment of the last REFRESH — so it is fast and a little bit wrong, and you choose how wrong by how often you refresh.
- Routine
Saved code you call, with arguments.
Not rows and not a query shape, but a body of SQL or PL/pgSQL. A function returns a value and is used inside an expression; a procedure is invoked with CALL, returns nothing, and can commit as it goes.
The source of truth. You can insert, update and delete here.
CREATE TABLE employees ( id serial PRIMARY KEY, name text, dept text, salary int );
6 rows stored on disk
Stores nothing. Re-runs the query every single time you read it.
CREATE VIEW v_dept_cost AS SELECT dept, count(*), sum(salary) FROM employees GROUP BY dept;
recomputed just now · scanned 6 rows
Stores the answer. Frozen at the last refresh, however old that is.
CREATE MATERIALIZED VIEW mv_dept_cost AS SELECT dept, count(*), sum(salary) FROM employees GROUP BY dept WITH DATA;
read from disk · as of —
Returns a value. Called inside an expression, and runs in your transaction.
CREATE FUNCTION fn_dept_cost(p_dept text) RETURNS TABLE (n bigint, payroll bigint) AS $$ SELECT count(*), sum(salary) FROM employees WHERE dept = p_dept; $$ LANGUAGE sql STABLE;
0 calls · recomputed on every one
Returns nothing. Called on its own — and it may COMMIT mid-body.
CREATE PROCEDURE sp_give_raise(p_dept text, p_pct int) AS $$ UPDATE employees SET salary = salary * (1 + p_pct/100.0) WHERE dept = p_dept; $$ LANGUAGE sql;
never called
Warm markers are bytes on disk, cool markers are computed on read. Timings and drift are simulated to make the behaviour visible; the semantics are Postgres 12+.
side_by_side
The differences that matter
| Table | View | Materialized view | Routine | |
|---|---|---|---|---|
| What's on disk | The rows themselves | Nothing — only the SQL text | The query's result rows | Nothing — only the source |
| Freshness | Live | Live, by definition | As of the last REFRESH | Live — it runs when called |
| Read cost | Index speed | Cost of the underlying query, every read | Index speed | Cost of the body, every call |
| Writable | Yes | Only if simple, or via an INSTEAD OF trigger | No — refresh is the only write | n/a, but it can write to tables |
| Own indexes | Yes | No — it borrows the base table's | Yes, and it needs a unique one for CONCURRENTLY | No |
| Takes arguments | No | No | No | Yes — the whole point |
| Costs disk space | Yes | No | Yes — a second copy of the data | No |
| Blocks readers | No | No | A plain REFRESH locks it; REFRESH … CONCURRENTLY does not | No |
| How you call it | SELECT … FROM | SELECT … FROM | SELECT … FROM | SELECT f(x) or CALL p(x) |
in_your_client
Telling them apart in DataGrip
The database explorer gives each kind its own icon, but the glyphs and the grouping shift between versions. When it actually matters, use one of these three checks.
- 1
Read the generated DDL
Open the object and look at its SQL tab. It says CREATE TABLE, CREATE VIEW or CREATE MATERIALIZED VIEW. Definitive, and one click.
- 2
Try to type in a cell
A table's result grid accepts edits and offers you a Submit. A materialized view's grid is always read-only. A view's is read-only unless Postgres considers it simple enough to update through. A routine has no grid at all — opening it shows source code, which is the fastest way to tell it from the other three.
- 3
Ask the catalog
Version-proof, and the one to keep in a scratch file. relkind is the letter Postgres itself stores. Routines are not in pg_class at all — they live in pg_proc, under prokind.
select c.relname,
case c.relkind
when 'r' then 'table'
when 'p' then 'partitioned table'
when 'v' then 'view'
when 'm' then 'materialized view'
when 'f' then 'foreign table'
end as kind
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
order by kind, c.relname;-- routines live somewhere else entirely
select p.proname,
case p.prokind
when 'f' then 'function'
when 'p' then 'procedure'
when 'a' then 'aggregate'
when 'w' then 'window function'
end as kind,
pg_get_function_arguments(p.oid) as args
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public'
order by kind, p.proname;which_one
Which one to reach for
Reach for a table
It's the source of truth
Someone writes to it and expects the write to stick. Nothing else qualifies.
Reach for a view
The query is cheap, and you want to stop repeating it
Hiding a join, applying a tenant filter, giving a report a stable shape. Zero staleness risk, zero disk, and changing the definition changes every caller at once.
Reach for a matview
The query is expensive and stale-by-minutes is fine
Dashboards, nightly rollups, search indexes. The price is a refresh you have to schedule and a number that is always a little behind.
Reach for a routine
It needs arguments, or it has to happen inside the database
A parameterised query, logic a trigger must run on write, or a batch job that has to commit as it goes. Everything else is easier to read, test and deploy in application code.
There is a fifth option people forget: a plain table you maintain yourself — a rollup row updated on write or by a background job. More work than a materialized view, but you get partial updates and control over exactly how fresh each row is, instead of a single all-or-nothing refresh.
faq
Questions & answers
- What is the difference between a view and a materialized view in Postgres?
- A view stores no data at all, only the SQL text, so reading it re-runs the underlying query and the answer is always current. A materialized view stores the query's result rows on disk, so reading it is as fast as reading a table, but the numbers are frozen at the last REFRESH and are wrong until you run one.
- Is a materialized view faster than a view?
- To read, yes, and often by a lot, because it is just rows on disk and can carry its own indexes. That speed is paid for with disk space, a refresh you have to schedule, and a window where the data is out of date. A view over a cheap query is usually the better trade.
- How do I refresh a materialized view?
- REFRESH MATERIALIZED VIEW your_matview re-runs the query and replaces the stored rows, holding a lock that blocks readers while it works. REFRESH MATERIALIZED VIEW CONCURRENTLY does it without blocking reads, but it requires a unique index on the matview and is slower overall.
- Can you insert or update a materialized view?
- No. A refresh is the only thing that writes to it. A plain view is sometimes updatable if it is simple enough for Postgres to work out which base row you mean, and you can always make one writable with an INSTEAD OF trigger.
- Why does my materialized view not appear in information_schema?
- Materialized views are a Postgres extension rather than standard SQL, so information_schema.tables and information_schema.views leave them out entirely. Query pg_class, where relkind is 'm', or the pg_matviews view instead.
- How do I tell a table from a view in DataGrip?
- The icons differ, but they shift between versions. Three reliable checks: open the object and read its generated DDL, try to type in a result cell (a table's grid accepts edits, a matview's never does), or query pg_class.relkind, where 'r' is a table, 'v' a view and 'm' a materialized view.
- Does refreshing in DataGrip refresh my materialized view?
- No, and this catches people out. The refresh action in the database explorer reloads DataGrip's own metadata cache, meaning the list of objects and columns. Your matview's data is untouched until you run REFRESH MATERIALIZED VIEW yourself.
- What is the difference between a function and a procedure?
- A function returns a value and is called inside an expression, and it runs inside the caller's transaction so it cannot commit. A procedure is invoked with CALL, returns nothing, and can COMMIT and ROLLBACK inside its own body, which is why batch jobs that need to checkpoint are written as procedures.
- Is any of this sent to a server?
- No. The explorer is a simulation running entirely in your browser, with no database behind it. The SQL shown is real and correct for Postgres 12 and later, but nothing you click leaves the page.