---
title: 'PostgreSQL para dashboards: particionado, vistas materializadas y JSONB | DevSense'
description: 'Cómo construir dashboards rápidos sobre PostgreSQL: particionado por fecha, eliminación instantánea de datos antiguos, vistas materializadas y tablas rollup, JSONB con los índices adecuados en Laravel.'
faq:
    - { question: '¿Cuándo conviene particionar una tabla por fecha?', answer: 'Cuando tiene cientos de millones de filas, las consultas casi siempre filtran por tiempo y hay que eliminar los datos antiguos con regularidad. El particionado permite descartar las particiones innecesarias al planificar la consulta (partition pruning) y eliminar un mes entero de datos con DROP o DETACH PARTITION en milisegundos, en lugar de un DELETE largo seguido de VACUUM.' }
    - { question: '¿Qué aporta REFRESH MATERIALIZED VIEW CONCURRENTLY?', answer: 'Un REFRESH normal adquiere un bloqueo exclusivo, y el dashboard no puede leer la vista mientras se recalcula. CONCURRENTLY construye la nueva versión en paralelo y aplica la diferencia sin bloquear la lectura. Para ello, la vista materializada debe tener un índice único que cubra todas las filas.' }
    - { question: '¿Qué índice se necesita para las consultas sobre JSONB?', answer: "Para búsquedas por contención (operador @>) sirve un índice GIN con la clase de operadores jsonb_path_ops: es más compacto que el jsonb_ops habitual. Si filtra constantemente por una sola clave, es más eficiente un índice de expresión (payload->>'game_id') o una columna generada con un índice B-tree normal." }
    - { question: '¿Cuándo deja PostgreSQL de ser adecuado para analítica?', answer: 'Cuando los volúmenes se cuentan en miles de millones de filas y las consultas agregan grandes rangos por muchas dimensiones en tiempo real. El almacenamiento por filas y MVCC encarecen esos escaneos, y las bases de datos columnares como ClickHouse ganan por órdenes de magnitud. Hasta ese punto, el particionado y la preagregación suelen cubrir las necesidades de los dashboards.' }
published: '2026-09-28'
---
# PostgreSQL para dashboards: particionado, vistas materializadas y JSONB

El dashboard de estadísticas tarda 40 segundos en abrirse. La tabla `events` ha crecido hasta 400 millones de filas, existe un índice sobre `occurred_at`, `EXPLAIN` muestra un Index Scan, pero la consulta «ingresos por proveedor en los últimos 30 días» sigue leyendo decenas de millones de filas. El `DELETE` nocturno de eventos antiguos dura tres horas y deja la tabla inflada, y el autovacuum no da abasto. Aquí no servirán nuevos índices: el problema no es encontrar las filas, sino que el dashboard recalcula cada vez lo que podría haberse calculado una sola vez.

**Guías relacionadas:** [Optimización de consultas: EXPLAIN y particionado](database-query-optimization) · [Índices de bases de datos](database-indexes-deep-dive) · [Ingesta de eventos bajo alta carga](high-load-event-ingestion)

## Contenido

* [Por qué una tabla transaccional es una mala fuente para un dashboard](#oltp-vs-dashboards)
* [Particionado por tiempo](#partitioning)
* [Eliminar datos antiguos en milisegundos](#retention)
* [Vistas materializadas](#materialized-views)
* [Tablas rollup: preagregación incremental](#rollups)
* [JSONB para los datos de los proveedores](#jsonb)
* [Juntándolo todo](#architecture)
* [Dónde deja de funcionar este enfoque](#limitations)
* [Errores frecuentes](#common-mistakes)
* [Checklist](#checklist)
* [Cuestionario de autoevaluación](#self-test-quiz)

---

<a id="oltp-vs-dashboards"></a>
## Por qué una tabla transaccional es una mala fuente para un dashboard

Una tabla transaccional está optimizada para operaciones cortas: insertar un evento, buscar un pedido por ID, actualizar un estado. Un dashboard hace lo contrario: lee rangos enormes y los reduce a unas pocas cifras. Cuando ambos tipos de carga van a la misma tabla:

* **Cada visualización del dashboard repite el mismo trabajo.** Diez managers que abren la página son diez agregaciones completas de un mes.
* **Un índice no salva del volumen.** Incluso un índice perfecto por fecha encuentra 30 millones de filas en un mes, y aun así hay que leerlas y sumarlas.
* **Eliminar datos antiguos es caro.** `DELETE` marca las filas como muertas, y la tabla se infla hasta el siguiente `VACUUM`.

**Un dashboard rápido sobre PostgreSQL no se construye con índices, sino con la forma de los datos: los eventos en bruto se particionan por tiempo, los agregados se calculan de antemano y se actualizan de forma incremental, y los atributos variables de los proveedores se guardan en JSONB con índices solo donde se filtra por ellos.**

---

<a id="partitioning"></a>
## Particionado por tiempo

El particionado declarativo (PostgreSQL 10+) divide una tabla lógica en particiones físicas según un rango de la clave. Para eventos, la clave es casi siempre el tiempo:

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

Lo importante aquí:

* **La clave primaria debe incluir la clave de particionado.** PostgreSQL solo comprueba la unicidad dentro de cada partición, por eso no se puede crear `PRIMARY KEY (id)` en una tabla particionada: hace falta `(id, occurred_at)`.
* **Los índices creados en la tabla padre se crean automáticamente en todas las particiones** (PostgreSQL 11+), incluidas las futuras.

Las particiones se crean de antemano, por ejemplo con un comando programado:

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

Ahora una consulta de los últimos 30 días afecta a una o dos particiones. En `EXPLAIN` se ve porque el plan solo enumera las particiones necesarias: eso es **partition pruning**. La condición debe aplicarse sobre la propia clave: `WHERE occurred_at >= now() - interval '30 days'` descarta particiones, pero `WHERE date(occurred_at) = ...` no, porque la función oculta la clave al planificador.

> [!TIP]
> **BRIN en lugar de B-tree para el tiempo.** En una tabla append-only, los eventos se escriben en un orden casi cronológico. Un índice BRIN sobre `occurred_at` ocupa kilobytes en lugar de gigabytes y es ideal para consultas por rango: `CREATE INDEX ON events USING brin (occurred_at);`

---

<a id="retention"></a>
## Eliminar datos antiguos en milisegundos

La principal ventaja práctica del particionado está en la política de retención. Eliminar los eventos de más de un año:

```sql
-- Sin particiones: horas de trabajo, tabla inflada, carga sobre el WAL y las réplicas
DELETE FROM events WHERE occurred_at < now() - interval '1 year';

-- Con particiones: una operación sobre metadatos
ALTER TABLE events DETACH PARTITION events_2025_09 CONCURRENTLY; -- PostgreSQL 14+
DROP TABLE events_2025_09;
```

La partición desacoplada no tiene por qué eliminarse de inmediato: se puede exportar a un almacenamiento frío (`pg_dump -t events_2025_09`) y solo después eliminarla.

> [!WARNING]
> **Cuidado con la partición por defecto.** La partición `DEFAULT` recibe las filas que no encajan en ningún rango. Pero al crear una nueva partición, PostgreSQL debe comprobar que en `DEFAULT` no hay filas de su rango, y eso es un escaneo completo bajo bloqueo. Es más fiable crear siempre las particiones de antemano y monitorizar que `DEFAULT` esté vacía.

---

<a id="materialized-views"></a>
## Vistas materializadas

Una vista materializada (materialized view) es el resultado de una consulta guardado como tabla. El dashboard lee el resultado ya calculado y el recálculo se hace de forma programada:

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

-- Obligatorio para 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;
}
```

El dashboard accede al modelo como a una tabla normal: `DailyProviderStat::where('day', '>=', now()->subDays(30))->get()`.

* **`CONCURRENTLY`** recalcula los datos sin bloquear la lectura. Sin él, el dashboard esperará a que termine el recálculo. `CONCURRENTLY` requiere un índice único sobre todas las filas de la vista.
* **`withoutOverlapping()` y `onOneServer()`** impiden lanzar dos recálculos a la vez si el anterior aún no ha terminado o si tiene varios servidores.

Las vistas materializadas tienen un inconveniente serio: `REFRESH` ejecuta la consulta **completa** cada vez. Mientras eso tarde segundos, perfecto. Cuando recalcular 90 días empiece a llevar minutos, es hora de pasar a la agregación incremental.

---

<a id="rollups"></a>
## Tablas rollup: preagregación incremental

Una tabla rollup guarda agregados por «cubos» (hora, día) y solo se actualiza para el último periodo:

```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
{
    // Recalculamos solo las 2 últimas horas: los eventos que llegan con retraso también se contabilizan
    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;
}
```

Cada ejecución lee los eventos de dos horas, no de 90 días, así que el coste no crece con el historial. El dashboard obtiene las cifras diarias y mensuales sumando las filas horarias: miles de filas en lugar de millones. La ventana de recálculo de más de una hora es necesaria para los eventos que llegan con retraso.

---

<a id="jsonb"></a>
## JSONB para los datos de los proveedores

Los proveedores envían campos distintos: uno manda `round_id` y `bet_lines`, otro `session` y `multiplier`. Crear una columna para cada campo de cada proveedor no tiene sentido, y aquí JSONB encaja bien:

```php
// Consultas desde 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');
```

Los índices se eligen según las consultas concretas:

```sql
-- Búsqueda por contención (@>): GIN compacto
CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);

-- Filtro constante por una sola clave: índice de expresión
CREATE INDEX events_game_id_idx ON events ((payload->>'game_id'));
```

Si por una clave no solo se filtra, sino que también se agrupa en los informes, llévela a una columna generada (PostgreSQL 12+). Tiene estadísticas para el planificador y un índice B-tree normal:

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

Tenga en cuenta que añadir una columna `STORED` reescribe toda la tabla bajo bloqueo. En una tabla grande esto se hace al crear el esquema o en una ventana de mantenimiento; en un sistema en funcionamiento se recurre al índice de expresión.

> [!NOTE]
> **JSONB no sustituye al esquema.** PostgreSQL no recopila estadísticas sobre las claves dentro de JSONB, por lo que el planificador estima mal la selectividad de esas condiciones. Todo aquello por lo que filtre, agrupe o haga join con regularidad debe ser una columna.

---

<a id="architecture"></a>
## Juntándolo todo

```
[ Proveedores / aplicación ]
            │  INSERT (por lotes)
            ▼
[ events ] — particiones mensuales, payload JSONB, BRIN por tiempo
            │  cada 5 minutos: rollup de las últimas 2 horas
            ▼
[ hourly_provider_stats ] — miles de filas en lugar de millones
            │  REFRESH CONCURRENTLY cada 15 minutos
            ▼
[ materialized views ] — cortes complejos para widgets concretos
            │
            ▼
[ Dashboard ] — lee solo agregados (mejor desde una réplica)
```

Los eventos en bruto se conservan para informes detallados e investigaciones, pero el dashboard no los toca. Las particiones antiguas se archivan de forma programada, y el tamaño de los datos «calientes» se mantiene constante.

---

<a id="limitations"></a>
## Dónde deja de funcionar este enfoque

* **Demasiadas particiones.** Cada partición aumenta el tiempo de planificación. Miles de particiones pequeñas (por ejemplo, diarias durante varios años) ralentizarán incluso las consultas sencillas. Para la mayoría de los dashboards bastan las particiones mensuales.
* **Unicidad entre particiones.** Las restricciones únicas deben incluir la clave de particionado. La unicidad global, por ejemplo de `external_id`, habrá que garantizarla con una tabla aparte o a nivel de aplicación.
* **En las tablas particionadas no se pueden construir índices `CONCURRENTLY` sobre la tabla padre.** Hay que crear el índice en cada partición por separado y después adjuntarlos mediante `ALTER INDEX ... ATTACH PARTITION`.
* **Retraso de los datos.** Los preagregados siempre van un poco por detrás. Si el negocio necesita cifras con precisión de segundos, muéstrelas a partir del rollup más la «cola» de datos en bruto de los últimos minutos.
* **Escala de la analítica.** Con miles de millones de filas y cortes arbitrarios, las bases de datos columnares (ClickHouse) ganan por órdenes de magnitud. PostgreSQL sigue siendo la fuente de verdad, y la analítica se va a donde le corresponde: consulte [ingesta de eventos bajo alta carga](high-load-event-ingestion).

---

<a id="common-mistakes"></a>
## Errores frecuentes

**1. Una función sobre la clave de particionado en el `WHERE`.**
`WHERE date(occurred_at) = '2026-09-01'` desactiva el partition pruning. Escriba un rango: `occurred_at >= '2026-09-01' AND occurred_at < '2026-09-02'`.

**2. `DELETE` para limpiar el historial.**
Horas de trabajo y una tabla inflada. Elimine particiones enteras mediante `DETACH` y `DROP`.

**3. Las particiones se crean «sobre la marcha».**
A principios de mes las inserciones fallan o acaban en `DEFAULT`. Cree las particiones con varios meses de antelación mediante una tarea programada.

**4. `REFRESH MATERIALIZED VIEW` sin `CONCURRENTLY`.**
El dashboard se bloquea mientras dura el recálculo. Añada un índice único y use `CONCURRENTLY`.

**5. Recalcular todo el historial en cada ejecución.**
Una vista materializada de 3 años tarda en recalcularse más que el intervalo de actualización. Pase a tablas rollup con upsert incremental.

**6. Todo en JSONB.**
Los filtros y agrupaciones por claves JSONB sin estadísticas producen malos planes. Lleve las claves frecuentes a columnas.

**7. Dashboard sobre la base de datos principal.**
Las agregaciones pesadas compiten con las transacciones de los usuarios. Lea los agregados desde una réplica.

---

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

1. Las tablas de eventos grandes están particionadas por tiempo; la clave primaria incluye la clave de particionado.
2. Las particiones se crean de antemano con un comando programado; la partición `DEFAULT` está vacía o no existe.
3. Las consultas filtran por la clave de particionado sin funciones; `EXPLAIN` muestra pruning.
4. Los datos antiguos se eliminan mediante `DETACH` y `DROP PARTITION`, con archivado si es necesario.
5. El dashboard lee solo agregados: tablas rollup con upsert incremental o vistas materializadas.
6. `REFRESH MATERIALIZED VIEW CONCURRENTLY` con índice único, `withoutOverlapping()` y `onOneServer()`.
7. Para JSONB: GIN `jsonb_path_ops` para `@>` e índices de expresión para las claves frecuentes; las claves usadas en agrupaciones están en columnas.
8. Las consultas pesadas del dashboard van a una réplica.

---

## Resumen

Un dashboard lento casi nunca se arregla con un índice más. Se arregla dando a los datos, de antemano, la forma en que se leen: las particiones descartan el tiempo innecesario y hacen gratuita la eliminación del historial, los preagregados convierten millones de filas en miles, y JSONB aporta flexibilidad allí donde el esquema realmente cambia de un proveedor a otro.

---

<a id="self-test-quiz"></a>
## Cuestionario de autoevaluación

### Pregunta 1: ¿Por qué no se puede crear `PRIMARY KEY (id)` en una tabla particionada por `occurred_at`?
- A) PostgreSQL no admite claves primarias en tablas particionadas.
- B) La unicidad se comprueba dentro de cada partición, por lo que la clave de particionado debe formar parte de cualquier restricción única.
- C) La columna `id` debe ser de tipo `uuid`.

<details>
<summary><b>Mostrar respuesta</b></summary>

**Respuesta: B**
Cada partición tiene su propio índice, y PostgreSQL no sabe comprobar la unicidad entre ellas. Incluir `occurred_at` garantiza que los valores iguales de la clave caigan siempre en la misma partición, donde la unicidad se comprueba localmente.
</details>

### Pregunta 2: ¿Qué se necesita para `REFRESH MATERIALIZED VIEW CONCURRENTLY`?
- A) Un índice único en la vista materializada que cubra todas las filas.
- B) Particionar la tabla de origen.
- C) Ejecutar el comando como superusuario.

<details>
<summary><b>Mostrar respuesta</b></summary>

**Respuesta: A**
En el modo `CONCURRENTLY`, PostgreSQL compara la versión antigua y la nueva fila a fila, y el índice único es necesario para emparejar las filas. Sin él solo está disponible el `REFRESH` normal, que bloquea la lectura.
</details>

### Pregunta 3: ¿Qué condición permite a PostgreSQL descartar las particiones innecesarias?
- 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>Mostrar respuesta</b></summary>

**Respuesta: B**
El partition pruning funciona cuando la condición se aplica directamente sobre la clave de particionado. Las funciones `date()` y `extract()` ocultan la clave al planificador, que se ve obligado a recorrer todas las particiones.
</details>