DEV Community

Sajjad Rahman
Sajjad Rahman

Posted on

Hands-On ACID in PostgreSQL : Part-1

#postgressql #dataengineering #aws #systemdesign

This lab is for anyone who knows just enough PostgreSQL to open psql, but wants to actually **see* how transactions, rollback, isolation, and durability work in real life.*

🧠 Step 0: If You’ve Never Used PostgreSQL Before…

Before starting this lab, make sure you know how to open psql and create a simple database. If not, start here first: A Beginner’s Journey with PostgreSQL

That guide shows you:

  • How to connect using psql, create your first database and table , Common beginner errors (and how to fix them)

Once you can run:

psql -U postgres
Enter fullscreen mode Exit fullscreen mode

and see the postgres=# prompt — you’re ready to start this ACID Lab!

What You’ll Learn

By the end of this lab, you’ll see and understand all 4 parts of the ACID model:

Property Meaning You’ll See It In Action
Atomicity All-or-nothing transactions Rollback demo
Consistency Constraints keep data valid Check constraint demo
Isolation Transactions don’t interfere Two sessions test
Durability Committed data stays saved After restart check

Want to more about acid

⚙️ Lab Setup

Assumptions

  • PostgreSQL is installed and psql works.

  • We will use two terminal windows:

    • Session A → to make changes
    • Session B → to observe from another user

  • User: postgres

✅ Create Database and Table

Run this once in Git Bash terminal:

psql -U postgres
CREATE DATABASE acid_lab;
Enter fullscreen mode Exit fullscreen mode

Database Created

Now enter the database

postgres=# \c acid_lab
acid_lab=#
Enter fullscreen mode Exit fullscreen mode

output

Enter DB

Then in psql:

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  balance NUMERIC(12,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (name, balance) VALUES
('A', 1000.00),
('B', 1000.00);

SELECT * FROM accounts ORDER BY id;
Enter fullscreen mode Exit fullscreen mode

Expected Output

 id | name | balance
----+------+---------
  1 | A    | 1000.00
  2 | B    | 1000.00
(2 rows)
Enter fullscreen mode Exit fullscreen mode

table create

📸 Caption: “Initial data — both accounts start at 1000 and 1500”

🧩 1. Atomicity — The All-or-Nothing Rule

Open Session A and start a transaction:

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance - 200 WHERE id = 2;
SELECT * FROM accounts;
Enter fullscreen mode Exit fullscreen mode

Output inside Session A:

 1 | A | 800.00
 2 | B | 1300.00
Enter fullscreen mode Exit fullscreen mode

Now open Session B (new terminal):

SELECT * FROM accounts;
Enter fullscreen mode Exit fullscreen mode

Output in Session B (unchanged):

 1 | A | 1000.00
 2 | B | 1500.00
Enter fullscreen mode Exit fullscreen mode

uncommited changes

📸“Uncommitted changes are invisible to other sessions.”

Now rollback in Session A:

ROLLBACK;
SELECT * FROM accounts;
Enter fullscreen mode Exit fullscreen mode

Output:

 1 | A | 1000.00
 2 | B | 1500.00
Enter fullscreen mode Exit fullscreen mode

roll back
📸 “Atomicity — no partial changes.”

Instead of Rollback, if I write COMMIT the change can see in the both terminals

COMMIT
📸 “Atomicity — Fully Changes.”

Next Part we will see about the

🌐 Connect & Share

If this lab helped you, leave a comment or share your screenshots —
Let’s connect and keep building together 💬

🐙 GitHub – @sajjadrahman56
💼 LinkedIn – Connect with me
🐦 X (Twitter) – @sajjadrahman56
📺 YouTube – Tutorials & tips for data engineers

Top comments (0)