DEV Community

Sai Swaroop Bijinapalli
Sai Swaroop Bijinapalli

Posted on

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

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

This index can help PostgreSQL execute queries such as:

SELECT *
FROM orders
WHERE user_id = 1
AND order_status = 'PAID';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

PostgreSQL may display:

Index Scan using users_pkey on users
  Index Cond: (user_id = 1)
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

Valid insertion:

INSERT INTO products_demo
VALUES (1, 'Keyboard', 10);
Enter fullscreen mode Exit fullscreen mode

Invalid insertion:

INSERT INTO products_demo
VALUES (2, 'Mouse', -5);
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)