Materialized Views in RDBMS - Is it a View or Table?

 

Google uses structured data to understand the content on the page and use that data to display in richer features in the search results.

Below you can see the difference in the search result data provided by Google for the OnePlus 8T product from official site vs amazon site. The amazon one has additional data rendered in the same search results.

Image Courtesy - Shushma

As we started to build the feature to feed the product details to Google, the data has to be retrieved from various tables from the catalog schema based on multiple  conditions.

Once we had the API ready, we did load testing, having 25 threads with a ramp-up time of 5 seconds and test duration of 30 seconds. Though we know the search engine bots won't put so much load on the site but the performance of the API was a concern to check.

If you look at the 95 percentile of time, it took 10 seconds to respond and the number of requests reached 430 only. Team members came up with the idea of caching the data on redis, so that next call will be faster. Caching at application layer for this use case will not be of that great help as we have a TTL of 600 seconds on Redis.

I recalled in one of my previous assignments, where we had a similar use case and we had utilised the caching of the data on the database (materialized view) side instead on the application layer which had given better results.

Understand Terminologies

Let's start with TABLE, it's basically an organized storage for your data in the form of rows and columns. You can easily query the table using predicates on the columns. 

To simplify the queries or maybe to apply different security mechanisms on data being accessed you can use VIEWs. Think of a view as glasses through which you can look at your data without knowing the actual tables and realtionship details etc.

If the table is a storage, a view is just a way of looking at it or a projection of the data as view doesn't store the data physically. If you query a table, you fetch its data directly. On the other hand, when you query a view, you are basically querying another query that is stored in the view's definition. But the query planner is aware of that and build a plan to merge the two together and give the results.

Between the table and view, we have the MATERIALIZED VIEW. It's a view that has a query in its definition and uses this query to fetch the data directly from the storage, but it also has it's own storage that basically acts as a cache in between the underlying table(s). 

Now you might have a question, if any data is updated in the underlying table(s) whether they will reflect in materialized view. The simple answer is NO as materialized view acts like a cache, it wont reflect the changes. We need to refresh the materialized view, through a process that would cause it's definition's query to be executed again on actual data and rebuilds the cache.

Materialized View Approach

We created a materialized view to cache the data on the database side, as we were retrieving the data from multiple tables. The required data is available in it, so we can avoid lot of computation at the real-time and also the fetched data won't change very frequently.

With the code changes, we did same load testing having 25 threads with a ramp-up time of 5 seconds and test duration of 30 seconds.

If you look at the 95 percentile of time it took 75 milli seconds to respond and the number of requests reached to 8376. The performance gain with respect to throughput is 19x and 95 percentile of time is 134x time faster. The results looks wonderful and it's without caching on the application layer side.

Query Plan

• The original query which used to join multiple tables with various conditions had the query planning time of 0.852 ms and execution time of 5.511 ms.
• The query which we fire on materialized view from the application layer had the query planning time of 0.087 ms and execution time of 0.052 ms.

Conclusion

• The Materialized View is supported by all relational databases like Oracle, MySQL, Postgres etc.
• The  Materialized View is a powerful tool enabling many performance improvements while providing another way of ensuring data consistency.

References

• Materialized Views by Oracle
• Materialized Views by PostgreSQL

Read More

Amazon DynamoDB

Amazon DynamoDB is a key-value and document database that delivers single-digit millisecond performance at any scale. It’s a fully managed, multi-region, multi-active, durable database with built-in security, backup and restore, and in-memory caching for internet-scale applications. DynamoDB can handle more than 10 trillion requests per day and can support peaks of more than 20 million requests per second.

This post aims to help developers and operations team to understand the strengths and weaknesses of DynamoDB.

Data Modeling

DynamoDB supports a document oriented data model. To create a table, we just define the primary / partition key. Items can be added into these tables with a dynamic set of attributes similar to MongoDB. Items in DynamoDB correspond to rows and attributes correspond to columns in RDBMS. DynamoDB supports these data types - Number, String, Binary, Boolean, Set, List, Map.

Operations Ease

As it’s a managed service from Amazon, users are abstracted away from the underlying infrastructure and interact only with the database over a remote endpoint. There is no need to worry about operational concerns such as hardware, setup/configuration, throughput capacity planning, replication, software patching, or cluster scaling — making it very easy to get started.

In fact, there is no way to access the underlying infrastructure components such as the instances or disks. DynamoDB tables require users to reserve read capacity units (RCUs) and write capacity units (WCUs) upfront. Users are charged by the hour for the throughput capacity reserved (whether or not these tables are receiving any reads or writes).

Linear Scalability

DynamoDB supports auto sharding and load-balancing. This allows applications to transparently store ever-growing amounts of data. The linear scalability of DynamoDB is good for applications that need to handle growing datasets and IOPS requirements. However, this linear scalability comes with extreme costs beyond a certain point.

Amazon Ecosystem Integration

DynamoDB is well integrated into the AWS ecosystem. It means that end users do not need to figure out how to perform various integrations by themselves. Below are couple of examples of these integrations:

• Data can easily and cost-effectively be backed up to S3
• Security and access control is integrated into AWS IAM

Cost Effectiveness

DynamoDB’s pricing model can easily make it the single most expensive AWS service for a fast growing data set. Here are some reasons:

Higher provisioning to handle partitions

In DynamoDB, the total provisioned IOPS is evenly divided across all the partitions. Therefore, it is extremely important to choose a partition key that will evenly distribute reads and writes across these partitions.

Cost explodes for fast growing data sets

As data grows, so do the number of partitions in order to automatically scale out the data (each partition is a maximum of 10GB). However, the total provisioned throughput for a table does not increase. Thus, the throughput available for each partition will constantly decrease with data growth.

Indexes will result in additional cost

Applications wanting to query data on attributes that are not a part of the primary key need to create secondary indexes. Local Secondary Indexes do not incur extra cost, but Global Secondary Indexes require additional read and write capacity provisioned leads to additional cost.

Additional cost for caching tier

Applications wanting less latency, we should add cache to increase the performance. The caching tier DAX or Elastic Cache is an additional expense on the top of the database tier.

The ideal workloads for DynamoDB should have the following characteristics:

• Low write throughput.
• Small and constant dataset size, doesn’t have unknown data growth.
• Constant or predictable read throughput, should not be explosion or unpredictable.
• Applications that can tolerate eventual consistent reads, the least expensive data access operation in DynamoDB.

Some guidelines if you are going to use DynamoDB.

• Use GUID’s or Unique Attributes, instead of incremental IDs.
• Don’t try to normalise your tables.
• Keeping pre-computed data upon updates is efficient with DynamoDB if you need to query them often.
• Don’t try to keep many relationships across tables. This will end up needing to query multiple tables to retrieve required attributes.
• Design your tables, attributes, and indexes thinking of the nature of queries.
• Think about item sizes and using indexes effectively when listing items to minimise throughput requirements.
• Avoid using DynamoDB Scan operation whenever possible.

Read More

MyBatis Database Migration Tool

Evolving databases has been one of the major challenges for software development. Often regardless of our software development methodology, the database follows a different change management process. The tools of the past have been GUI centric, proprietary for a particular database and/or carried a steep license cost. Yet, at the end of the day they suffered from the same challenges.

The MyBatis Migration Maven plugin is a simple command line tool that helps us to manage the database schema changes in a more systematic way where manual work can be avoided. It can be used for any relational database systems like postgres, mysql, oracle etc.

For migration, you create the sql script files as usual which will contain one more sql statements and save the file with a specific naming convention provided by the MyBatis Miration. File naming convention YYYYMMDDHHMMSS_file_name.sql. The timestamp provided in the filename should be unique otherwise the migration will abort with an error.

• Eg:- 20170506163015_create_member_details.sql

One of the good thing about MyBatis Migration is that the script file will contain sql statements to make the changes as well as to rever those changes. Hence the script file has been divided into two sections like below

• --// First migration. -- Migration SQL that makes the change goes here. 
• --//@UNDO -- SQL to undo the change goes here.

Here are the steps to integrate your new or an existing project with MyBatis Migration.

Add Maven Plugin and Dependency

<plugin>
         <groupId>org.mybatis.maven</groupId>
         <artifactId>migrations-maven-plugin</artifactId>
         <version>1.1.2</version>
         <configuration>
             <repository>src/main/resources/db/</repository>
             <output>dist/migration-name.sql</output>
         </configuration>
             <dependencies>
                 <dependency>
                     <groupId>org.postgresql</groupId>
                     <artifactId>postgresql</artifactId>
                     <version>9.4-1205-jdbc4</version>

                 </dependency>
             </dependencies>
     </plugin>

Add a property file

Under your src/main/resources add a sub-directory named environments. And create a property file and name it say development.properties. You can also create test.properties and production.properties files. The environment can be specified when running a migration by using the –env=<environment> option (without the path or “.properties” part). Below is the template which should be used for the property file.

## Base time zone to ensure times are consistent across machines
time_zone=GMT+0:00

## The character set that scripts are encoded with
# script_char_set=UTF-8

## JDBC connection properties.
driver=
url=
username=
password=

# Name of the table that tracks changes to the database
changelog=db_changelog

# If set to true, each statement is isolated in its own transaction.
# Otherwise the entire script is executed in one transaction.
auto_commit=false

# This controls how statements are delimited. 
# By default statements are delimited by an end of line semicolon. 
# Some databases may (e.g. MS SQL Server) may require a full line delimiter such as GO.
delimiter=;
full_line_delimiter=false

# This ignores the line delimiters and simply sends the entire script at once.
# Use with JDBC drivers that can accept large blocks of delimited text at once.
send_full_script=false

# Custom driver path to avoid copying your drivers
# driver_path=

Maven Goals

mvn migration:status

As the goal is straight forward, it provides the status of the migration whether the script has been executed or not. It provides a simple tabular information that contains id (timestamp provided in the filename), description (filename after the timestamp) and applied_at specifies when the migration has run for each file on the database.

mvn migration:up

A goal which will execute all pending migation scripts one by one based on the timestamp provided in the order.

mvn migration:down

A goal which will execute the undo section of the last migrated script.

mvn migration:pending

A goal which will execute all pending migation scripts one by one based on the timestamp provided in the order.

An alternate for MyBatis Migration is FlyWay and Liquidbase. You can download the fully functional working copy of the project from GitHub.

In case if you are wondering if we have any similar tool for MongoDB, then you can explore MongoBee which works similarly but only thing it lacks is reverting the changes as it’s not supported currently. To know more about it, please visit MongoBee.

Read More