PostgreSQL Transaction

A transaction is a single unit of work composed of one or more SQL statements. PostgreSQL transactions are ACID-compliant: Atomicity guarantees all-or-nothing execution; Consistency enforces data integrity rules; Isolation controls how concurrent transactions see each other’s changes; Durability ensures committed changes survive crashes. Every statement in PostgreSQL runs inside a transaction — when you don’t use BEGIN, each statement is implicitly committed on success.

Syntax

BEGIN;

-- one or more SQL statements

COMMIT;   -- save all changes permanently

To discard all changes since BEGIN:

BEGIN;

-- one or more SQL statements

ROLLBACK; -- undo everything

All three keyword forms are equivalent:

• BEGIN / BEGIN WORK / BEGIN TRANSACTION
• COMMIT / COMMIT WORK / COMMIT TRANSACTION
• ROLLBACK / ROLLBACK WORK / ROLLBACK TRANSACTION

Practical Example

Model an invoicing workflow where creating an invoice and inserting its line items must succeed together or not at all:

CREATE TABLE invoices (
  id          SERIAL PRIMARY KEY,
  client      VARCHAR(150) NOT NULL,
  total       NUMERIC(12, 2) NOT NULL DEFAULT 0,
  issued_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE invoice_lines (
  id          SERIAL PRIMARY KEY,
  invoice_id  INT NOT NULL REFERENCES invoices(id),
  description VARCHAR(200) NOT NULL,
  qty         INT NOT NULL,
  unit_price  NUMERIC(10, 2) NOT NULL
);

Insert the invoice and its lines as a single atomic unit:

BEGIN;

INSERT INTO invoices (client, total)
VALUES ('Redwood Consulting', 0)
RETURNING id;
-- assume returned id = 1

INSERT INTO invoice_lines (invoice_id, description, qty, unit_price)
VALUES
  (1, 'Strategy workshop',  1, 3500.00),
  (1, 'Follow-up session',  2,  750.00);

UPDATE invoices
SET total = (
  SELECT SUM(qty * unit_price)
  FROM invoice_lines
  WHERE invoice_id = 1
)
WHERE id = 1;

COMMIT;

If any statement fails — for example, the UPDATE references a non-existent invoice — issue ROLLBACK to undo everything:

BEGIN;

INSERT INTO invoices (client, total)
VALUES ('Coastal Designs', 0);

-- Oops — wrong table name in the next step
INSERT INTO invoice_linex (invoice_id, description, qty, unit_price)
VALUES (2, 'Brand audit', 1, 2000.00);
-- ERROR: relation "invoice_linex" does not exist

ROLLBACK;
-- Both the invoice and the (failed) line insert are undone

Savepoints

Savepoints let you roll back to a specific point within a transaction without abandoning all of the earlier work:

BEGIN;

INSERT INTO invoices (client, total)
VALUES ('Summit Group', 0)
RETURNING id;
-- id = 3

SAVEPOINT after_invoice;

INSERT INTO invoice_lines (invoice_id, description, qty, unit_price)
VALUES (3, 'Consulting retainer', 1, 5000.00);

-- Something looks wrong with this line — undo only it
ROLLBACK TO SAVEPOINT after_invoice;

-- Try again with corrected data
INSERT INTO invoice_lines (invoice_id, description, qty, unit_price)
VALUES (3, 'Consulting retainer', 1, 4500.00);

COMMIT;

Release a savepoint when it is no longer needed to free the associated resources:

RELEASE SAVEPOINT after_invoice;

Testing with Vela

Long-running or multi-step transactions can be risky to prototype directly on production data. Vela database branching lets you create an instant copy-on-write snapshot of your production database and run the full transaction sequence — including error and rollback paths — in an isolated environment. You can verify that the COMMIT path produces the correct final state and that ROLLBACK truly leaves the data unchanged before deploying the transaction logic to production.

Production Tips

• Keep transactions as short as possible. Long-running transactions hold locks, block autovacuum, and increase the risk of conflicts under concurrent load.
• Never leave a transaction open in application code without explicit error handling — always issue ROLLBACK in exception handlers.
• Use SAVEPOINT and ROLLBACK TO SAVEPOINT to implement partial retry logic within a transaction rather than rolling back the entire unit of work.
• Set idle_in_transaction_session_timeout in postgresql.conf (e.g., idle_in_transaction_session_timeout = '5min') to automatically cancel sessions that hold an open transaction without doing work.
• PostgreSQL supports transactional DDL — you can include CREATE TABLE, ALTER TABLE, and DROP TABLE inside a transaction and roll them back if needed.
• MVCC means readers never block writers in PostgreSQL: SELECT queries inside a transaction see a consistent snapshot of the data as of the transaction start, even while other transactions are committing changes.
• In application frameworks (psycopg2, JDBC, SQLAlchemy), confirm whether autocommit mode is enabled. If it is, BEGIN has no effect and you must use the framework’s transaction API instead.