PostgreSQL Savepoints: Nested Transactions

Tip: Create Savepoint

BEGIN;
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

Gotcha: Rollback to Savepoint

ROLLBACK TO SAVEPOINT my_savepoint;

Undoes everything after the savepoint, keeps the transaction open.

Tip: Release Savepoint

RELEASE SAVEPOINT my_savepoint;

Removes the savepoint. Can't rollback to it after release.

Gotcha: Multiple Savepoints

SAVEPOINT sp1;
-- operations
SAVEPOINT sp2;
-- more operations
ROLLBACK TO sp1;

Rolling back to sp1 also removes sp2.

Tip: Laravel Savepoints

DB::transaction(function () {
    DB::statement('SAVEPOINT my_savepoint');
    try {
        // risky operation
    } catch (\Exception $e) {
        DB::statement('ROLLBACK TO SAVEPOINT my_savepoint');
    }
});

Gotcha: Savepoints in ORMs

Laravel simulates nested transactions with savepoints. Not true nested transactions.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Savepoints in PostgreSQL are essential for partial rollback within a transaction. I've used them in Laravel's DB::transaction() nesting — each nested call creates a savepoint, not a real subtransaction. The performance implication: savepoints are lightweight but have overhead proportional to the number of modifications since the savepoint. For deeply nested transactions with many modifications, the savepoint rollback can be surprisingly slow.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)