Indexed view functionality

This topic provides reference information comparing the feature compatibility between Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL, specifically focusing on indexed views and materialized views. You can understand the differences in implementation and limitations between these two database systems when it comes to creating and managing views with indexes. The topic highlights that while SQL Server supports indexed views with specific requirements, PostgreSQL offers similar functionality through materialized views.

Feature compatibility	AWS SCT / AWS DMS automation level	AWS SCT action code index	Key differences
		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

You can’t use indexed views 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 indexed views, but does provide similar functionality with materialized views. You can run queries associated with materialized views, and populate the view data with the REFRESH command.

The PostgreSQL implementation of materialized views has three primary limitations:

• You can refresh PostgreSQL materialized views either manually or using a job running the REFRESH MATERIALIZED VIEW command. To refresh materialized views automatically, create 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.