---
title: 'PostgreSQL pour les dashboards : partitionnement, vues matérialisées et JSONB | DevSense'
description: 'Comment construire des dashboards rapides sur PostgreSQL : partitionnement par date, suppression instantanée des anciennes données, vues matérialisées et tables de rollup, JSONB avec les bons index dans Laravel.'
faq:
    - { question: 'Quand faut-il partitionner une table par date ?', answer: "Quand elle contient des centaines de millions de lignes, que les requêtes filtrent presque toujours sur le temps et que les anciennes données doivent être supprimées régulièrement. Le partitionnement permet d'écarter les partitions inutiles dès la planification de la requête (partition pruning) et de supprimer un mois entier de données avec DROP ou DETACH PARTITION en quelques millisecondes, au lieu d'un long DELETE suivi d'un VACUUM." }
    - { question: "Qu'apporte REFRESH MATERIALIZED VIEW CONCURRENTLY ?", answer: "Un REFRESH classique prend un verrou exclusif, et le dashboard ne peut pas lire la vue pendant son recalcul. CONCURRENTLY construit la nouvelle version à côté et applique la différence, sans bloquer la lecture. Pour cela, la vue matérialisée doit disposer d'un index unique couvrant toutes les lignes." }
    - { question: 'Quel index faut-il pour les requêtes sur du JSONB ?', answer: "Pour la recherche par inclusion (opérateur @>), un index GIN avec la classe d'opérateurs jsonb_path_ops convient : il est plus compact que le jsonb_ops standard. Si vous filtrez en permanence sur une seule clé, un index sur expression (payload->>'game_id') ou une colonne générée avec un index B-tree classique sera plus efficace." }
    - { question: "Quand PostgreSQL cesse-t-il de convenir à l'analytique ?", answer: "Quand les volumes se comptent en milliards de lignes et que les requêtes agrègent en temps réel de larges plages selon de nombreuses dimensions. Le stockage orienté lignes et le MVCC rendent ces parcours coûteux, et les bases orientées colonnes comme ClickHouse l'emportent de plusieurs ordres de grandeur. D'ici là, le partitionnement et la pré-agrégation couvrent généralement les besoins des dashboards." }
published: '2026-09-28'
---
# PostgreSQL pour les dashboards : partitionnement, vues matérialisées et JSONB

Le dashboard de statistiques met 40 secondes à s'ouvrir. La table `events` a atteint 400 millions de lignes, il existe un index sur `occurred_at`, `EXPLAIN` affiche un Index Scan, et pourtant la requête « chiffre d'affaires par fournisseur sur 30 jours » lit toujours des dizaines de millions de lignes. Le `DELETE` nocturne des anciens événements dure trois heures et laisse la table gonflée, tandis que l'autovacuum n'arrive pas à suivre. De nouveaux index n'y changeront rien : le problème n'est pas de trouver les lignes, mais le fait que le dashboard recalcule à chaque fois ce qui aurait pu être calculé une seule fois.

**Voir aussi :** [Optimisation des requêtes : EXPLAIN et partitionnement](database-query-optimization) · [Index de bases de données](database-indexes-deep-dive) · [Ingestion d'événements à forte charge](high-load-event-ingestion)

## Sommaire

* [Pourquoi une table transactionnelle est une mauvaise source pour un dashboard](#oltp-vs-dashboards)
* [Partitionnement par le temps](#partitioning)
* [Supprimer les anciennes données en quelques millisecondes](#retention)
* [Vues matérialisées](#materialized-views)
* [Tables de rollup : pré-agrégation incrémentale](#rollups)
* [JSONB pour les données des fournisseurs](#jsonb)
* [Assembler le tout](#architecture)
* [Les limites de l'approche](#limitations)
* [Erreurs fréquentes](#common-mistakes)
* [Checklist](#checklist)
* [Quiz d'auto-évaluation](#self-test-quiz)

---

<a id="oltp-vs-dashboards"></a>
## Pourquoi une table transactionnelle est une mauvaise source pour un dashboard

Une table transactionnelle est optimisée pour des opérations courtes : insérer un événement, trouver une commande par son ID, mettre à jour un statut. Un dashboard fait l'inverse : il lit d'énormes plages et les réduit à quelques chiffres. Quand les deux types de charge portent sur la même table :

* **Chaque affichage du dashboard répète le même travail.** Dix managers qui ouvrent la page, ce sont dix agrégations complètes sur un mois.
* **Un index ne protège pas du volume.** Même un index parfait sur la date trouve 30 millions de lignes pour un mois, et il faut malgré tout les lire et les additionner.
* **Supprimer les anciennes données coûte cher.** `DELETE` marque les lignes comme mortes, et la table gonfle jusqu'au prochain `VACUUM`.

**Un dashboard rapide sur PostgreSQL ne repose pas sur les index, mais sur la forme des données : les événements bruts sont partitionnés par le temps, les agrégats sont calculés à l'avance et mis à jour de façon incrémentale, et les attributs variables des fournisseurs sont stockés en JSONB, avec des index uniquement là où l'on filtre dessus.**

---

<a id="partitioning"></a>
## Partitionnement par le temps

Le partitionnement déclaratif (PostgreSQL 10+) divise une table logique en partitions physiques selon une plage de valeurs de la clé. Pour des événements, la clé est presque toujours le temps :

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

Ce qui compte ici :

* **La clé primaire doit inclure la clé de partitionnement.** PostgreSQL ne vérifie l'unicité qu'à l'intérieur d'une partition : il est donc impossible de créer `PRIMARY KEY (id)` sur une table partitionnée, il faut `(id, occurred_at)`.
* **Les index créés sur la table parente sont automatiquement créés sur toutes les partitions** (PostgreSQL 11+), y compris les futures.

Les partitions sont créées à l'avance, par exemple via une commande planifiée :

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

Désormais, une requête sur les 30 derniers jours ne touche qu'une ou deux partitions. Dans `EXPLAIN`, on le voit au fait que seules les partitions nécessaires figurent dans le plan : c'est le **partition pruning**. La condition doit porter sur la clé elle-même : `WHERE occurred_at >= now() - interval '30 days'` élague les partitions, alors que `WHERE date(occurred_at) = ...` ne le fait pas, car la fonction masque la clé au planificateur.

> [!TIP]
> **BRIN plutôt que B-tree pour le temps.** Dans une table append-only, les événements sont écrits dans un ordre quasi chronologique. Un index BRIN sur `occurred_at` occupe quelques kilo-octets au lieu de plusieurs giga-octets et convient parfaitement aux requêtes par plage : `CREATE INDEX ON events USING brin (occurred_at);`

---

<a id="retention"></a>
## Supprimer les anciennes données en quelques millisecondes

Le principal gain pratique du partitionnement se situe dans la politique de rétention. Supprimer les événements de plus d'un an :

```sql
-- Sans partitions : des heures de travail, gonflement de la table, charge sur le WAL et les réplicas
DELETE FROM events WHERE occurred_at < now() - interval '1 year';

-- Avec partitions : une opération sur les métadonnées
ALTER TABLE events DETACH PARTITION events_2025_09 CONCURRENTLY; -- PostgreSQL 14+
DROP TABLE events_2025_09;
```

Plutôt que de supprimer directement une partition détachée, on peut l'exporter vers un stockage froid (`pg_dump -t events_2025_09`) et ne la supprimer qu'ensuite.

> [!WARNING]
> **Attention à la partition par défaut.** La partition `DEFAULT` accueille les lignes qui ne tombent dans aucune plage. Mais lors de la création d'une nouvelle partition, PostgreSQL doit vérifier que `DEFAULT` ne contient aucune ligne de sa plage, ce qui implique un parcours complet sous verrou. Il est plus sûr de toujours créer les partitions à l'avance et de surveiller que `DEFAULT` reste vide.

---

<a id="materialized-views"></a>
## Vues matérialisées

Une vue matérialisée (materialized view) est le résultat d'une requête stocké sous forme de table. Le dashboard lit un résultat tout prêt, et le recalcul est planifié :

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

-- Obligatoire pour 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;
}
```

Le dashboard interroge le modèle comme une table ordinaire : `DailyProviderStat::where('day', '>=', now()->subDays(30))->get()`.

* **`CONCURRENTLY`** recalcule les données sans bloquer la lecture. Sans lui, le dashboard attendra la fin du recalcul. `CONCURRENTLY` exige un index unique sur toutes les lignes de la vue.
* **`withoutOverlapping()` et `onOneServer()`** empêchent de lancer deux recalculs simultanés, si le précédent n'est pas terminé ou si vous avez plusieurs serveurs.

Les vues matérialisées ont un inconvénient sérieux : `REFRESH` exécute à chaque fois la requête **dans son intégralité**. Tant que cela prend quelques secondes, c'est parfait. Quand le recalcul de 90 jours commence à prendre des minutes, il est temps de passer à l'agrégation incrémentale.

---

<a id="rollups"></a>
## Tables de rollup : pré-agrégation incrémentale

Une table de rollup stocke des agrégats par « tranches » (heure, jour) et n'est mise à jour que pour la période la plus récente :

```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
{
    // On ne recalcule que les 2 dernières heures : les événements en retard seront aussi pris en compte
    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;
}
```

Chaque exécution lit les événements des deux dernières heures, et non des 90 derniers jours : le coût ne croît donc pas avec l'historique. Le dashboard obtient les chiffres journaliers et mensuels en additionnant les lignes horaires, soit des milliers de lignes au lieu de millions. Une fenêtre de recalcul supérieure à une heure est nécessaire pour les événements qui arrivent en retard.

---

<a id="jsonb"></a>
## JSONB pour les données des fournisseurs

Les fournisseurs envoient des champs différents : l'un a `round_id` et `bet_lines`, l'autre `session` et `multiplier`. Créer une colonne pour chaque champ de chaque fournisseur n'a aucun sens, et c'est là que JSONB est pertinent :

```php
// Requêtes depuis 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');
```

Les index se choisissent en fonction des requêtes concrètes :

```sql
-- Recherche par inclusion (@>) : GIN compact
CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);

-- Filtre récurrent sur une seule clé : index sur expression
CREATE INDEX events_game_id_idx ON events ((payload->>'game_id'));
```

Si une clé sert non seulement à filtrer, mais aussi à grouper dans les rapports, extrayez-la dans une colonne générée (PostgreSQL 12+). Elle dispose de statistiques pour le planificateur et d'un index B-tree classique :

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

Gardez à l'esprit que l'ajout d'une colonne `STORED` réécrit toute la table sous verrou. Sur une grande table, on le fait lors de la création du schéma ou pendant une fenêtre de maintenance ; sur un système en production, on se contente d'un index sur expression.

> [!NOTE]
> **JSONB ne remplace pas un schéma.** PostgreSQL ne collecte pas de statistiques sur les clés à l'intérieur du JSONB : le planificateur estime donc mal la sélectivité de ces conditions. Tout ce sur quoi vous filtrez, groupez ou faites des jointures régulièrement doit être une colonne.

---

<a id="architecture"></a>
## Assembler le tout

```
[ Fournisseurs / application ]
            │  INSERT (par lots)
            ▼
[ events ] — partitions mensuelles, payload JSONB, BRIN sur le temps
            │  toutes les 5 minutes : rollup des 2 dernières heures
            ▼
[ hourly_provider_stats ] — des milliers de lignes au lieu de millions
            │  REFRESH CONCURRENTLY toutes les 15 minutes
            ▼
[ vues matérialisées ] — coupes complexes pour certains widgets
            │
            ▼
[ Dashboard ] — ne lit que des agrégats (idéalement depuis un réplica)
```

Les événements bruts restent disponibles pour les rapports détaillés et les investigations, mais le dashboard n'y touche pas. Les anciennes partitions partent en archive selon un planning, et le volume des données « chaudes » reste constant.

---

<a id="limitations"></a>
## Les limites de l'approche

* **Trop de partitions.** Chaque partition allonge le temps de planification. Des milliers de petites partitions (par exemple, journalières sur plusieurs années) ralentiront même les requêtes simples. Pour la plupart des dashboards, des partitions mensuelles suffisent.
* **L'unicité à travers les partitions.** Les contraintes d'unicité doivent inclure la clé de partitionnement. Une unicité globale, par exemple sur `external_id`, devra être assurée par une table séparée ou au niveau de l'application.
* **Sur une table partitionnée, on ne peut pas construire d'index en `CONCURRENTLY` sur la table parente.** Il faut créer l'index sur chaque partition séparément, puis les rattacher via `ALTER INDEX ... ATTACH PARTITION`.
* **Le décalage des données.** Les pré-agrégats sont toujours légèrement en retard. Si le métier a besoin de chiffres à la seconde près, affichez-les à partir du rollup plus une « traîne » de données brutes des dernières minutes.
* **L'échelle de l'analytique.** Sur des milliards de lignes avec des coupes arbitraires, les bases orientées colonnes (ClickHouse) l'emportent de plusieurs ordres de grandeur. PostgreSQL reste la source de vérité, et l'analytique part là où est sa place : voir [ingestion d'événements à forte charge](high-load-event-ingestion).

---

<a id="common-mistakes"></a>
## Erreurs fréquentes

**1. Une fonction appliquée à la clé de partitionnement dans le `WHERE`.**
`WHERE date(occurred_at) = '2026-09-01'` désactive le partition pruning. Écrivez une plage : `occurred_at >= '2026-09-01' AND occurred_at < '2026-09-02'`.

**2. `DELETE` pour purger l'historique.**
Des heures de travail et une table gonflée. Supprimez des partitions entières via `DETACH` et `DROP`.

**3. Des partitions créées « au fil de l'eau ».**
En début de mois, les insertions échouent ou atterrissent dans `DEFAULT`. Créez les partitions plusieurs mois à l'avance via une tâche planifiée.

**4. `REFRESH MATERIALIZED VIEW` sans `CONCURRENTLY`.**
Le dashboard est bloqué pendant le recalcul. Ajoutez un index unique et utilisez `CONCURRENTLY`.

**5. Recalculer tout l'historique à chaque exécution.**
Une vue matérialisée couvrant 3 ans met plus de temps à se recalculer que l'intervalle de rafraîchissement. Passez à des tables de rollup avec upsert incrémental.

**6. Tout en JSONB.**
Les filtres et regroupements sur des clés JSONB sans statistiques produisent de mauvais plans. Extrayez les clés fréquentes dans des colonnes.

**7. Le dashboard sur la base principale.**
Les agrégations lourdes entrent en concurrence avec les transactions des utilisateurs. Lisez les agrégats depuis un réplica.

---

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

1. Les grandes tables d'événements sont partitionnées par le temps ; la clé primaire inclut la clé de partitionnement.
2. Les partitions sont créées à l'avance par une commande planifiée ; la partition `DEFAULT` est vide ou absente.
3. Les requêtes filtrent sur la clé de partitionnement sans fonctions ; `EXPLAIN` montre le pruning.
4. Les anciennes données sont supprimées via `DETACH` et `DROP PARTITION`, avec archivage si nécessaire.
5. Le dashboard ne lit que des agrégats : tables de rollup avec upsert incrémental ou vues matérialisées.
6. `REFRESH MATERIALIZED VIEW CONCURRENTLY` avec un index unique, `withoutOverlapping()` et `onOneServer()`.
7. Pour le JSONB : GIN `jsonb_path_ops` pour `@>` et index sur expression pour les clés fréquentes ; les clés de regroupement sont extraites dans des colonnes.
8. Les requêtes lourdes du dashboard partent vers un réplica.

---

## Conclusion

Un dashboard lent ne se soigne presque jamais avec un index de plus. Il se soigne en donnant à l'avance aux données la forme sous laquelle on les lit : les partitions écartent les périodes inutiles et rendent la suppression de l'historique gratuite, les pré-agrégats transforment des millions de lignes en milliers, et JSONB apporte de la souplesse là où le schéma varie réellement d'un fournisseur à l'autre.

---

<a id="self-test-quiz"></a>
## Quiz d'auto-évaluation

### Question 1 : Pourquoi ne peut-on pas créer `PRIMARY KEY (id)` sur une table partitionnée par `occurred_at` ?
- A) PostgreSQL ne prend pas en charge les clés primaires sur les tables partitionnées.
- B) L'unicité est vérifiée à l'intérieur de chaque partition : la clé de partitionnement doit donc faire partie de toute contrainte d'unicité.
- C) La colonne `id` doit être de type `uuid`.

<details>
<summary><b>Afficher la réponse</b></summary>

**Réponse : B**
Chaque partition possède son propre index, et PostgreSQL ne sait pas vérifier l'unicité entre elles. Inclure `occurred_at` garantit que des valeurs de clé identiques tombent toujours dans la même partition, où l'unicité est vérifiée localement.
</details>

### Question 2 : Que faut-il pour `REFRESH MATERIALIZED VIEW CONCURRENTLY` ?
- A) Un index unique sur la vue matérialisée, couvrant toutes les lignes.
- B) Le partitionnement de la table source.
- C) L'exécution de la commande en tant que superutilisateur.

<details>
<summary><b>Afficher la réponse</b></summary>

**Réponse : A**
En mode `CONCURRENTLY`, PostgreSQL compare l'ancienne et la nouvelle version ligne par ligne, et l'index unique est nécessaire pour faire correspondre les lignes. Sans lui, seul le `REFRESH` classique est disponible, et celui-ci bloque la lecture.
</details>

### Question 3 : Quelle condition permet à PostgreSQL d'écarter les partitions inutiles ?
- 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>Afficher la réponse</b></summary>

**Réponse : B**
Le partition pruning fonctionne lorsque la condition porte directement sur la clé de partitionnement. Les fonctions `date()` et `extract()` masquent la clé au planificateur, qui est alors contraint de parcourir toutes les partitions.
</details>