How to Leverage Views and Materialized Views in CockroachDB

In modern databases, efficient data access and maintainability are crucial — especially as schemas evolve and queries grow more complex. Views in CockroachDB help bridge this gap by allowing developers to create logical representations of data without altering the underlying tables.

CockroachDB supports both regular views and materialized views, each serving distinct use cases:

• Regular views provide real-time access to data but compute results on every query.

• Materialized views store precomputed results, enabling faster queries but requiring data refresh to bring the precompiled results up to date.

To illustrate the difference between regular views and materialized views, let's compare them to interpreted vs. compiled programming languages.

Regular Views are like Interpreted Languages

Interpreted languages — such as Python, JavaScript, Ruby, and PHP — execute code on the fly without pre-compilation. Every time a program is run, the interpreter steps through multiple phases:

1. Lexical Analysis (Tokenization) – Breaking down the code into tokens.

2. Syntax Analysis (Parsing) – Checking grammar and constructing an Abstract Syntax Tree (AST), an internal structural representation of the program.

3. Semantic Analysis – Detecting logical errors, such as type mismatches or undefined variables.

4. Bytecode Generation (Optional) – Compiling parts of the code into an optimized format for faster repeated execution.

5. Execution – Running the program statement by statement.

Since these steps repeat every time the program runs, there is a significant overhead, making interpreted languages slower and more resource-intensive over time. However, they offer the huge advantage of enabling rapid code changes, where a developer can modify the code and execute it immediately—a key factor in productivity.

Similarly, regular views in CockroachDB are dynamically evaluated each time they are queried. They allow for quick modifications to the view structure without affecting the underlying data, but at the expense of querying the base tables on every execution, leading to potential performance overhead.

For example, a banking application might use a regular view to simplify queries for customer account information. Instead of writing complex joins across multiple tables (like accounts, transactions, and customers), a view can combine the data. This allows it to present it in an easy-to-access format for the customer support team, which ensures they always have the latest information without needing to manually manage the query logic.

Materialized Views are like Compiled Languages

Compiled languages—such as Go, Rust, and Java—require a compiler that processes the code through similar lexical, syntax, and semantic analysis steps. However, instead of interpreting the code every time it runs, the compiler translates it into machine code and stores it as an executable binary.

1. The compiled binary can be executed multiple times without needing to re-run the compiler, making execution fast and efficient.

2. However, the executable is essentially a snapshot of the program at the time of compilation.

3. If the source code changes, the compiler must be re-run to generate a new executable.

Materialized views in CockroachDB work the same way as compiled programs—they precompute and store query results for fast execution, but they represent a snapshot of the data at the time of materialization. If the underlying data changes, the materialized view must be refreshed to reflect the latest information.

In the e-commerce industry, for instance, an online store may use a materialized view to store aggregated sales data, such as the total sales per product category over the past week. This way, marketing and sales teams can access the aggregated data instantly without having to perform complex calculations or queries on the fly. This leads to faster reporting and decision-making.

This article explores the key differences, advantages, and practical applications of both view types in CockroachDB. We'll also discuss how they enhance query performance, simplify schema changes, and improve application maintainability — all while reducing the complexity of working with large datasets. 

Views: The Fundamentals

As noted above, a view is a read-only virtual table based on a SELECT statement, pulling data from one or more tables. Once created, it can be queried like a regular table. Let’s look at the fundamentals of views and their applications.

In this article, I'll be using a simple database with two tables to demonstrate the various features and capabilities of CockroachDB views. Before we begin delving into the details, let's look at the database schema:

The `datapoints` table has a composite primary key and a foreign key that references the primary key from the `stations` table. Simply put, a `station` with a unique ID and located in some geographic region logs a timestamped datapoint consisting of two integers, two floats, and a string.

Let's now create our first view:

The newly created view will show up in the list of tables:

And just like with regular tables, we can inspect the view structure:

We can now query our new view:

From an application programmer’s perspective, querying a view is much simpler than querying the underlying tables. This is especially true for complex queries that involve selecting specific columns across multiple tables, performing aggregations, calculations, ordering, and grouping.

Here’s a slightly more complex example:

This view tracks the number of data points logged by all stations in each region, along with statistical insights about their values. It effectively abstracts the underlying tables, making it easier and more flexible for users or applications to consume these statistics.

Since views act as a buffer between underlying tables and the queries run against them, they can serve as an effective mechanism for controlling data exposure. For example, station IDs in the `stations` and `datapoints` tables are not exposed through the view. As a result, a user with access to the view but not the underlying tables will be unable to see them.

Let's demonstrate this with an example.

First, we create a new user:

The only permission granted to the new user will be the ability to read from this specific view:

After logging out and back in as the view_reader user, I can successfully SELECT from the view while the underlying tables remain inaccessible.

WARNING! Don't forget to log out and log back in as `root`user to continue.

Improving Application Maintainability

Often, multiple applications access the same database. SQL statements may be scattered across the source code and maintained by different developers. As applications evolve, the underlying tables may require modifications, such as adding new columns.

Let's consider an application that queries the `datapoints` table for ten random rows using a wildcard to select all columns:

The application logic expects the result set to contain seven columns. Now, imagine a new column is added to the `datapoints` table:

If the application logic is not designed to handle an eight-column result set from the same SELECT query, the change in table structure we introduced has broken the application:

Instead of modifying queries across multiple applications, we can decouple the application code from the database tables by using a view as an intermediary layer:

Using this new view instead of querying the underlying table directly protects the application logic from breaking when new columns are introduced in the underlying table over time.

WARNING! This approach using views works only when new columns are added. However, to be fair, removing columns from a table should be rare. If truly necessary, it should be treated as a special effort, requiring a thorough review and retesting of associated applications.

What are materialized views?

Let’s look at views vs. materialized views. While views are "virtual" tables that query the underlying physical tables each time they are accessed, materialized views store a physical copy of the query result. 

This key distinction makes materialized views extremely powerful. However, before exploring their advantages, let's address their main drawback: Unlike regular views, which always retrieve up-to-date data from the underlying tables, materialized views act as snapshots that must be refreshed to reflect the latest changes. We’ll discuss how to refresh a materialized view later in this article.

Performance via Pre-Compute

A materialized view doesn’t re-run its base query each time it’s accessed. Instead, it serves a snapshot of the data saved when the view was created or last refreshed. Of course, querying a materialized view (SELECT) may involve filtering, ordering, grouping, and some calculations, but these operations are minimal compared to the compute time and resources required to refresh the view itself. Let's experience this with some examples.

Earlier, we created the `datapoint_aggregations_by_region` view. Let's create its materialized counterpart and run the same queries against both:

We can observe the execution time improvement from almost 15 seconds to 31 milliseconds. The query against the materialized view is almost 500 times faster!

Here is a more complex example – I'd like to compile a report of the datapoints created during the previous day on an hourly basis:

Creating a regular view took 364 milliseconds, while creating a materialized view took 63.260 seconds. If the goal is to repeatedly access the generated report – such as through a microservice supporting a web or mobile application – the upfront compute cost of creating the materialized view is well justified. Over time, it will save the same time difference on every subsequent access.

Here’s what the resulting report looks like:

Performance via Indexing

Materialized views are essentially physical tables, much like regular tables. The key difference is that data in a regular table is modified directly through operations like `INSERT`, `UPDATE`, `DELETE`, and `LOAD`, whereas a materialized view’s data changes indirectly by inheriting updates from its underlying tables.

Once created, additional indexes can be added to further optimize `SELECT` queries against the materialized view. Let's create a materialized view that joins the `stations` and `datapoints` tables, creating a result set that could support operational analytics and reporting:

Now, let's look at the following query:

Not a great timing – this is likely because the SQL statement `WHERE` clause condition needs to filter on the length of the string in the `param4` column.  We can review the query plan with the `EXPLAIN` statement:

Our estimate of the bottleneck was correct; the `FULL SCAN` is what takes a long time here. We can address this with a new index on length of `param4`:

If we revisit the statement plan, we can see that the new index helps find the rows where `param4` is 23 characters long:

Re-running the `SELECT` statement yields a much better result – in fact 132 times faster:

Here is another example. Consider the following query:

Using `EXPLAIN` again, we can see a full scan with the consecutive filtering on the `id` column:

This time, `EXPLAIN` even suggests how to create an index to optimize the query. Note the use of `STORING`, which is useful when the query selects certain columns without filtering on them.

After creating the recommended index, the same SELECT query runs much faster:

Refreshing Materialized Views

As mentioned earlier, materialized views are physical data snapshots based on the underlying `SELECT` query, following the "compute once, use many times" model. They don't continually update as the underlying tables' data changes. This enables users to save on complex computing at the expense of not having the real-time reporting. The reality is that many reporting and analytics applications can tolerate a near-real time or even discrete, periodic data updates.

A materialized view is first populated at creation time, and then updated, or re-freshed, with a `REFRESH` statement. Let see how it works in practice.

Here is the partial out, just the first few rows of the result set, from a simple query against the materialized view we've already used previously:

This view computes a number of data points for all the stations within each geographical region. We're going to insert some new data points for the `AP East (Hong Kong)` region and verify that our materialized view reflects that.

First, let's pick a station for our new data points to insert:

Then, we insert fifteen new rows by running the following statement repeatedly:

Remember that just because we have now introduced fifteen rows in the `datapoints` table, we don't expect it to be reflected automatically in the materialized view:

And as we can see, the count in the same column hasn't changed. What if we refresh our materialized view?

We can now observe that the data point count for the `AP East (Hong Kong)` region has increased by 15, as expected.

Takeaways

Views and Materialized Views: What You Can Do in CockroachDB

CockroachDB offers both regular and materialized views, allowing developers to organize and optimize data access efficiently. Views provide several key benefits:

• Schema Abstraction – Simplifies database structure by presenting a logical view of the data.

• Complexity Reduction – Offers a structured, application-friendly format without modifying underlying tables.

• Backward Compatibility – Helps applications adapt to schema changes without breaking queries.

• Security Control – Enables granular access by exposing or restricting specific data.

Regular vs. Materialized Views: Choosing the Right Approach

Both types of views have their strengths, and the right choice depends on performance needs and data freshness requirements:

• Regular Views provide real-time, up-to-date data but require computation on every query, which may impact performance. They rely on underlying table indexes, but adding extra indexes for optimization isn’t always ideal.

• Materialized Views store precomputed results, offering faster query performance at the cost of requiring manual refreshes to reflect new data. They are best suited for scenarios where fast, repeated access to static data is critical.

Regular vs. Materialized Views: The Interpreted vs. Compiled Analogy

Revisiting our earlier comparison, regular views function like interpreted languages, dynamically retrieving the latest data on each query — flexible but computationally expensive. Materialized views, on the other hand, resemble compiled programs, delivering precomputed, high-speed results but requiring manual refreshes to stay current.

Which Approach is Right for You?

The choice depends on your specific use case: - If immediacy and flexibility are key, regular views are ideal. - If performance and efficiency matter most, materialized views offer the speed advantage.

By understanding these trade-offs, developers and database architects can fine-tune CockroachDB’s capabilities to balance query performance, data accuracy, and system efficiency.

Ready to try views and materialized views in CockroachDB? You can get hands-on with CockroachDB’s free cloud offering today.