PostgreSQL Schema Design for Payment Systems
PostgreSQL Schema Design for Payment Systems
When you're building systems that handle money, your database schema isn't just a technical decision—it's a business-critical one. A poorly designed schema can lead to data inconsistencies, failed audits, and lost revenue.
Here's what I've learned from building payment systems.
Core Principles
1. Immutability Over Updates
Never update financial records. Instead, create new records that reference the original.
-- Bad: Updating a transaction
UPDATE transactions SET amount = 100 WHERE id = '...';
-- Good: Creating a reversal
INSERT INTO transactions (type, reference_id, amount)
VALUES ('reversal', 'original-tx-id', -50);This approach gives you a complete audit trail and makes debugging much easier.
2. Use Proper Decimal Types
Never use floats for money. Ever.
-- Bad
amount FLOAT
-- Good
amount DECIMAL(10, 2)Floating-point arithmetic errors will eventually cause your books not to balance.
3. Foreign Keys Are Your Friends
In payment systems, data integrity is paramount. Use foreign keys liberally.
CREATE TABLE payments (
id UUID PRIMARY KEY,
order_id UUID NOT NULL REFERENCES orders(id),
method_id UUID NOT NULL REFERENCES payment_methods(id),
amount DECIMAL(10, 2) NOT NULL,
CONSTRAINT positive_amount CHECK (amount > 0)
);Common Patterns
The Ledger Pattern
Every financial system is essentially a ledger. Design around this.
CREATE TABLE ledger_entries (
id UUID PRIMARY KEY,
account_id UUID NOT NULL REFERENCES accounts(id),
entry_type VARCHAR(20) NOT NULL, -- 'credit' or 'debit'
amount DECIMAL(10, 2) NOT NULL,
balance_after DECIMAL(10, 2) NOT NULL,
reference_type VARCHAR(50),
reference_id UUID,
created_at TIMESTAMP DEFAULT NOW()
);Idempotency Keys
For any operation that can be retried, use idempotency keys.
CREATE TABLE payments (
id UUID PRIMARY KEY,
idempotency_key VARCHAR(255) UNIQUE,
-- ... other fields
);This prevents duplicate charges when network issues cause retries.
Testing Your Schema
Before going to production:
- Load test with realistic data volumes
- Test concurrent transactions to find race conditions
- Verify constraints actually prevent bad data
- Run EXPLAIN ANALYZE on your common queries
Conclusion
A well-designed payment schema is boring—and that's exactly what you want. Predictable, auditable, and reliable.
Building a payment system? Let's chat about your architecture.