Aprende a Particionar tablas grandes por fecha en PostgreSQL

El particionamiento declarativo divide una tabla lógica en tablas físicas más pequeñas. Puede acelerar consultas que filtran períodos concretos, simplificar la eliminación de históricos y acotar el mantenimiento. No mejora automáticamente todas las consultas: la clave debe coincidir con los filtros, la retención y el crecimiento de los datos.

Esta guía utiliza PostgreSQL 16–18, created_at timestamptz y particiones mensuales. Antes de ejecutar comandos, elige un recorrido:

Recorrido A: public.events todavía no existe.
Recorrido B: public.events ya contiene datos y debe migrarse.

No combines ambos recorridos sobre la misma base.

Cómo funcionan los rangos

Los límites inferiores están incluidos y los superiores excluidos:

FROM ('2026-09-01 00:00:00+00')
TO   ('2026-10-01 00:00:00+00')

La fila de las 00:00 del 1 de septiembre pertenece a septiembre; la de las 00:00 del 1 de octubre pertenece a octubre. Las consultas con filtros compatibles pueden descartar las demás particiones mediante partition pruning. Documentación oficial.

Recorrido A: crear una tabla nueva

Crea el padre con una clave primaria que incluya la clave de partición:

CREATE TABLE public.events (
  id bigint GENERATED BY DEFAULT AS IDENTITY,
  created_at timestamptz NOT NULL,
  account_id bigint NOT NULL,
  event_type text NOT NULL,
  payload jsonb NOT NULL,
  PRIMARY KEY (created_at, id)
) PARTITION BY RANGE (created_at);

Una PRIMARY KEY o restricción UNIQUE del padre debe incluir todas las columnas de partición. Si otras tablas necesitan referenciar solamente id, debes rediseñar esas relaciones antes de continuar.

Crea rangos adyacentes:

CREATE TABLE public.events_2026_08 PARTITION OF public.events
FOR VALUES FROM ('2026-08-01 00:00:00+00')
           TO ('2026-09-01 00:00:00+00');

CREATE TABLE public.events_2026_09 PARTITION OF public.events
FOR VALUES FROM ('2026-09-01 00:00:00+00')
           TO ('2026-10-01 00:00:00+00');

CREATE TABLE public.events_2026_10 PARTITION OF public.events
FOR VALUES FROM ('2026-10-01 00:00:00+00')
           TO ('2026-11-01 00:00:00+00');

Opcionalmente, crea una partición de seguridad:

CREATE TABLE public.events_default
PARTITION OF public.events DEFAULT;

DEFAULT evita fallos de inserción, pero puede ocultar períodos faltantes. Debe permanecer monitorizada y preferentemente vacía.

Define en el padre los índices necesarios:

CREATE INDEX events_account_created_idx
ON public.events (account_id, created_at DESC);

CREATE INDEX events_type_created_idx
ON public.events (event_type, created_at DESC);

PostgreSQL crea índices equivalentes en las particiones. No los agregues por costumbre: conserva solo los que respondan a consultas reales.

Comprueba las fronteras sin conservar datos ficticios:

BEGIN;

INSERT INTO public.events
  (created_at, account_id, event_type, payload)
VALUES
  ('2026-08-31 23:59:59.999999+00',1001,'boundary_a','{}'),
  ('2026-09-01 00:00:00+00',       1001,'boundary_b','{}'),
  ('2026-09-15 12:00:00+00',       1001,'middle','{}')
RETURNING tableoid::regclass AS physical_partition,
          created_at, event_type;

ROLLBACK;

boundary_a debe aparecer en agosto; las otras filas, en septiembre.

Comprueba la jerarquía y la poda:

SELECT relid::regclass, parentrelid::regclass, level, isleaf
FROM pg_partition_tree('public.events');

ANALYZE public.events;

EXPLAIN (COSTS, VERBOSE)
SELECT count(*)
FROM public.events
WHERE created_at >= '2026-09-01 00:00:00+00'
  AND created_at <  '2026-10-01 00:00:00+00';

El plan debería leer septiembre y omitir agosto y octubre. EXPLAIN no ejecuta la consulta; reserva EXPLAIN (ANALYZE, BUFFERS) para una copia o ventana autorizada.

Recorrido B: migrar una tabla existente

PostgreSQL no convierte directamente una tabla normal en particionada. Debes crear una tabla sombra, copiar, validar, detener escrituras, cambiar nombres y recrear dependencias.

Comienza con estimaciones de bajo impacto:

SELECT c.reltuples::bigint AS estimated_rows,
       s.n_live_tup,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid=c.oid
WHERE c.oid='public.events'::regclass;

Los conteos exactos y agrupaciones mensuales pueden escanear toda la tabla. Ejecútalos en una réplica, una copia restaurada o una ventana controlada.

Guarda el esquema completo e inventaría claves, índices, vistas, triggers, grants, RLS, políticas, secuencias, publicaciones y claves foráneas:

STAMP=$(date -u +%Y%m%dT%H%M%SZ)
WORKDIR="$HOME/postgresql-partition-$STAMP"
install -d -m 0700 "$WORKDIR"

pg_dump --schema-only --dbname=appdb \
  --file="$WORKDIR/appdb-schema-before.sql"

sha256sum "$WORKDIR/appdb-schema-before.sql" \
  > "$WORKDIR/SHA256SUMS"

Crea la tabla sombra sin copiar a ciegas los índices originales:

CREATE TABLE public.events_part (
  LIKE public.events
    INCLUDING DEFAULTS
    INCLUDING GENERATED
    INCLUDING IDENTITY
    INCLUDING STORAGE
    INCLUDING COMMENTS
    INCLUDING CONSTRAINTS
) PARTITION BY RANGE (created_at);

ALTER TABLE public.events_part
ADD PRIMARY KEY (created_at, id);

Si el id original era IDENTITY, INCLUDING IDENTITY crea una secuencia independiente. Si provenía de serial, el default puede seguir apuntando a la secuencia antigua. Compruébalo:

SELECT column_name, is_identity, identity_generation, column_default
FROM information_schema.columns
WHERE table_schema='public'
  AND table_name='events_part'
  AND column_name='id';

SELECT pg_get_serial_sequence('public.events_part','id');

Si no es una identidad y el default referencia la secuencia original, crea una independiente:

CREATE SEQUENCE public.events_part_id_seq
OWNED BY public.events_part.id;

ALTER TABLE public.events_part
ALTER COLUMN id
SET DEFAULT nextval('public.events_part_id_seq'::regclass);

Crea las particiones históricas y futuras y copia por rangos. Esto solo es consistente si las escrituras están detenidas o el rango es demostrablemente inmutable:

INSERT INTO public.events_part
  (id, created_at, account_id, event_type, payload)
SELECT id, created_at, account_id, event_type, payload
FROM public.events
WHERE created_at >= '2026-08-01 00:00:00+00'
  AND created_at <  '2026-09-01 00:00:00+00';

Una copia concurrente simple no captura correctamente UPDATE o DELETE. Una migración online necesita dual-write o replicación con reconciliación. La replicación lógica tampoco replica automáticamente el esquema ni las secuencias. Restricciones oficiales.

Antes del corte, compara conteos por período, duplicados, contenido, routing, pruning, permisos, RLS, triggers y claves. Prepara y ensaya dos manifiestos: uno para recrear dependencias sobre la tabla nueva y otro para devolverlas a la original.

Con las escrituras detenidas:

BEGIN;
SET LOCAL lock_timeout='5s';

LOCK TABLE public.events, public.events_part
IN ACCESS EXCLUSIVE MODE;

ALTER TABLE public.events RENAME TO events_legacy;
ALTER TABLE public.events_part RENAME TO events;

COMMIT;

Las vistas y claves foráneas siguen el OID de la tabla anterior aunque cambie de nombre. Ejecuta el manifiesto previamente ensayado para que apunten a la nueva public.events. No reanudes la aplicación hasta validar lecturas, escrituras, secuencia, constraints, grants y planes.

Si falla una comprobación antes de reanudar escrituras:

BEGIN;
SET LOCAL lock_timeout='5s';

LOCK TABLE public.events, public.events_legacy
IN ACCESS EXCLUSIVE MODE;

ALTER TABLE public.events RENAME TO events_part_failed;
ALTER TABLE public.events_legacy RENAME TO events;

COMMIT;

Después de reanudar escrituras, el rollback deja de ser un simple cambio de nombres: requiere reconciliar los cambios nuevos.

Mantenimiento

Crea particiones futuras antes de necesitarlas. Si utilizas DEFAULT, alerta cuando contenga filas:

SELECT count(*), min(created_at), max(created_at)
FROM public.events_default;

Para retirar históricos:

ALTER TABLE public.events
DETACH PARTITION public.events_2025_08;

Después puedes respaldar y eliminar la tabla separada según la política de retención. Agregar o retirar particiones requiere bloqueos; ensaya siempre el procedimiento.