---
title: 'PostgreSQL per le dashboard: partizionamento, viste materializzate e JSONB | DevSense'
description: 'Come realizzare dashboard veloci su PostgreSQL: partizionamento per data, eliminazione istantanea dei dati vecchi, viste materializzate e tabelle di rollup, JSONB con gli indici giusti in Laravel.'
faq:
    - { question: 'Quando conviene partizionare una tabella per data?', answer: 'Quando contiene centinaia di milioni di righe, le query filtrano quasi sempre per tempo e i dati vecchi vanno eliminati regolarmente. Il partizionamento consente di escludere le partizioni superflue in fase di pianificazione della query (partition pruning) e permette di eliminare un intero mese di dati con DROP o DETACH PARTITION in pochi millisecondi, invece di un lungo DELETE seguito da VACUUM.' }
    - { question: 'Che vantaggio offre REFRESH MATERIALIZED VIEW CONCURRENTLY?', answer: 'Un normale REFRESH acquisisce un lock esclusivo e la dashboard non può leggere la vista mentre viene ricalcolata. CONCURRENTLY costruisce la nuova versione a parte e applica le differenze senza bloccare le letture. Per farlo, la vista materializzata deve avere un indice univoco che copra tutte le righe.' }
    - { question: 'Quale indice serve per le query su JSONB?', answer: "Per la ricerca per contenimento (operatore @>) è adatto un indice GIN con la classe di operatori jsonb_path_ops: è più compatto del normale jsonb_ops. Se filtrate costantemente per una sola chiave, è più efficiente un indice su espressione (payload->>'game_id') oppure una colonna generata con un normale indice B-tree." }
    - { question: "Quando PostgreSQL smette di essere adatto all'analisi dei dati?", answer: "Quando i volumi si misurano in miliardi di righe e le query aggregano ampi intervalli su molte dimensioni in tempo reale. L'archiviazione per righe e MVCC rendono costose queste scansioni, e i database colonnari come ClickHouse vincono di ordini di grandezza. Fino a quel momento, partizionamento e preaggregazione di solito coprono le esigenze delle dashboard." }
published: '2026-09-28'
---
# PostgreSQL per le dashboard: partizionamento, viste materializzate e JSONB

La dashboard delle statistiche si apre in 40 secondi. La tabella `events` è cresciuta fino a 400 milioni di righe, c'è un indice su `occurred_at`, `EXPLAIN` mostra un Index Scan, eppure la query «ricavi per provider negli ultimi 30 giorni» legge comunque decine di milioni di righe. Il `DELETE` notturno degli eventi vecchi dura tre ore e lascia la tabella gonfia, mentre l'autovacuum non riesce a tenere il passo. Nuovi indici qui non servono: il problema non è trovare le righe, ma il fatto che la dashboard ricalcola ogni volta ciò che si sarebbe potuto calcolare una volta sola.

**Materiali correlati:** [Ottimizzazione delle query: EXPLAIN e partizionamento](database-query-optimization) · [Indici dei database](database-indexes-deep-dive) · [Ingestion di eventi ad alto carico](high-load-event-ingestion)

## Indice

* [Perché una tabella transazionale è una cattiva sorgente per una dashboard](#oltp-vs-dashboards)
* [Partizionamento per tempo](#partitioning)
* [Eliminare i dati vecchi in millisecondi](#retention)
* [Viste materializzate](#materialized-views)
* [Tabelle di rollup: preaggregazione incrementale](#rollups)
* [JSONB per i dati dei provider](#jsonb)
* [Mettiamo tutto insieme](#architecture)
* [Dove l'approccio smette di funzionare](#limitations)
* [Errori comuni](#common-mistakes)
* [Checklist](#checklist)
* [Quiz di autoverifica](#self-test-quiz)

---

<a id="oltp-vs-dashboards"></a>
## Perché una tabella transazionale è una cattiva sorgente per una dashboard

Una tabella transazionale è ottimizzata per operazioni brevi: inserire un evento, trovare un ordine per ID, aggiornare uno stato. Una dashboard fa l'opposto: legge intervalli enormi e li riduce a pochi numeri. Quando entrambi i tipi di carico colpiscono la stessa tabella:

* **Ogni visualizzazione della dashboard ripete lo stesso lavoro.** Dieci manager che aprono la pagina significano dieci aggregazioni complete su un mese.
* **L'indice non salva dal volume.** Anche un indice perfetto sulla data trova 30 milioni di righe per un mese, e vanno comunque lette e sommate.
* **Eliminare i dati vecchi è costoso.** `DELETE` marca le righe come morte e la tabella si gonfia fino al `VACUUM` successivo.

**Una dashboard veloce su PostgreSQL non si costruisce sugli indici, ma sulla forma dei dati: gli eventi grezzi sono partizionati per tempo, gli aggregati sono calcolati in anticipo e aggiornati in modo incrementale, e gli attributi variabili dei provider sono conservati in JSONB, con indici solo dove si filtra su di essi.**

---

<a id="partitioning"></a>
## Partizionamento per tempo

Il partizionamento dichiarativo (PostgreSQL 10+) suddivide una tabella logica in partizioni fisiche in base a un intervallo della chiave. Per gli eventi la chiave è quasi sempre il tempo:

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

Cosa è importante qui:

* **La chiave primaria deve includere la chiave di partizionamento.** PostgreSQL verifica l'unicità solo all'interno di una partizione, quindi non si può creare `PRIMARY KEY (id)` su una tabella partizionata: serve `(id, occurred_at)`.
* **Gli indici creati sulla tabella padre vengono creati automaticamente su tutte le partizioni** (PostgreSQL 11+), comprese quelle future.

Le partizioni si creano in anticipo, per esempio con un comando pianificato:

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

Ora una query sugli ultimi 30 giorni tocca una o due partizioni. In `EXPLAIN` lo si vede dal fatto che nel piano compaiono solo le partizioni necessarie: questo è il **partition pruning**. La condizione deve riguardare la chiave stessa: `WHERE occurred_at >= now() - interval '30 days'` esclude le partizioni, mentre `WHERE date(occurred_at) = ...` no, perché la funzione nasconde la chiave al planner.

> [!TIP]
> **BRIN invece di B-tree per il tempo.** In una tabella append-only gli eventi vengono scritti in ordine quasi cronologico. Un indice BRIN su `occurred_at` occupa kilobyte invece di gigabyte ed è ideale per le query su intervalli: `CREATE INDEX ON events USING brin (occurred_at);`

---

<a id="retention"></a>
## Eliminare i dati vecchi in millisecondi

Il principale vantaggio pratico del partizionamento riguarda la politica di conservazione. Eliminare gli eventi più vecchi di un anno:

```sql
-- Senza partizioni: ore di lavoro, tabella gonfia, carico sul WAL e sulle repliche
DELETE FROM events WHERE occurred_at < now() - interval '1 year';

-- Con le partizioni: un'operazione sui metadati
ALTER TABLE events DETACH PARTITION events_2025_09 CONCURRENTLY; -- PostgreSQL 14+
DROP TABLE events_2025_09;
```

Una partizione staccata non va necessariamente eliminata: la si può esportare in uno storage freddo (`pg_dump -t events_2025_09`) e solo dopo eliminarla.

> [!WARNING]
> **Attenzione alla partizione di default.** La partizione `DEFAULT` accoglie le righe che non rientrano in nessun intervallo. Ma quando si crea una nuova partizione, PostgreSQL deve verificare che in `DEFAULT` non ci siano righe del suo intervallo, e questo comporta una scansione completa sotto lock. È più affidabile creare sempre le partizioni in anticipo e monitorare che `DEFAULT` sia vuota.

---

<a id="materialized-views"></a>
## Viste materializzate

Una vista materializzata (materialized view) è il risultato di una query salvato come tabella. La dashboard legge il risultato già pronto, mentre il ricalcolo avviene secondo una pianificazione:

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

-- Obbligatorio per 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;
}
```

La dashboard accede al modello come a una normale tabella: `DailyProviderStat::where('day', '>=', now()->subDays(30))->get()`.

* **`CONCURRENTLY`** ricalcola i dati senza bloccare le letture. Senza di esso la dashboard dovrà attendere la fine del ricalcolo. Per `CONCURRENTLY` serve un indice univoco su tutte le righe della vista.
* **`withoutOverlapping()` e `onOneServer()`** impediscono di avviare due ricalcoli contemporaneamente, se il precedente non è ancora terminato o se avete più server.

Le viste materializzate hanno un grosso svantaggio: `REFRESH` esegue ogni volta la query **per intero**. Finché si tratta di secondi, va benissimo. Quando il ricalcolo di 90 giorni inizia a richiedere minuti, è il momento di passare all'aggregazione incrementale.

---

<a id="rollups"></a>
## Tabelle di rollup: preaggregazione incrementale

Una tabella di rollup conserva gli aggregati per «bucket» (ora, giorno) e viene aggiornata solo per il periodo più recente:

```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
{
    // Ricalcoliamo solo le ultime 2 ore: verranno conteggiati anche gli eventi arrivati in ritardo
    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;
}
```

Ogni esecuzione legge gli eventi di due ore, non di 90 giorni, quindi il costo non cresce insieme allo storico. La dashboard ottiene i dati giornalieri e mensili sommando le righe orarie: migliaia di righe invece di milioni. Una finestra di ricalcolo superiore a un'ora serve per gli eventi che arrivano in ritardo.

---

<a id="jsonb"></a>
## JSONB per i dati dei provider

I provider inviano campi diversi: uno ha `round_id` e `bet_lines`, un altro `session` e `multiplier`. Creare una colonna per ogni campo di ogni provider non ha senso, ed è qui che JSONB è appropriato:

```php
// Query da 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');
```

Gli indici si scelgono in base alle query concrete:

```sql
-- Ricerca per contenimento (@>): GIN compatto
CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);

-- Filtro costante su una sola chiave: indice su espressione
CREATE INDEX events_game_id_idx ON events ((payload->>'game_id'));
```

Se su una chiave non solo si filtra ma si raggruppa anche nei report, spostatela in una colonna generata (PostgreSQL 12+). Questa dispone di statistiche per il planner e di un normale indice B-tree:

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

Tenete presente che l'aggiunta di una colonna `STORED` riscrive l'intera tabella sotto lock. Su una tabella grande lo si fa al momento della creazione dello schema o durante una finestra di manutenzione, mentre in un sistema in produzione ci si accontenta di un indice su espressione.

> [!NOTE]
> **JSONB non sostituisce lo schema.** PostgreSQL non raccoglie statistiche sulle chiavi all'interno di JSONB, quindi il planner stima male la selettività di queste condizioni. Tutto ciò su cui filtrate, raggruppate o fate join regolarmente deve essere una colonna.

---

<a id="architecture"></a>
## Mettiamo tutto insieme

```
[ Provider / applicazione ]
            │  INSERT (a batch)
            ▼
[ events ] — partizioni mensili, payload JSONB, BRIN sul tempo
            │  ogni 5 minuti: rollup delle ultime 2 ore
            ▼
[ hourly_provider_stats ] — migliaia di righe invece di milioni
            │  REFRESH CONCURRENTLY ogni 15 minuti
            ▼
[ materialized views ] — viste complesse per singoli widget
            │
            ▼
[ Dashboard ] — legge solo gli aggregati (meglio da una replica)
```

Gli eventi grezzi restano disponibili per i report dettagliati e le indagini, ma la dashboard non li tocca. Le partizioni vecchie vanno in archivio secondo una pianificazione e la dimensione dei dati «caldi» resta costante.

---

<a id="limitations"></a>
## Dove l'approccio smette di funzionare

* **Troppe partizioni.** Ogni partizione aumenta il tempo di pianificazione. Migliaia di piccole partizioni (per esempio giornaliere per diversi anni) rallenteranno anche le query semplici. Per la maggior parte delle dashboard bastano partizioni mensili.
* **Unicità tra partizioni.** I vincoli di unicità devono includere la chiave di partizionamento. L'unicità globale, per esempio di `external_id`, andrà garantita con una tabella separata o a livello applicativo.
* **Sulle tabelle partizionate non si possono costruire indici `CONCURRENTLY` sulla tabella padre.** Bisogna creare l'indice su ogni partizione separatamente e poi collegarli tramite `ALTER INDEX ... ATTACH PARTITION`.
* **Ritardo dei dati.** I preaggregati sono sempre leggermente indietro. Se il business ha bisogno di numeri precisi al secondo, mostrateli dal rollup più una «coda» di dati grezzi degli ultimi minuti.
* **Scala dell'analisi.** Su miliardi di righe con viste arbitrarie, i database colonnari (ClickHouse) vincono di ordini di grandezza. PostgreSQL resta la fonte di verità, mentre l'analisi si sposta dove deve stare: vedete [ingestion di eventi ad alto carico](high-load-event-ingestion).

---

<a id="common-mistakes"></a>
## Errori comuni

**1. Una funzione sulla chiave di partizionamento nel `WHERE`.**
`WHERE date(occurred_at) = '2026-09-01'` disattiva il partition pruning. Scrivete un intervallo: `occurred_at >= '2026-09-01' AND occurred_at < '2026-09-02'`.

**2. `DELETE` per ripulire lo storico.**
Ore di lavoro e una tabella gonfia. Eliminate intere partizioni tramite `DETACH` e `DROP`.

**3. Partizioni create «al bisogno».**
All'inizio del mese gli insert falliscono o finiscono in `DEFAULT`. Create le partizioni con qualche mese di anticipo tramite una pianificazione.

**4. `REFRESH MATERIALIZED VIEW` senza `CONCURRENTLY`.**
La dashboard resta bloccata per tutta la durata del ricalcolo. Aggiungete un indice univoco e usate `CONCURRENTLY`.

**5. Ricalcolo dell'intero storico a ogni esecuzione.**
Una vista materializzata su 3 anni impiega a ricalcolarsi più dell'intervallo di aggiornamento. Passate alle tabelle di rollup con upsert incrementale.

**6. Tutto in JSONB.**
Filtri e raggruppamenti su chiavi JSONB prive di statistiche producono piani scadenti. Spostate le chiavi usate spesso in colonne.

**7. Dashboard sul database principale.**
Le aggregazioni pesanti competono con le transazioni degli utenti. Leggete gli aggregati da una replica.

---

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

1. Le grandi tabelle di eventi sono partizionate per tempo; la chiave primaria include la chiave di partizionamento.
2. Le partizioni vengono create in anticipo con un comando pianificato; la partizione `DEFAULT` è vuota o assente.
3. Le query filtrano sulla chiave di partizionamento senza funzioni; `EXPLAIN` mostra il pruning.
4. I dati vecchi vengono eliminati tramite `DETACH` e `DROP PARTITION`, con archiviazione se necessario.
5. La dashboard legge solo aggregati: tabelle di rollup con upsert incrementale o viste materializzate.
6. `REFRESH MATERIALIZED VIEW CONCURRENTLY` con indice univoco, `withoutOverlapping()` e `onOneServer()`.
7. Per JSONB: GIN `jsonb_path_ops` per `@>` e indici su espressione per le chiavi frequenti; le chiavi usate nei raggruppamenti sono spostate in colonne.
8. Le query pesanti della dashboard vanno su una replica.

---

## Conclusione

Una dashboard lenta non si cura quasi mai con un indice in più. Si cura portando in anticipo i dati nella forma in cui vengono letti: le partizioni escludono i periodi non necessari e rendono gratuita l'eliminazione dello storico, i preaggregati trasformano milioni di righe in migliaia e JSONB offre flessibilità là dove lo schema cambia davvero da un provider all'altro.

---

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

### Domanda 1: Perché su una tabella partizionata per `occurred_at` non si può creare `PRIMARY KEY (id)`?
- A) PostgreSQL non supporta le chiavi primarie sulle tabelle partizionate.
- B) L'unicità viene verificata all'interno di ciascuna partizione, quindi la chiave di partizionamento deve far parte di qualsiasi vincolo di unicità.
- C) La colonna `id` deve essere di tipo `uuid`.

<details>
<summary><b>Mostra la risposta</b></summary>

**Risposta: B**
Ogni partizione ha il proprio indice e PostgreSQL non è in grado di verificare l'unicità tra di esse. Includere `occurred_at` garantisce che valori uguali della chiave finiscano sempre nella stessa partizione, dove l'unicità viene verificata localmente.
</details>

### Domanda 2: Cosa serve per `REFRESH MATERIALIZED VIEW CONCURRENTLY`?
- A) Un indice univoco sulla vista materializzata che copra tutte le righe.
- B) Il partizionamento della tabella sorgente.
- C) L'esecuzione del comando come superutente.

<details>
<summary><b>Mostra la risposta</b></summary>

**Risposta: A**
In modalità `CONCURRENTLY` PostgreSQL confronta la vecchia e la nuova versione riga per riga, e l'indice univoco serve ad abbinare le righe. Senza di esso è disponibile solo il normale `REFRESH`, che blocca le letture.
</details>

### Domanda 3: Quale condizione permette a PostgreSQL di escludere le partizioni non necessarie?
- 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>Mostra la risposta</b></summary>

**Risposta: B**
Il partition pruning funziona quando la condizione è applicata direttamente alla chiave di partizionamento. Le funzioni `date()` ed `extract()` nascondono la chiave al planner, che è costretto a esaminare tutte le partizioni.
</details>