Scaling PostgreSQL for Real-Time Analytics Dashboard Queries

1. The Scaling Bottleneck

When building real-time dashboards for logistics or analytical SaaS platforms, database write and read operations scale exponentially. A single slow query can lock tables, blocking incoming data feeds.

In this guide, we look at how to tune PostgreSQL configurations to return rapid results across millions of rows.

---

2. Table Partitioning by Time Ranges

Partitioning large historical tables divides massive indexes into manageable chunks. Here is the SQL query to create a range-partitioned log table:

CREATE TABLE project_logs (
    id SERIAL,
    project_id INTEGER,
    event_type VARCHAR(50),
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    payload JSONB
) PARTITION BY RANGE (created_at);

-- Create individual month partitions
CREATE TABLE logs_2026_05 PARTITION OF project_logs
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE logs_2026_06 PARTITION OF project_logs
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

---

3. Materialized Views with Concurrent Refresh

For dashboard widgets requiring aggregate counts (e.g. daily sales totals), querying historical tables on every render is prohibitively expensive.

We create Materialized Views that can be updated asynchronously:

CREATE MATERIALIZED VIEW daily_project_aggregates AS
SELECT 
    project_id,
    DATE_TRUNC('day', created_at) as log_day,
    COUNT(*) as total_events
FROM project_logs
GROUP BY project_id, log_day;

-- Refresh the view concurrently in background tasks
CREATE UNIQUE INDEX CONCURRENTLY idx_mview_proj_day 
    ON daily_project_aggregates (project_id, log_day);

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_project_aggregates;

Using partitioning alongside materialized views keeps query times under 80ms, even under heavy load.