Materialized Views in PostgreSQL

Kyle Waters

Endurance International Group

Why a materialized view?

• Materialized views are a form of optimization
• Materialized views create a cache of an aggragate query
• Cache can be updated in real time, creating a trade off
• We will create and update the cache using sql and triggers

Materials

• PostgreSQL
• PGAdmin III
• SQL Files
• Create Database
• Run 1-tables.sql
• Run 2-data.sql

Our Tables

• Methods
• Page Views

Methods

• Method Index
• Method

Page Views

• page_view_index
• page
• view_time
• load_time
• method_index
• remote_address
• user_agent

Our Report

• View Date
• Page
• Request Method
• Number of Requests
• Average Load Time
• 03-report.sql

Views in Postgresql

• A view allows you to use the results of a query like a table
• CREATE VIEW view_name AS SELECT ...
• 04-create_view.sql

Materialized Views in Postgresql

• A materialized view is a table
• Can be updated via cron(Lazy) or triggers(Eager)
• Materialized View Extension(Lazy)
• Can create indexes and cluster
• 05-create_materialized_view.sql

Simple Eager View

• Create row refresh function
• Create trigger(s)
• 06-create_eager_trigger.sql
• 07-more_data.sql

Optimized Eager View

• Can get very complex
• Insert Function
• Delete Function
• Update Function

Questions? Comments?

• https://joind.in/talk/00b1a