---
title: 'PostgreSQL für Dashboards: Partitionierung, Materialized Views und JSONB | DevSense'
description: 'Wie Sie schnelle Dashboards auf PostgreSQL bauen: Partitionierung nach Datum, sofortiges Löschen alter Daten, Materialized Views und Rollup-Tabellen sowie JSONB mit den richtigen Indizes in Laravel.'
faq:
    - { question: 'Wann lohnt es sich, eine Tabelle nach Datum zu partitionieren?', answer: 'Wenn sie Hunderte Millionen Zeilen enthält, Queries fast immer nach Zeit filtern und alte Daten regelmäßig gelöscht werden müssen. Partitionierung ermöglicht es, bei der Query-Planung irrelevante Partitionen auszuschließen (Partition Pruning), und erlaubt es, einen ganzen Monat an Daten per DROP oder DETACH PARTITION in Millisekunden zu löschen – statt eines langen DELETE mit anschließendem VACUUM.' }
    - { question: 'Was bringt REFRESH MATERIALIZED VIEW CONCURRENTLY?', answer: 'Ein gewöhnliches REFRESH nimmt eine exklusive Sperre, und das Dashboard kann die View nicht lesen, solange sie neu berechnet wird. CONCURRENTLY baut die neue Version daneben auf und wendet die Differenz an, ohne Lesezugriffe zu blockieren. Dafür braucht die Materialized View einen Unique-Index, der alle Zeilen abdeckt.' }
    - { question: 'Welcher Index wird für Queries auf JSONB benötigt?', answer: "Für die Suche nach Enthaltensein (Operator @>) eignet sich ein GIN-Index mit der Operatorklasse jsonb_path_ops: Er ist kompakter als das gewöhnliche jsonb_ops. Wenn Sie ständig nach einem einzelnen Schlüssel filtern, ist ein Ausdrucksindex auf (payload->>'game_id') oder eine generierte Spalte mit einem gewöhnlichen B-Tree-Index effizienter." }
    - { question: 'Wann ist PostgreSQL für Analytics nicht mehr geeignet?', answer: 'Wenn die Datenmengen in Milliarden Zeilen gemessen werden und Queries große Zeiträume über viele Dimensionen in Echtzeit aggregieren. Die zeilenorientierte Speicherung und MVCC machen solche Scans teuer, und spaltenorientierte Datenbanken wie ClickHouse sind um Größenordnungen schneller. Bis dahin decken Partitionierung und Voraggregation den Bedarf von Dashboards in der Regel ab.' }
published: '2026-09-28'
---
# PostgreSQL für Dashboards: Partitionierung, Materialized Views und JSONB

Das Statistik-Dashboard braucht 40 Sekunden zum Laden. Die Tabelle `events` ist auf 400 Millionen Zeilen angewachsen, ein Index auf `occurred_at` existiert, `EXPLAIN` zeigt einen Index Scan, und trotzdem liest die Query „Umsatz pro Anbieter der letzten 30 Tage“ Dutzende Millionen Zeilen. Das nächtliche `DELETE` alter Events dauert drei Stunden und hinterlässt eine aufgeblähte Tabelle, während Autovacuum nicht hinterherkommt. Neue Indizes helfen hier nicht: Das Problem ist nicht das Finden der Zeilen, sondern dass das Dashboard jedes Mal neu berechnet, was man auch einmal hätte berechnen können.

**Verwandte Leitfäden:** [Query-Optimierung: EXPLAIN und Partitionierung](database-query-optimization) · [Datenbankindizes](database-indexes-deep-dive) · [Event-Ingestion unter hoher Last](high-load-event-ingestion)

## Inhalt

* [Warum eine Transaktionstabelle eine schlechte Quelle für ein Dashboard ist](#oltp-vs-dashboards)
* [Partitionierung nach Zeit](#partitioning)
* [Alte Daten in Millisekunden löschen](#retention)
* [Materialized Views](#materialized-views)
* [Rollup-Tabellen: inkrementelle Voraggregation](#rollups)
* [JSONB für Anbieterdaten](#jsonb)
* [Alles zusammengesetzt](#architecture)
* [Wo der Ansatz an seine Grenzen stößt](#limitations)
* [Häufige Fehler](#common-mistakes)
* [Checkliste](#checklist)
* [Selbsttest-Quiz](#self-test-quiz)

---

<a id="oltp-vs-dashboards"></a>
## Warum eine Transaktionstabelle eine schlechte Quelle für ein Dashboard ist

Eine Transaktionstabelle ist auf kurze Operationen optimiert: ein Event einfügen, eine Bestellung per ID finden, einen Status aktualisieren. Ein Dashboard tut das Gegenteil: Es liest riesige Bereiche und verdichtet sie zu ein paar Zahlen. Wenn beide Lastarten auf dieselbe Tabelle gehen:

* **Jeder Aufruf des Dashboards wiederholt dieselbe Arbeit.** Zehn Manager, die die Seite öffnen, bedeuten zehn vollständige Aggregationen über einen Monat.
* **Ein Index schützt nicht vor dem Volumen.** Selbst ein perfekter Index auf das Datum findet 30 Millionen Zeilen pro Monat, und die müssen trotzdem gelesen und aufsummiert werden.
* **Das Löschen alter Daten ist teuer.** `DELETE` markiert Zeilen als tot, und die Tabelle bläht sich bis zum nächsten `VACUUM` auf.

**Ein schnelles Dashboard auf PostgreSQL basiert nicht auf Indizes, sondern auf der Form der Daten: Rohe Events werden nach Zeit partitioniert, Aggregate werden vorab berechnet und inkrementell aktualisiert, und variable Anbieterattribute liegen in JSONB – mit Indizes nur dort, wo danach gefiltert wird.**

---

<a id="partitioning"></a>
## Partitionierung nach Zeit

Deklarative Partitionierung (PostgreSQL 10+) teilt eine logische Tabelle anhand eines Schlüsselbereichs in physische Partitionen auf. Bei Events ist der Schlüssel fast immer die Zeit:

```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');
    }
};
```

Worauf es hier ankommt:

* **Der Primärschlüssel muss den Partitionierungsschlüssel enthalten.** PostgreSQL prüft die Eindeutigkeit nur innerhalb einer Partition, deshalb lässt sich auf einer partitionierten Tabelle kein `PRIMARY KEY (id)` anlegen – nötig ist `(id, occurred_at)`.
* **Indizes, die auf der Elterntabelle angelegt werden, entstehen automatisch auf allen Partitionen** (PostgreSQL 11+), auch auf zukünftigen.

Die Partitionen werden im Voraus angelegt, zum Beispiel mit einem zeitgesteuerten 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();
```

Jetzt berührt eine Query über die letzten 30 Tage nur eine oder zwei Partitionen. In `EXPLAIN` erkennt man das daran, dass im Plan nur die benötigten Partitionen aufgeführt sind – das ist **Partition Pruning**. Die Bedingung muss direkt auf dem Schlüssel liegen: `WHERE occurred_at >= now() - interval '30 days'` schließt Partitionen aus, `WHERE date(occurred_at) = ...` dagegen nicht, weil die Funktion den Schlüssel vor dem Planer verbirgt.

> [!TIP]
> **BRIN statt B-Tree für Zeitstempel.** In einer Append-only-Tabelle werden Events nahezu chronologisch geschrieben. Ein BRIN-Index auf `occurred_at` belegt Kilobytes statt Gigabytes und eignet sich hervorragend für Bereichsabfragen: `CREATE INDEX ON events USING brin (occurred_at);`

---

<a id="retention"></a>
## Alte Daten in Millisekunden löschen

Der größte praktische Gewinn der Partitionierung liegt in der Aufbewahrungsrichtlinie. Events löschen, die älter als ein Jahr sind:

```sql
-- Ohne Partitionen: stundenlange Laufzeit, aufgeblähte Tabelle, Last auf WAL und Replikas
DELETE FROM events WHERE occurred_at < now() - interval '1 year';

-- Mit Partitionen: eine reine Metadaten-Operation
ALTER TABLE events DETACH PARTITION events_2025_09 CONCURRENTLY; -- PostgreSQL 14+
DROP TABLE events_2025_09;
```

Eine abgehängte Partition muss man nicht sofort löschen: Man kann sie in einen Cold Storage exportieren (`pg_dump -t events_2025_09`) und erst danach entfernen.

> [!WARNING]
> **Vorsicht mit der Default-Partition.** Die `DEFAULT`-Partition nimmt Zeilen auf, die in keinen Bereich fallen. Beim Anlegen einer neuen Partition muss PostgreSQL jedoch prüfen, dass in `DEFAULT` keine Zeilen aus deren Bereich liegen – und das ist ein vollständiger Scan unter Sperre. Zuverlässiger ist es, Partitionen immer im Voraus anzulegen und zu überwachen, dass `DEFAULT` leer ist.

---

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

Eine Materialized View ist das Ergebnis einer Query, das als Tabelle gespeichert wird. Das Dashboard liest das fertige Ergebnis, die Neuberechnung läuft zeitgesteuert:

```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;

-- Pflicht für 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;
}
```

Das Dashboard greift auf das Model zu wie auf eine gewöhnliche Tabelle: `DailyProviderStat::where('day', '>=', now()->subDays(30))->get()`.

* **`CONCURRENTLY`** berechnet die Daten neu, ohne Lesezugriffe zu blockieren. Ohne diese Option wartet das Dashboard, bis die Neuberechnung abgeschlossen ist. `CONCURRENTLY` benötigt einen Unique-Index über alle Zeilen der View.
* **`withoutOverlapping()` und `onOneServer()`** verhindern, dass zwei Neuberechnungen gleichzeitig starten, wenn die vorherige noch nicht fertig ist oder Sie mehrere Server betreiben.

Materialized Views haben einen gravierenden Nachteil: `REFRESH` führt die Query jedes Mal **vollständig** aus. Solange das Sekunden dauert – bestens. Sobald die Neuberechnung über 90 Tage Minuten braucht, ist es Zeit für inkrementelle Aggregation.

---

<a id="rollups"></a>
## Rollup-Tabellen: inkrementelle Voraggregation

Eine Rollup-Tabelle speichert Aggregate nach „Buckets“ (Stunde, Tag) und wird nur für den jüngsten Zeitraum aktualisiert:

```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
{
    // Nur die letzten 2 Stunden neu berechnen: Auch verspätete Events werden berücksichtigt
    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;
}
```

Jeder Lauf liest die Events von zwei Stunden statt von 90 Tagen, daher wachsen die Kosten nicht mit der Historie mit. Tages- und Monatszahlen erhält das Dashboard durch Aufsummieren der Stundenzeilen – das sind Tausende Zeilen statt Millionen. Ein Neuberechnungsfenster von mehr als einer Stunde ist für Events nötig, die verspätet eintreffen.

---

<a id="jsonb"></a>
## JSONB für Anbieterdaten

Anbieter liefern unterschiedliche Felder: der eine `round_id` und `bet_lines`, der andere `session` und `multiplier`. Für jedes Feld jedes Anbieters eine eigene Spalte anzulegen, ist sinnlos – hier ist JSONB angebracht:

```php
// Queries aus 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');
```

Die Indizes werden auf die konkreten Queries zugeschnitten:

```sql
-- Suche nach Enthaltensein (@>): kompakter GIN-Index
CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);

-- Ständiger Filter auf einen Schlüssel: Ausdrucksindex
CREATE INDEX events_game_id_idx ON events ((payload->>'game_id'));
```

Wenn nach einem Schlüssel nicht nur gefiltert, sondern in Reports auch gruppiert wird, lagern Sie ihn in eine generierte Spalte aus (PostgreSQL 12+). Für sie gibt es Statistiken für den Planer und einen gewöhnlichen 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');
});
```

Beachten Sie, dass das Hinzufügen einer `STORED`-Spalte die gesamte Tabelle unter Sperre neu schreibt. Bei einer großen Tabelle macht man das beim Anlegen des Schemas oder in einem Wartungsfenster; im laufenden System behilft man sich mit einem Ausdrucksindex.

> [!NOTE]
> **JSONB ist kein Ersatz für ein Schema.** PostgreSQL sammelt keine Statistiken über Schlüssel innerhalb von JSONB, daher schätzt der Planer die Selektivität solcher Bedingungen schlecht ein. Alles, wonach Sie regelmäßig filtern, gruppieren oder joinen, sollte eine Spalte sein.

---

<a id="architecture"></a>
## Alles zusammengesetzt

```
[ Anbieter / Anwendung ]
            │  INSERT (in Batches)
            ▼
[ events ] — Monatspartitionen, JSONB payload, BRIN auf Zeit
            │  alle 5 Minuten: Rollup über die letzten 2 Stunden
            ▼
[ hourly_provider_stats ] — Tausende Zeilen statt Millionen
            │  REFRESH CONCURRENTLY alle 15 Minuten
            ▼
[ materialized views ] — komplexe Auswertungen für einzelne Widgets
            │
            ▼
[ Dashboard ] — liest nur Aggregate (am besten von einer Replika)
```

Die rohen Events bleiben für detaillierte Reports und Analysen erhalten, das Dashboard rührt sie aber nicht an. Alte Partitionen wandern zeitgesteuert ins Archiv, und die Größe der „heißen“ Daten bleibt konstant.

---

<a id="limitations"></a>
## Wo der Ansatz an seine Grenzen stößt

* **Zu viele Partitionen.** Jede Partition verlängert die Planungszeit. Tausende kleiner Partitionen (etwa tageweise über mehrere Jahre) verlangsamen selbst einfache Queries. Für die meisten Dashboards reichen Monatspartitionen.
* **Eindeutigkeit über Partitionen hinweg.** Unique-Constraints müssen den Partitionierungsschlüssel enthalten. Globale Eindeutigkeit, etwa für `external_id`, muss über eine separate Tabelle oder auf Anwendungsebene sichergestellt werden.
* **Indizes auf partitionierten Tabellen lassen sich auf der Elterntabelle nicht mit `CONCURRENTLY` bauen.** Man muss den Index auf jeder Partition einzeln anlegen und die Indizes anschließend per `ALTER INDEX ... ATTACH PARTITION` anhängen.
* **Datenverzögerung.** Voraggregate hinken immer etwas hinterher. Braucht das Business sekundengenaue Zahlen, zeigen Sie sie aus dem Rollup plus einem „Tail“ aus den Rohdaten der letzten Minuten an.
* **Analytics-Skalierung.** Bei Milliarden Zeilen mit beliebigen Auswertungen sind spaltenorientierte Datenbanken (ClickHouse) um Größenordnungen schneller. PostgreSQL bleibt die Source of Truth, und die Analytics zieht dorthin um, wo sie hingehört – siehe [Event-Ingestion unter hoher Last](high-load-event-ingestion).

---

<a id="common-mistakes"></a>
## Häufige Fehler

**1. Eine Funktion auf dem Partitionierungsschlüssel in `WHERE`.**
`WHERE date(occurred_at) = '2026-09-01'` schaltet Partition Pruning ab. Schreiben Sie einen Bereich: `occurred_at >= '2026-09-01' AND occurred_at < '2026-09-02'`.

**2. `DELETE` zum Bereinigen der Historie.**
Stundenlange Laufzeit und eine aufgeblähte Tabelle. Löschen Sie ganze Partitionen per `DETACH` und `DROP`.

**3. Partitionen werden erst „bei Bedarf“ angelegt.**
Zu Monatsbeginn schlagen Inserts fehl oder landen in `DEFAULT`. Legen Sie Partitionen zeitgesteuert mehrere Monate im Voraus an.

**4. `REFRESH MATERIALIZED VIEW` ohne `CONCURRENTLY`.**
Das Dashboard ist für die Dauer der Neuberechnung blockiert. Fügen Sie einen Unique-Index hinzu und verwenden Sie `CONCURRENTLY`.

**5. Neuberechnung der gesamten Historie bei jedem Lauf.**
Eine Materialized View über 3 Jahre braucht zum Neuberechnen länger als das Aktualisierungsintervall. Wechseln Sie auf Rollup-Tabellen mit inkrementellem Upsert.

**6. Alles in JSONB.**
Filter und Gruppierungen nach JSONB-Schlüsseln ohne Statistiken führen zu schlechten Plänen. Häufig genutzte Schlüssel gehören in Spalten.

**7. Dashboard auf der primären Datenbank.**
Schwere Aggregationen konkurrieren mit den Transaktionen der Nutzer. Lesen Sie Aggregate von einer Replika.

---

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

1. Große Event-Tabellen sind nach Zeit partitioniert; der Primärschlüssel enthält den Partitionierungsschlüssel.
2. Partitionen werden im Voraus per zeitgesteuertem Command angelegt; die `DEFAULT`-Partition ist leer oder existiert nicht.
3. Queries filtern ohne Funktionen auf den Partitionierungsschlüssel; `EXPLAIN` zeigt Pruning.
4. Alte Daten werden per `DETACH` und `DROP PARTITION` gelöscht, bei Bedarf mit Archivierung.
5. Das Dashboard liest nur Aggregate: Rollup-Tabellen mit inkrementellem Upsert oder Materialized Views.
6. `REFRESH MATERIALIZED VIEW CONCURRENTLY` mit Unique-Index, `withoutOverlapping()` und `onOneServer()`.
7. Für JSONB: GIN mit `jsonb_path_ops` für `@>` und Ausdrucksindizes für häufig genutzte Schlüssel; Schlüssel für Gruppierungen sind in Spalten ausgelagert.
8. Schwere Dashboard-Queries laufen gegen eine Replika.

---

## Zusammenfassung

Ein langsames Dashboard lässt sich fast nie mit noch einem Index kurieren. Es wird dadurch kuriert, dass die Daten vorab in die Form gebracht werden, in der sie gelesen werden: Partitionen schneiden irrelevante Zeiträume ab und machen das Löschen der Historie kostenlos, Voraggregate verwandeln Millionen Zeilen in Tausende, und JSONB bietet Flexibilität dort, wo sich das Schema tatsächlich von Anbieter zu Anbieter unterscheidet.

---

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

### Frage 1: Warum lässt sich auf einer nach `occurred_at` partitionierten Tabelle kein `PRIMARY KEY (id)` anlegen?
- A) PostgreSQL unterstützt keine Primärschlüssel auf partitionierten Tabellen.
- B) Die Eindeutigkeit wird innerhalb jeder Partition geprüft, deshalb muss der Partitionierungsschlüssel Teil jeder Unique-Constraint sein.
- C) Die Spalte `id` muss vom Typ `uuid` sein.

<details>
<summary><b>Antworten anzeigen</b></summary>

**Antwort: B**
Jede Partition hat ihren eigenen Index, und PostgreSQL kann die Eindeutigkeit nicht partitionsübergreifend prüfen. Die Aufnahme von `occurred_at` garantiert, dass gleiche Schlüsselwerte immer in derselben Partition landen, wo die Eindeutigkeit lokal geprüft wird.
</details>

### Frage 2: Was wird für `REFRESH MATERIALIZED VIEW CONCURRENTLY` benötigt?
- A) Ein Unique-Index auf der Materialized View, der alle Zeilen abdeckt.
- B) Eine Partitionierung der Quelltabelle.
- C) Die Ausführung des Befehls als Superuser.

<details>
<summary><b>Antworten anzeigen</b></summary>

**Antwort: A**
Im Modus `CONCURRENTLY` vergleicht PostgreSQL die alte und die neue Version zeilenweise, und der Unique-Index wird benötigt, um die Zeilen einander zuzuordnen. Ohne ihn steht nur das gewöhnliche `REFRESH` zur Verfügung, das Lesezugriffe blockiert.
</details>

### Frage 3: Welche Bedingung ermöglicht es PostgreSQL, irrelevante Partitionen auszuschließen?
- 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><b>Antworten anzeigen</b></summary>

**Antwort: B**
Partition Pruning funktioniert, wenn die Bedingung direkt auf dem Partitionierungsschlüssel liegt. Die Funktionen `date()` und `extract()` verbergen den Schlüssel vor dem Planer, sodass er alle Partitionen durchsuchen muss.
</details>