DEV Community

Cover image for # 💸 The $2.3 Million Lesson: Why Your PostgreSQL Money Transactions Are Probably Wrong
Igor Nosatov
Igor Nosatov

Posted on

# 💸 The $2.3 Million Lesson: Why Your PostgreSQL Money Transactions Are Probably Wrong

Table of Contents

The 3 AM Wake-Up Call

It was a Friday night. I was three episodes deep into a Netflix binge when my phone exploded with notifications. Our payment system had just processed 47 duplicate charges. Real money. Real angry customers. Real problem.

The culprit? A single line of code I'd written six months earlier:

CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    amount DECIMAL(10, 2),  -- Looks fine, right? 🚨
    user_id INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);
Enter fullscreen mode Exit fullscreen mode

Spoiler alert: This is like building a bank vault with a screen door.

Why Money Isn't Just Another Number

Here's a fun fact that cost us $2.3 million in chargebacks: floating-point arithmetic doesn't play nice with money.

Try this in your PostgreSQL console:

SELECT 0.1 + 0.2;
-- May give unexpected results

SELECT (0.1::DECIMAL + 0.2::DECIMAL);
-- Result: 0.3 (always precise)
Enter fullscreen mode Exit fullscreen mode

The Five Horsemen of Financial Apocalypse

1. The Phantom Precision Problem 

-- ❌ WRONG: Seems reasonable
CREATE TABLE wallet (
    balance DECIMAL(10, 2)  -- Max: $99,999,999.99
);

-- What happens with cryptocurrency microtransactions?
INSERT INTO wallet VALUES (0.00000001);
-- Silently rounds to 0.00 💀
Enter fullscreen mode Exit fullscreen mode

The Fix:

-- ✅ RIGHT: Think in smallest units
CREATE TABLE wallet (
    balance BIGINT  -- Store cents, not dollars
);

-- Store $100.50 as 10050 cents
INSERT INTO wallet VALUES (10050);
Enter fullscreen mode Exit fullscreen mode

2. The Race Condition Heist

Picture this: Two processes trying to withdraw from the same account simultaneously.

-- Thread 1 & 2 both read: balance = $1000
SELECT balance FROM accounts WHERE id = 123;

-- Both withdraw $600...
-- Final balance: -$200 (Free money giveaway!)
Enter fullscreen mode Exit fullscreen mode

The Atomic Solution:

-- ✅ Use SELECT FOR UPDATE to lock the row
BEGIN;

SELECT balance FROM accounts 
WHERE id = 123 
FOR UPDATE;  -- 🔒 Lock acquired!

-- Now safely check and update
UPDATE accounts 
SET balance = balance - 600 
WHERE id = 123 
AND balance >= 600;

COMMIT;
Enter fullscreen mode Exit fullscreen mode

3. The Idempotency Illusion

Users love double-clicking "Submit Payment". Your database should not love processing it twice.

-- ❌ WRONG: Every request creates a new transaction
INSERT INTO transactions (user_id, amount)
VALUES (123, 50.00);
Enter fullscreen mode Exit fullscreen mode

The Idempotent Approach:

-- ✅ Use idempotency keys
CREATE TABLE transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    idempotency_key VARCHAR(255) UNIQUE NOT NULL,
    user_id INTEGER NOT NULL,
    amount BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Client generates unique key per operation
INSERT INTO transactions (idempotency_key, user_id, amount, status)
VALUES ('payment-123-20250120-abc', 123, 5000, 'pending')
ON CONFLICT (idempotency_key) DO NOTHING;
Enter fullscreen mode Exit fullscreen mode

4. The Audit Trail Amnesia

Six months later, a customer disputes a charge. Without an audit trail, you're toast.

-- ❌ WRONG: Updating balances directly
UPDATE accounts SET balance = balance + 100;
Enter fullscreen mode Exit fullscreen mode

The Auditable Way:

-- ✅ Event sourcing approach
CREATE TABLE account_events (
    id BIGSERIAL PRIMARY KEY,
    account_id INTEGER NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    amount BIGINT NOT NULL,
    balance_after BIGINT NOT NULL,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    created_by VARCHAR(255)
);

-- Every change is recorded
INSERT INTO account_events 
(account_id, event_type, amount, balance_after, metadata)
VALUES 
(123, 'deposit', 10000, 25000, '{"source": "wire_transfer", "ref": "INV-001"}');
Enter fullscreen mode Exit fullscreen mode

5. The Orphaned Transaction Syndrome

Network fails mid-transaction. Half your ledger updates. Chaos ensues.

-- ❌ WRONG: Multiple tables without transaction
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- *network failure*
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Second update never happens!
Enter fullscreen mode Exit fullscreen mode

The ACID Solution:

-- ✅ Use transactions with proper isolation
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Debit
UPDATE accounts 
SET balance = balance - 10000
WHERE id = 1 
AND balance >= 10000;

-- Credit
UPDATE accounts 
SET balance = balance + 10000
WHERE id = 2;

-- Log the transfer
INSERT INTO transfers (from_account, to_account, amount)
VALUES (1, 2, 10000);

COMMIT;
Enter fullscreen mode Exit fullscreen mode

Building a Bulletproof Transaction System

Here's the complete schema I wish I'd built from day one:

-- Accounts table with immutable balance history
CREATE TABLE accounts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL UNIQUE,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- All money is stored in smallest units (cents)
CREATE TABLE account_balances (
    account_id BIGINT PRIMARY KEY REFERENCES accounts(id),
    available_balance BIGINT NOT NULL DEFAULT 0,
    pending_balance BIGINT NOT NULL DEFAULT 0,
    CHECK (available_balance >= 0),
    CHECK (pending_balance >= 0)
);

-- Every financial event is immutable
CREATE TABLE ledger_entries (
    id BIGSERIAL PRIMARY KEY,
    idempotency_key VARCHAR(255) UNIQUE NOT NULL,
    account_id BIGINT NOT NULL REFERENCES accounts(id),
    transaction_type VARCHAR(50) NOT NULL,
    amount BIGINT NOT NULL,
    balance_after BIGINT NOT NULL,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Index for fast lookups
CREATE INDEX idx_ledger_account_created 
ON ledger_entries(account_id, created_at DESC);

CREATE INDEX idx_ledger_idempotency 
ON ledger_entries(idempotency_key);
Enter fullscreen mode Exit fullscreen mode

The Safe Transaction Function

Here's a production-ready function that handles all the edge cases:

CREATE OR REPLACE FUNCTION process_transaction(
    p_idempotency_key VARCHAR(255),
    p_account_id BIGINT,
    p_amount BIGINT,
    p_transaction_type VARCHAR(50),
    p_metadata JSONB DEFAULT '{}'
) RETURNS BIGINT AS $$
DECLARE
    v_new_balance BIGINT;
    v_existing_entry BIGINT;
BEGIN
    -- Check for existing transaction (idempotency)
    SELECT id INTO v_existing_entry
    FROM ledger_entries
    WHERE idempotency_key = p_idempotency_key;

    IF v_existing_entry IS NOT NULL THEN
        -- Already processed, return existing entry
        RETURN v_existing_entry;
    END IF;

    -- Lock the account balance row
    SELECT available_balance INTO v_new_balance
    FROM account_balances
    WHERE account_id = p_account_id
    FOR UPDATE;

    -- Calculate new balance
    v_new_balance := v_new_balance + p_amount;

    -- Ensure balance doesn't go negative
    IF v_new_balance < 0 THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    -- Update balance
    UPDATE account_balances
    SET available_balance = v_new_balance,
        updated_at = NOW()
    WHERE account_id = p_account_id;

    -- Create immutable ledger entry
    INSERT INTO ledger_entries (
        idempotency_key,
        account_id,
        transaction_type,
        amount,
        balance_after,
        metadata
    ) VALUES (
        p_idempotency_key,
        p_account_id,
        p_transaction_type,
        p_amount,
        v_new_balance,
        p_metadata
    ) RETURNING id INTO v_existing_entry;

    RETURN v_existing_entry;
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

Using the Function 

-- Deposit $100.00 (10000 cents)
BEGIN;
SELECT process_transaction(
    'deposit-user-123-' || gen_random_uuid()::text,
    123,
    10000,
    'deposit',
    '{"method": "credit_card", "last4": "4242"}'::jsonb
);
COMMIT;

-- Withdraw $50.00 (5000 cents)
BEGIN;
SELECT process_transaction(
    'withdrawal-user-123-' || gen_random_uuid()::text,
    123,
    -5000,
    'withdrawal',
    '{"method": "bank_transfer"}'::jsonb
);
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Money Transfer Between Accounts 

CREATE OR REPLACE FUNCTION transfer_money(
    p_idempotency_key VARCHAR(255),
    p_from_account BIGINT,
    p_to_account BIGINT,
    p_amount BIGINT,
    p_metadata JSONB DEFAULT '{}'
) RETURNS VOID AS $$
BEGIN
    -- Ensure positive amount
    IF p_amount <= 0 THEN
        RAISE EXCEPTION 'Transfer amount must be positive';
    END IF;

    -- Debit from source account
    PERFORM process_transaction(
        p_idempotency_key || '-debit',
        p_from_account,
        -p_amount,
        'transfer_out',
        p_metadata || jsonb_build_object('to_account', p_to_account)
    );

    -- Credit to destination account
    PERFORM process_transaction(
        p_idempotency_key || '-credit',
        p_to_account,
        p_amount,
        'transfer_in',
        p_metadata || jsonb_build_object('from_account', p_from_account)
    );
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

Testing Your Defenses

Here's how to test race conditions:

-- Create test accounts
INSERT INTO accounts (user_id) VALUES (1), (2);
INSERT INTO account_balances (account_id, available_balance) 
VALUES (1, 100000), (2, 0);

-- Test concurrent withdrawals (run in multiple sessions)
-- Session 1:
BEGIN;
SELECT process_transaction(
    'test-concurrent-1',
    1,
    -60000,
    'withdrawal',
    '{}'
);
-- Wait 5 seconds before committing
SELECT pg_sleep(5);
COMMIT;

-- Session 2 (start immediately after Session 1):
BEGIN;
SELECT process_transaction(
    'test-concurrent-2',
    1,
    -60000,
    'withdrawal',
    '{}'
);
COMMIT;

-- One should fail with "Insufficient funds"
Enter fullscreen mode Exit fullscreen mode

Monitoring and Alerting 

-- View to detect anomalies
CREATE VIEW suspicious_transactions AS
SELECT 
    account_id,
    COUNT(*) as transaction_count,
    SUM(ABS(amount)) as total_volume
FROM ledger_entries
WHERE created_at > NOW() - INTERVAL '1 hour'
GROUP BY account_id
HAVING COUNT(*) > 100 OR SUM(ABS(amount)) > 100000000;

-- Check balance consistency
CREATE VIEW balance_audit AS
SELECT 
    ab.account_id,
    ab.available_balance as current_balance,
    COALESCE(SUM(le.amount), 0) as calculated_balance,
    ab.available_balance - COALESCE(SUM(le.amount), 0) as discrepancy
FROM account_balances ab
LEFT JOIN ledger_entries le ON ab.account_id = le.account_id
GROUP BY ab.account_id, ab.available_balance
HAVING ab.available_balance != COALESCE(SUM(le.amount), 0);
Enter fullscreen mode Exit fullscreen mode

Lessons Learned (The Hard Way)

  1. Money is not a float - Store smallest units as integers
  2. Idempotency is mandatory - Every operation needs a unique key
  3. Audit everything - Future you will thank present you
  4. Lock before you leap - Use SELECT FOR UPDATE
  5. Test race conditions - Your production users will find them if you don't
  6. Never trust the client - Validate everything server-side
  7. Monitor obsessively - Discrepancies compound fast

The 3 AM Follow-Up

After rebuilding our system with these principles, we went 18 months without a single financial discrepancy. The duplicate charge incident? Fixed with proper idempotency keys. The race conditions? Eliminated with row-level locking. The audit nightmare? Solved with immutable ledger entries.

Your Turn

Here are some questions to ask yourself:

  • Can two requests with the same idempotency key create duplicate charges?
  • What happens if your server crashes mid-transaction?
  • Can you reconstruct any account balance from your ledger history?
  • Have you tested concurrent operations on the same account?

If you answered "I don't know" to any of these, it's time to revisit your transaction system.

Further Reading

Tags: #postgresql #database #fintech #transactions

Cover Image Concept: A dramatic image of a piggy bank wearing a superhero cape with binary code in the background

Meta Description: Learn how to build bulletproof money transaction systems in PostgreSQL. Real-world lessons from a $2.3M mistake, including race conditions, idempotency, and audit trails.

Have you had your own financial transaction horror story? Share it in the comments! Let's learn from each other's mistakes so we don't repeat them. 🚀

Top comments (0)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.