Monthly Partitioning and Automatic Rotation of Tables in PostgreSQL (AWS RDS)

🇪🇸 Lee también este post en español

---

🧩 Introduction

This tutorial explains how to transform the my_table table in your PostgreSQL database (on AWS RDS) into a monthly partitioned structure using a UTC range, with automatic rotation and minimal downtime. The process is designed to maintain compatibility with existing applications and to make it easy to maintain and automatically delete old data according to retention policies.

---

⚙️ Environment Used

• PostgreSQL 14.17 (AWS RDS)
• TimeZone: UTC
• Main table: public.my_table (names have been changed for anonymity)
• Automation via Python (AWS Lambda) and/or pg_cron

---

🧩 Problem Found

• The my_table table was not partitioned and grew quickly.
• Need to split the data by month (UTC) without stopping services.
• Retention must be respected: 2 months.
• It should be possible to create and drop partitions automatically.
• Rename old indexes and constraints to free reserved names and avoid conflicts during migration.
• Compatible with current scripts and applications.

---

🔧 Step-by-Step Tutorial

1. Rename the current table and release names

-- Rename table my_table to my_table_old
ALTER TABLE public.my_table RENAME TO my_table_old;

-- Rename primary index (example)
ALTER INDEX public.my_table_pkey RENAME TO my_table_pkey_old;

2. Create the new partitioned table

-- Create audit_log parent table partitioned by RANGE on "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. Create the needed monthly partitions

-- Previous month partition
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');

-- Current month partition
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');

-- Next month partition
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. Create global indexes if needed

-- Example of global index on "event_time"
CREATE INDEX audit_log_event_time_idx ON public.audit_log(event_time);

5. (Optional) Migrate some recent test data

-- Insert recent records for testing (without complete history)
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. Update your scripts and applications

Make sure you have updated the necessary references to operate on the new my_table, now partitioned. If you previously used my_table, names should be retained unless additional requirements exist.

7. Automate partition rotation

Implement maintenance via pg_cron or Lambda + Python:

Example rotation function in 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
-- Create future partitions
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;

-- Drop old partitions
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;

Schedule execution via pg_cron each day:

SELECT cron.schedule('rotate_audit_log', '5 0 1 * *', 'SELECT audit_log_rotate(2,2);');

Or, implement the logic in AWS Lambda with Python and pg8000, connecting to each database with secure credentials (using AWS Secrets Manager).

---

💡 Recommendations

• Rename all objects before swapping to avoid naming conflicts.
• Automate rotation: create future partitions and drop old ones based on retention.
• Make sure your applications and scripts don’t depend on specific partitions; always operate on the parent.
• Protect and centralize your maintenance parameters (retention, horizon, etc.).

---

🔗 Useful Links

• Official PostgreSQL Partitioning Documentation
• pg_cron for automation
• AWS Lambda and RDS
• pg8000 - Python driver for PostgreSQL