⚛️ 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

1. What is a database transaction?
2. BEGIN, COMMIT and ROLLBACK
3. Atomicity and Consistency in practice
4. Hands-on: transfer stock between two products
5. What are database indexes?
6. Primary, Unique and Composite indexes
7. Other index types worth knowing
8. EXPLAIN and EXPLAIN ANALYZE
9. Hands-on: add an index and compare plans
10. Transactions in SQLAlchemy 2.0
11. Indexes in SQLAlchemy models and Alembic
12. Real-world usage
13. Common beginner mistakes
14. Quick revision sheet
15. Quiz

1️⃣ What is a database transaction?

Here's a products table:

products

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

Two operations are required:

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

Expected after

But if only the first operation succeeds:

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; 

1. Transaction begins
2. Stock is reduced by 5
3. The database commits
4. 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; 

2.4 📊 The three commands

⚠️ 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 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:

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; 

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; 

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:

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

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

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.

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

🧠 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

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 🧰

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:

1. Scan every row?
2. Use an index?
3. 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 

🚨 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

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)