commit ece80255195dd6c3ef68bed57c9c1bb8e67d9e48
parent 8b5f26362930b13d9332335e2410d6e3426678d8
Author: root <root>
Date: Thu, 29 May 2025 19:20:02 +0200
new file
Diffstat:
1 file changed, 16 insertions(+), 0 deletions(-)
diff --git a/notes/sql/view_vs_materialized_view.txt b/notes/sql/view_vs_materialized_view.txt
@@ -0,0 +1,16 @@
+# Difference between view and materialized view?
+
+-> View:
+
+It's just a handy way to save/store a query, but not its results. The results are fetched fresh from the underlying table(s). It can be a nice way for simplifying queries down to something manageable for less technical end users like data analysts, since a view can easily be reused and shared. It's also a neat way to give access only to a specific sub-set of data to some users or roles.
+
+-> Materialized view
+
+This is also a way of saving a query, but it materializes the result set, meaning it saves a copy of the data onto the disk. The data does not get automatically refreshed, and becomes stale over time. Accessing data from a materialized view means that i nstead of performing an expensive query over and over, the data is just simply fetched from the saved result set. So despite the data being stale, it can still be useful when the volume of the base data is so big that real-time querying the underlying table would be impractically slow.
+
+These objects can be treated somewhat similarly to tables, they can have indexes, or be referenced in a foreign key, which is not possible view views.
+
+The data needs to be explicitly refreshed in a materialized view as a whole. (TRUNCATE the table, INSERT INTO mv SELECT ...)
+This would generally be best done in a timeframe that is well outside of peak business hours.
+
+