# postgres view/ material view ###### tags: `view`,`postgres` ### view and original table * create a table like structure, those data point to the location of some table row, hence update the data in view will update the data in table,too. ### view : provide dynamic access to data with real-time updates * A view is a virtual table created by a query. A view does not store any data itself. * It is a saved SQL query that can be referenced and used like a table. * The query is executed every time the view is accessed, * the results are dynamically generated. * create another table by exposing the data from some table,if we dont want only expose some column to user, we can only give user the priviledge to read a view rather than table. ### materialized view : offer better performance of reading data, but the data might be stale. * A materialized view is a physical copy of the result set of a query. * It is essentially a table that stores the results of a query and must be explicitly refreshed to update its data. * The data in a materialized view is persistent and does not change unless refreshed. * A materialized view offers better performance for read-intensive operations since the data is precomputed and stored. It avoids executing the query every time ```sql= CREATE MATERIALIZED VIEW tickets_view AS SELECT ticket_no, passenger_name FROM tickets WITH DATA; ``` * wont sync the freshdata in default, might get outdated data. * when refresh put exclusive lock on the materialize view, ps. with "concurrently", the non-changed materialized view will still can be read. ```sql= REFRESH MATERIALIZED VIEW CONCURRENTLY tickets_view; ``` #### auto refresh materialized view when table updated To make the process automatic, <font color="red">we can create a schedule or set the database triggers to execute the REFRESH command.</font> For instance, you can create a trigger that launches the updating process when any changes take place in the tables that feed the materialized view. It synchronizes the data for all the users working with the tables. ### material view vs view * view : get fresh data each time * material view: * 1. new data set from real table, the data might be outdated but has greater performance than view when query * 2. Using the data stored by the materialized view, you can retrieve the results drastically faster. When the data is synchronized directly with the database, the time is only spent on the INSERT, UPDATE, and DELETE operations.