Reading Postgres EXPLAIN plans: Scan Types & Fixes

Starting a Transaction

A transaction wraps multiple database operations into a single unit of work. If one operation fails, the entire transaction is rolled back, preventing partial updates that could leave data in an inconsistent state.

Connect to the bank database:

sudo user postgres psql
\c bank

View the accounts table:

SELECT * FROM accounts;

The table contains Carla, Alice, and Bob. To transfer $200 from Alice to Bob, begin a transaction:

BEGIN;

The prompt shows an asterisk (*), indicating you are inside a transaction.

Updating Accounts Within a Transaction

Deduct $200 from Alice's account:

UPDATE accounts SET balance = balance - 200 WHERE id = 'ACC001';

Add $200 to Bob's account:

UPDATE accounts SET balance = balance + 200 WHERE id = 'ACC002';

Check the current state (still not permanent):

SELECT * FROM accounts;

Committing the Transaction

Make the changes permanent by committing:

COMMIT;

The asterisk disappears from the prompt. Verify the permanent change:

SELECT * FROM accounts;

Rolling Back a Failed Transaction

If an error occurs during a transaction, you cannot commit. Instead, roll back to the stable state as if the transaction never happened.

Start a new transaction:

BEGIN;

Update Alice's account:

UPDATE accounts SET balance = balance - 200 WHERE id = 'ACC001';

Simulate an error by rolling back:

ROLLBACK;

Check that Alice's balance is unchanged:

SELECT * FROM accounts;

ACID Properties

Every PostgreSQL transaction satisfies ACID:

• Atomic: If one part of the transaction fails, the whole transaction fails.
• Consistent: Constraints (check, unique, foreign key, primary key) always hold.
• Isolation: Uncommitted changes are not visible to other transactions.
• Durable: After a commit, the change persists on disk.

Isolation in Action

Open a second terminal and connect to the same database:

psql
\c bank

In the first terminal, begin a transaction and update Alice's balance without committing:

BEGIN;
UPDATE accounts SET balance = balance - 67 WHERE id = 'ACC001';
SELECT * FROM accounts;

The first terminal shows Alice's updated balance (e.g., 933).

In the second terminal, query the accounts table:

SELECT * FROM accounts;

Alice's balance remains unchanged because the uncommitted draft is isolated.

Commit the transaction in the first terminal:

COMMIT;

Now query again in the second terminal:

SELECT * FROM accounts;

The change is visible and durable.