Particionamiento mensual y rotación automática de tablas en PostgreSQL (AWS RDS)
🇬🇧 Also read this post in English
🧩 Introducción
Este tutorial explica cómo transformar la tabla my_table de tu base de datos PostgreSQL (en AWS RDS) a una estructura particionada por mes usando rango sobre UTC, con rotación automática y mínima caída de servicio. El proceso está pensado para mantener la compatibilidad con las aplicaciones existentes y facilitar el mantenimiento y borrado automático de datos antiguos según las políticas de retención.
⚙️ Entorno usado
- PostgreSQL 14.17 (AWS RDS)
- TimeZone: UTC
- Tabla principal:
public.my_table(nombres modificados para anonimidad) - Automatización vía Python (AWS Lambda) y/o pg_cron
🧩 Problema encontrado
- La tabla
my_tableera no particionada y acumulaba datos rápidamente. - Necesidad de dividir los datos por mes (UTC) sin detener los servicios.
- Se debe respetar la retención: 2 meses.
- Debe ser posible crear y eliminar particiones de forma automática.
- Renombrar índices y constraints antiguos para liberar nombres reservados y evitar conflictos durante la migración.
- Compatible con scripts y aplicaciones actuales.
🔧 Tutorial paso a paso
1. Renombrar la tabla actual y liberar nombres
-- Renombrar tabla my_table a my_table_old
ALTER TABLE public.my_table RENAME TO my_table_old;
-- Renombrar índice primario (ejemplo)
ALTER INDEX public.my_table_pkey RENAME TO my_table_pkey_old;
2. Crear la nueva tabla particionada
-- Crear tabla audit_log parent particionada por RANGE en la columna "event_time"
CREATE TABLE public.audit_log (
event_time timestamptz NOT NULL,
action_type text NOT NULL,
user_id int,
details text,
request_payload text,
query_params text,
app_instance text,
response_payload text,
log_id int NOT NULL,
duration_ms int,
source_ip text,
CONSTRAINT audit_log_pkey PRIMARY KEY (log_id, event_time)
) PARTITION BY RANGE (event_time);
3. Crear los partitions mensuales necesarias
-- Partición para el mes anterior
CREATE TABLE public.audit_log_2025_09 PARTITION OF public.audit_log
FOR VALUES FROM ('2025-09-01 00:00:00+00') TO ('2025-10-01 00:00:00+00');
-- Partición para el mes actual
CREATE TABLE public.audit_log_2025_10 PARTITION OF public.audit_log
FOR VALUES FROM ('2025-10-01 00:00:00+00') TO ('2025-11-01 00:00:00+00');
-- Partición para el mes siguiente
CREATE TABLE public.audit_log_2025_11 PARTITION OF public.audit_log
FOR VALUES FROM ('2025-11-01 00:00:00+00') TO ('2025-12-01 00:00:00+00');
4. Crear índices globales si se requiere
-- Ejemplo de índice global en campo "date"
CREATE INDEX my_table_date_idx ON public.my_table(date);
5. (Opcional) Migrar algunos datos de prueba
-- Insertar registros recientes para pruebas (sin histórico completo)
INSERT INTO public.audit_log (
event_time, action_type, user_id, details, request_payload, query_params, app_instance, response_payload, log_id, duration_ms, source_ip
)
SELECT
event_time, action_type, user_id, details, request_payload, query_params, app_instance, response_payload, log_id, duration_ms, source_ip
FROM public.audit_log_old
WHERE event_time >= '2025-10-01 00:00:00+00';
6. Actualiza tus scripts y aplicaciones
Confirma que modificaste las referencias necesarias para operar sobre la nueva tabla my_table, ahora particionada. Si usabas my_table, los nombres se deben conservar salvo si hubo requerimientos extra.
7. Automatiza la rotación de particiones
Implementa el mantenimiento con pg_cron o Lambda + Python:
Ejemplo función de rotación en PL/pgSQL
CREATE OR REPLACE FUNCTION audit_log_rotate(retention_months INT, horizon_months INT) RETURNS VOID AS $$
DECLARE
current_month date := date_trunc('month', now() AT TIME ZONE 'UTC');
first_keep date := current_month - INTERVAL '1 month' * retention_months;
last_create date := current_month + INTERVAL '1 month' * horizon_months;
BEGIN
-- Crear particiones futuras
FOR m IN 0..horizon_months LOOP
EXECUTE format(
'CREATE TABLE IF NOT EXISTS public.audit_log_%s PARTITION OF public.audit_log FOR VALUES FROM (%L) TO (%L);',
to_char(current_month + INTERVAL '1 month' * m, 'YYYY_MM'),
current_month + INTERVAL '1 month' * m,
current_month + INTERVAL '1 month' * (m+1)
);
END LOOP;
-- Borrar antiguas
FOR r IN 1..retention_months LOOP
EXECUTE format(
'DROP TABLE IF EXISTS public.audit_log_%s CASCADE;',
to_char(first_keep - INTERVAL '1 month' * r, 'YYYY_MM')
);
END LOOP;
END;
$$ LANGUAGE plpgsql;
Programar ejecución vía pg_cron todos los días:
SELECT cron.schedule('rotate_audit_log', '5 0 1 * *', 'SELECT audit_log_rotate(2,2);');
O implementa la lógica en AWS Lambda con Python y pg8000, conectando a cada base con los credentials seguros (usando AWS Secrets Manager).
💡 Recomendaciones
- Renombra todos los objetos antes del swap para evitar conflictos de nombres.
- Automatiza la rotación: crea las particiones futuras y borra las antiguas según retención.
- Valida que las aplicaciones y scripts no dependan de particiones específicas; opera siempre sobre el parent.
- Protege y centraliza los parámetros de mantenimiento (retención, horizon, etc.).
Top comments (0)