matview.slide

Facts on Materialized Views

John Scott
Founder, RJ2 Technologies, Inc, 1998-now
Consultant, Telco Industries, 2006-now
Creator www.setspace.com

jmscott@setspace.com
jmscott@rj2tech.com
@setspace@twitter

* The single biggest problem in communication is the illusion that it has taken place.
.caption George Bernard Shaw

* Evangelize on Materialized Views, in and out
- Most Large DB Projects use MatViews
- Inside PostgresSQL (CREATE MATERIALIZED VIEW)
- Running Outside PostgreSQL

* What is a Fact?

- facts are in the past
- facts never change
- all you perceive is in the past
- video "the Value of Values" by rich hinkey (Datomic)!

  so why not take advantage of facts?

* Relational Database Do Best with Facts.
- this is why queues not so efficient.
- is the database "the source of truth" practically or literally?
- do the "facts" stored in a DB actually occur outside of the DB?
- is the DB just a camera recording the facts
- merging differing DBs (M&A)

* What is a Materialzed View?
- SQL Table derived from an SQL query (formal)
- MatView can be Indexed
- Generated Columns (stored)
- An SQL Table sourced from a process outside of database
- is a common table expression a lightweight matview?
- MatView generates new facts, in a sense

* What is Wrong with Matview
- redundancy
- out of sync with source tables/data
- updates can be expensive (incremental!)
- programmer must explcitly use a matview, unlike index
- when to delete matview - data leakage
- no side effects!

* What is Right about Matview
- Very good for expensive or slow queries 

* What is an Index ... a Materialized View?
- a materialized view on physical location of tuples ... in a sense
- can we fold a matview into an index (oracle claims)?
- or just improve optimzer to recognize complex partial index.
- does improving partial index address aggregation queries

* Can Optimizer Transparently Use MatView? 

  CREATE MATERIALIZED VIEW daily_sales(cust, day, sales_count)
    AS
      SELECT
          cust,
          to_char(now(), 'YYYY-MM-DD')::timestamptz;,
          count(*)
        FROM
          sales
        GROUP
          BY 1, 2 ;
  CREATE UNIQUE INDEX ON daily_sales(day);
  REFRESH MATERIALIZED VIEW daily_sales;
  
  SELECT
      day,
      count(*)
    FROM 
      sales
    WHERE
      cust = 'Exxon Mobil'
      AND
      day = '2023/01/06'
    GROUP BY
      1 ;

* Review PG15/16 materialize views
- full view update (PG15)

- incremental in SQL (PG16) - breakdown in PGWik in Relational Algrbra
- PG16 has proposed path for immediate update of IVM
- incremental (extension pg_ivm @ pganalyze)
- interest in MatViews slowing on PG mail lists (~2020)

- external (updates much easier)

* SetSpace and Materialized views
- MatViews strictly immutable facts derived from external processes

* Links
- [[https://wiki.postgresql.org/wiki/Incremental_View_Maintenance][PG Wiki Incremental View Maintenance]]
- [[https://pganalyze.com/blog/5mins-postgres-15-beta1-incremental-materialized-views-pg-ivm][Extension pg_ivm @ pganalyze]]
- [[https://www.postgresql.org/docs/current/queries-with.html][PG CTE Docs]]
- [[https://www.infoq.com/presentations/Value-Values/][Value of Values by Rich Hinkey@Datatomic]]
- [[https://philbooth.me/blog/nine-ways-to-shoot-yourself-in-the-foot-with-postgresql][Nine Ways to Shoot Your Foot with PostgreSQL]]
- [[https://www.postgresql.org/docs/current/queries-with.html][Discuss Materized Concepts in CTE]]
- [[https://scholar.google.com/citations?view_op=view_citation&hl=en&user=64zxhRUAAAAJ&citft=1&citft=2&citft=3&email_for_op=jmscott%40setspace.com&citation_for_view=64zxhRUAAAAJ:qjMakFHDy7sC][Efficient exploitation of similar subexpressions for query processing @ AliBaba]]
- [[https://patentimages.storage.googleapis.com/4a/58/cb/ea54fd820575fc/US20060047696A1.pdf][Patent on "Partial Index" on MatView @AliBaba]]