The Power of Precomputed Data: How Materialized Views Boosted Performance
In today’s data-driven healthcare landscape, providers have access to vast amounts of patient data, which is critical for diagnosing and treating life-threatening conditions like sepsis. Sepsis, which can cause a rapid and severe decline in a patient's health, requires prompt and effective intervention. However, the volume of healthcare data combined with the need for real-time decision-making makes it challenging to process and retrieve relevant information quickly.
In this blog, we’ll explore how PostgreSQL’s materialized views can be used to optimize querying real-time healthcare data, specifically focusing on a sepsis dataset. Materialized views allow for precomputing and storing the results of complex queries, significantly reducing query times and resource consumption when the same data is requested repeatedly.
In healthcare, one widely used scoring system is the APACHE II score (Acute Physiology and Chronic Health Evaluation), which evaluates the severity of illness in ICU patients based on vital signs, lab results, age, and health history within the first 24 hours of ICU admission. A higher APACHE score indicates a greater risk, helping guide prognosis, decision-making, and resource planning. However, querying large datasets of patient records, especially with millions of rows and complex variables like heart rate, blood pressure, and lactate levels, can result in slow query performance.
Healthcare providers often rely on reports/dashboards to query the database and display patient information, including predicted risk scores. These reports/dashboards are accessed by multiple users and frequently refreshed throughout the day. With large volumes of data, queries can take considerable time to process. This is where materialized views come into play. By refreshing the data once a day, materialized views allow query results to be reused efficiently throughout the day, improving the performance of dashboard queries and ensuring faster access to critical information.
APACHE Score Calculation Example
Here’s a query used to calculate the APACHE II score by joining multiple tables: patients, vitals, labs, and sepsis_labels.
Query to return APACHE report is in the dropdown below:
This query took approximately 7 minutes to execute, primarily due to the aggregations and the underlying joins of multiple tables performed on a large dataset.
Optimizing with Materialized Views
Since the Apache score is calculated only once every 24 hours, it’s unnecessary for the query to hit the database repeatedly with multiple joins and aggregations. Instead, a more efficient approach would be to compute the score once and allow users to reuse the precomputed data. To optimize this process, we can leverage materialized views. A materialized view stores the results of a complex query as a static result set in memory. Once created, the materialized view can be refreshed periodically, eliminating the need to recompute the results each time the query is executed.
Here’s how you can create a materialized view to compute the APACHE score for each patient within 24 hours of ICU admission:
Query encapsulated in the materialized view is available in the dropdown below:
CREATE MATERIALIZED VIEW sepsis_apache_ii_score AS
WITH score_calculations AS (
SELECT p.Patient_ID,p.Age,p.Gender,
-- Temperature score (worst value)
CASE
WHEN MAX(v.Temp) < 30 THEN 4
WHEN MAX(v.Temp) BETWEEN 30 AND 34.9 THEN 3
WHEN MAX(v.Temp) BETWEEN 35 AND 39.9 THEN 0
WHEN MAX(v.Temp) BETWEEN 40 AND 41.9 THEN 2
WHEN MAX(v.Temp) >= 42 THEN 4
ELSE 0
END AS temp_score,
-- MAP score (worst value)
CASE
WHEN MIN(v.MAP) < 50 THEN 4
WHEN MIN(v.MAP) BETWEEN 50 AND 69.9 THEN 3
WHEN MIN(v.MAP) BETWEEN 70 AND 99.9 THEN 0
WHEN MIN(v.MAP) BETWEEN 100 AND 119.9 THEN 1
WHEN MIN(v.MAP) >= 120 THEN 2
ELSE 0
END AS map_score,
-- Heart Rate (HR) score (worst value)
CASE
WHEN MAX(v.HR) < 40 THEN 4
WHEN MAX(v.HR) BETWEEN 40 AND 49.9 THEN 3
WHEN MAX(v.HR) BETWEEN 50 AND 69.9 THEN 0
WHEN MAX(v.HR) BETWEEN 70 AND 109.9 THEN 0
WHEN MAX(v.HR) BETWEEN 110 AND 139.9 THEN 2
WHEN MAX(v.HR) >= 140 THEN 4
ELSE 0
END AS hr_score,
-- Respiratory Rate (RR) score (worst value)
CASE
WHEN MAX(v.Resp) < 6 THEN 4
WHEN MAX(v.Resp) BETWEEN 6 AND 9.9 THEN 3
WHEN MAX(v.Resp) BETWEEN 10 AND 29.9 THEN 0
WHEN MAX(v.Resp) BETWEEN 30 AND 34.9 THEN 1
WHEN MAX(v.Resp) >= 35 THEN 2
ELSE 0
END AS rr_score,
-- Oxygenation (PaCO2/FiO2) score (worst value)
CASE
WHEN MIN(l.FiO2) IS NULL OR MIN(l.FiO2) = 0 THEN 0 -- Handle division by zero or NULL FiO2
WHEN MIN(l.PaCO2) IS NULL THEN 0 -- Handle NULL PaCO2
WHEN (MIN(l.PaCO2) / NULLIF(MIN(l.FiO2), 0)) < 150 THEN 4
WHEN (MIN(l.PaCO2) / NULLIF(MIN(l.FiO2), 0)) BETWEEN 150 AND 249.9 THEN 3
WHEN (MIN(l.PaCO2) / NULLIF(MIN(l.FiO2), 0)) BETWEEN 250 AND 399.9 THEN 1
WHEN (MIN(l.PaCO2) / NULLIF(MIN(l.FiO2), 0)) >= 400 THEN 0
ELSE 0
END AS ox_score,
-- pH score (worst value)
CASE
WHEN MIN(l.pH) < 7.15 THEN 4
WHEN MIN(l.pH) BETWEEN 7.15 AND 7.29 THEN 3
WHEN MIN(l.pH) BETWEEN 7.30 AND 7.49 THEN 0
WHEN MIN(l.pH) BETWEEN 7.50 AND 7.59 THEN 2
WHEN MIN(l.pH) >= 7.60 THEN 4
ELSE 0
END AS ph_score
FROM patients p
JOIN vitals v ON p.Patient_ID = v.Patient_ID
JOIN labs l ON p.Patient_ID = l.Patient_ID
JOIN sepsis_labels sl ON sl.patient_id = p.Patient_ID
WHERE v.ICULOS <= 24 -- Patients in ICU for 24 hours or less
GROUP BY p.Patient_ID, p.Age, p.Gender
)
SELECT Patient_ID,Age,Gender,temp_score,map_score,hr_score,rr_score,ox_score,ph_score,
(temp_score + map_score + hr_score + rr_score + ox_score + ph_score) AS apache_score-- Calculate Apache II score
FROM score_calculations order by apache_score;
This materialized view took around 60 seconds to create and can now be queried much faster than repeatedly executing the original complex query. Now, we can use materialized view that has been created above as below:
SELECT * FROM sepsis_apache_ii_score ;
The above query returns data in approximately 390 milliseconds, providing users with significantly faster access to the data and improving overall performance.
Refreshing the Materialized View
Since the data is dynamic, the materialized view needs to be refreshed regularly to reflect changes in the underlying tables. We can refresh the materialized view with the following command:
REFRESH MATERIALIZED VIEW sepsis_apache_ii_score;
Real-Time Benefits:
Reduced Database Hits and Faster Query Retrieval
Materialized views significantly improve performance when refreshing data for every dashboard hit is unnecessary, especially when working with large datasets. By storing the precomputed query results, materialized views reduce the need for repeated database queries. This not only reduces database hits but also speeds up query retrieval, making reports/dashboards more responsive.
Optimized Refresh Rate
In the case of APACHE score calculation, for example, the data is typically refreshed once every 24 hours. However, the frequency of refreshing the materialized view is flexible and can be adjusted based on the application's needs. This allows a balance between data freshness and performance, ensuring efficient application performance without unnecessary overhead.
Views vs. Materialized Views
A view is a virtual table defined by a query. Every time a view is queried, the database executes the underlying query and retrieves fresh data from the source tables. As a result, views do not store any data themselves.
In contrast, a materialized view stores the result of a query in memory (or on disk). This precomputed result is used for subsequent queries instead of re-executing the underlying query each time. This approach speeds up query performance by reducing the need to repeatedly access the underlying tables.
Materialized views are particularly useful in scenarios where performance is critical, and real-time data freshness is not a strict requirement. They are ideal when you want to avoid the overhead of re-running expensive queries, especially for large datasets.
When to Use Each
Views:
Use views when you need always-up-to-date data and the performance overhead of querying is acceptable. They are ideal for reports or dashboards where fresh data is crucial.
Materialized Views:
Use materialized views when you need to optimize query performance and can tolerate slightly stale data (e.g., reports that are updated periodically rather than in real time). They're especially useful for complex aggregation queries or large datasets.
By using materialized views in PostgreSQL, healthcare providers can efficiently handle large, complex datasets, like sepsis data, and significantly improve the performance of real-time decision-making tools such as reports/dashboards. Materialized views offer an effective way to balance the need for fast query performance with the flexibility of periodically refreshing data.
