DEV Community: Mia Liang

Materialized Views: An Introduction

Mia Liang — Wed, 03 Feb 2021 15:08:23 +0000

A materialized view is one that is precomputed, allowing it to periodically cache the results of an incoming query in the name of increasing performance and efficiency as much as possible. Generally speaking, those queries that use materialized views are faster and consume fewer resources than those that process queries to base tables - something that is true even if they're both retrieving the exact same data.

What is a Materialized View?

Based on all the above, it's easy to see why materialized views have become one of the best opportunities that organizations have to boost the performance of workloads that use common and repeated queries. Materialized views are also notable because they require no maintenance to speak of, as the view is always recomputed in the background after the base table in question has been changed. Any incremental changes from that base table is added to the materialized view automatically, with absolutely no inputs required on behalf of users.

Another major benefit is that a materialized view is always 100% consistent with the base table, meaning that you're always working from fresh data without exception. Even if a base table is changed in some way due to an update, or because of the expiration of a partition, the materialized view tools you're using will invalidate anything that was impacted and re-read the portion of the base table in question to guarantee accuracy.

Use Cases for Materialized Views

One of the biggest use cases for materialized views involves unlocking faster query performance whenever possible. Organizations who are working with raw tables to perform online analytical processing aggregation that depend on significant processing, for example, stand to benefit enormously from materialized views. The same is true if they are working within a situation that has predictable and repeatable queries, like if they were hoping to improve their business intelligence pipeline.

But as referenced, one of the main use cases for materialized views involves the aggregation of real time data across an enterprise. Some materialized views tools you may be using perform all aggregations in real time, thus making sure that organizational leaders always have up-to-date and totally accurate information to work from when making decisions.

How to Get Started With Materialized Views

To get a better understanding of how to get started with materialized views, one must first learn more about the actual flow of data across a materialized view to begin with.

After the users of your application or other system begin to trigger events and create transactions, that data will then be committed to the source table that you're working with. At that point, an internal trigger in the source table will populate the materialized view log table.

Then, a fast refresh will be executed and any changes that have been created since the last time this happened will be applied to the materialized view. All users can now query data from the materialized view using a variety of tools, all with the confidence that comes with knowing they're working with the latest information from the most recent version of that table's data.

To create a materialized view in the tool you're working with, you can use a DML statement to create a basic table, to load data into it, and to create a materialized view as a result. An example of an SQL command that could be used to create a materialized view is as follows:

CREATE [ OR REPLACE ] [ SECURE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <name> [ COPY GRANTS ] ( <column_list> ) [ COMMENT = '<string_literal>' ] [ CLUSTER BY ( <expr1> [, <expr2> ... ] ) ] AS <select_statement>

Of course, there are a number of potential limitations that you should be aware of. By its nature, materialized views are only as "current" as they were the last time you ran the query. This is why you should make an effort to do so as often as possible. Likewise, a materialized view will only ever be able to query a single table - so you should keep that in mind when making decisions moving forward.

Materialized Views Tools

There are a wide range of different materialized views tools you can choose to work from depending on your needs. They were first implemented by the Oracle Database, for example, and the "Query Rewrite" feature is available from version 8I onward. Any version of PostgreSQL after 9.3 also natively supports materialized views, and you can even use the "Concurrently" syntax to refresh the contents automatically.

SQL Server works with materialized views, although here they go by a different name - "Indexed Views." They're the same basic concept, but they don't require a refresh because they are always in full sync with the original data of the tables by design. This has been a part of every version of SQL Server released since the year 2000.

Just a few of the other materialized views tools you may use include but are not limited to Sybase SQL Anywhere, ClickHouse, Amazon DynamoDB, BigQuery and others. The one that makes the most sense given your use case will obviously vary depending on your needs.

Database Migrations: A Complete Guide

Mia Liang — Fri, 29 Jan 2021 14:34:32 +0000

The world of application development is continually changing. Since the early 2000s, agile development processes introduced controlled change (not chaos) into the development process. Instead of long-run development projects that are difficult to change, agile projects make incremental changes in short sprints, from a few days to a few weeks. Database changes are a part of the agile process, and the migration process from one version to the next must be carefully documented along the way. Database migration processes and tools are critical ingredients in the agile methodology.

What is Database Migration?

Any change in a relational database requires a migration to the new design. This process includes any changes or modifications to the current database table. Keeping track of the database changes is an essential step in development and testing. Developers can write migration code in the programming language or use a migration tool to manage the process and write notes (aka artifacts) in the code to track those changes.

There are two types of database migration processes. The state-based migration process starts with a blank database and tracks the changes until the new database features are coded. Change-based migration begins with the current database table and adds the incremental stages on top of that table. The advantage of using change-based migration is the speed of development. The drawback is the need to look at each incremental step to fully view the database evolution.

Benefits of Database Migration

As applications are incrementally changed, databases need to be changed too. The database migration process keeps track of these changes to aid in the development and testing process. Migration tools create clear documentation of this process helps developers and users keep track of new versions for quality control purposes.

Database migrations are made for many reasons. One reason may be to improve application performance. Saving money is another reason for migrating to a new database structure. An application may have an old database structure, and upgrading to a new database may lower operating costs. Technology improvements are another way to migrate databases. A typical business case includes moving an on-premise application to a cloud-based application. The new cloud technology may have different data needs, and there needs to be a process to track and control these changes.

Cyber threats often target database tables. Security concerns are another reason to initiate database migrations. If the database structure is left unchanged, it may make it easier for hackers to make future attacks. Updating the data structure on a regular basis will help prevent future attacks.

Database Migration Challenges

Complex applications with multiple databases make the migration process difficult. It can be challenging to identify all of the databases that need updating. If the data isn’t correctly identified, schema mapping can prevent problems and extend the time and cost involved with the migration process.

According to Simform, “Enterprise database migration projects can cost up to $875K and experience more than $250K in cost overruns.” A significant reason for these overruns is the lack of an integrated strategy and project plan. Without proper advanced planning, change orders can add considerable time and costs.

What Happens During Database Migration?

Database applications need to be upgraded and migrated regularly. For instance, the database might need to add new properties or objects. Perhaps the business logic changed, and the dataset needs to be adjusted. These types of upgrades happen regularly for SaaS applications that use databases to power their server-side applications. Well organized migrations will create a cleaner database and even improve the response and productivity of the application.

Why use Database Migrations?

Database migrations can be used in the development stage of an application and the testing and deployment stage. Migration tools generate documentation threads from an empty state to an initial-level schema. During the testing and development stage, migration tools keep track of the test results and database changes until the application is deemed secure and ready for deployment.

How do you Perform A Database Migration?

Most developers use migration tools to create the scripts and artifacts necessary to track the database state changes. Many tools are available in the development platform you are using to write the program and SQL code. These tools tend to be popular since they integrate with the platform making it convenient for the developer.

Beyond the tools, database migration projects require planning and coordination between the entire development and testing team. If the team uses an agile strategy, daily stand-up calls and one to two-week sprints split the migration steps into manageable steps. The migration tool will create migration code in the platform’s language—the migration code is written in the same development language you are using. Artifacts should be clearly written, and you should avoid tools that create difficult to understand artifacts.

There are several features and factors to consider before investing in a database migration tool. The development platform you are using may include migration software. These tools are often a good choice because they integrate with the other development tools. The maturity of the tool is another consideration. The tool should have a good track record and a lot of positive reviews. It should also come with adequate documentation and technical support in case you run into technical snags. Another consideration is the artifacts the migration tool creates.

Database Migration Best Practices

Best practices develop based on the trials and errors experienced by fellow developers. For starters, migration scripts should be kept separate from the production code. Complex projects need pre-planning, which will make the process more efficient and less costly. Keeping your data clean will pay off with a smoother migration process. Conduct your cleansing and normalizing operations before you start your database migration project. You should test each migration step separately so you don’t confuse test results. It will be easier to run your migration project if you use a strategic plan and include data migration as part of your agile development process.

Conclusion

Database structures and schemas evolve as the applications they support change. Using an agile development process requires database migrations to happen on a continual basis to keep up with application updates. Providing a path of well-defined artifacts will keep you from missing critical steps during the development, test, and deployment stages. To improve the process, you can use migration tools integrated with popular development platforms. There are third-party tools that can be downloaded or purchased separately. You should keep your migration code separate from your development code and maintain accurate records to keep pace with the rapid development process.