CODEWITHSUNDEEP
databasepostgresql
user228229
user228229

Reputation: 821

Table vs View vs Materialized View

I'm currently learning PostgreSQL. However, I am a little bit confused about table, view, and materialized view. I understand the basic definitions as well as conceptions. But sometimes, I have a trouble to make a decision that I should create a table, a view, or materialized view. Would anyone share some experience how to apply it correctly? What are the pros and cons of one over the others?

Upvotes: 62

Views: 39639

Answers (2)

Devi
Devi

Reputation: 5443

This article has a nice explanation on this part. Quoting from it,

When you query a TABLE, you fetch its data directly. On the other hand, when you query a VIEW, you are basically querying another query that is stored in the VIEW's definition.

...

Between the two there is MATERIALIZED VIEW - it's a VIEW that has a query in its definition and uses this query to fetch the data directly from the storage, but it also has it's own storage that basically acts as a cache in between the underlying TABLE(s) and the queries operating on the MATERIALIZED VIEW. It can be refreshed, just like an invalidated cache - a process that would cause its definition's query to be executed again against the actual data.

Upvotes: 30

wrschneider
wrschneider

Reputation: 18770

A table is where data is stored. You always start with tables first, and then your usage pattern dictates whether you need views or materialized views.

A view is like a stored query for future use, if you're frequently joining or filtering the same tables the same way in multiple places.

A materialized view is like a combination of both: it's a table that is automatically populated and refreshed via a view. You'd use this if you were using views, and want to pre-join or pre-aggregate the rows to speed up queries.

Upvotes: 64

Related Questions