Materialized View or Table Partitioning in PostgreSQL?

Recently, I faced a challenging situation at work. We needed to optimize the performance of queries on a massive table in the school system, which records all conducted lessons. The problem? The data volume was growing exponentially, and queries weren’t as fast as we wanted. You know that "what now?" moment? Yeah, that was me.

After thorough analysis and several cups of coffee, I decided to use two solutions: table partitioning and materialized views. Let me show you how each fit perfectly into our scenario!

The Scenario

To give you some context, here’s a summary of the lessons table:

• lesson_date: the date of the lesson.
• lesson_number: the lesson number.
• class: the class that attended.
• subject: the subject taught.
• teacher: the responsible teacher.

With years of accumulated history, queries were becoming slow. The goal was to optimize both system operations and report generation.

Table Partitioning: The Application’s Backbone

The first solution was to partition the table by year. This means each "chunk" of the table stores data for a specific year.

How Did We Do It?

We created a parent table and child tables (partitions):

-- Create the parent table
CREATE TABLE lessons (
    lesson_date DATE NOT NULL,
    lesson_number INT NOT NULL,
    class TEXT NOT NULL,
    subject TEXT NOT NULL,
    teacher TEXT NOT NULL
) PARTITION BY RANGE (lesson_date);

-- Create the partitions
CREATE TABLE lessons_2024 PARTITION OF lessons
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE lessons_2023 PARTITION OF lessons
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');        

Why Did It Work?

• Faster Queries: When the application queries 2024 data, PostgreSQL only accesses the corresponding partition. This drastically reduced response times.
• Backend Simplicity: No need to map views or do tricks. Just filter the parent table:

SELECT * FROM lessons WHERE lesson_date >= '2024-01-01' AND lesson_date < '2025-01-01';        

For application use, partitioning was the perfect solution. But for reports and documents, the story was different...

Materialized Views: The Report Solution

Reports like student histories require fast, efficient queries, but the data doesn’t change often. Enter MATERIALIZED VIEWS!

What Did We Do?

We created a materialized view to store historical data for previous years:

-- Create the materialized view
CREATE MATERIALIZED VIEW lessons_history AS
SELECT *
FROM lessons
WHERE lesson_date < '2024-01-01';

-- Create an index on the materialized view
CREATE INDEX idx_lessons_history_date ON lessons_history (lesson_date);        

Why Did It Work?

• Read Performance: Queries on old data became incredibly fast since results were precomputed and indexed.
• Report Simplicity: With static data, there was no need to constantly synchronize.

Comparing the Solutions

Conclusion

In the end, each solution had its role. Partitioning ensured the application ran smoothly, while materialized views boosted reports.

Moral of the story? Understanding your tools is key to using them at the right time. What about you? Have you faced something similar? Share your experience in the comments!

#PostgreSQL #DatabaseOptimization #MaterializedView #Partitioning #BackendTips #Reports #SQLPerformance