Back to Blog

Boosting Map Performance with Materialized Views in Postgres

Mohamed Almadih

Mohamed Almadih

9/5/2025
4 min read
Boosting Map Performance with Materialized Views in Postgres

The Goal

I had geospatial data in a PostgreSQL database that I wanted to display as a heatmap for the last 7 days.
The straightforward approach was to query the raw reports table every time the page loads.

But when you’re dealing with thousands of rows, that becomes painfully inefficient.

So, how do we make this fast?

The Solution: Materialized Views

A materialized view is like a cached version of a query. Unlike a normal view, which runs the query every time it’s accessed, a materialized view stores the result physically on disk.

This makes it perfect for cases where:

  • The query is expensive (geospatial + date filters).
  • The data doesn’t change every second.
  • You want fast lookups without hammering the main table.

Defining a Materialized View

Here’s a simple example:

1
CREATE MATERIALIZED VIEW view_name AS
2
SELECT
3
    -- query here
4
FROM
5
	-- target table here
6
WHERE
7
	-- condition here

In my case, I had a reports table with a location column (geometry point with latitude/longitude).
I created a materialized view that snaps points into grid cells and counts reports per cell.

Here’s the migration in Laravel:

1
DB::statement('
2
CREATE MATERIALIZED VIEW mv_heatmap_cells AS
3
SELECT
4
    MD5(ST_AsText(ST_SnapToGrid(r.location, 0.01, 0.01)))::uuid AS cell_id,
5
    ST_SnapToGrid(r.location, 0.01, 0.01) AS geom,
6
    COUNT(*) AS report_count,
7
    CURRENT_DATE AS data_until_date
8
FROM reports r
9
WHERE r.created_at >= (CURRENT_DATE - INTERVAL \'6 days\')
10
  AND r.created_at < (CURRENT_DATE + INTERVAL \'1 day\')
11
GROUP BY geom;
12
');
13


14
DB::statement('CREATE UNIQUE INDEX IF NOT EXISTS uidx_mv_heatmap_cells_cell_id ON mv_heatmap_cells (cell_id);');
15
DB::statement('CREATE INDEX IF NOT EXISTS idx_mv_heatmap_cells_week_geom ON mv_heatmap_cells (cell_id, geom);');
16
DB::statement('CREATE INDEX IF NOT EXISTS idx_mv_heatmap_cells_geom_gist ON mv_heatmap_cells USING GIST (geom);');

What’s Happening Here?

  • Cell ID: I used an MD5 hash of the grid cell as a unique identifier.
  • ST_SnapToGrid: Groups all points into a grid of 0.01 x 0.01 units.
  • Report Count: Counts how many reports fall into each grid cell.
  • 7-Day Filter: Only considers reports created in the last 7 days.
  • Indexes: Added for faster queries.

Keeping It Fresh

A materialized view doesn’t update automatically. You need to refresh it:

1
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_heatmap_cells;

In Laravel, I made a command and hooked it into the scheduler:

1
class RefreshHeatmapCells extends Command
2
{
3
    protected $signature = 'app:refresh-heatmap-cells';
4
    protected $description = 'Refresh heatmap cells materialized view.';
5


6
    public function handle()
7
    {
8
        DB::statement('REFRESH MATERIALIZED VIEW CONCURRENTLY mv_heatmap_cells;');
9
    }
10
}

This way, the view stays up-to-date without blocking queries.

Displaying on a Map

Finally, let’s put it on a map using Vue and Google Maps:

1
<script lang="ts" setup>
2
import axios from 'axios';
3
import { ref, watch, onMounted } from 'vue';
4


5
const center = ref({ lat: 30.89, lng: 29.87 });
6
const heatmapData = ref([]);
7
const mapRef = ref<google.maps.Map | null>(null);
8


9
const fetchHeatmapData = () => {
10
  axios.get(route('reports-heatmap')).then((res) => {
11
    heatmapData.value = res.data.rows.map((feature: any) => {
12
      const geometry = JSON.parse(feature.geometry);
13
      const props = JSON.parse(feature.properties);
14
      return {
15
        location: new google.maps.LatLng(geometry.coordinates[1], geometry.coordinates[0]),
16
        weight: props.report_count * 10
17
      };
18
    });
19
  });
20
};
21


22
const mapLoaded = (map: any) => { mapRef.value = map; };
23


24
watch([mapRef], () => { if (mapRef.value) fetchHeatmapData(); });
25
</script>
26


27
<template>
28
  <Map :center="center" :zoom="8" @map:loaded="mapLoaded" class="h-[500px]">
29
    <GMapHeatmap :data="heatmapData" />
30
  </Map>
31
</template>

Here, the report count is used as the weight of each heatmap point.
You could get fancier by adding weights based on priority, severity, or other attributes.

Wrapping Up

With materialized views, you can:

  • Pre-compute expensive queries.
  • Keep your heatmaps responsive even with thousands of rows.
  • Control when to refresh the data.

This approach gives you the best of both worlds: performance and flexibility. 🚀