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:
- Create an order.
- Insert order items.
- 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.
Top comments (0)