How Crypto Exchanges Store Every Operation

Every real cryptocurrency exchange maintains a comprehensive relational database (typically PostgreSQL, MySQL, or CockroachDB) that records every single user operation. This is not optional — it is mandated by financial regulations (MiCA in the EU, FinCEN in the US, MAS in Singapore) and is critical for:

• Regulatory compliance — Exchanges must produce transaction records on demand for auditors and law enforcement.
• Dispute resolution — When a user claims a deposit was not credited, the exchange needs immutable proof.
• Fraud detection — Anomalous patterns (e.g., rapid withdrawals from multiple IPs) trigger automated alerts.
• Financial reconciliation — The sum of all ledger entries must match the actual on-chain balances at all times.

Binance processes over 1.4 million transactions per second at peak. Coinbase stores billions of historical records. The database is the single source of truth for all off-chain operations.

The core of any exchange database is a unified ledger_entries table. Here is a real-world schema:

CREATE TABLE ledger_entries (
  id             BIGSERIAL PRIMARY KEY,
  user_id        UUID NOT NULL REFERENCES users(id),
  asset          VARCHAR(16) NOT NULL,
  entry_type     VARCHAR(24) NOT NULL,
  amount         NUMERIC(28,8) NOT NULL,
  balance_after  NUMERIC(28,8) NOT NULL,
  reference_id   VARCHAR(128),
  reference_type VARCHAR(30),
  description    TEXT,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Key design principles:

• Append-only — Entries are never updated or deleted. If a correction is needed, a compensating entry is inserted.
• Double-entry accounting — Every trade generates at least two entries: a debit from the buyer's quote asset and a credit to the buyer's base asset.
• balance_after — Running balance snapshot after each entry, enabling instant balance reconstruction at any point in time.
• reference_id — Links each entry back to the originating order, trade, deposit, or withdrawal for traceability.

Entry types include: deposit, withdrawal, trade, fee, transfer, order_lock, order_unlock.

Beyond the ledger, exchanges maintain separate tables for the order lifecycle:

CREATE TABLE orders (
  id          UUID PRIMARY KEY,
  user_id     UUID NOT NULL,
  pair        VARCHAR(20) NOT NULL,
  side        VARCHAR(4) NOT NULL,
  order_type  VARCHAR(10) NOT NULL,
  price       NUMERIC(28,8),
  amount      NUMERIC(28,8) NOT NULL,
  filled      NUMERIC(28,8) DEFAULT 0,
  remaining   NUMERIC(28,8) NOT NULL,
  status      VARCHAR(20) NOT NULL,
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  updated_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE trades ( id UUID PRIMARY KEY, pair VARCHAR(20) NOT NULL, buy_order_id UUID REFERENCES orders(id), sell_order_id UUID REFERENCES orders(id), buyer_id UUID NOT NULL, seller_id UUID NOT NULL, price NUMERIC(28,8) NOT NULL, amount NUMERIC(28,8) NOT NULL, quote_amount NUMERIC(28,8) NOT NULL, buyer_fee NUMERIC(28,8) NOT NULL, seller_fee NUMERIC(28,8) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); ```

Binance uses an in-memory matching engine (Java/C++) processing orders in microseconds. It emits trade events via Kafka, persisted asynchronously to the database. The order book itself lives in memory.

Coinbase uses a PostgreSQL-based system with an in-memory matching engine. Trade events are streamed to a separate analytics pipeline (Apache Flink) for real-time risk monitoring.

Separate from financial ledger entries, exchanges maintain a comprehensive audit_log table that tracks every user action:

CREATE TABLE audit_log (
  id          BIGSERIAL PRIMARY KEY,
  user_id     UUID NOT NULL,
  action      VARCHAR(60) NOT NULL,
  category    VARCHAR(30) NOT NULL,
  details     JSONB DEFAULT '{}',
  ip_address  INET,
  user_agent  TEXT,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

Actions include: login, login_failed, logout, order.place, order.cancel, kyc.submit, 2fa.enable, api_key.create, password.change, withdrawal.request, address_whitelist.add.

Why JSONB for details? Different actions carry different metadata. A trade event includes {order_id, pair, side, amount, price, fee}. A login event includes {device_type, os, browser}. JSONB allows heterogeneous data without schema changes.

Retention: Binance retains audit logs 5+ years (regulatory). Coinbase (NASDAQ: COIN) retains indefinitely for SEC compliance. Most exchanges use partitioned tables or TimescaleDB for efficient storage of billions of rows.

Deposit and withdrawal tracking links off-chain records to on-chain blockchain transactions.

The deposit flow (Binance model): 1. User requests a deposit address — exchange generates it from an HD wallet (BIP-32/BIP-44). 2. A blockchain monitor daemon (full node) detects an incoming transaction. 3. A row is inserted into deposits with status='pending'. 4. As blocks are mined, confirmations is incremented. BTC requires 3; ETH requires 12. 5. When confirmations >= required, status becomes 'completed' and the user's balance is credited via a ledger entry.

The withdrawal flow: 1. User requests withdrawal → row inserted with status='pending'. 2. Risk engine checks: amount thresholds, address whitelist, velocity limits, KYC level. 3. If manual review is required, an admin must approve. 4. The exchange's hot wallet signing service broadcasts the transaction → tx_hash is stored. 5. Status is updated to 'completed' once the blockchain confirms.

At the scale of Binance (1.4M TPS peak), naive queries collapse. Real exchanges use:

Indexing: ``sql CREATE INDEX idx_ledger_user_time ON ledger_entries(user_id, created_at DESC); CREATE INDEX idx_orders_pair_status ON orders(pair, status); CREATE INDEX idx_orders_open ON orders(pair, price) WHERE status = 'open'; ``

Sharding: Binance shards by user_id — each user's data lives on a specific database shard, enabling horizontal scaling to thousands of instances.

Read replicas: Writes go to the primary database. Reads (balances, trade history) are served from replicas with sub-second replication lag.

CQRS pattern: The matching engine and query layer use separate databases. The matching engine writes to Kafka. Consumers update read-optimized databases.

Hot vs. cold data: Recent data (last 90 days) stays in PostgreSQL. Historical data is archived to columnar stores (Parquet on S3, or ClickHouse) for analytics and compliance.