Efficient Database Transactions Using PostgreSQL: Best Practices and Optimization Techniques

Learning ACID Principle using example with Golang & PostgreSQL

13 min readDec 25, 2024

Imagine you’re booking a vacation package online. The system needs to:

Reserve your airline seats
Book your hotel room
Schedule airport transfers
Process your payment

What happens if your payment fails after the airline seats and hotel are reserved? Or if the hotel booking succeeds but the airline reservation fails? Without proper handling, you could end up with a hotel but no flight, or be charged for services you didn’t receive. This is where database transactions become crucial.

What is a Database Transaction?

A database transaction is a logical unit of work that contains one or more database operations. These operations could be reading data (SELECT), creating data (INSERT), updating data (UPDATE), or deleting data (DELETE). The key principle is that all these operations are treated as a single, indivisible unit of work.

Why Do We Need Transactions?

Let’s explore some real-world scenarios where transactions are essential:

Banking Systems

Money transfers between accounts
ATM withdrawals
Bill payments
Investment transactions

-- Without transaction (dangerous!)
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'sender';
-- If system crashes here, money vanishes!
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'receiver';

-- With transaction (safe)
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 'sender';
  UPDATE accounts SET balance = balance + 100 WHERE account_id = 'receiver';
COMMIT;

E-commerce Systems

Processing orders
Managing inventory
Handling payments
Updating customer points

-- E-commerce transaction example
BEGIN;
  -- Check inventory
  SELECT stock FROM products WHERE id = 'item1' FOR UPDATE;
  -- Update inventory
  UPDATE products SET stock = stock - 1 WHERE id = 'item1';
  -- Create order
  INSERT INTO orders (product_id, quantity) VALUES ('item1', 1);
  -- Record payment
  INSERT INTO payments (order_id, amount) VALUES (LASTVAL(), 99.99);
COMMIT;

Common Transaction Patterns

Short-lived Transactions

Short-lived transactions are ideal for operations that can be completed quickly. These transactions are generally initiated with BEGIN, followed by a series of operations (such as an UPDATE or INSERT), and then committed using COMMIT. This pattern helps avoid locking resources for too long and ensures high throughput. Example:

BEGIN;
  -- Quick operations (recommended)
  UPDATE inventory SET quantity = quantity - 1;
  INSERT INTO orders (item_id) VALUES (123);
COMMIT;

Long-running Transactions

Long-running transactions involve multiple operations that are spread over a period of time. They should be avoided when possible, as they can hold locks for extended periods and negatively impact performance. Example:

-- Avoid when possible
BEGIN;
  -- Multiple operations over time
  UPDATE large_table SET status = 'processing';
  -- ... some time passes ...
  UPDATE another_table SET completed = true;
COMMIT;

Nested Transactions

PostgreSQL does not support true nested transactions, but it offers SAVEPOINT and ROLLBACK TO SAVEPOINT to simulate nested transaction behavior. This allows for more granular control over commit/rollback actions within a larger transaction. Example:

BEGIN;  -- Outer transaction
  UPDATE accounts SET balance = balance - 100;
  
  SAVEPOINT before_fee;
  -- Inner transaction-like behavior
  UPDATE accounts SET balance = balance - fee;
  
  -- If fee processing fails
  ROLLBACK TO before_fee;
  
COMMIT;

ACID Principles: The Four Pillars of Transactions

Let’s break down the ACID principles using real-world analogies:

Atomicity: The “All or Nothing” Principle

Database: All operations in a transaction must succeed, or none will.
Real world example: When transferring money between bank accounts
If Step 2 fails, Step 1 is automatically undone. You won’t lose your money!

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'your_account';    -- Step 1
  UPDATE accounts SET balance = balance + 100 WHERE id = 'friend_account';  -- Step 2
COMMIT;

Consistency: The “Rules Are Rules” Principle

Database: All data must follow defined rules (constraints) before and after the transaction.
Real world example: You can’t book a room hotel that’s already occupied.

CREATE TABLE rooms (
    id SERIAL PRIMARY KEY,
    number TEXT,
    status TEXT CHECK (status IN ('available', 'occupied', 'maintenance'))  -- Consistency rule
);

Isolation: The “Private Workspace” Principle

Database: Multiple transactions shouldn’t interfere with each other.
Think of it like multiple cashiers at a store, each handling different customers without mixing up orders.
Real world example: Two people booking the last hotel room won’t both get it.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    -- Check and book hotel room
    UPDATE hotels 
    SET is_available = false 
    WHERE id = 123 AND is_available = true;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Room not available';
    END IF;
COMMIT;

Durability: The “Set in Stone” Principle

Database: Once a transaction is committed, changes are permanent.
Like writing in pen versus pencil — committed transactions survive even if the system crashes.
Real world example: Once confirmed, your booking survives system crashes.

BEGIN;
-- Once this commits, the booking is permanent
UPDATE bookings 
SET status = 'confirmed' 
WHERE id = 123;
COMMIT;

Transaction Isolation Levels

Transaction isolation levels are crucial for preventing double bookings and ensuring accurate room availability. Just as a hotel’s front desk needs clear protocols for handling overlapping reservations, your database needs well-defined rules for managing concurrent transactions.

Transaction isolation levels are like rules for hotel reservation clerks handling multiple bookings. When multiple guests try to book the same room at the same time, these rules determine how the system handles overlapping requests. Without proper isolation levels, you might end up with frustrated guests arriving to find their “confirmed” room already occupied by someone else.

For example, if two guests are trying to book the last available suite simultaneously, the isolation level determines whether the second guest sees the room as available while the first guest’s booking is being processed. This is particularly important during peak booking seasons when many users are competing for the same rooms.

Understanding Each Isolation Level

READ COMMITTED (Default in PostgreSQL)

In a hotel context, READ COMMITTED is like having a real-time availability display at the front desk. When one clerk makes a booking, other clerks will see the updated availability immediately after the booking is confirmed.

-- Terminal 1: Clerk Alice checking room availability
BEGIN;
SELECT is_available FROM rooms 
WHERE room_number = '501' AND room_type = 'Suite';  -- Shows true

-- Terminal 2: Clerk Bob processes a booking
BEGIN;
UPDATE rooms SET is_available = false 
WHERE room_number = '501';
INSERT INTO bookings (room_id, guest_name, check_in_date, check_out_date, status) 
VALUES (1, 'John Smith', '2024-12-27', '2024-12-29', 'confirmed');
COMMIT;

-- Terminal 1: Alice checks availability again
SELECT is_available FROM rooms 
WHERE room_number = '501';  -- Shows false

This level is perfect for basic room availability checks and simple bookings. However, it may lead to situations where a clerk sees a room as available, but by the time they try to book it, another clerk has already reserved it. This is acceptable for most booking scenarios as it reflects real-world availability accurately.

REPEATABLE READ

REPEATABLE READ is like having a snapshot of the hotel’s availability at a specific moment. If a clerk starts checking room availability for a group booking, they’ll see consistent data throughout their transaction, regardless of other bookings being made.

-- Terminal 1: Alice starts group booking with stricter rules
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM rooms 
WHERE room_type = 'Deluxe' AND is_available = true;  -- Shows 5

-- Terminal 2: Bob books a deluxe room
BEGIN;
UPDATE rooms SET is_available = false 
WHERE id = 3 AND room_type = 'Deluxe';
COMMIT;

-- Terminal 1: Alice checks again
SELECT COUNT(*) FROM rooms 
WHERE room_type = 'Deluxe' AND is_available = true;  -- Still shows 5!

This isolation level is particularly useful when handling group bookings or when generating occupancy reports. When a hotel manager needs to check room availability for a large group, they need consistent numbers throughout their planning process, even if individual bookings are happening simultaneously.

SERIALIZABLE

SERIALIZABLE is the strictest level, like having a policy where clerks must process group bookings one at a time, with no overlapping transactions allowed:

-- Terminal 1: Alice starts a group booking
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM rooms 
WHERE room_type = 'Suite' AND is_available = true;

-- Terminal 2: Bob tries to book a suite
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE rooms SET is_available = false 
WHERE room_number = '501' AND room_type = 'Suite';
-- This will wait or fail if Alice's transaction is still running

This level ensures absolute consistency, which is crucial for situations like managing VIP block bookings or handling conference room reservations where accuracy is paramount. It prevents any possibility of double bookings but may require transactions to be retried if conflicts occur.

Choosing the Right Isolation Level

READ COMMITTED (Default)

Use when: You need basic consistency
Good for: Most web applications
Trade-off: May see data change between reads

REPEATABLE READ

Use when: You need consistent views of data
Good for: Financial reports, analytics
Trade-off: Higher chance of serialization failures

SERIALIZABLE

Use when: You need perfect isolation
Good for: Financial transactions
Trade-off: Lower concurrency, more retries needed

Hands-on Example

Let’s create a practical example using Go and PostgreSQL.

This schema provides a solid foundation for our hotel booking system. The rooms table tracks individual rooms and their status, while bookings manages reservations and payments handles financial transactions. First, our database schema:

CREATE TABLE rooms (
    id SERIAL PRIMARY KEY,
    number TEXT NOT NULL,
    type TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('available', 'occupied', 'maintenance')),
    price_per_night DECIMAL(10,2) NOT NULL
);

CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    guest_email TEXT NOT NULL,
    room_id INTEGER REFERENCES rooms(id),
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
);

CREATE TABLE payments (
    id SERIAL PRIMARY KEY,
    booking_id INTEGER REFERENCES bookings(id),
    amount DECIMAL(10,2) NOT NULL,
    status TEXT NOT NULL,
    transaction_id TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Basic Data Structures in Go

Let’s define our core data structures:

package main

import (
    "context"
    "database/sql"
    "fmt"
    "time"
    _ "github.com/lib/pq"
)

// Room represents a hotel room
type Room struct {
    ID            int
    Number        string
    Type          string
    Floor         int
    Status        string
    PricePerNight float64
    CreatedAt     time.Time
}

// Booking represents a room reservation
type Booking struct {
    ID          int
    GuestEmail  string
    RoomID      int
    CheckIn     time.Time
    CheckOut    time.Time
    TotalAmount float64
    Status      string
    CreatedAt   time.Time
}

// Payment represents a financial transaction
type Payment struct {
    ID            int
    BookingID     int
    Amount        float64
    Status        string
    TransactionID string
    CreatedAt     time.Time
}

These structs mirror our database tables and provide type safety in our Go code. Each field corresponds to a database column, making it easy to map database rows to Go objects.

Hotel system setup

Here’s our main system structure and initialization:

// HotelSystem handles all database operations
type HotelSystem struct {
    db *sql.DB
}

// NewHotelSystem creates a new hotel management system
func NewHotelSystem(connStr string) (*HotelSystem, error) {
    // Open database connection
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        return nil, fmt.Errorf("failed to connect to database: %v", err)
    }
    
    // Test the connection
    if err := db.Ping(); err != nil {
        return nil, fmt.Errorf("failed to ping database: %v", err)
    }
    
    // Configure connection pool
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(5 * time.Minute)
    
    return &HotelSystem{db: db}, nil
}

This setup code establishes and configures our database connection. We set reasonable defaults for the connection pool and verify the connection is working before proceeding.

Feature Room Availability Check

Before creating a booking, we need to verify room availability

// CheckRoomAvailability verifies if a room is available for given dates
func (hs *HotelSystem) CheckRoomAvailability(ctx context.Context, roomNumber string, 
    checkIn, checkOut time.Time) (*Room, error) {
    
    // Start a transaction for consistent reading
    tx, err := hs.db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelReadCommitted,
    })
    if err != nil {
        return nil, fmt.Errorf("failed to start transaction: %v", err)
    }
    defer tx.Rollback()

    var room Room
    // Check room status and existing bookings
    err = tx.QueryRowContext(ctx, `
        SELECT r.id, r.number, r.type, r.price_per_night 
        FROM rooms r
        WHERE r.number = $1 
        AND r.status = 'available'
        AND NOT EXISTS (
            SELECT 1 FROM bookings b
            WHERE b.room_id = r.id
            AND b.status != 'cancelled'
            AND (
                (b.check_in_date, b.check_out_date) OVERLAPS ($2, $3)
            )
        )
        FOR UPDATE`,
        roomNumber, checkIn, checkOut).
        Scan(&room.ID, &room.Number, &room.Type, &room.PricePerNight)

    if err == sql.ErrNoRows {
        return nil, fmt.Errorf("room not available for selected dates")
    }
    if err != nil {
        return nil, fmt.Errorf("failed to check room availability: %v", err)
    }

    // Commit the transaction
    if err = tx.Commit(); err != nil {
        return nil, fmt.Errorf("failed to commit transaction: %v", err)
    }

    return &room, nil
}

This function performs a critical check to ensure we don’t double-book rooms. The SQL query uses the OVERLAPS operator to check for date range conflicts, and we use FOR UPDATE to lock the row until our transaction completes.

Feature Booking

Once we’ve confirmed availability, we can create the booking:

// CreateBooking handles the entire booking process
func (hs *HotelSystem) CreateBooking(ctx context.Context, guestEmail string, 
    roomNumber string, checkIn, checkOut time.Time) (*Booking, error) {
    
    // Start transaction with serializable isolation
    tx, err := hs.db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelSerializable,
    })
    if err != nil {
        return nil, fmt.Errorf("failed to start transaction: %v", err)
    }
    defer tx.Rollback()

    // 1. Get and lock the room
    var room Room
    err = tx.QueryRowContext(ctx, `
        SELECT id, price_per_night 
        FROM rooms 
        WHERE number = $1 AND status = 'available'
        FOR UPDATE`,
        roomNumber).Scan(&room.ID, &room.PricePerNight)
    if err != nil {
        return nil, fmt.Errorf("room not available: %v", err)
    }

    // 2. Calculate total amount
    nights := int(checkOut.Sub(checkIn).Hours() / 24)
    totalAmount := room.PricePerNight * float64(nights)
    tax := totalAmount * 0.12 // 12% tax
    totalWithTax := totalAmount + tax

    // 3. Create booking record
    var booking Booking
    err = tx.QueryRowContext(ctx, `
        INSERT INTO bookings (
            guest_email, room_id, check_in_date, 
            check_out_date, total_amount, status
        ) VALUES ($1, $2, $3, $4, $5, 'confirmed')
        RETURNING id, created_at`,
        guestEmail, room.ID, checkIn, checkOut, totalWithTax).
        Scan(&booking.ID, &booking.CreatedAt)
    if err != nil {
        return nil, fmt.Errorf("failed to create booking: %v", err)
    }

    // 4. Update room status
    _, err = tx.ExecContext(ctx, `
        UPDATE rooms 
        SET status = 'occupied' 
        WHERE id = $1`,
        room.ID)
    if err != nil {
        return nil, fmt.Errorf("failed to update room status: %v", err)
    }

    // Commit the transaction
    if err = tx.Commit(); err != nil {
        return nil, fmt.Errorf("failed to commit booking transaction: %v", err)
    }

    // Fill booking details
    booking.GuestEmail = guestEmail
    booking.RoomID = room.ID
    booking.CheckIn = checkIn
    booking.CheckOut = checkOut
    booking.TotalAmount = totalWithTax
    booking.Status = "confirmed"

    return &booking, nil
}

This function demonstrates a complete transaction that:

Locks the room to prevent double-booking
Calculates the total amount with tax
Creates the booking record
Updates the room status All operations must succeed, or none will take effect.

Feature Payment

Finally, let’s handle the payment:

// ProcessPayment handles the payment for a booking
func (hs *HotelSystem) ProcessPayment(ctx context.Context, bookingID int, 
    paymentInfo PaymentInfo) error {
    
    // Start transaction
    tx, err := hs.db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelSerializable,
    })
    if err != nil {
        return fmt.Errorf("failed to start transaction: %v", err)
    }
    defer tx.Rollback()

    // 1. Verify booking exists and get amount
    var expectedAmount float64
    err = tx.QueryRowContext(ctx, `
        SELECT total_amount 
        FROM bookings 
        WHERE id = $1 AND status = 'confirmed'
        FOR UPDATE`,
        bookingID).Scan(&expectedAmount)
    if err != nil {
        return fmt.Errorf("booking not found or invalid: %v", err)
    }

    // 2. Process payment (simulate external payment processing)
    transactionID := fmt.Sprintf("TXN_%d_%s", bookingID, time.Now().Format("20060102150405"))
    
    // 3. Record payment
    _, err = tx.ExecContext(ctx, `
        INSERT INTO payments (
            booking_id, amount, status, transaction_id
        ) VALUES ($1, $2, 'completed', $3)`,
        bookingID, expectedAmount, transactionID)
    if err != nil {
        return fmt.Errorf("failed to record payment: %v", err)
    }

    // 4. Update booking status
    _, err = tx.ExecContext(ctx, `
        UPDATE bookings 
        SET status = 'paid' 
        WHERE id = $1`,
        bookingID)
    if err != nil {
        return fmt.Errorf("failed to update booking status: %v", err)
    }

    // Commit the transaction
    if err = tx.Commit(); err != nil {
        return fmt.Errorf("failed to commit payment transaction: %v", err)
    }

    return nil
}

This payment processing ensures that:

The booking exists and is in a valid state
The payment amount matches the booking
The payment record is created
The booking status is updated All within a single transaction for data consistency.

Best practices & Optimization Techniques

1. Keep Transactions Short and Focused

Long-running transactions can cause several problems:

They hold locks longer, blocking other transactions
Increase chance of deadlocks
Take up more database resources
Higher risk of failure

// BAD: Long-running transaction
func (hs *HotelSystem) BadBookingProcess(ctx context.Context, bookingID int) error {
    tx, err := hs.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // DON'T: External API call within transaction
    paymentStatus := processExternalPayment(bookingID)  // Could take several seconds
    
    // DON'T: Long processing within transaction
    generatePDFInvoice(bookingID)  // CPU-intensive task
    
    // DON'T: Email sending within transaction
    sendConfirmationEmail(bookingID)  // Network I/O
    
    return tx.Commit()
}

// GOOD: Short, focused transaction
func (hs *HotelSystem) GoodBookingProcess(ctx context.Context, bookingID int) error {
    // Prepare data before transaction
    bookingDetails, err := hs.getBookingDetails(bookingID)
    if err != nil {
        return err
    }

    // Process payment outside transaction
    paymentStatus := processExternalPayment(bookingID)

    // Short, focused transaction
    tx, err := hs.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // Only database operations in transaction
    err = updateBookingStatus(tx, bookingID, paymentStatus)
    if err != nil {
        return err
    }

    err = tx.Commit()
    if err != nil {
        return err
    }

    // Post-transaction operations
    go generatePDFInvoice(bookingID)  // Async operation
    go sendConfirmationEmail(bookingID)  // Async operation
    
    return nil
}

2. Implement Proper Error Handling

Comprehensive error handling should:

Catch and categorize different types of errors
Implement retry logic for transient failures
Log errors with context
Clean up resources properly

// Generic retry function for transactions
func (hs *HotelSystem) RetryableTransaction(
    ctx context.Context, 
    maxAttempts int, 
    fn func(*sql.Tx) error,
) error {
    var lastErr error
    
    for attempt := 1; attempt <= maxAttempts; attempt++ {
        func() {
            tx, err := hs.db.BeginTx(ctx, nil)
            if err != nil {
                lastErr = fmt.Errorf("begin transaction failed: %v", err)
                return
            }
            defer tx.Rollback()

            if err := fn(tx); err != nil {
                lastErr = err
                
                // Check for retriable errors
                if isDeadlock(err) || isSerializationFailure(err) {
                    time.Sleep(time.Duration(attempt*100) * time.Millisecond)
                    return
                }
                
                // Non-retriable error
                lastErr = err
                return
            }

            if err := tx.Commit(); err != nil {
                lastErr = fmt.Errorf("commit failed: %v", err)
                return
            }
            
            lastErr = nil
        }()

        if lastErr == nil {
            return nil
        }
    }

    return fmt.Errorf("transaction failed after %d attempts: %v", maxAttempts, lastErr)
}

// Example usage with error handling
func (hs *HotelSystem) CreateBookingWithRetry(ctx context.Context, booking BookingRequest) error {
    return hs.RetryableTransaction(ctx, 3, func(tx *sql.Tx) error {
        // Custom error types
        var ErrRoomNotAvailable = errors.New("room not available")
        var ErrInvalidDates = errors.New("invalid dates")

        // Validate dates
        if booking.CheckOut.Before(booking.CheckIn) {
            return ErrInvalidDates
        }

        // Check room availability
        available, err := checkRoomAvailable(tx, booking.RoomID)
        if err != nil {
            return fmt.Errorf("failed to check room: %w", err)
        }
        if !available {
            return ErrRoomNotAvailable
        }

        // Perform booking operations
        return createBookingRecords(tx, booking)
    })
}

3. Choose Appropriate Isolation Levels

Different isolation levels offer different guarantees:

Higher isolation = stronger consistency but lower concurrency
Lower isolation = better performance but potential anomalies

// READ COMMITTED: Good for simple reads
func (hs *HotelSystem) GetRoomInfo(ctx context.Context, roomID int) (*Room, error) {
    tx, err := hs.db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelReadCommitted,
    })
    defer tx.Rollback()
    
    // Simple read operation
    var room Room
    err = tx.QueryRowContext(ctx, 
        "SELECT * FROM rooms WHERE id = $1", 
        roomID).Scan(&room)
    
    if err != nil {
        return nil, err
    }
    
    tx.Commit()
    return &room, nil
}

// SERIALIZABLE: For critical booking operations
func (hs *HotelSystem) CreateBooking(ctx context.Context, booking BookingRequest) error {
    tx, err := hs.db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelSerializable,
    })
    defer tx.Rollback()
    
    // Critical booking operations that need strong consistency
    // ...
    
    return tx.Commit()
}

4. Implement Connection and Resource Management

Proper connection management is crucial for transaction performance:

Configure appropriate pool sizes
Set reasonable timeouts
Monitor connection usage

Remember to always test your transaction code under load to verify it behaves correctly under concurrent access patterns.

If you’re interested in diving deeper into system design and backend development, be sure to follow me for more insights, tips, and practical examples. Together, we can explore the intricacies of creating efficient systems, optimizing database performance, and mastering the tools that drive modern applications. Join me on this journey to enhance your skills and stay updated on the latest trends in the tech world! 🚀

Read the design system in bahasa on iniakunhuda.com