Deepika Pusala

Posted on Nov 11

Transactions & Basic Indexing in PostgreSQL: COMMIT, ROLLBACK, EXPLAIN & SQLAlchemy ⚛️

#postgres #sql #database #backend

⚛️ Transactions & Basic Indexing

This is another VERY important backend concept, especially when you work with PostgreSQL, SQLAlchemy, FastAPI and real-world applications. 💪

Imagine you're building an e-commerce app 🛒. A user buys a product and your app must reduce its stock. Now imagine something breaks halfway:

✅ Product A's stock gets reduced
❌ Product B's stock does not get increased
💥 Your database now holds incorrect information

How do we prevent this? That's exactly where transactions, ROLLBACK, COMMIT and indexing come in.

We'll learn everything from absolute zero, with SQL examples, real-world analogies, PostgreSQL experiments and FastAPI connections. 🚀

📚 Table of Contents

What is a database transaction?
BEGIN, COMMIT and ROLLBACK
Atomicity and Consistency in practice
Hands-on: transfer stock between two products
What are database indexes?
Primary, Unique and Composite indexes
Other index types worth knowing
EXPLAIN and EXPLAIN ANALYZE
Hands-on: add an index and compare plans
Transactions in SQLAlchemy 2.0
Indexes in SQLAlchemy models and Alembic
Real-world usage
Common beginner mistakes
Quick revision sheet
Quiz

1️⃣ What is a database transaction?

Here's a products table:

products

product_id	product_name	stock
1	Laptop	50
2	Mobile	30
3	Headphones	100

A customer buys 5 laptops, so your app runs:

UPDATE products
SET stock = stock - 5
WHERE product_id = 1;

Laptop stock is now 45. But what if...

💥 the app crashes right after this update?
💥 the second query fails?
💥 the database connection drops?

We need a way to make a group of related operations succeed together or fail together. That mechanism is a transaction.

📦 Definition: a database transaction is a group of one or more SQL operations treated as a single logical unit of work.

The principle:

✅ If all operations succeed → save the changes
❌ If something fails → undo the changes made by that transaction

💸 Bank transfer example

Transfer ₹1,000 from Account A to Account B.

Before

Account	Balance
A	₹5,000
B	₹3,000

Two operations are required:

Subtract ₹1,000 from A
Add ₹1,000 to B

Expected after

Account	Balance
A	₹4,000
B	₹4,000

But if only the first operation succeeds:

Account	Balance
A	₹4,000
B	₹3,000

Where did ₹1,000 go? 😱 That's an inconsistent state. A transaction prevents it by making both operations succeed together, or neither.

🔄 How a transaction flows

BEGIN                      → start the transaction
  ├─ Operation 1           → subtract ₹1,000 from A
  ├─ Operation 2           → add ₹1,000 to B
  │
  ├─ all good?  → COMMIT   → save changes permanently ✅
  └─ failure?   → ROLLBACK → undo everything ❌

Three SQL commands drive this: BEGIN, COMMIT and ROLLBACK.

2️⃣ BEGIN, COMMIT and ROLLBACK

2.1 🚦 `BEGIN`: start a transaction

It tells the database: "I'm starting a transaction. Treat the upcoming operations as one unit."

BEGIN;

UPDATE products
SET stock = stock - 5
WHERE product_id = 1;

The database starts tracking the changes. They are not yet permanent.

2.2 💾 `COMMIT`: save the transaction

It tells the database: "Everything worked. Save all changes permanently."

BEGIN;

UPDATE products
SET stock = stock - 5
WHERE product_id = 1;

COMMIT;

Transaction begins
Stock is reduced by 5
The database commits
Changes become permanent ✅

2.3 ↩️ `ROLLBACK`: undo the transaction

It tells the database: "Something went wrong. Undo all changes made in this transaction."

BEGIN;

UPDATE products
SET stock = stock - 5
WHERE product_id = 1;

ROLLBACK;

Moment	Laptop stock
Initially	50
After the `UPDATE` (inside the transaction)	45
After `ROLLBACK`	50 ✅ (restored)

2.4 📊 The three commands

Command	Meaning
`BEGIN`	Start a transaction
`COMMIT`	Save changes
`ROLLBACK`	Undo uncommitted changes

⚠️ PostgreSQL detail: if a statement fails inside a transaction, PostgreSQL marks the transaction as aborted. Until you run ROLLBACK, further ordinary statements in that transaction are rejected.

3️⃣ Atomicity and Consistency in practice ⚛️

Remember ACID?

A: Atomicity
C: Consistency
I: Isolation
D: Durability

Today we focus on Atomicity and Consistency.

3.1 ⚛️ Atomicity

All operations in a transaction happen together, or none of them happen.

Think of an electrical switch: it's ON or OFF, never "half on". 💡

A bank transfer needs both:

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;

If the second query fails, the first must be undone. That's atomicity.

❌ WITHOUT atomicity
   Account A: ₹5,000 → updated
   Account B: ₹3,000 → FAILED
   Result: money disappeared from the system!

✅ WITH atomicity
   Account A: ₹5,000 → restored
   Account B: ₹3,000 → unchanged
   Result: the whole transaction was rolled back.

3.2 ✅ Consistency

A transaction must take the database from one valid state to another valid state.

Suppose your rule is: stock must never be negative. We enforce it with a constraint:

CHECK (stock >= 0)

Laptop stock is 10, and a customer tries to buy 15:

UPDATE products
SET stock = stock - 15
WHERE product_id = 1;

That would give 10 - 15 = -5. PostgreSQL rejects the operation because it violates the CHECK constraint. The database never enters an invalid state. 🛡️

3.3 ⚖️ Atomicity vs Consistency

Atomicity	Consistency
All or nothing	Valid state → valid state
Focuses on transaction execution	Focuses on database rules
Uses `COMMIT` and `ROLLBACK`	Uses constraints and correct transaction logic

🧠 Atomicity protects a transaction from partial completion. Consistency protects the correctness of the data.

4️⃣ Hands-on: transfer stock between two products 🧪

You have two products:

product_id	product	stock
1	Laptop	50
2	Mobile	30

Goal: move 10 units from Laptop to Mobile. After success: Laptop = 40, Mobile = 40. Then we'll deliberately fail halfway to see ROLLBACK in action.

Step 1️⃣: create the table

CREATE TABLE products (
    product_id   INTEGER PRIMARY KEY,
    product_name VARCHAR(100),
    stock        INTEGER CHECK (stock >= 0)
);

product_id → unique identifier
product_name → the name
stock → available quantity
CHECK (stock >= 0) → blocks negative stock

Step 2️⃣: insert sample data

INSERT INTO products
VALUES
    (1, 'Laptop', 50),
    (2, 'Mobile', 30);

SELECT * FROM products;

product_id	product_name	stock
1	Laptop	50
2	Mobile	30

Step 3️⃣: a successful transfer ✅

BEGIN;

-- Reduce Laptop stock by 10
UPDATE products
SET stock = stock - 10
WHERE product_id = 1;

-- Increase Mobile stock by 10
UPDATE products
SET stock = stock + 10
WHERE product_id = 2;

COMMIT;

Product	Before	After
Laptop	50	40
Mobile	30	40

Both operations succeeded, so we committed.

Step 4️⃣: deliberately fail midway 💥

First, reset the stock:

UPDATE products SET stock = 50 WHERE product_id = 1;
UPDATE products SET stock = 30 WHERE product_id = 2;

Now run this:

BEGIN;

-- Operation 1: reduce Laptop stock (this works)
UPDATE products
SET stock = stock - 10
WHERE product_id = 1;

-- Operation 2: deliberately cause an error (division by zero!)
UPDATE products
SET stock = stock / 0
WHERE product_id = 2;

-- Undo everything
ROLLBACK;

What happens:

Operation 1 executes inside the transaction, and Laptop stock is temporarily 40
Operation 2 fails with division by zero
PostgreSQL marks the transaction as aborted
We run ROLLBACK;, and the first operation is undone too ↩️

Product	Initial	After rollback
Laptop	50	50 ✅
Mobile	30	30 ✅

Atomicity demonstrated! 🎉

💡 Practical detail: after the error, you must send ROLLBACK; as a separate statement. The remaining statements in an aborted transaction can't run normally. (If you paste everything in one go in some clients, the error may stop the script before reaching ROLLBACK. Just run ROLLBACK; yourself afterwards.)

🧪 Bonus: break the CHECK constraint

BEGIN;

UPDATE products
SET stock = stock - 100   -- Laptop only has 50!
WHERE product_id = 1;
-- ERROR: new row violates check constraint

ROLLBACK;

This shows Consistency protecting you, and Atomicity cleaning up. 🛡️

5️⃣ What are database indexes? 📇

Now the second big concept: indexing.

Imagine a library with 1,00,000 books 📚. A student asks: "Where's the book Database Management Systems?"

❌ Without an index: you check Book 1, Book 2, Book 3... up to Book 1,00,000. You may examine almost every book.

✅ With an index: the library has an alphabetical index. You jump to D, find the location, done. Much faster!

📦 Definition: a database index is a data structure that helps the database find rows faster without scanning the entire table.

users

user_id	username	email
1	deepika	deepika@gmail.com
2	ishu	ishu@gmail.com
3	rahul	rahul@gmail.com
4	priya	priya@gmail.com

SELECT *
FROM users
WHERE email = 'ishu@gmail.com';

Without an index → PostgreSQL may scan the whole table
With a suitable index → PostgreSQL can locate the row efficiently

⚠️ Indexes have costs!

Every INSERT, UPDATE or DELETE may also need to update the indexes

Indexes consume storage space

So never create indexes on every column blindly.

6️⃣ Types of indexes you should know

6.1 🆔 Primary Key index

CREATE TABLE users (
    user_id  INTEGER PRIMARY KEY,
    username VARCHAR(100),
    email    VARCHAR(150)
);

PostgreSQL automatically creates a unique B-tree index for the primary key, because every user_id must be unique.

user_id	username
1	Deepika
2	Rahul
3	Priya

You can't insert another row with user_id = 1. The database rejects the duplicate.

Facts to remember:

✅ A primary key can't contain NULL
✅ A table has only one primary key constraint
✅ It can span one or multiple columns
✅ PostgreSQL auto-creates a unique index to enforce it

You generally don't need to manually index a primary key again.

6.2 🦄 Unique index

A unique index prevents duplicate values in the indexed column (or column combination).

CREATE UNIQUE INDEX idx_users_email
ON users(email);

Now:

INSERT INTO users
VALUES (5, 'john', 'deepika@gmail.com');

If that email already exists, PostgreSQL rejects the insert.

Useful for:

📧 Preventing duplicate email accounts
👤 Preventing duplicate usernames
🆔 Preventing duplicate employee IDs
📏 Enforcing uniqueness in business data

Primary Key	Unique Index
Identifies a row	Enforces uniqueness
Cannot contain `NULL`	Can allow `NULL` values
One per table	Multiple unique indexes possible
Automatically creates a unique index	Created explicitly or via a `UNIQUE` constraint

🧠 Nuance: PostgreSQL unique indexes normally allow multiple NULLs, since NULLs aren't considered equal. You can change that with NULLS NOT DISTINCT (PostgreSQL 15+).

A UNIQUE constraint also creates a unique index automatically:

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    email   VARCHAR(150) UNIQUE
);

No need for a second unique index on email.

6.3 🧩 Composite index

A composite index covers multiple columns together. It's especially important in backend work.

orders

order_id	user_id	status	amount
1	101	completed	500
2	102	pending	700
3	101	pending	300
4	103	completed	900

Your app often runs:

SELECT *
FROM orders
WHERE user_id = 101
  AND status = 'pending';

We filter on two columns, so we create one index on both:

CREATE INDEX idx_orders_user_status
ON orders(user_id, status);

🚨 Column order matters!

The index (user_id, status) is useful for:

WHERE user_id = 101

and:

WHERE user_id = 101 AND status = 'pending'

But it is generally less useful for:

WHERE status = 'pending'

Why? The index is organized by user_id first, then status.

📞 Analogy: a phone directory sorted by last name, then first name. Know the last name? Easy. Only know the first name? You'd basically have to read the whole book.

This is the leftmost-prefix principle. For PostgreSQL B-tree indexes, the leading column matters most, though the planner can sometimes still use the index in other ways.

When to use composite indexes

When your app repeatedly filters on multiple columns:

WHERE user_id = 10 AND status = 'active'
WHERE category_id = 5 AND created_at > '2026-01-01'
WHERE tenant_id = 20 AND email = 'abc@gmail.com'

The right column order depends on your actual query patterns.

7️⃣ Other index types worth knowing 🧰

Index type	Purpose
B-tree	General-purpose: equality, range and sorting (the default)
Hash	Equality comparisons
GIN	Searching inside arrays, JSONB and full-text data
GiST	Geometric, range and specialized searches
BRIN	Very large tables whose values correlate with physical order (like timestamps)

For now, focus on B-tree. It's what PostgreSQL uses by default.

CREATE INDEX idx_products_name
ON products USING BTREE (product_name);

Useful for:

SELECT * FROM products WHERE product_name = 'Laptop';

and for range comparisons on sortable values.

8️⃣ EXPLAIN: how does the database run your query? 🔍

You write:

SELECT *
FROM users
WHERE email = 'deepika@gmail.com';

You know what you want. But how does PostgreSQL find it? Does it:

Scan every row?
Use an index?
Use another strategy?

PostgreSQL has a query planner that picks an execution plan. EXPLAIN lets you see it.

📦 Definition: EXPLAIN displays the execution plan PostgreSQL intends to use for a query.

EXPLAIN
SELECT *
FROM users
WHERE email = 'deepika@gmail.com';

Possible output:

Seq Scan on users
  Filter: (email = 'deepika@gmail.com')

8.1 📖 Sequential Scan (Seq Scan)

PostgreSQL reads the table's rows one after another to find matches, like searching a notebook page by page. 📓

EXPLAIN
SELECT *
FROM users
WHERE username = 'deepika';

With no suitable index, PostgreSQL may choose a Seq Scan.

❓ Is a Seq Scan always bad? NO! For a table with only 10 rows, reading everything is cheaper than using an index. Even with an index, PostgreSQL may choose a Seq Scan if it estimates that reading most of the table is cheaper.

8.2 🎯 Index Scan

PostgreSQL uses an index to locate matching rows, then fetches those rows from the table.

CREATE INDEX idx_users_username
ON users(username);

EXPLAIN
SELECT *
FROM users
WHERE username = 'deepika';

Possible output:

Index Scan using idx_users_username on users
  Index Cond: (username = 'deepika')

This tells us PostgreSQL found a suitable index and used it. ✅

8.3 ⚡ Index Only Scan

Sometimes PostgreSQL can answer the query using only the index, without touching the table.

CREATE INDEX idx_users_email
ON users(email);

EXPLAIN
SELECT email
FROM users
WHERE email = 'deepika@gmail.com';

PostgreSQL may choose an Index Only Scan because the requested column is already in the index. (It may still check visibility information via the visibility map.)

8.4 📏 EXPLAIN ANALYZE

EXPLAIN → shows the estimated plan
EXPLAIN ANALYZE → actually runs the query and reports measured details

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'deepika@gmail.com';

Sample output:

Index Scan using idx_users_email on users
  (cost=0.15..8.17 rows=1 width=...)
  (actual time=0.020..0.022 rows=1 loops=1)
Planning Time: 0.100 ms
Execution Time: 0.050 ms

Term	Meaning
`cost`	Estimated planner cost (not milliseconds)
`rows`	Estimated number of returned rows
`width`	Estimated average row size in bytes
`actual time`	Measured time in milliseconds
`loops`	Times the plan node executed
`Planning Time`	Time taken to create the plan
`Execution Time`	Time taken to run the query

🚨 Very important: EXPLAIN ANALYZE executes the query! Be careful with UPDATE, DELETE and other data-changing statements. Wrap them in a transaction and roll back:

BEGIN;

EXPLAIN ANALYZE
UPDATE products
SET stock = stock - 1
WHERE product_id = 1;

ROLLBACK;

The update runs, then gets undone. 😎 (Transactions and EXPLAIN working together!)

9️⃣ Hands-on: add an index and compare plans 🧪

Let's create a big table so the difference is visible.

Step 1️⃣: create the table

CREATE TABLE users (
    user_id  SERIAL PRIMARY KEY,
    username VARCHAR(100),
    email    VARCHAR(150),
    city     VARCHAR(100)
);

Step 2️⃣: insert 1 lakh (100,000) rows

INSERT INTO users (username, email, city)
SELECT
    'user_' || i,
    'user_' || i || '@gmail.com',
    CASE
        WHEN i % 3 = 0 THEN 'Bangalore'
        WHEN i % 3 = 1 THEN 'Hyderabad'
        ELSE 'Chennai'
    END
FROM generate_series(1, 100000) AS i;

generate_series(1, 100000) is a PostgreSQL function that generates the numbers 1 to 100000. So we insert 1 lakh sample users. 🎉

Refresh planner statistics:

ANALYZE users;

Step 3️⃣: query WITHOUT an email index

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'user_50000@gmail.com';

You'll likely see:

Seq Scan on users
  Filter: (email = 'user_50000@gmail.com')
  Rows Removed by Filter: 99999

PostgreSQL scanned the table because there's no index on email.

Step 4️⃣: create the index

CREATE INDEX idx_users_email
ON users(email);

ANALYZE users;

Step 5️⃣: run the same query again

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'user_50000@gmail.com';

Now you'll likely see:

Index Scan using idx_users_email on users

📊 What to compare

	Before index	After index
Scan type	Seq Scan	Index Scan
Rows examined	Whole table	Just the match
Execution time	Slower	Much faster ⚡

Compare: scan type, estimated cost, actual execution time and rows scanned or returned.

⚠️ Heads up: PostgreSQL can still choose a Seq Scan after you create an index. That doesn't mean the index is broken. The planner picks whatever it estimates is cheaper.

For learning only, you can discourage Seq Scans in your current session:

SET enable_seqscan = OFF;
-- ...run EXPLAIN ANALYZE...
RESET enable_seqscan;

🚫 Never use this as a production performance fix. It's only a way to inspect alternative plans.

🎁 Bonus experiment: composite index

-- Before
EXPLAIN ANALYZE
SELECT * FROM users
WHERE city = 'Bangalore' AND username = 'user_300';

-- Create a composite index
CREATE INDEX idx_users_city_username
ON users(city, username);
ANALYZE users;

-- After
EXPLAIN ANALYZE
SELECT * FROM users
WHERE city = 'Bangalore' AND username = 'user_300';

Then try WHERE username = 'user_300' alone and see whether the planner still uses the composite index. This helps you feel the leftmost-prefix rule. 🧠

🔟 Transactions in SQLAlchemy 2.0 🐍

Now let's connect all this to your real stack:

⚡ FastAPI
🧪 SQLAlchemy 2.0
🐘 PostgreSQL
🔄 AsyncSession

In raw SQL we write BEGIN; COMMIT; ROLLBACK;. In SQLAlchemy we use transaction methods or context managers.

10.1 ✅ Basic transaction

from sqlalchemy import update


async def transfer_stock(session):
    async with session.begin():
        await session.execute(
            update(Product)
            .where(Product.id == 1)
            .values(stock=Product.stock - 10)
        )
        await session.execute(
            update(Product)
            .where(Product.id == 2)
            .values(stock=Product.stock + 10)
        )

async with session.begin():

Starts a transaction
If the block finishes normally → COMMIT
If an exception escapes the block → ROLLBACK

async with session.begin()  →  transaction starts
    UPDATE #1
    UPDATE #2
    no exception  →  COMMIT ✅
    exception     →  ROLLBACK ↩️

⚠️ Heads up: session.begin() raises an error if a transaction has already started on that session (for example, because you already ran a query on it). In FastAPI apps where a dependency provides the session, many projects instead use await session.commit() / await session.rollback() explicitly, or session.begin_nested(). Follow whichever pattern your project already uses, consistently.

10.2 💥 Deliberately failing

async def transfer_stock(session):
    async with session.begin():
        await session.execute(
            update(Product)
            .where(Product.id == 1)
            .values(stock=Product.stock - 10)
        )

        raise Exception("Something went wrong!")

        await session.execute(          # never reached
            update(Product)
            .where(Product.id == 2)
            .values(stock=Product.stock + 10)
        )

What happens:

Transaction begins
First stock update executes
An exception occurs
The second update is never reached
SQLAlchemy rolls back the transaction
The first update is undone ↩️

That's atomicity inside your FastAPI backend! 🎉

🚨 Important: the exception must escape the async with block for the automatic rollback. If you catch and swallow it inside the block, the context manager sees a normal exit and commits. And never keep using a failed transaction without rolling it back.

10.3 🧰 The explicit style (very common in FastAPI)

async def create_post(session, user_id: int, caption: str):
    try:
        post = Post(user_id=user_id, caption=caption)
        session.add(post)
        await session.flush()          # get post.id without committing

        session.add(PostMetadata(post_id=post.id, language="en"))

        await session.commit()         # ✅ save everything
        return post
    except Exception:
        await session.rollback()       # ↩️ undo everything
        raise

flush() sends pending SQL to the database inside the transaction, but doesn't commit
commit() makes it permanent
rollback() undoes uncommitted work

1️⃣1️⃣ Adding indexes with SQLAlchemy models and Alembic 🧱

from sqlalchemy import Integer, String
from sqlalchemy.orm import Mapped, mapped_column


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String(100))
    email: Mapped[str] = mapped_column(String(150), unique=True)

primary_key=True → creates the primary key constraint (and its index)
unique=True → creates a uniqueness constraint (and its unique index)

Creating a normal index

The simplest way is index=True on the column:

username: Mapped[str] = mapped_column(String(100), index=True)

Or declare it explicitly in __table_args__:

from sqlalchemy import Index


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(String(100))

    __table_args__ = (
        Index("idx_users_username", "username"),
    )

Creating a composite index

class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(Integer)
    status: Mapped[str] = mapped_column(String(20))

    __table_args__ = (
        Index("idx_orders_user_status", "user_id", "status"),
    )

💡 A bare Index("name", User.username) statement on its own line only works when the columns are already attached to a table. Putting indexes inside __table_args__ is the cleanest, most reliable pattern.

🧬 Using Alembic migrations

In real projects, schema changes go through Alembic migrations:

alembic revision --autogenerate -m "add user indexes"

Review the generated file, then apply it:

alembic upgrade head

You can also write a migration by hand:

from alembic import op


def upgrade():
    op.create_index(
        "idx_users_username",
        "users",
        ["username"],
    )


def downgrade():
    op.drop_index(
        "idx_users_username",
        table_name="users",
    )

upgrade() → applies the change
downgrade() → reverses it

Schema changes should be version-controlled just like application code. 📜

1️⃣2️⃣ Where are transactions and indexes used in real life? 🌍

Application	Transactions	Indexes
🏦 Banking	Money transfers	Account ID lookup
🛒 E-commerce	Stock and order updates	Product ID, category
📸 Instagram-like app	Creating a post with related records	User ID, post ID, `created_at`
🍕 Food delivery	Order creation + payment records	Restaurant ID, order status
🏥 Hospital management	Updating patient records	Patient ID
🔐 Authentication	Creating sessions	Email, token hash

📸 In a social-feed backend

When creating a post:

Insert the post record
Insert media records
Insert related metadata
Commit

If any step fails, you want the entire database operation rolled back.

And if you frequently run:

SELECT *
FROM posts
WHERE user_id = 10
ORDER BY created_at DESC;

a composite index designed around that query helps:

CREATE INDEX idx_posts_user_created
ON posts(user_id, created_at DESC);

✅ Always confirm with EXPLAIN ANALYZE on realistic data. Don't guess.

1️⃣3️⃣ Common beginner mistakes ⚠️

❌ Mistake 1: thinking every SQL statement is automatically a big safe transaction.
Without an explicit BEGIN, PostgreSQL treats each statement as its own transaction (autocommit). Database libraries often manage transactions for you, so know what yours does.

❌ Mistake 2: thinking ROLLBACK can undo everything.
It only undoes uncommitted changes in the current transaction. Once committed, an ordinary rollback can't reverse it.

❌ Mistake 3: indexing every column.
Indexes consume storage and slow down writes.

❌ Mistake 4: thinking an index always makes queries faster.
The planner may choose a Seq Scan if it's cheaper.

❌ Mistake 5: thinking COMMIT covers external actions.
A database transaction can't roll back an email, an external payment gateway call or an already-uploaded image. Those need extra patterns such as compensating actions or the transactional outbox pattern.

❌ Mistake 6: confusing consistency with atomicity.

Atomicity = all or nothing
Consistency = valid data before and after

1️⃣4️⃣ Quick revision sheet 🧾

Transactions

Command	Purpose
`BEGIN`	Start a transaction
`COMMIT`	Save changes
`ROLLBACK`	Undo uncommitted changes

ACID

⚛️ Atomicity: all or nothing
✅ Consistency: maintain valid database states
🚦 Isolation: concurrent transactions don't interfere incorrectly
💾 Durability: committed changes survive failures covered by the database's durability guarantees

Indexes

🆔 Primary key index: created automatically
🦄 Unique index: prevents duplicate indexed values
🧩 Composite index: indexes multiple columns together (order matters!)
🌳 B-tree: PostgreSQL's general-purpose default

EXPLAIN

📖 Seq Scan: reads rows sequentially
🎯 Index Scan: uses an index to find rows
⚡ Index Only Scan: answers from the index alone
📏 EXPLAIN ANALYZE: executes and measures the query

1️⃣5️⃣ Test your understanding! 🧪

Answer without scrolling up. (Answers are at the bottom.)

1. You subtract ₹1,000 from Account A, but adding it to Account B fails. What should happen?

A) Commit the first operation
B) Roll back the entire transaction
C) Ignore the error

2. Which SQL command permanently saves a successful transaction?

A) BEGIN
B) ROLLBACK
C) COMMIT

3. What does CHECK (stock >= 0) help ensure?

A) Stock cannot become negative
B) Stock is always increasing
C) Every product has a unique name

4. Which index suits a frequent query filtering by user_id and status together?

A) Composite index
B) Only a primary key index
C) No index can do this

5. What does EXPLAIN ANALYZE do?

A) Only displays the SQL query
B) Executes the query and reports actual execution details
C) Creates an index automatically

👀 Click to reveal answers

B: roll back the entire transaction (atomicity)
C: COMMIT
A: stock can't go negative (consistency)
A: a composite index on (user_id, status)
B: it runs the query and measures real timings

🎯 What to remember as a backend developer

When you build a real application, you use these concepts together:

Transactions protect multiple related database operations
COMMIT saves successful operations
ROLLBACK undoes failed operations
Atomicity prevents partial transactions
Consistency ensures database rules are maintained
Indexes help retrieve records efficiently
EXPLAIN ANALYZE helps investigate query performance
SQLAlchemy transactions manage database operations safely in FastAPI
Alembic manages index and schema changes over time

💡 One final distinction: transactions protect the correctness of your database operations, while indexes help the database retrieve information efficiently. They solve two different problems, and both are essential for reliable, scalable backend systems.

⚛️ Transactions & Basic Indexing

📚 Table of Contents

1️⃣ What is a database transaction?

💸 Bank transfer example

🔄 How a transaction flows

2️⃣ BEGIN, COMMIT and ROLLBACK

2.1 🚦 BEGIN: start a transaction

2.2 💾 COMMIT: save the transaction

2.3 ↩️ ROLLBACK: undo the transaction

2.4 📊 The three commands

3️⃣ Atomicity and Consistency in practice ⚛️

3.1 ⚛️ Atomicity

3.2 ✅ Consistency

3.3 ⚖️ Atomicity vs Consistency

4️⃣ Hands-on: transfer stock between two products 🧪

Step 1️⃣: create the table

Step 2️⃣: insert sample data

Step 3️⃣: a successful transfer ✅

Step 4️⃣: deliberately fail midway 💥

🧪 Bonus: break the CHECK constraint

5️⃣ What are database indexes? 📇

6️⃣ Types of indexes you should know

6.1 🆔 Primary Key index

6.2 🦄 Unique index

6.3 🧩 Composite index

🚨 Column order matters!

When to use composite indexes

7️⃣ Other index types worth knowing 🧰

8️⃣ EXPLAIN: how does the database run your query? 🔍

8.1 📖 Sequential Scan (Seq Scan)

8.2 🎯 Index Scan

8.3 ⚡ Index Only Scan

8.4 📏 EXPLAIN ANALYZE

9️⃣ Hands-on: add an index and compare plans 🧪

Step 1️⃣: create the table

Step 2️⃣: insert 1 lakh (100,000) rows

Step 3️⃣: query WITHOUT an email index

Step 4️⃣: create the index

Step 5️⃣: run the same query again

📊 What to compare

🎁 Bonus experiment: composite index

🔟 Transactions in SQLAlchemy 2.0 🐍

10.1 ✅ Basic transaction

10.2 💥 Deliberately failing

10.3 🧰 The explicit style (very common in FastAPI)

1️⃣1️⃣ Adding indexes with SQLAlchemy models and Alembic 🧱

Creating a normal index

Creating a composite index

🧬 Using Alembic migrations

1️⃣2️⃣ Where are transactions and indexes used in real life? 🌍

📸 In a social-feed backend

1️⃣3️⃣ Common beginner mistakes ⚠️

1️⃣4️⃣ Quick revision sheet 🧾

Transactions

ACID

Indexes

EXPLAIN

1️⃣5️⃣ Test your understanding! 🧪

🎯 What to remember as a backend developer

🙌 Enjoyed this post?

postgres #sql #database #backend #sqlalchemy

2.1 🚦 `BEGIN`: start a transaction

2.2 💾 `COMMIT`: save the transaction

2.3 ↩️ `ROLLBACK`: undo the transaction