SQL Server Indexed Views and PostgreSQL Materialized Views - SQL Server to Aurora PostgreSQL Migration Playbook
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China.

SQL Server Indexed Views and PostgreSQL Materialized Views

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences


                              Two star feature compatibility


                              No automation

N/A

Different paradigm and syntax will require rewriting the application.

SQL Server Usage

The first index created on a view must be a clustered index. Subsequent indexes can be non-clustered indexes. For more information, see Clustered and nonclustered indexes described in the SQL Server documentation.

Before creating an index on a view, the following requirements must be met:

  • The WITH SCHEMABINDING option must be used when creating the view.

  • Verify the SET options are correct for all existing tables referenced in the view and for the session. Find the link at the end of this section for required values.

  • Ensure that a clustered index on the view is exists.

Note

Indexed views can’t be used with temporal queries (FOR SYSTEM_TIME).

Examples

Set the required SET options, create a view with the WITH SCHEMABINDING option, and create an index on this view.

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

CREATE VIEW Sales.Ord_view
WITH SCHEMABINDING
AS
  SELECT SUM(Price*Qty*(1.00-Discount)) AS Revenue,
    OrdTime, ID, COUNT_BIG(*) AS COUNT
  FROM Sales.OrderDetail AS ordet, Sales.OrderHeader AS ordhead
  WHERE ordet.SalesOrderID = ordhead.SalesOrderID
  GROUP BY OrdTime, ID;
GO

CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON Sales.Ord_view (OrdTime, ID);
GO

For more information, see Create Indexed Views in the SQL Server documentation.

PostgreSQL Usage

PostgreSQL doesn’t support view indexes, but does provide similar functionality with Materialized Views. Queries associated with materialized views are run and the view data is populated when the REFRESH command is issued.

The PostgreSQL implementation of Materialized Views has three primary limitations:

  • PostgreSQL materialized views may be refreshed either manually or using a job running the REFRESH MATERIALIZED VIEW command. Automatic refresh of Materialized Views require the creation of a trigger.

  • PostgreSQL materialized views only support complete or full refresh.

  • DML on materialized views isn’t supported.

In some cases, when the tables are big, full REFRESH can cause performance issues. In this case, you can use triggers to sync between one table to the new table. You can use the new table as an indexed view.

Examples

The following example creates a materialized view named sales_summary using the sales table as the source.

CREATE MATERIALIZED VIEW sales_summary AS
SELECT seller_no,sale_date,sum(sale_amt)::numeric(10,2) as sales_amt
FROM sales
WHERE sale_date < CURRENT_DATE
GROUP BY seller_no, sale_date
ORDER BY seller_no, sale_date;

The following example runs a manual refresh of the materialized view:

REFRESH MATERIALIZED VIEW sales_summary;
Note

The materialized view data isn’t refreshed automatically if changes occur to its underlying tables. For automatic refresh of materialized view data, a trigger on the underlying tables must be created.

Creating a Materialized View

When you create a materialized view in PostgreSQL, it uses a regular database table underneath. You can create database indexes on the materialized view directly and improve performance of queries that access the materialized view.

Example

The following example creates an index on the sellerno and sale_date columns of the sales_summary materialized view.

CREATE UNIQUE INDEX sales_summary_seller
ON sales_summary (seller_no, sale_date);

Summary

Feature Indexed views Materialized view

Create materialized view

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL,
  ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

CREATE VIEW Sales.Ord_view WITH SCHEMABINDING
  AS SELECT SUM(Price*Qty*(1.00-Discount)) AS Revenue,
  OrdTime, ID, COUNT_BIG(*) AS
    COUNT FROM Sales.OrderDetail AS ordet,
  Sales.OrderHeader AS ordhead
    WHERE ordet.SalesOrderID = ordhead.SalesOrderID
    GROUP BY OrdTime, ID;
GO

CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.Ord_view (OrdTime, ID);
GO
CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM employees;

Indexed refreshed

Automatic

Manual. You can automate refreshes using triggers.

Create a trigger that initiates a refresh after every DML command on the underlying tables:

CREATE OR REPLACE FUNCTION
refresh_mv1()
returns trigger language plpgsql as
$$ begin
refresh materialized view mv1;
return null;
end $$;

Create the refresh_mv1 trigger after insert, update, delete, or truncate on employees. For each statement, run the refresh_mv1(); procedure.

DML

Supported

Not Supported

For more information, see Materialized Views in the PostgreSQL documentation.