SQL Materialized Views
A Cloud Database Purpose-Built for
Data Warehouse Offload
Take your expensive and slow queries off of your data warehouse and serve them in real-time with Materialize.
“Our data team was serving real-time delivery status and location analytics to customers with two weeks of work and minimal ongoing maintenance.”
Clayton Von Hovel Data Engineer, Onward Delivery
![](https://res.cloudinary.com/mzimgcdn/image/upload/v1679938191/onward-square.webp)
Operate your business in real-time for less
Dashboards and Business Intelligence
Materialize is PostgreSQL wire-compatible, enabling connection with BI tools like Looker, Metabase and more.
User-Facing Analytics
Query or subscribe to data in Materialize directly from customer-facing applications, without the need for complex caching.
Real-time data quality tests
Monitor your data pipelines as you would any other production system and get notified - in real-time - when data quality expectations fail. No need for an orchestrator to schedule data model runs.
Operational and IoT Reporting
Create live customer service dashboards, manage IoT device performance, and automate logistics-focused dashboards.
Learn how to get started with
Materialize
It is easy to get lulled into a false sense of security with a data warehouse: it works! But, at what cost? Many organizations today are struggling with both high cost and slow performance when they try to do operational work on their data warehouse. Read the resources below to see how you can reduce your data warehouse costs while using the SQL and team you already have by migrating to a real-time data warehouse.
Modern Data Applications need Modern Solutions
Real-time analytics workloads need a different computation paradigm, but stream processing is not the answer.
![undefined](https://res.cloudinary.com/mzimgcdn/image/upload/v1687874986/Warehouse.gif)
Traditional Warehouses: Too Slow
Cloud Warehouses are easy to use, but get expensive when run continuously, and hit hard limits on latency and concurrency.
![undefined](https://res.cloudinary.com/mzimgcdn/image/upload/v1687874985/Streaming_Processor.gif)
Stream Processors: Too Complicated
Stream Processors are fast, but they're a low-level tool, using them has high engineering costs, creates complex architectures.
Materialize packages the speed of stream processors in a familiar database abstraction.
Use the same SQL workflows from traditional warehouses but get results
updated continuously to power real-time analytics in production.
Streaming Engine
Work is done at the moment of data arrival, rather than query time, so that maintained results are available almost instantly.
PostgreSQL Serving Layer
Materialize presents as Postgres - query it with high concurrency from any tool or driver compatible with Postgres.
Access via standard SQL
Incrementally Maintained Views
Write complex SQL transformations as materialized views that efficiently update themselves as inputs change.
Learn MoreBuilt for JOINs
Multi-way, complex join support, even across databases - all in standard SQL.
Learn MoreSliding Windows
Write queries that filter to a window of time anchored to the present, Materialize will update results as time advances.
Learn MoreSQL Subscriptions
Write alerts as SQL queries with filters and subscribe to new rows as they appear.
Learn MoreCREATE MATERIALIZED VIEW my_view AS
SELECT userid, COUNT(api.id), COUNT(pageviews.id)
FROM users
JOIN pageviews on users.id = pageviews.userid
JOIN api ON users.id = api.userId
GROUP BY userid;
userID | api_calls | pageviews |
---|---|---|
VPLaKV | 400 | 20 |
MN37Mt | 60 | 9 |
1fT4KY | 72 | 42 |
sT4QY | 10 | 342 |
Incrementally Maintained Views
Write complex SQL transformations as materialized views that efficiently update themselves as inputs change.
Learn MoreCREATE MATERIALIZED VIEW my_view AS
SELECT userid, COUNT(api.id), COUNT(pageviews.id)
FROM users
JOIN pageviews on users.id = pageviews.userid
JOIN api ON users.id = api.userId
GROUP BY userid;
userID | api_calls | pageviews |
---|---|---|
VPLaKV | 400 | 20 |
MN37Mt | 60 | 9 |
1fT4KY | 72 | 42 |
sT4QY | 10 | 342 |
Built for JOINs
Multi-way, complex join support, even across databases - all in standard SQL.
Learn MoreSELECT DISTINCT ON (auctions.id)
bids.amount,
auctions.item,
auctions.seller
FROM auctions, bids
WHERE auctions.id = bids.auction_id
ORDER BY auctions.id,
bids.amount DESC,
bids.buyer;
amount | item | seller |
---|
Sliding Windows
Write queries that filter to a window of time anchored to the present, Materialize will update results as time advances.
Learn MoreCREATE MATERIALIZED VIEW my_window AS
SELECT date_trunc('minute', received_at),
COUNT(*) as order_ct, SUM(amount) as revenue
FROM orders
WHERE mz_now() < received_at + interval '5 minutes'
GROUP BY 1;
minute | order_ct | revenue |
---|
SQL Subscriptions
Write alerts as SQL queries with filters and subscribe to new rows as they appear.
Learn MoreSELECT userID, email, MAX(orders.id) as last_order
FROM users
JOIN orders ON orders.userID = users.id
GROUP BY userId, email
-- Use a filter to surface users with a high % of fraud
HAVING SUM(is_fraud) / COUNT(orders.id)::FLOAT > 0.5;
userID | last_order | |
---|---|---|
REOtIb | a@gmail.com | 13/12/2022 |
Y5KBE8 | b@yahoo.com | 9/12/2022 |
Wj7JQ0 | c@hotmail.com | 13/12/2022 |
tPCQ0 | d@xyz.com | 13/11/2022 |
Built for JOINs
Multi-way, complex join support across real-time streams - all in standard SQL.
Active Replication
Use replication to increase availability, reduce downtime, scale seamlessly.
Secure and Compliant
SOC 2 Type 2 compliant, encrypted at rest, secure connectivity to your infra.
The ease of a data warehouse with the speed of streaming
You don't need to compromise on speed, data quality, control, or simplicity.
“My warehouse is too slow - but real-time is too expensive”
Get access to real-time without rebuilding or rehiring: Materialize uses familiar database abstractions and ANSI-standard SQL.
“Our analytics are too join-heavy to move to real-time”
Materialize efficiently handles complex SQL joins on fast-changing data.
“We don't want to take on the operational burden of Kafka”
No Kafka? No problem. Materialize connects directly to your Postgres DB via Change Data Capture (CDC).
“This is high-stakes data, we can't show incorrect results.”
Unlike other solutions, Materialize is strongly consistent: Incomplete results are never served to the user.
“We’d like to keep our existing visualization tools”
Materialize is Postgres wire-compatible: Tools that connect to Postgres can connect and query Materialize.
“We already move Kafka data into our data warehouse”
Warehouses help you report on data, move the same SQL to Materialize to serve high concurrency, low latency data products.
More Use Cases
Automation and Alerting
→Save time for your users, and build value by taking action or notifying at only the right moments.
![Automation and Alerting](https://res.cloudinary.com/mzimgcdn/image/upload/v1721033892/home/automation-and-alerting.webp)
Real-time Fraud Detection
→Monitor transactions as they occur and stop fraud in seconds.
![Real-time Fraud Detection](https://res.cloudinary.com/mzimgcdn/image/upload/v1711479124/home/fraud-detection.webp)
Segmentation and Personalization
→Value of personalization, recommendations, dynamic pricing increases as latency of data aggregations approaches zero.
![Segmentation and Personalization](https://res.cloudinary.com/mzimgcdn/image/upload/v1711479125/home/segmentation-and-personalization.webp)
ML in Production
→Online feature stores need continually updated data, operators need to monitor and react to changes in ML effectiveness.
![ML in Production](https://res.cloudinary.com/mzimgcdn/image/upload/v1711479125/home/ml-in-production.webp)