---
title: 'PostgreSQL for dashboards: partitioning, materialized views, and JSONB | DevSense'
description: 'Build fast PostgreSQL dashboards: date partitioning, instant retention drops, materialized views and rollup tables, and properly indexed JSONB in Laravel.'
faq:
    - { question: 'When should you partition a table by date?', answer: 'When it holds hundreds of millions of rows, queries almost always filter by time, and old data has to be removed regularly. Partitioning lets the planner skip irrelevant partitions (partition pruning) and lets you remove a whole month of data with DROP or DETACH PARTITION in milliseconds instead of a long DELETE followed by VACUUM.' }
    - { question: 'What does REFRESH MATERIALIZED VIEW CONCURRENTLY give you?', answer: "A plain REFRESH takes an exclusive lock, so the dashboard can't read the view while it's being recomputed. CONCURRENTLY builds the new version alongside and applies the difference without blocking reads. For this to work, the materialized view must have a unique index covering all rows." }
    - { question: 'Which index do you need for JSONB queries?', answer: "For containment queries (the @> operator), a GIN index with the jsonb_path_ops operator class works well: it's more compact than the default jsonb_ops. If you constantly filter on a single key, an expression index on (payload->>'game_id') or a generated column with a regular B-tree index is more efficient." }
    - { question: 'When does PostgreSQL stop being a good fit for analytics?', answer: 'When data volumes reach billions of rows and queries aggregate large ranges across many dimensions in real time. Row-oriented storage and MVCC make such scans expensive, and columnar databases like ClickHouse win by orders of magnitude. Up to that point, partitioning and pre-aggregation usually cover what dashboards need.' }
published: '2026-09-28'
---
# PostgreSQL for dashboards: partitioning, materialized views, and JSONB

The stats dashboard takes 40 seconds to load. The `events` table has grown to 400 million rows, there's an index on `occurred_at`, `EXPLAIN` shows an Index Scan, and yet the "revenue by provider over 30 days" query still reads tens of millions of rows. The nightly `DELETE` of old events runs for three hours and leaves the table bloated, and autovacuum can't keep up. More indexes won't help here: the problem isn't finding rows, it's that the dashboard recomputes on every load what could have been computed once.

**Related guides:** [Query optimization: EXPLAIN and partitioning](database-query-optimization) · [Database indexes](database-indexes-deep-dive) · [High-load event ingestion](high-load-event-ingestion)

## Contents

* [Why a transactional table is a poor source for a dashboard](#oltp-vs-dashboards)
* [Time-based partitioning](#partitioning)
* [Removing old data in milliseconds](#retention)
* [Materialized views](#materialized-views)
* [Rollup tables: incremental pre-aggregation](#rollups)
* [JSONB for provider data](#jsonb)
* [Putting it all together](#architecture)
* [Where this approach stops working](#limitations)
* [Common Mistakes](#common-mistakes)
* [Checklist](#checklist)
* [Self-Test Quiz](#self-test-quiz)

---

<a id="oltp-vs-dashboards"></a>
## Why a transactional table is a poor source for a dashboard

A transactional table is optimized for short operations: insert an event, look up an order by ID, update a status. A dashboard does the opposite: it reads huge ranges and collapses them into a handful of numbers. When both kinds of workload hit the same table:

* **Every dashboard view repeats the same work.** Ten managers opening the page means ten full aggregations over a month of data.
* **An index doesn't save you from volume.** Even a perfect date index finds 30 million rows for a month, and they still have to be read and summed.
* **Removing old data is expensive.** `DELETE` marks rows as dead, and the table stays bloated until the next `VACUUM`.

**A fast PostgreSQL dashboard is built not on indexes but on the shape of the data: raw events are partitioned by time, aggregates are computed ahead of time and updated incrementally, and variable provider attributes are stored in JSONB with indexes only where you actually filter on them.**

---

<a id="partitioning"></a>
## Time-based partitioning

Declarative partitioning (PostgreSQL 10+) splits one logical table into physical partitions by key range. For events, the key is almost always time:

```php
// database/migrations/2026_09_28_200000_create_events_table.php
<?php

declare(strict_types=1);

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        DB::statement(<<<'SQL'
            CREATE TABLE events (
                id           bigint GENERATED ALWAYS AS IDENTITY,
                occurred_at  timestamptz NOT NULL,
                provider     text        NOT NULL,
                event_type   text        NOT NULL,
                amount       bigint      NOT NULL DEFAULT 0,
                payload      jsonb       NOT NULL DEFAULT '{}',
                PRIMARY KEY (id, occurred_at)
            ) PARTITION BY RANGE (occurred_at)
        SQL);

        DB::statement('CREATE INDEX events_provider_time_idx ON events (provider, occurred_at)');
    }

    public function down(): void
    {
        DB::statement('DROP TABLE IF EXISTS events');
    }
};
```

What matters here:

* **The primary key must include the partition key.** PostgreSQL enforces uniqueness only within a partition, so you can't create `PRIMARY KEY (id)` on a partitioned table; you need `(id, occurred_at)`.
* **Indexes created on the parent table are automatically created on every partition** (PostgreSQL 11+), including future ones.

Partitions are created ahead of time, for example by a scheduled command:

```php
// app/Console/Commands/CreateEventPartitions.php
<?php

declare(strict_types=1);

namespace App\Console\Commands;

use Carbon\CarbonImmutable;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

final class CreateEventPartitions extends Command
{
    protected $signature = 'events:partitions {--months=3}';

    protected $description = 'Create monthly partitions for the events table ahead of time';

    public function handle(): int
    {
        $start = CarbonImmutable::now()->startOfMonth();

        for ($i = 0; $i <= (int) $this->option('months'); $i++) {
            $from = $start->addMonths($i);
            $to = $from->addMonth();
            $name = 'events_'.$from->format('Y_m');

            DB::statement(sprintf(
                "CREATE TABLE IF NOT EXISTS %s PARTITION OF events FOR VALUES FROM ('%s') TO ('%s')",
                $name,
                $from->toDateString(),
                $to->toDateString(),
            ));
        }

        return self::SUCCESS;
    }
}
```

```php
// routes/console.php
Schedule::command('events:partitions')->daily();
```

Now a query for the last 30 days touches one or two partitions. In `EXPLAIN` you can see this because the plan lists only the relevant partitions: that's **partition pruning**. The condition must be on the key itself: `WHERE occurred_at >= now() - interval '30 days'` prunes partitions, while `WHERE date(occurred_at) = ...` doesn't, because the function hides the key from the planner.

> [!TIP]
> **BRIN instead of B-tree for time.** In an append-only table, events are written in near-chronological order. A BRIN index on `occurred_at` takes kilobytes instead of gigabytes and works great for range queries: `CREATE INDEX ON events USING brin (occurred_at);`

---

<a id="retention"></a>
## Removing old data in milliseconds

The main practical payoff of partitioning is in your retention policy. Removing events older than a year:

```sql
-- Without partitions: hours of work, table bloat, load on WAL and replicas
DELETE FROM events WHERE occurred_at < now() - interval '1 year';

-- With partitions: a metadata operation
ALTER TABLE events DETACH PARTITION events_2025_09 CONCURRENTLY; -- PostgreSQL 14+
DROP TABLE events_2025_09;
```

Instead of dropping a detached partition right away, you can export it to cold storage (`pg_dump -t events_2025_09`) and only then drop it.

> [!WARNING]
> **Be careful with the default partition.** The `DEFAULT` partition accepts rows that don't fall into any range. But when you create a new partition, PostgreSQL has to verify that `DEFAULT` contains no rows from its range, and that's a full scan under a lock. It's safer to always create partitions ahead of time and monitor that `DEFAULT` stays empty.

---

<a id="materialized-views"></a>
## Materialized views

A materialized view is a query result stored as a table. The dashboard reads the precomputed result, and recomputation runs on a schedule:

```sql
CREATE MATERIALIZED VIEW daily_provider_stats AS
SELECT
    date_trunc('day', occurred_at)::date AS day,
    provider,
    count(*)                             AS events,
    sum(amount)                          AS revenue
FROM events
WHERE occurred_at >= now() - interval '90 days'
GROUP BY 1, 2;

-- Required for REFRESH ... CONCURRENTLY
CREATE UNIQUE INDEX daily_provider_stats_uidx ON daily_provider_stats (day, provider);
```

```php
// routes/console.php
Schedule::call(fn () => DB::statement('REFRESH MATERIALIZED VIEW CONCURRENTLY daily_provider_stats'))
    ->everyFifteenMinutes()
    ->name('refresh-daily-provider-stats')
    ->withoutOverlapping()
    ->onOneServer();
```

```php
// app/Models/DailyProviderStat.php
final class DailyProviderStat extends Model
{
    protected $table = 'daily_provider_stats';

    public $timestamps = false;

    public $incrementing = false;
}
```

The dashboard queries the model like a regular table: `DailyProviderStat::where('day', '>=', now()->subDays(30))->get()`.

* **`CONCURRENTLY`** recomputes the data without blocking reads. Without it, the dashboard waits for the refresh to finish. `CONCURRENTLY` requires a unique index over all rows of the view.
* **`withoutOverlapping()` and `onOneServer()`** prevent two refreshes from running at once if the previous one hasn't finished yet or you have multiple servers.

Materialized views have one serious drawback: `REFRESH` re-runs the query **in full** every time. While that takes seconds, it's fine. Once recomputing 90 days starts taking minutes, it's time to move to incremental aggregation.

---

<a id="rollups"></a>
## Rollup tables: incremental pre-aggregation

A rollup table stores aggregates by "bucket" (hour, day) and is updated only for the most recent period:

```php
// database/migrations/2026_09_28_210000_create_hourly_provider_stats_table.php
Schema::create('hourly_provider_stats', function (Blueprint $table) {
    $table->timestampTz('hour');
    $table->string('provider');
    $table->unsignedBigInteger('events');
    $table->bigInteger('revenue');
    $table->primary(['hour', 'provider']);
});
```

```php
// app/Console/Commands/RollupHourlyStats.php
public function handle(): int
{
    // Recompute only the last 2 hours: late-arriving events get counted too
    DB::statement(<<<'SQL'
        INSERT INTO hourly_provider_stats (hour, provider, events, revenue)
        SELECT date_trunc('hour', occurred_at), provider, count(*), sum(amount)
        FROM events
        WHERE occurred_at >= date_trunc('hour', now()) - interval '2 hours'
        GROUP BY 1, 2
        ON CONFLICT (hour, provider)
        DO UPDATE SET events = EXCLUDED.events, revenue = EXCLUDED.revenue
    SQL);

    return self::SUCCESS;
}
```

Each run reads two hours of events rather than 90 days, so the cost doesn't grow with the history. The dashboard gets daily and monthly figures by summing hourly rows: thousands of rows instead of millions. The recomputation window is longer than one hour to account for events that arrive late.

---

<a id="jsonb"></a>
## JSONB for provider data

Providers send different fields: one has `round_id` and `bet_lines`, another has `session` and `multiplier`. Creating a column for every field from every provider makes no sense, and this is where JSONB fits:

```php
// Queries from Laravel
Event::where('payload->game_id', 'book-of-ra')->count();          // payload->>'game_id' = ?
Event::whereJsonContains('payload->tags', 'bonus')->count();      // payload->'tags' @> '["bonus"]'
Event::whereRaw("payload @> ?", [json_encode(['currency' => 'EUR'])])->sum('amount');
```

Indexes are chosen to match specific queries:

```sql
-- Containment search (@>): a compact GIN index
CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);

-- A constant filter on a single key: an expression index
CREATE INDEX events_game_id_idx ON events ((payload->>'game_id'));
```

If a key is not only filtered on but also grouped by in reports, move it into a generated column (PostgreSQL 12+). It gets planner statistics and a regular B-tree index:

```php
// database/migrations/2026_09_28_220000_add_game_id_to_events.php
Schema::table('events', function (Blueprint $table) {
    $table->string('game_id')->nullable()->storedAs("payload->>'game_id'");
    $table->index('game_id');
});
```

Keep in mind that adding a `STORED` column rewrites the entire table under a lock. On a large table, you do this when the schema is created or during a maintenance window; in a live system, an expression index is the way to go.

> [!NOTE]
> **JSONB is not a substitute for a schema.** PostgreSQL doesn't collect statistics on keys inside JSONB, so the planner estimates the selectivity of such conditions poorly. Anything you regularly filter, group, or join on should be a column.

---

<a id="architecture"></a>
## Putting it all together

```
[ Providers / application ]
            │  INSERT (in batches)
            ▼
[ events ] — monthly partitions, JSONB payload, BRIN on time
            │  every 5 minutes: rollup of the last 2 hours
            ▼
[ hourly_provider_stats ] — thousands of rows instead of millions
            │  REFRESH CONCURRENTLY every 15 minutes
            ▼
[ materialized views ] — complex slices for individual widgets
            │
            ▼
[ Dashboard ] — reads only aggregates (preferably from a replica)
```

Raw events remain available for detailed reports and investigations, but the dashboard never touches them. Old partitions are archived on a schedule, and the size of the "hot" data stays constant.

---

<a id="limitations"></a>
## Where this approach stops working

* **Too many partitions.** Every partition adds to planning time. Thousands of small partitions (daily ones over several years, say) will slow down even simple queries. Monthly partitions are enough for most dashboards.
* **Uniqueness across partitions.** Unique constraints must include the partition key. Global uniqueness, for example on `external_id`, has to be enforced with a separate table or at the application level.
* **Indexes on partitioned tables can't be built `CONCURRENTLY` on the parent.** You have to create the index on each partition individually and then attach them with `ALTER INDEX ... ATTACH PARTITION`.
* **Data lag.** Pre-aggregates always trail slightly behind. If the business needs to-the-second numbers, serve them from the rollup plus a "tail" of raw data for the last few minutes.
* **Analytics scale.** With billions of rows and arbitrary slicing, columnar databases (ClickHouse) win by orders of magnitude. PostgreSQL remains the source of truth, and analytics moves to where it belongs; see [high-load event ingestion](high-load-event-ingestion).

---

<a id="common-mistakes"></a>
## Common Mistakes

**1. Wrapping the partition key in a function in `WHERE`.**
`WHERE date(occurred_at) = '2026-09-01'` disables partition pruning. Write a range instead: `occurred_at >= '2026-09-01' AND occurred_at < '2026-09-02'`.

**2. Using `DELETE` to purge history.**
Hours of work and a bloated table. Remove whole partitions with `DETACH` and `DROP`.

**3. Creating partitions only when they're needed.**
At the start of a month, inserts fail or land in `DEFAULT`. Create partitions several months ahead on a schedule.

**4. `REFRESH MATERIALIZED VIEW` without `CONCURRENTLY`.**
The dashboard is blocked for the duration of the refresh. Add a unique index and use `CONCURRENTLY`.

**5. Recomputing the entire history on every run.**
A materialized view over 3 years takes longer to refresh than the refresh interval. Move to rollup tables with incremental upserts.

**6. Putting everything in JSONB.**
Filtering and grouping on JSONB keys without statistics produces bad plans. Move frequently used keys into columns.

**7. Running the dashboard against the primary database.**
Heavy aggregations compete with user transactions. Read aggregates from a replica.

---

<a id="checklist"></a>
## Checklist

1. Large event tables are partitioned by time; the primary key includes the partition key.
2. Partitions are created ahead of time by a scheduled command; the `DEFAULT` partition is empty or absent.
3. Queries filter on the partition key without functions; `EXPLAIN` shows pruning.
4. Old data is removed via `DETACH` and `DROP PARTITION`, with archiving if needed.
5. The dashboard reads only aggregates: rollup tables with incremental upserts or materialized views.
6. `REFRESH MATERIALIZED VIEW CONCURRENTLY` with a unique index, `withoutOverlapping()`, and `onOneServer()`.
7. For JSONB: GIN `jsonb_path_ops` for `@>` and expression indexes for frequently used keys; keys used for grouping are moved into columns.
8. Heavy dashboard queries go to a replica.

---

## Summary

A slow dashboard is almost never fixed by one more index. It's fixed by shaping the data in advance into the form in which it's read: partitions prune irrelevant time ranges and make purging history free, pre-aggregates turn millions of rows into thousands, and JSONB provides flexibility where the schema genuinely varies from provider to provider.

---

<a id="self-test-quiz"></a>
## Self-Test Quiz

### Question 1: Why can't you create `PRIMARY KEY (id)` on a table partitioned by `occurred_at`?
- A) PostgreSQL doesn't support primary keys on partitioned tables.
- B) Uniqueness is enforced within each partition, so the partition key must be part of every unique constraint.
- C) The `id` column must be of type `uuid`.

<details>
<summary>Click to view the answer</summary>

**Answer: B**
Each partition has its own index, and PostgreSQL can't enforce uniqueness across them. Including `occurred_at` guarantees that identical key values always land in the same partition, where uniqueness is checked locally.
</details>

### Question 2: What does `REFRESH MATERIALIZED VIEW CONCURRENTLY` require?
- A) A unique index on the materialized view that covers all rows.
- B) Partitioning of the source table.
- C) Running the command as a superuser.

<details>
<summary>Click to view the answer</summary>

**Answer: A**
In `CONCURRENTLY` mode, PostgreSQL compares the old and new versions row by row, and the unique index is needed to match rows up. Without it, only a plain `REFRESH` is available, and that blocks reads.
</details>

### Question 3: Which condition lets PostgreSQL prune irrelevant partitions?
- A) `WHERE date(occurred_at) >= '2026-09-01'`
- B) `WHERE occurred_at >= '2026-09-01'`
- C) `WHERE extract(month FROM occurred_at) = 9`

<details>
<summary>Click to view the answer</summary>

**Answer: B**
Partition pruning works when the condition is applied directly to the partition key. The `date()` and `extract()` functions hide the key from the planner, forcing it to scan every partition.
</details>