DEV Community

Cover image for Particionamiento mensual y rotación automática de tablas en PostgreSQL (AWS RDS)
Ivaj O'Franc
Ivaj O'Franc

Posted on

Particionamiento mensual y rotación automática de tablas en PostgreSQL (AWS RDS)

#postgres #particionamiento #automatización #aws

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_table era 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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);');
Enter fullscreen mode Exit fullscreen mode

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.).

🔗 Enlaces útiles

Top comments (0)