Beyond MySQL
For years, MySQL was our default. It's familiar, well-documented, and works fine for most CRUD applications. But as our fintech platforms grew — millions of trades, complex commission calculations, multi-currency accounting — we kept hitting MySQL's limitations.
PostgreSQL solved problems we were constantly working around.
ACID Compliance That Actually Works
Both MySQL and PostgreSQL claim ACID compliance, but PostgreSQL's implementation is more robust for financial workloads:
-- PostgreSQL: Serializable isolation prevents phantom reads
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Check balance and withdraw atomically
SELECT balance FROM wallets WHERE client_id = 123 FOR UPDATE;
-- If balance sufficient, debit
UPDATE wallets SET balance = balance - 500.00
WHERE client_id = 123 AND balance >= 500.00;
-- This either succeeds completely or rolls back
COMMIT;
PostgreSQL's MVCC (Multi-Version Concurrency Control) handles concurrent transactions without the locking issues we experienced with MySQL's InnoDB under high write loads.
Table Partitioning for Trade Data
Trade tables grow fast. A broker with 10,000 active clients generates millions of rows per month. PostgreSQL's native partitioning keeps queries fast:
CREATE TABLE trades (
id BIGSERIAL,
client_id INT NOT NULL,
symbol VARCHAR(20) NOT NULL,
action VARCHAR(10) NOT NULL,
volume DECIMAL(15,5) NOT NULL,
open_price DECIMAL(15,5) NOT NULL,
close_price DECIMAL(15,5),
profit DECIMAL(15,2),
opened_at TIMESTAMP NOT NULL,
closed_at TIMESTAMP,
PRIMARY KEY (id, opened_at)
) PARTITION BY RANGE (opened_at);
-- Monthly partitions
CREATE TABLE trades_2026_01 PARTITION OF trades
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE trades_2026_02 PARTITION OF trades
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
Queries that filter by date automatically hit only the relevant partition. Monthly reporting that used to take 45 seconds now completes in under 2 seconds.
Automate partition creation:
-- Create next month's partition automatically
CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
DECLARE
next_month DATE := date_trunc('month', NOW() + INTERVAL '1 month');
partition_name TEXT;
BEGIN
partition_name := 'trades_' || to_char(next_month, 'YYYY_MM');
EXECUTE format(
'CREATE TABLE IF NOT EXISTS %I PARTITION OF trades
FOR VALUES FROM (%L) TO (%L)',
partition_name,
next_month,
next_month + INTERVAL '1 month'
);
END;
$$ LANGUAGE plpgsql;
JSONB for Flexible Schemas
Fintech platforms deal with data that doesn't always fit rigid schemas. PSP responses vary wildly. MT4 and MT5 return different trade structures. Commission schemes have varied parameters.
PostgreSQL's JSONB columns handle this elegantly:
CREATE TABLE transactions (
id BIGSERIAL PRIMARY KEY,
client_id INT NOT NULL,
provider VARCHAR(50) NOT NULL,
amount DECIMAL(15,2) NOT NULL,
status VARCHAR(20) NOT NULL,
-- PSP-specific data stored as JSONB
provider_data JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMP DEFAULT NOW()
);
-- Index specific JSON fields for fast queries
CREATE INDEX idx_transactions_provider_ref
ON transactions ((provider_data->>'reference_id'));
-- Query JSON data naturally
SELECT * FROM transactions
WHERE provider_data->>'reference_id' = 'PAY-123456'
AND provider = 'payretailers';
This means adding a new PSP doesn't require schema migrations for PSP-specific fields. The core columns stay structured, and provider-specific data lives in JSONB.
Advanced Aggregations
Commission reporting requires complex aggregations across hierarchies. PostgreSQL's window functions and CTEs make this readable:
-- Monthly commission report with running totals per IB
WITH monthly_commissions AS (
SELECT
c.ib_id,
date_trunc('month', c.created_at) AS month,
SUM(c.amount) AS total,
COUNT(*) AS trade_count
FROM commissions c
WHERE c.created_at >= '2026-01-01'
GROUP BY c.ib_id, date_trunc('month', c.created_at)
)
SELECT
ib_id,
month,
total,
trade_count,
SUM(total) OVER (
PARTITION BY ib_id
ORDER BY month
ROWS UNBOUNDED PRECEDING
) AS running_total
FROM monthly_commissions
ORDER BY ib_id, month;
Connection Pooling
PostgreSQL creates a new process per connection (unlike MySQL's thread-per-connection). Under high concurrency, this matters. Use PgBouncer:
[databases]
crm = host=primary.rds.amazonaws.com port=5432 dbname=crm
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
Transaction pooling mode lets 1,000 application connections share 50 actual database connections. This is essential when running multiple Laravel workers, queue processors, and API servers.
Performance Tuning
Key PostgreSQL settings for fintech workloads:
# Memory
shared_buffers = 8GB # 25% of RAM
effective_cache_size = 24GB # 75% of RAM
work_mem = 256MB # Per-operation sort memory
# Write performance
wal_buffers = 64MB
checkpoint_completion_target = 0.9
# Query planning
random_page_cost = 1.1 # For SSD storage
effective_io_concurrency = 200 # For SSD storage
# Autovacuum (aggressive for high-write tables)
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
Monitor with pg_stat_statements to find slow queries:
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
Key Takeaways
- PostgreSQL's ACID compliance handles concurrent financial transactions more robustly than MySQL
- Native partitioning keeps trade tables performant as they grow into millions of rows
- JSONB columns accommodate varying PSP response formats without schema migrations
- Window functions and CTEs make complex commission reporting readable and fast
- PgBouncer is essential for connection pooling under high concurrency
- Tune aggressively — default PostgreSQL settings are conservative for fintech workloads
The switch from MySQL to PostgreSQL was one of the highest-ROI infrastructure decisions we made. The querying capabilities alone justified the migration.