PostgreSQL Transactions & Basic Indexing: Understanding Atomicity, Consistency and Query Performance

Introduction

PostgreSQL is a powerful relational database management system that provides features to manage data safely and efficiently.

Two important concepts in PostgreSQL are Transactions and Indexing.

Transactions help maintain data integrity by ensuring that database operations are completed successfully as a single unit. Indexing improves query performance by helping PostgreSQL locate records efficiently.

In this blog, we will explore transaction commands, different types of indexes, EXPLAIN basics, and how Atomicity and Consistency work in real-world applications.

1. Understanding Transactions in PostgreSQL

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

Consider an e-commerce application where a customer purchases a product. The application needs to:

  • Create a new order.
  • Insert order details.
  • Update product stock.

All these operations should succeed together. If one operation fails, the database should undo the changes.

PostgreSQL provides three important transaction commands.

BEGIN

BEGIN starts a new transaction.

BEGIN; 

After executing BEGIN, PostgreSQL groups subsequent SQL operations into the current transaction.

COMMIT

COMMIT saves all successful changes permanently.

BEGIN; INSERT INTO users (full_name, email) VALUES ('Swaroop', 'swaroop@example.com'); COMMIT; 

Once COMMIT executes successfully, the inserted user is saved.

ROLLBACK

ROLLBACK cancels changes made in the current transaction that have not yet been committed.

BEGIN; INSERT INTO users (full_name, email) VALUES ('Ravi', 'ravi@example.com'); ROLLBACK; 

The inserted record will not be saved.

Real-world example: Bank transfer

Imagine Ravi transfers ₹1,000 to Swaroop.

Initially:

Person Balance
Ravi ₹5,000
Swaroop ₹2,000
BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1; UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2; COMMIT; 

After successful execution:

Person Balance
Ravi ₹4,000
Swaroop ₹3,000

If the second operation fails, the application should execute ROLLBACK so the first deduction is also undone.

This prevents incomplete financial transactions.

2. Understanding Indexing in PostgreSQL

An index is a database structure that helps PostgreSQL find records faster.

Think of a textbook. Instead of searching every page for a topic, you can use the index at the back to locate the required page.

Database indexes work similarly.

PostgreSQL supports different types of indexes.

Primary Key Index

A primary key uniquely identifies each row in a table.

CREATE TABLE students ( student_id INT PRIMARY KEY, student_name VARCHAR(100), email VARCHAR(100) ); 

Here, student_id uniquely identifies every student.

PostgreSQL automatically creates a unique B-tree index for a primary key.

Unique Index

A unique index prevents duplicate values in an indexed column.

CREATE UNIQUE INDEX idx_students_email ON students(email); 

Now two students cannot have the same email address.

A unique index enforces uniqueness, while a primary key also identifies the row and does not allow NULL values.

Composite Index

A composite index is created using two or more columns.

CREATE INDEX idx_orders_user_status ON orders(user_id, order_status); 

This index can help PostgreSQL execute queries such as:

SELECT * FROM orders WHERE user_id = 1 AND order_status = 'PAID'; 

Column order matters in a composite index. An index on (user_id, order_status) is generally most useful for queries that filter using the leading user_id column.

Comparison of indexes

Index type Purpose
Primary Key Index Uniquely identifies rows
Unique Index Prevents duplicate indexed values
Composite Index Indexes multiple columns together

Indexes improve read performance, but they also require storage and add work to INSERT, UPDATE and DELETE operations. Therefore, indexes should be created based on actual query requirements.

3. Understanding EXPLAIN in PostgreSQL

EXPLAIN is used to understand how PostgreSQL plans to execute a SQL query.

It displays the query execution plan, including the operations PostgreSQL expects to perform.

Example:

EXPLAIN SELECT * FROM users WHERE user_id = 1; 

PostgreSQL may display:

Index Scan using users_pkey on users Index Cond: (user_id = 1) 

This indicates that PostgreSQL plans to use the primary key index to find the requested user.

Sequential Scan

A Sequential Scan means PostgreSQL reads rows from the table one by one.

EXPLAIN SELECT * FROM users WHERE full_name = 'Swaroop'; 

If no suitable index exists, PostgreSQL may choose a sequential scan.

Index Scan

An Index Scan means PostgreSQL uses an index to locate matching records.

CREATE INDEX idx_users_name ON users(full_name); EXPLAIN SELECT * FROM users WHERE full_name = 'Swaroop'; 

PostgreSQL may use the newly created index.

EXPLAIN ANALYZE

EXPLAIN ANALYZE executes the query and displays actual execution measurements.

EXPLAIN ANALYZE SELECT * FROM users WHERE user_id = 1; 

Difference:

  • EXPLAIN displays the planned execution.
  • EXPLAIN ANALYZE executes the query and displays actual execution statistics.

A sequential scan is not always inefficient. For small tables or queries that return many rows, it may be faster than using an index.

4. Atomicity in Practice

Atomicity is one of the ACID properties of database transactions.

It means all operations in a transaction succeed together, or none of them are applied.

Consider an e-commerce order placement process:

  1. Create an order.
  2. Insert order items.
  3. Reduce product stock.

If stock updating fails, the order and its items should also be rolled back.

Example:

BEGIN; INSERT INTO orders ( user_id, order_status, shipping_name, shipping_line1, shipping_city, shipping_postal_code ) VALUES ( 1, 'PENDING', 'Asha Rao', '12 Lake Road', 'Bengaluru', '560001' ); UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 1 AND stock_qty >= 1; COMMIT; 

In a real application, we must check whether the stock update affected exactly one row. If it affected zero rows, we should execute ROLLBACK instead of COMMIT.

This ensures that an incomplete order is not saved.

5. Consistency in Practice

Consistency ensures that every committed transaction maintains the database's rules and constraints.

For example, product stock should never become negative.

We can enforce this using a CHECK constraint.

CREATE TABLE products_demo ( product_id INT PRIMARY KEY, product_name VARCHAR(100), stock_qty INT CHECK (stock_qty >= 0) ); 

Valid insertion:

INSERT INTO products_demo VALUES (1, 'Keyboard', 10); 

Invalid insertion:

INSERT INTO products_demo VALUES (2, 'Mouse', -5); 

PostgreSQL rejects the second insertion because the stock quantity cannot be negative.

Other examples of consistency rules include:

  • Primary keys prevent duplicate row identifiers.
  • Foreign keys maintain valid relationships between tables.
  • Unique constraints prevent duplicate email addresses.
  • CHECK constraints validate prices and quantities.
  • Order status must belong to the permitted values.

These rules help maintain reliable and valid database records.

6. Practical Importance in Real-world Applications

Transactions and indexing are important in applications such as:

  • E-commerce: Maintaining reliable order placement and stock updates.
  • Banking: Ensuring correct money transfers.
  • Booking systems: Preventing multiple customers from purchasing the same last available seat.
  • User management: Efficiently searching users by email or ID.
  • Reporting systems: Improving query performance on frequently filtered columns.

Conclusion

PostgreSQL transactions and indexing play an important role in building reliable and efficient database applications.

Transactions use BEGIN, COMMIT and ROLLBACK to manage groups of SQL operations safely. Indexes improve data retrieval, while EXPLAIN helps developers understand query execution plans.

Atomicity ensures all-or-nothing execution, and Consistency ensures database constraints remain valid.

Understanding these concepts provides a strong foundation for developing database-driven applications that are both reliable and performant.