Dual-Write Migrations with Triggers

Hello! Welcome back to our course on designing high-load distributed systems.

In the previous lesson, we designed a comprehensive shadow table migration strategy. We outlined the key components: creating the shadow table, planning the batched backfill, and designing the atomic cutover. We conceptually described the need for a synchronization mechanism to capture live data changes during the migration.

Today, we move from design to implementation. This lesson focuses on building that synchronization engine. Our learning outcome is to implement triggers to dually write to an original table and a shadow table during a migration. We will write the specific PostgreSQL code required to ensure that every INSERT, UPDATE, and DELETE on our original table is mirrored onto the shadow table, keeping them perfectly in sync.

This step is critical. Without a flawless dual-writing mechanism, data would be lost during the backfill period, making a safe cutover impossible.

1. PostgreSQL Triggers: The Foundation of Synchronization

Before we write the code, let's establish a solid understanding of how triggers work in PostgreSQL. Unlike some other database systems where the logic is embedded directly in the CREATE TRIGGER statement, PostgreSQL uses a two-part system:

1. Trigger Function: A user-defined function, typically written in PL/pgSQL, that contains the logic to be executed. This function must be declared to return a special type: trigger.
2. Trigger Definition: The CREATE TRIGGER statement itself, which binds the trigger function to a specific table, event (INSERT, UPDATE, DELETE), and timing (BEFORE or AFTER the event).

For our shadow table migration, we will use AFTER triggers. This ensures that the operation on the original table has successfully completed before we attempt to replicate it to the shadow table.

To get familiar with the precise syntax and capabilities, please review the official PostgreSQL documentation.

From the documentation, the key concepts for our task are:

• FOR EACH ROW: Our trigger must execute for every row that is modified, not just once per statement.
• Trigger Function: The function receives context about the event that fired it. Inside the function, we can access special variables:
  • NEW: A record variable holding the new database row for INSERT and UPDATE operations.
  • OLD: A record variable holding the old database row for UPDATE and DELETE operations.
  • TG_OP: A string variable containing 'INSERT', 'UPDATE', or 'DELETE' to identify which operation caused the trigger to fire.

2. Implementing the Dual-Write Logic

Let's continue with the scenario from our previous lesson. We are migrating the orders table to a new orders_shadow table to extract provider_id and fraud_score from a JSONB column.

Original Table:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    order_data JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Shadow Table:

CREATE TABLE orders_shadow (
    id BIGINT PRIMARY KEY,
    order_data JSONB,
    created_at TIMESTAMPTZ NOT NULL,
    provider_id VARCHAR(255),
    fraud_score INT
);

Instead of creating three separate functions, a common and efficient pattern is to create a single trigger function that handles all three DML operations (INSERT, UPDATE, DELETE).

Step 1: Create the Trigger Function

Here is the PL/pgSQL function that will contain our synchronization logic. It uses the TG_OP variable to branch its logic accordingly.

CREATE OR REPLACE FUNCTION sync_orders_to_shadow()
RETURNS TRIGGER AS $$
BEGIN
    -- Branch logic based on the operation
    IF (TG_OP = 'INSERT') THEN
        -- A new order was created, insert it into the shadow table with extracted fields.
        INSERT INTO orders_shadow (id, order_data, created_at, provider_id, fraud_score)
        VALUES (
            NEW.id,
            NEW.order_data,
            NEW.created_at,
            NEW.order_data ->> 'providerId',
            (NEW.order_data ->> 'fraudScore')::integer
        );
        RETURN NEW;

    ELSIF (TG_OP = 'UPDATE') THEN
        -- An order was updated. Use ON CONFLICT (UPSERT) to handle the update.
        -- This is crucial because the backfill might not have copied this row yet.
        INSERT INTO orders_shadow (id, order_data, created_at, provider_id, fraud_score)
        VALUES (
            NEW.id,
            NEW.order_data,
            NEW.created_at,
            NEW.order_data ->> 'providerId',
            (NEW.order_data ->> 'fraudScore')::integer
        )
        ON CONFLICT (id) DO UPDATE SET
            order_data = EXCLUDED.order_data,
            created_at = EXCLUDED.created_at,
            provider_id = EXCLUDED.provider_id,
            fraud_score = EXCLUDED.fraud_score;
        RETURN NEW;

    ELSIF (TG_OP = 'DELETE') THEN
        -- An order was deleted, remove it from the shadow table.
        DELETE FROM orders_shadow WHERE id = OLD.id;
        RETURN OLD;
    END IF;
    
    RETURN NULL; -- Should not happen
END;
$$ LANGUAGE plpgsql;

Key Implementation Detail: The UPSERT

Notice the ON CONFLICT (id) DO UPDATE clause for the UPDATE operation. This is a critical detail for ensuring correctness in a live migration. It's possible for an UPDATE to occur on a row that your backfill script has not yet copied. A simple UPDATE orders_shadow ... would fail in that case. By using an UPSERT, we guarantee that the change is captured correctly, whether the row already exists in the shadow table or not.

Step 2: Create the Triggers

Now that we have our logic encapsulated in the function, we bind it to the orders table. We need to create one trigger for each event.

-- Trigger for INSERT operations
CREATE TRIGGER orders_insert_sync_trigger
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION sync_orders_to_shadow();

-- Trigger for UPDATE operations
CREATE TRIGGER orders_update_sync_trigger
AFTER UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION sync_orders_to_shadow();

-- Trigger for DELETE operations
CREATE TRIGGER orders_delete_sync_trigger
AFTER DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION sync_orders_to_shadow();

With these three triggers in place, any modification to the orders table will be automatically and atomically replicated to the orders_shadow table, including the necessary data transformation.

3. Triggers vs. Application-Level Synchronization

The article on the shadow table strategy you'll read next presents two approaches for data synchronization: database triggers and application-level logic.

As you've seen, we opted for the trigger-based approach. This is a significant architectural decision with trade-offs that are important to consider in the context of high-load systems:

For a zero-downtime migration where data integrity is paramount, the atomicity and transparency of database triggers often make them the superior choice, despite the "hidden logic" drawback. Your background in building resilient payment and trading systems, where transactional integrity is non-negotiable, likely makes the benefits of the trigger approach very clear.

Conclusion

In this lesson, we have successfully implemented the synchronization engine for our shadow table migration. You now know how to create robust PL/pgSQL trigger functions and bind them to a table to capture all data changes.

Key Takeaways:

• PostgreSQL uses a two-part system of trigger functions and trigger definitions.
• A single function using the TG_OP special variable can efficiently handle INSERT, UPDATE, and DELETE events.
• Using an UPSERT (INSERT ... ON CONFLICT) for the UPDATE trigger is a crucial technique to ensure correctness during a live backfill.
• Choosing between database triggers and application-level synchronization involves a trade-off between transactional integrity and explicitness of logic. For critical migrations, triggers are often the safer bet.

Preview of the Next Lesson

With our synchronization triggers active, any new data modifications are being captured. The next step is to handle the historical data. In our next lesson, we will focus on how to execute and monitor a data backfill process to populate a shadow table without locking the original table. We will build the script to copy the hundreds of millions of existing rows in safe, manageable, and observable batches.