# Postgres Rollback Explained

> A walk through the three ways to roll back changes in Postgres: transactions and SAVEPOINT, Point-in-Time Recovery, and compensating DML, and when each one fits.

Adela | 2025-09-04 | Source: https://www.bytebase.com/blog/postgres-rollback/

---

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:

```sql
SAVEPOINT my_savepoint;
```

Roll back to it:

```sql
ROLLBACK TO SAVEPOINT my_savepoint;
```

A practical pattern for risky operations:

```sql
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:

```sql
-- 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:

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

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

```sql
-- 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](https://docs.bytebase.com/change-database/rollback-data-changes) 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.
1. **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:

| Situation                             | Best Tool                                 | Why                                                  |
| ------------------------------------- | ----------------------------------------- | ---------------------------------------------------- |
| Still in session, haven't committed   | **Transaction rollback / SAVEPOINT**      | Instant, lossless; keep good work, discard bad chunk |
| Committed a small wrong UPDATE/DELETE | **Cross-transaction rollback (Bytebase)** | Surgical fix; no cluster restore                     |
| Dropped table / mass data corruption  | **PITR**                                  | Ubiquitous, reliable; recovers to clean time point   |

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.