# Week 09 – Task 2: PostgreSQL Transactions and Basic Indexing

In Task 1, we designed the e-commerce database and learned how its tables are connected. In Task 2, we build on that same database and focus on two important PostgreSQL topics:

1. Transactions – making sure a group of database changes succeeds or fails together.
2. Indexes – helping PostgreSQL find data efficiently.

We'll use a stock-transfer example to understand transactions and a stock_movements table to explore indexes.

---

1. Before starting

Task 2 depends on the schema created in Week 09 Task 1. Make sure you have already run:

createdb ecommerce_db
psql -d ecommerce_db -f week-09/Task-01/ecommerce-schema.sql

Then run Task 2:

psql -d ecommerce_db -f week-09/Task-02/transactions-demo.sql

Use a practice database. The Task 1 setup recreates the dedicated ecommerce schema, and Task 2 rebuilds its own stock_movements exercise table when it runs.

2. What is a database transaction?

A transaction is a group of SQL operations treated as one unit.

Imagine a store transfers 10 units from one product to another stock record. There are two updates:

• Reduce the first product's stock by 10.
• Increase the second product's stock by 10.

Both updates should succeed together. If one fails, we should not leave the database with only half of the transfer completed.

PostgreSQL gives us three important transaction commands:

Command	What it does
BEGIN	Starts a transaction
COMMIT	Saves all changes made in the transaction
ROLLBACK	Cancels the transaction's changes

Basic transaction example

BEGIN;

UPDATE products
SET stock_quantity = stock_quantity - 5
WHERE product_id = 101;

UPDATE products
SET stock_quantity = stock_quantity + 5
WHERE product_id = 102;

COMMIT;

The two updates are saved together when PostgreSQL reaches COMMIT.

If something goes wrong before the commit, use ROLLBACK instead.

3. Adding stock quantity to products

Task 2 adds a stock_quantity column to the existing products table:

ALTER TABLE ecommerce.products
ADD COLUMN IF NOT EXISTS stock_quantity INTEGER NOT NULL DEFAULT 100
CHECK (stock_quantity >= 0);

Let's understand each part:

• ALTER TABLE changes an existing table.
• ADD COLUMN adds a new column.
• IF NOT EXISTS avoids an error if the column is already present.
• INTEGER stores whole numbers.
• NOT NULL means stock cannot be unknown.
• DEFAULT 100 gives existing and new rows a default stock value when no value is supplied.
• CHECK (stock_quantity >= 0) prevents negative stock.

The CHECK constraint is important. A product cannot have -5 units in stock.

4. ROLLBACK – what happens when a transaction fails?

Let's try to transfer 10 units from product 101 to product 102.

BEGIN;

UPDATE ecommerce.products
SET stock_quantity = stock_quantity - 10
WHERE product_id = 101;

UPDATE ecommerce.products
SET stock_quantity = stock_quantity + 10
WHERE product_id = 102;

-- This deliberately breaks the CHECK constraint.
UPDATE ecommerce.products
SET stock_quantity = -1
WHERE product_id = 101;

ROLLBACK;

The first two updates are valid. The third update tries to set stock to -1, which violates the constraint.

PostgreSQL reports a constraint error and marks the transaction as failed. The ROLLBACK cancels the entire transaction, including the first two updates.

Important: The error in this exercise is intentional. It is there to show that a failed transaction does not leave partial changes behind.

The SQL file prints stock values before and after the failed transaction. They should be the same.

5. COMMIT – saving a successful transaction

Now let's transfer five units without breaking any rules:

BEGIN;

UPDATE ecommerce.products
SET stock_quantity = stock_quantity - 5
WHERE product_id = 101
  AND stock_quantity >= 5;

UPDATE ecommerce.products
SET stock_quantity = stock_quantity + 5
WHERE product_id = 102;

COMMIT;

This time, both updates are valid, so COMMIT saves them.

If the starting stock is 100 for each product, the expected values are:

Product ID	Before	After
101	100	95
102	100	105

The condition stock_quantity >= 5 helps prevent the first update from subtracting more stock than is available. In a real application, the application should also check that the expected row was updated and handle concurrent purchases safely.

6. ACID properties

Transactions are commonly explained through the ACID properties.

Atomicity

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

In our stock transfer, we should not subtract stock from product 101 unless we also add it to product 102. ROLLBACK demonstrates this all-or-nothing behavior.

Consistency

A transaction must preserve the database's rules.

For example, the constraint CHECK (stock_quantity >= 0) prevents a transaction from committing a negative stock value.

Isolation

Concurrent transactions should not interfere in a way that produces incorrect results.

Imagine two customers trying to buy the last unit of a product at almost the same time. A production inventory flow may lock the relevant row using SELECT ... FOR UPDATE while checking and updating stock.

Durability

Once PostgreSQL confirms a successful COMMIT, the committed change is expected to survive a database restart or crash, subject to the database's storage and durability configuration.

Easy way to remember ACID:

• Atomicity – all or nothing.
• Consistency – rules remain valid.
• Isolation – concurrent work is controlled.
• Durability – committed data is persisted.

7. What is an index?

An index is a data structure PostgreSQL can use to locate rows more efficiently.

Think of a textbook. Without an index, you may need to scan page after page to find a topic. With an index, you can look up the topic and jump to the relevant page.

A database index serves a similar purpose: it can help PostgreSQL find matching rows without reading every row in a table.

Example:

CREATE INDEX idx_products_name
ON ecommerce.products(product_name);

This creates an index on product_name, which can help queries that search by product name.

Indexes created in Task 2

CREATE INDEX IF NOT EXISTS idx_products_name
ON ecommerce.products(product_name);

CREATE INDEX IF NOT EXISTS idx_orders_status_date
ON ecommerce.orders(status, order_date DESC);

CREATE INDEX IF NOT EXISTS idx_orders_user_date
ON ecommerce.orders(user_id, order_date DESC);

What are they intended to help with?

Index	Query pattern it can support
idx_products_name	Searching products by name
idx_orders_status_date	Filtering orders by status and sorting by date
idx_orders_user_date	Finding one user's orders in date order

Task 1 already creates idx_orders_user_date. The IF NOT EXISTS in Task 2 makes the statement safe to run again without creating a duplicate index.

Why not create an index on every column?

Indexes have benefits, but they also have costs:

• They use additional disk space.
• PostgreSQL must maintain them when rows are inserted, updated or deleted.
• Too many indexes can make write operations slower.

Create indexes based on the queries your application actually runs.

8. Composite indexes

A composite index contains more than one column.

For example:

CREATE INDEX idx_orders_user_date
ON ecommerce.orders(user_id, order_date DESC);

This index is designed for a query like:

SELECT order_id, order_date, status
FROM ecommerce.orders
WHERE user_id = 1
ORDER BY order_date DESC
LIMIT 10;

The query filters using user_id and sorts by order_date in descending order.

The order of columns in a composite index matters. PostgreSQL can generally make the best use of the index when a query uses its leading (leftmost) column or columns.

9. EXPLAIN – seeing how PostgreSQL plans a query

PostgreSQL has a command called EXPLAIN. It shows the execution plan the query planner expects to use.

EXPLAIN
SELECT movement_id, product_id, quantity_delta, created_at
FROM ecommerce.stock_movements
WHERE product_id = 777;

The plan may contain operations such as:

Sequential Scan

PostgreSQL reads the table rows and checks which ones match the condition.

This can be reasonable for a small table or when a query needs a large percentage of the rows.

Index Scan

PostgreSQL uses an index to locate matching rows, then fetches the required table data.

Bitmap Index Scan / Bitmap Heap Scan

PostgreSQL can use an index to identify matching row locations, then visit the table pages in a more grouped way. This can be useful when several rows match.

Remember: An index does not guarantee an index scan. PostgreSQL's planner chooses the plan it estimates will be cheaper for the table size, statistics, query and database environment.

10. EXPLAIN versus EXPLAIN ANALYZE

These commands are related, but they are not the same.

EXPLAIN

EXPLAIN
SELECT *
FROM ecommerce.stock_movements
WHERE product_id = 777;

Shows the estimated execution plan. It does not normally run the query itself.

EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM ecommerce.stock_movements
WHERE product_id = 777;

Actually executes the query and reports measured execution details.

• ANALYZE includes actual timing and row counts.
• BUFFERS reports information about the database pages read or found in memory.

Use EXPLAIN ANALYZE carefully: it executes the statement. For a SELECT, this means the query runs and returns its result as part of the plan output.

11. Why does the exercise create 200,000 rows?

The Task 2 script creates a table called stock_movements:

CREATE TABLE IF NOT EXISTS ecommerce.stock_movements (
    movement_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id INTEGER NOT NULL,
    quantity_delta INTEGER NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

It then inserts 200,000 sample stock movement records:

INSERT INTO ecommerce.stock_movements
    (product_id, quantity_delta, created_at)
SELECT
    ((n - 1) % 1000) + 1,
    CASE WHEN n % 2 = 0 THEN 1 ELSE -1 END,
    TIMESTAMPTZ '2026-01-01 00:00:00+00'
        + (n * INTERVAL '1 second')
FROM generate_series(1, 200000) AS n;

A few things are happening here:

• generate_series(1, 200000) generates 200,000 numbers.
• The modulo operation distributes the rows across 1,000 product IDs.
• CASE assigns a positive or negative quantity change.
• The timestamp increases by one second for each generated row.

A larger sample table makes it more useful to observe how PostgreSQL might choose between a sequential scan and an index-based plan.

12. Comparing the query plan before and after an index

First, the script runs EXPLAIN before creating the product index:

EXPLAIN
SELECT movement_id, product_id, quantity_delta, created_at
FROM ecommerce.stock_movements
WHERE product_id = 777;

Then it creates the index:

CREATE INDEX IF NOT EXISTS idx_stock_movements_product_id
ON ecommerce.stock_movements(product_id);

After updating the table statistics, it runs the same query again:

ANALYZE ecommerce.stock_movements;

EXPLAIN
SELECT movement_id, product_id, quantity_delta, created_at
FROM ecommerce.stock_movements
WHERE product_id = 777;

The purpose is to compare the planner's choice before and after the index is available.

You may see a sequential scan before the index and an index-based plan afterwards. However, this is not guaranteed. The exact plan depends on PostgreSQL's estimates, statistics, configuration and environment.

13. What does ANALYZE do?

ANALYZE collects statistics about the contents of a table. PostgreSQL's query planner uses these statistics to estimate how many rows a query will return and which plan is likely to be efficient.

Example:

ANALYZE ecommerce.products;
ANALYZE ecommerce.orders;
ANALYZE ecommerce.stock_movements;

After a large data load or significant data changes, refreshing statistics can help the planner make better decisions.

14. Important SQL commands from Task 2

Command	Purpose
BEGIN	Starts a transaction
COMMIT	Saves transaction changes
ROLLBACK	Cancels transaction changes
ALTER TABLE	Changes an existing table
ADD COLUMN	Adds a column
CREATE INDEX	Creates an index
IF NOT EXISTS	Avoids an error if the object already exists
ANALYZE	Updates table statistics
EXPLAIN	Displays the estimated query plan
EXPLAIN ANALYZE	Executes a query and reports actual plan details
TRUNCATE	Removes all rows from a table
generate_series()	Generates a sequence of values

15. Running and checking Task 2

Run Task 1 first, then Task 2:

psql -d ecommerce_db -f week-09/Task-01/ecommerce-schema.sql
psql -d ecommerce_db -f week-09/Task-02/transactions-demo.sql

The Task 2 script intentionally causes one CHECK constraint error during the rollback demonstration. That error is expected; the script continues to ROLLBACK and prints the restored stock values.

You can inspect the final stock values with:

SELECT product_id, product_name, stock_quantity
FROM ecommerce.products
WHERE product_id IN (101, 102)
ORDER BY product_id;

You can check how many stock movements were generated:

SELECT COUNT(*)
FROM ecommerce.stock_movements;

Expected row count:

200000

You can also inspect the indexes on the exercise tables in psql:

\di ecommerce.*

16. Quick revision

Topic	Simple explanation
Transaction	A group of database operations treated as one unit
BEGIN	Start the transaction
COMMIT	Save all changes
ROLLBACK	Undo uncommitted changes
ACID	Atomicity, Consistency, Isolation, Durability
Constraint	A rule that protects valid data
Index	A structure that can speed up suitable lookups
Composite index	An index containing multiple columns
Sequential scan	Read rows from the table and check them
Index scan	Use an index to locate matching rows
EXPLAIN	See the estimated query plan
EXPLAIN ANALYZE	Run the query and see actual execution details
ANALYZE	Collect statistics for the query planner

Final takeaway

Transactions protect data when an operation involves multiple changes. In our stock-transfer example, ROLLBACK prevents a failed transfer from leaving partial updates, while COMMIT saves a successful transfer.

Indexes help PostgreSQL locate data efficiently for suitable query patterns, but they require storage and maintenance. EXPLAIN and EXPLAIN ANALYZE help us understand the plan PostgreSQL chooses instead of assuming that an index will always be faster.

The key lesson from Task 2 is simple: use transactions to protect correctness, and use indexes thoughtfully to improve query performance.