PostgreSQL savepoints mark positions inside a transaction that can be used for partial rollback. They can help a workflow recover from a local failure while preserving earlier work in the same transaction. They do not create independently committed subtransactions or undo an email, API call, or other external effect.
The useful question is whether partial success is valid for the business operation. This guide explains savepoint lifecycle, error recovery, application drivers, and tests so a database feature does not quietly change the meaning of an all-or-nothing workflow.
Decide whether partial success is allowed
Describe the operation and its invariant. Importing several independent records may permit rejecting one record while accepting others. Transferring value between related accounts may require the whole operation to succeed or fail together.
Do not add a savepoint simply to silence an error that should abort the transaction. The application must know which failures are expected and whether continuing preserves a valid state. Unexpected programming errors should remain visible.
Record the user-facing outcome. A batch with partial acceptance should identify the accepted and rejected scope through an appropriate nonsecret result. Returning generic success after discarding part of the requested work is misleading.
Understand the transaction boundary
A savepoint exists within a transaction. Rolling back to it discards relevant work performed afterward while keeping earlier transaction work available according to PostgreSQL’s semantics. None of the remaining work becomes committed merely because a savepoint is released.
The outer commit remains the durable acceptance boundary. If the transaction later rolls back entirely, earlier surviving work is also discarded. An application should not send a completed-success message for a savepoint release as if it were a final commit.
Keep transaction duration reasonable. Savepoints do not make a long transaction free of resource, locking, or maintenance consequences. Avoid holding a transaction open while waiting for unrelated user interaction or slow external dependencies.
Use names and scope that are understandable
Create the savepoint immediately before the operation whose failure may be recoverable. Keep its lifetime narrow and use a clear naming convention. A large transaction with many forgotten savepoints is harder to inspect and reason about.
BEGIN;
SAVEPOINT optional_step;
SELECT 1;
ROLLBACK TO SAVEPOINT optional_step;
RELEASE SAVEPOINT optional_step;
COMMIT;This harmless syntax example shows lifecycle, not a production partial-import implementation. The rollback has no important business effect here because the selected statement only reads a constant. Real work needs explicit acceptance rules.
After rollback to a savepoint, that savepoint remains defined according to PostgreSQL’s documented behavior. Releasing or rolling back can affect later savepoints. Read the nesting rules rather than assuming each name is an independent persistent bookmark.
Recover from database errors correctly
A statement error can leave a transaction in a failed state. Rolling back to an appropriate savepoint can provide a supported recovery path when one was established before the operation. The client must perform the recovery before issuing further work that assumes a healthy transaction.
Inspect structured error information through the driver where available. Distinguish expected data rejection from connection loss, transaction-level failures, and unexpected errors. Not every exception should be handled by skipping one row.
Some failures require retrying the complete transaction or abandoning the connection. Do not use a savepoint as a universal substitute for serialization-failure handling or uncertain-commit reconciliation. The error’s semantics determine the right boundary.
Review ORM and driver behavior
Libraries can expose savepoints through APIs described as nested transactions. Read what that API actually guarantees. It may use a database savepoint without providing a separately committed inner transaction.
Check session state, pending changes, automatic flushes, and exception handling. An ORM may issue SQL before the point you expected or require its own rollback method to restore usable client state. Test the library path, not only handwritten SQL in an administrative console.
Keep the actual database connection consistent for the transaction. Pooling should not move pieces of one transaction across unrelated sessions. Review the deployed connection and pool mode when the application relies on transaction-local state.
Separate database rollback from external actions
A savepoint cannot recall a sent notification or reverse a remote payment automatically. If the optional database step and external effect must agree, use an appropriate durable workflow, outbox, idempotency, or reconciliation design.
Avoid making irreversible calls before the application knows which database work will commit. Conversely, an external operation may have an uncertain outcome even when the local transaction fails. Preserve enough state to resolve that uncertainty safely.
Document which actions are reversible and which are not. A cleanup callback that tries to compensate for an external action is not equivalent to database atomicity. Its own failures and permissions need review.
Test successful, rejected, and interrupted work
Create synthetic cases that verify work before the savepoint, work afterward, rollback to it, and the outer transaction outcome. Check final database state from another appropriate connection after commit or rollback.
Test an expected constraint failure and confirm the application recovers both database and client-library state. Then test an unexpected failure that should abort the whole operation. These cases demonstrate that the policy distinguishes partial acceptance from corruption.
Include connection loss and final commit uncertainty according to the application’s requirements. A savepoint being present does not answer whether the final transaction became durable when the response was lost.
A practical import workflow
Suppose an approved import contains independent optional descriptions. Each item has a defined validation policy, and the product permits recording valid items while reporting invalid ones. A narrow savepoint can isolate a rejected item while the outer transaction preserves accepted work.
At the end, the application commits once and returns a clear accepted/rejected summary. It does not publish external notifications for rejected items, and any accepted-item notifications follow the approved transaction-to-message workflow.
If the import instead requires all records to establish one consistent configuration, partial rollback may be the wrong design. The business invariant should choose the transaction boundary, not the convenience of continuing after errors.
Keep the recovery policy visible
Document expected recoverable errors, savepoint scope, outer commit behavior, and external effects. Review the policy when the schema or driver changes. A new constraint or automatic flush can alter where failure occurs.
Keep diagnostics proportionate. Record useful error categories and item identifiers without exporting full sensitive rows into a support ticket. Partial recovery should improve reliability without widening data exposure.
Frequently asked questions
Does RELEASE SAVEPOINT commit the inner work?
No. The outer transaction still controls commitment. A later full rollback can discard the surviving work.
Can a savepoint undo an external API call?
No. External effects need their own coordinated workflow and recovery semantics.
Where can I check exact lifecycle behavior?
Read the PostgreSQL transaction tutorial. For database-enforced rules that may reject writes, see our PostgreSQL constraints guide.
