Introduction

When working with large volumes of data in PostgreSQL, we often encounter the need for table partitioning. This approach allows for more efficient data management and querying of extensive datasets. In this article, I will focus on updating partitioned tables using dynamic SQL commands in PL/pgSQL.

The Problem

I have a partitioned table audit_logs, which is divided into monthly partitions named in the format audit_logs_pYYYY_MM (e.g., audit_logs_p2025_01, audit_logs_p2025_02, ...). The goal is to update the column is_system_event to true for records with specific values in the action column, but only if this value is not already set.

The Solution

I used a PL/pgSQL block with a FOR loop that iterates through all monthly partitions and performs an update using dynamic SQL. Here is the code:

DO
$$
DECLARE
  i integer;
  table_name text;
BEGIN
  FOR i IN 1..12 LOOP
    table_name := format('audit_logs_p2025_%s', to_char(i, 'FM00'));
    RAISE NOTICE 'Processing table: %', table_name;
    EXECUTE format(
      'UPDATE %I
       SET is_system_event = true
       WHERE id IN (
         SELECT id
         FROM %I
         WHERE action IN (
           'email_bounce',
           'email_click',
           'email_deferred',
           'email_delivered',
           'email_dropped',
           'email_open',
           'email_processed',
           'email_spamreport'
         )
         AND is_system_event = false
       );',
       table_name, table_name
    );
  END LOOP;
END
$$;

Code Explanation

1. Variable Declarations
2. i is a loop counter that iterates through 12 months.
3. table_name is a text variable that dynamically constructs the table name.
4. FOR** Loop for 12 Months**
5. table_name is set to audit_logs_p2025_MM, where MM is the month number (formatted to two digits using to_char(i, 'FM00')).
6. RAISE NOTICE prints the currently processed table (useful for debugging).
7. **Dynamic UPDATE with **EXECUTE
8. format() constructs an SQL query with safely escaped table names (%I).
9. UPDATE sets is_system_event = true if action is among the listed values and is_system_event = false.

Advantages of This Approach

• Automation: No need to manually write UPDATE statements for each partition.
• Safety: Using format() with %I prevents SQL injection when dealing with table names.
• Efficiency: WHERE id IN (SELECT id ...) ensures that only relevant records are updated.

Possible Improvements

• Parallelization: If the database is powerful enough, updates can be executed in parallel.
• Logging: We can add logging of updated rows to an audit table.
• Conditional Execution: Adding a check to ensure the table exists to avoid errors.

Conclusion

This approach allows for an elegant and efficient update of partitioned tables in PostgreSQL. Using dynamic SQL, we can easily manage data in large databases and automate repetitive operations without the need to manually write SQL queries for each partition.