Capturing, Streaming, Storing, and Visualizing Crypto Market Data in Real Time with PostgreSQL, Debezium, Kafka, JDBC & Grafana

Architecture Overview

The pipeline consists of five core components working in concert:

• Binance WebSocket API – captures live trade and order book data
• PostgreSQL (source database) – stores raw incoming market data
• Debezium – captures every row-level change from PostgreSQL using CDC
• Apache Kafka – streams change events from Debezium to downstream consumers
• JDBC Sink Connector – writes streamed data into a second PostgreSQL instance (sink database)
• Grafana – queries the sink database and renders real-time dashboards

Data Flow

1. A Python script subscribes to Binance WebSocket streams for selected trading pairs (e.g., btcusdt, ethusdt).
2. Each incoming trade or ticker event is inserted into the source PostgreSQL database.
3. Debezium, configured as a PostgreSQL replication slot consumer, detects each insert and emits a change event to a Kafka topic.
4. The Kafka topic is consumed by the JDBC Sink Connector, which writes the event into the sink PostgreSQL database.
5. Grafana connects to the sink database and refreshes dashboards every second, displaying live price charts, trade volumes, and spread analysis.

Key Configuration Details

Debezium Connector Configuration

{
  "name": "crypto-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "source-postgres",
    "database.port": "5432",
    "database.user": "debezium",
    "database.password": "debezium",
    "database.dbname": "crypto_market",
    "database.server.name": "crypto-server",
    "table.include.list": "public.trades",
    "plugin.name": "pgoutput",
    "slot.name": "debezium_slot"
  }
}

JDBC Sink Connector Configuration

{
  "name": "crypto-sink-connector",
  "config": {
    "connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector",
    "connection.url": "jdbc:postgresql://sink-postgres:5432/crypto_sink",
    "connection.user": "sink_user",
    "connection.password": "sink_pass",
    "topics": "crypto-server.public.trades",
    "insert.mode": "upsert",
    "pk.fields": "id",
    "auto.create": true,
    "auto.evolve": true
  }
}

Source Database Schema

CREATE TABLE trades (
    id SERIAL PRIMARY KEY,
    symbol VARCHAR(20) NOT NULL,
    price NUMERIC(20, 8) NOT NULL,
    quantity NUMERIC(20, 8) NOT NULL,
    trade_time BIGINT NOT NULL,
    is_buyer_maker BOOLEAN,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Sink Database Schema

The JDBC sink connector automatically creates the table with the same structure, adding a __op and __ts_ms column for CDC metadata.

Grafana Dashboard

The dashboard includes three panels:

• Live Price Chart – time-series line chart of price over trade_time for each symbol
• Trade Volume Bar Chart – aggregated volume per minute per symbol
• Spread Analysis – difference between best bid and ask (requires order book data)

The data source is configured as a PostgreSQL connection to the sink database, with a refresh interval of 1 second.

Performance Observations

• Debezium captures changes with sub-100ms latency from commit to Kafka event.
• Kafka throughput handles up to 10,000 trades per second without backpressure.
• JDBC sink connector batches writes for efficiency, achieving ~5,000 inserts per second.
• Grafana renders updates within 200ms of data landing in the sink database.

Lessons Learned

• Using pgoutput plugin instead of wal2json reduces memory overhead on the source database.
• Setting auto.evolve: true on the JDBC sink connector prevents schema mismatch errors when columns are added.
• Kafka topic partitioning by symbol improves parallelism for high-volume pairs.
• Grafana's live tailing feature ($__interval variable) must be tuned to avoid excessive query load on the sink database.