AWS Database Blog

Migrate SQL Server indexed views to materialized views in Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL

This is the first post of a two-part series. In this post, we discuss how to migrate SQL Server indexed views to materialized views in Amazon Aurora PostgreSQL-Compatible Edition or Amazon Relational Database Service (Amazon RDS) for PostgreSQL. The second post in this series will walk you through the migration of SQL Server indexed views to materialized views in Babelfish for Aurora PostgreSQL.

SQL Server indexed views are used to improve the performance of queries that aggregate data from multiple tables. Materialized views, on the other hand, are used in PostgreSQL databases to store a query result and make it available for fast retrieval. The key difference is that materialized views use the rule system like views do but persist the results in table like form on disk, whereas indexed views only store the table’s definition substituted in the main query and the execution will be like reading from these tables directly.

Creating a unique clustered index on a complex indexed view can significantly improve performance for frequent queries. The indexed view stores the result set like a table, allowing queries to benefit from the clustered index without directly accessing the view. The unique clustered index provides that changes in the source table are quickly tracked and reflected in the view. Although it’s possible to modify data directly through the indexed view, it’s generally not recommended. Additionally, non-clustered indexes can be created on the view to further enhance query performance. However, you can create database indexes on the PostgreSQL materialized view directly and improve performance of queries that access the materialized view.

Solution overview

To migrate SQL Server indexed views to PostgreSQL materialized views, do the following:

  1. Analyze the SQL Server indexed view – Understand the structure and logic of the indexed view in SQL Server, including the underlying tables, joins, and aggregations involved.
  2. Create the materialized view in PostgreSQL – Convert the SQL Server indexed view’s query into a corresponding PostgreSQL query and create a new materialized view using the CREATE MATERIALIZED VIEW statement. Verify that the column names, table names, and join conditions are adapted to the PostgreSQL syntax.
  3. Define indexes on the materialized view – Identify the relevant columns that need indexing for improved query performance. Use the CREATE INDEX statement to create indexes on the materialized view, taking into account the specific requirements of your queries.
  4. Set up a refresh mechanism – Determine how the materialized view should be refreshed to update its data. PostgreSQL doesn’t currently provide native automatic refresh for materialized views, so you must manually refresh the view periodically using the REFRESH MATERIALIZED VIEW statement. You can set up a schedule (using cron jobs & using pg_cron extension) or triggers to initiate the refresh process.
  5. Modify dependent queries – Review and modify queries or applications that rely on the SQL Server indexed view to use the new PostgreSQL materialized view instead. Update the queries to reference the materialized view and adjust syntax differences between SQL Server and PostgreSQL.
  6. Test and optimize – Thoroughly test the migrated materialized view and its dependent queries to verify that they produce the expected results. Monitor query performance and consider further optimization techniques, such as additional indexing or query rewriting, if necessary.

The following diagram outlines the critical steps and decisions involved across the involved entities in this migration. Follow this visual flow to ensure a smooth transition preserving the integrity of your data and the performance benefits of materialized views in PostgreSQL by means of pre-calculated data, reduced row size, pre-selected rows, and possibly less bloating than underlying tables.

Process flow diagram

Prerequisites

Complete the following prerequisite steps:

Analyze SQL Server indexed views

The first step is to analyze the SQL Server indexed views and identify the table columns and aggregate functions involved. This will help in creating equivalent PostgreSQL views.

For illustration purposes, let’s pick a sample Product Inventory table and create the following SQL Server indexed view:

-- Create the view
CREATE VIEW InventorySummaryView WITH SCHEMABINDING AS
SELECT ProductID, COUNT_BIG(*) AS TotalCount, SUM(Quantity) AS TotalQuantity
FROM Production.ProductInventory
GROUP BY ProductID;

-- Create the clustered index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_InventorySummaryView ON InventorySummaryView (ProductID);

To analyze the indexed views in SQL Server, use the following query:

--Query for identifying the created view  
SELECT OBJECT_NAME(object_id) AS view_name, name AS index_name, index_id, type_desc,
is_unique, data_space_id, ignore_dup_key, is_primary_key,
is_unique_constraint, fill_factor, is_padded, is_disabled,
is_hypothetical, allow_row_locks, allow_page_locks,
has_filter, filter_definition, compression_delay
FROM sys.indexes
WHERE objectproperty(object_id,'IsIndexed') = 1
ORDER BY object_name(object_id), index_id;

This gives you a list of the indexed views in the SQL Server database.

Convert SQL Server indexed views to PostgreSQL materialized views

The next step is to convert the SQL Server indexed views to PostgreSQL materialized views using the following steps:

  1. Extract the SQL Server indexed view definition using the following script: 
    SELECT OBJECT_NAME(object_id) AS view_name, definition
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsView') = 1;
  2. Create a materialized view in PostgreSQL using the result set of the PostgreSQL view: 
    CREATE MATERIALIZED VIEW InventorySummaryView AS
SELECT ProductID, COUNT(*) AS TotalCount, SUM(Quantity) AS TotalQuantity
FROM adventureworks2k7.Production.ProductInventory
GROUP BY ProductID;
  3. Index the materialized view to improve performance using the following syntax: 
    CREATE UNIQUE INDEX IDX_InventorySummaryView ON InventorySummaryView (ProductID);
  4. Configure a refresh or schedule the refresh window in PostgreSQL: 
    ALTER MATERIALIZED VIEW InventorySummaryView CLUSTER ON IDX_InventorySummaryView;
REFRESH MATERIALIZED VIEW CONCURRENTLY InventorySummaryView;

Clean up

To avoid incurring ongoing charges, clean up the following resources:

  • Delete the provisioned Aurora PostgreSQL-Compatible or Amazon RDS for PostgreSQL resources.
  • Delete the Amazon Elastic Compute Cloud (Amazon EC2) instance created for SQL developer tools like SSMS and DBeaver.

Known limitations of this solution

  • Data types – PostgreSQL and SQL Server support different data types, so you may need to modify your SQL Server indexed views to maintain compatibility with PostgreSQL. (For more details refer)
  • Aggregates – Materialized views in PostgreSQL don’t support all aggregate functions that SQL Server indexed views support, so you may need to adjust your queries accordingly
  • Update availability – Changes made to the underlying tables will not be reflected in the materialized view automatically until it’s refreshed.
  • Storage space – Materialized views can take up additional storage space, ensure that you have enough storage available in your PostgreSQL instance
  • Refresh Times – Depending on the size of your materialized views and underlying query complexity, the refresh times may vary. You can monitor “vacuum_count” and “vacuum_cost” for base tables to estimate update frequency and potential refresh cost. In addition, available CPU, memory and disk I/O can also impact refresh speed.
  • Upgrades – Materialized view definitions and storage mechanisms can change between PostgreSQL versions. If you plan to upgrade to a newer version of PostgreSQL, ensure that your materialized views are compatible with the new version. Use the “pg_upgrade_support” function or manual review to identify potential compatibility issues.

Considerations when using PostgreSQL materialized views for index views

It’s important to consider the following aspects of materialized views.

  • Performance – Materialized views can improve query performance, but it comes at the cost of additional storage space and slower refresh times. You need to carefully evaluate the benefits and drawbacks of keeping a materialized view in sync with the underlying data requires periodic refreshes. These refreshes can be slow, especially for large datasets, potentially impacting application performance during the process.
  • Query optimization – Materialized views are most effective when they are used to improve the performance of commonly used queries. Materialized views improves performance by precomputing frequently accessed data, reducing load on base tables, providing optimized access paths and caching. However, its crucial to evaluate the trade-offs involved such as additional storage space and refresh time.
  • Data refresh – Materialized views must be refreshed periodically to make sure that they reflect the latest data from the underlying tables. You can determine the frequency of refreshes based on the requirements of keeping the data updated.
  • Indexing – Materialized views can be indexed to improve query performance, but you must ensure that the indexes are optimized for your use case. You can achieve optimal materialized view indexing by analyzing the query patterns, choosing relevant columns used in WHERE and ORDER BY clause , index type selection and examine the index utilization statistics.
  • Compatibility – Ensure that your application is compatible with PostgreSQL materialized views implementation before migrating from SQL Server.
  • Testing – Before migrating your SQL Server indexed views to materialized views in PostgreSQL, you need to thoroughly test your queries to ensure that they produce the expected results and perform well.

Conclusion

The post showed how migrating SQL Server indexed views to materialized views in Aurora PostgreSQL or Amazon RDS for PostgreSQL can significantly improve query performance for your application However, there are limitations and considerations that you need to keep in mind, such as data type compatibility, aggregate function support, transaction control, storage space requirements, refresh times, and query optimization. By evaluating these factors and testing your queries, you can have a successful migration and improved performance for your application.

We invite you to leave your feedback in the comments sections.

About the authors

Yugundhar Kalikapuram is a Lead Consultant at AWS Professional Services and is a subject matter expert in database migrations to Amazon RDS. He has 15 years of experience and is an ardent enthusiast in relational database management systems and NoSQL technologies.

Sawan Saxena is a Lead Consultant with the AWS Professional Services team. He helps customers architect, modernize, and migrate their database solutions to AWS.