Postgres Rollback Explained

When something goes wrong in Postgres, "roll it back" can mean three very different things depending on how far the change has traveled. If you're still inside the transaction, a rollback is instant and lossless. If you've already committed, you're into recovery territory, and the right answer depends on how much damage you did and when you noticed. This post walks through the three approaches Postgres gives you, and where each one helps and where it gets in your way.

Built-in Transaction Rollback and SAVEPOINT#

Postgres transactions let you roll back every change inside a transaction block. For finer control, SAVEPOINT lets you set markers within a transaction so you can undo part of it without throwing away the work you did earlier.

Using SAVEPOINT#

Create a savepoint:

SAVEPOINT my_savepoint;

Roll back to it:

ROLLBACK TO SAVEPOINT my_savepoint;

A practical pattern for risky operations:

BEGIN;

-- Step 1: safe operations
INSERT INTO employees (name, department) VALUES ('Alice', 'Engineering');

SAVEPOINT sp_batch;

-- Step 2: risky operations
INSERT INTO employees (name, department) VALUES ('Bob', 'Marketing');
-- Oops, Bob is actually in Sales

-- Roll back only the risky step
ROLLBACK TO SAVEPOINT sp_batch;

-- Step 3: continue with corrected operation
INSERT INTO employees (name, department) VALUES ('Bob', 'Sales');

COMMIT;

The savepoint stays usable after you roll back to it. One thing to watch: any savepoints you created after it are not just released, they're destroyed and invalidated by the rollback.

Limitations#

• Some DDL statements (CREATE DATABASE, DROP DATABASE, CREATE TABLESPACE, DROP TABLESPACE) can't run inside transactions at all.
• It only works while the transaction is open. Once you COMMIT, ROLLBACK can't undo anything.

Point-In-Time Recovery (PITR)#

PITR restores a database to a specific moment in time using continuous WAL archiving. Postgres's Write-Ahead Log records every change, and PITR combines a base backup with the archived WAL files to replay changes up to the moment you want.

Cloud Provider Support#

The major cloud providers all give you a one-click PITR experience:

• AWS RDS for Postgres: Restore to point in time via Console/CLI/API
• Google Cloud SQL: PITR from the console interface
• Azure Database for Postgres: Portal "Restore" to latest or a chosen restore point

Named Restore Points#

You can create targeted recovery points so you don't have to hunt for the right timestamp later:

-- Before risky migration
SELECT pg_create_restore_point('pre_migration_2025_09_04');

When you recover, point at recovery_target_name = 'pre_migration_2025_09_04' instead of guessing the moment things went wrong.

Advantages#

• Works no matter what, whether the transaction was committed or not.
• Recovers from mistakes you discover hours or days later, long after a transaction rollback is off the table.

Limitations#

• It operates at the cluster level. PITR rolls back the entire database, not a single table or row.
• That makes it a heavyweight tool, and a poor fit for small, isolated changes.
• Rolling back one bad UPDATE this way also undoes every valid change that came after it.

Cross-Transaction DML Rollback (Compensating Changes)#

Once a bad UPDATE, DELETE, or INSERT is committed, transaction rollback is gone and PITR is overkill. What you actually want is compensating DML: new statements that put the previous values back. Think of it as git revert for data.

Manual Compensating DML Example#

Say you accidentally ran:

UPDATE accounts SET status = 'inactive' WHERE org_id = 42;

If you keep an audit or history table, you can compensate from it:

-- Revert to last known status per row
UPDATE accounts a
SET status = h.old_status
FROM account_status_history h
WHERE a.id = h.account_id
  AND h.org_id = 42
  AND h.changed_at = (
       SELECT max(h2.changed_at)
       FROM account_status_history h2
       WHERE h2.account_id = a.id
         AND h2.changed_at < :mistake_time
     );

That looks tidy on a single column. In a real system you also have to think about sequences, cascades, triggers, and other side effects, which is where hand-writing these scripts gets error-prone.

Bytebase Solution#

Bytebase offers point-and-click rollback for exactly this case:

1. Prior Backup: Before a DML statement runs, Bytebase captures the affected rows and stores them in a dedicated bbdataarchive schema.
2. 1-Click Rollback: It generates and runs the rollback script for you.

Workflow Benefits#

• No hand-written rollback scripts to get wrong.
• The rollback goes through the same review and approval process as any other change.
• You can roll back across multiple databases in one go.
• The whole thing stays controlled and auditable.

Choosing the Right Rollback Method#

With the three approaches in hand, here's how they line up against the situations you'll actually hit:

The short version: transactions and SAVEPOINT are how you avoid the mistake in the first place, so reach for them before you run anything risky. PITR is the safety net for when the blast radius is unclear or the damage is large, and it's everywhere and cloud-friendly. And compensating DML, whether you write it by hand or let Bytebase's workflow handle it, is for the small, precise fixes after a commit where PITR would be far too blunt.