JupLend PostgreSQL to ClickHouse Migration Tool

A high-performance Rust-based migration tool for transferring jup lend operations data from PostgreSQL to ClickHouse with parallel processing, progress tracking, and data verification.

Features

• ✅ Two Migration Strategies

  • Range-based (recommended for large tables)
  • Offset-based (simpler, good for smaller datasets)

• ✅ Parallel Processing

  • Concurrent batch processing
  • Configurable concurrency levels
  • Efficient memory usage

• ✅ Data Integrity

  • Automatic verification
  • Transaction safety
  • Error handling and retry logic

• ✅ Performance Optimized

  • Batch processing
  • Async I/O
  • Progress tracking
  • Post-migration optimization

• ✅ ClickHouse Optimizations

  • MergeTree engine with partitioning
  • Optimized indexing strategy
  • LowCardinality for categorical data
  • Decimal128 for precise amount handling

Prerequisites

• Rust 1.70+ (install from rustup.rs)
• PostgreSQL 12+ with juplend_operations table
• ClickHouse 23.3+
• Network access between databases

Installation

1. Clone or extract the project

   cd juplend-migration

2. Configure environment variables

   cp .env.example .env
   nano .env  # Edit with your database credentials

3. Build the project

   cargo build --release

   The binary will be at: target/release/juplend-migration

Configuration

Edit .env file with your database credentials:

# PostgreSQL Configuration
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_USER=your_user
POSTGRES_PASSWORD=your_password
POSTGRES_DB=your_database

# ClickHouse Configuration
CLICKHOUSE_HOST=localhost
CLICKHOUSE_PORT=9000
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=
CLICKHOUSE_DB=juplend
CLICKHOUSE_USE_HTTPS=false  # Set to true for HTTPS (port 8443)

# Migration Settings
BATCH_SIZE=10000
MAX_CONCURRENT_BATCHES=4

Configuration Parameters

• BATCH_SIZE: Number of rows per batch (default: 10000)

  • Smaller values: Lower memory usage, more database roundtrips
  • Larger values: Higher throughput, more memory usage
  • Recommended: 10000-50000 for most cases

• MAX_CONCURRENT_BATCHES: Number of parallel batch operations (default: 4)

  • Increase for better throughput on powerful systems
  • Decrease if experiencing connection issues
  • Recommended: 2-8 depending on database capacity

Usage

Basic Migration (Recommended)

./target/release/juplend-migration

This uses the range-based strategy with all verification and optimization steps.

With Terminal Dashboard

./target/release/juplend-migration --dashboard

Uses a beautiful terminal UI with real-time stats, graphs, and keyboard controls. See DASHBOARD.md for details.

Migration Options

# Use offset-based strategy
./target/release/juplend-migration --strategy offset

# Skip schema initialization (if already created)
./target/release/juplend-migration --skip-init

# Skip verification step
./target/release/juplend-migration --skip-verify

# Skip optimization step
./target/release/juplend-migration --skip-optimize

# Dry run (test connections and check data)
./target/release/juplend-migration --dry-run

# Custom logging level
RUST_LOG=debug ./target/release/juplend-migration

Migration Strategies

Range-Based (Default - Recommended)

./target/release/juplend-migration --strategy range

Pros:

• More efficient for large tables
• Better performance with indexes
• Predictable batch sizes
• Less lock contention on PostgreSQL

Use when:

• Table has millions of rows
• ID column is sequential
• Performance is critical

Offset-Based

./target/release/juplend-migration --strategy offset

Pros:

• Simpler implementation
• Works with any data distribution
• Good for smaller tables

Use when:

• Table has < 1 million rows
• IDs are not sequential
• Simplicity is preferred

ClickHouse Schema

The migration creates an optimized ClickHouse schema:

CREATE TABLE juplend_operations
(
    id Int64,
    signature String,
    slot Int64,
    instruction_index Int32,
    user_address String,
    action_type LowCardinality(String),
    token_mint String,
    amount Int64,
    created_at DateTime64(3, 'UTC')
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (action_type, created_at, user_address, id)
SETTINGS index_granularity = 8192

Schema Optimizations

1. Partitioning: Monthly partitions for time-series queries
2. Ordering: Optimized for common query patterns
3. LowCardinality: Efficient storage for action_type
4. Int64: Native integer type for amount values
5. DateTime64: Microsecond precision timestamps

Performance Tips

For Large Migrations (>10M rows)

1. Increase batch size

   BATCH_SIZE=50000

2. Tune concurrency

   MAX_CONCURRENT_BATCHES=8

3. Use range-based strategy

   ./target/release/juplend-migration --strategy range

4. Optimize PostgreSQL

   -- Increase work_mem for faster sorts
   SET work_mem = '256MB';

5. Optimize ClickHouse

   -- Increase max_insert_threads
   SET max_insert_threads = 8;

For Slow Networks

1. Reduce concurrency

   MAX_CONCURRENT_BATCHES=2

2. Increase batch size

   BATCH_SIZE=20000

For Memory-Constrained Systems

1. Reduce batch size

   BATCH_SIZE=5000

2. Reduce concurrency

   MAX_CONCURRENT_BATCHES=2

Verification

The tool automatically verifies:

• Row count matches between databases
• All batches completed successfully
• No data loss during migration

To skip verification:

./target/release/juplend-migration --skip-verify

Post-Migration

After successful migration:

1. Check table statistics

   SELECT
       count() as total_rows,
       formatReadableSize(sum(bytes)) as total_size,
       count(DISTINCT user_address) as unique_users,
       count(DISTINCT token_mint) as unique_tokens,
       min(created_at) as min_date,
       max(created_at) as max_date
   FROM juplend_operations;

2. Verify data samples

   SELECT * FROM juplend_operations LIMIT 10;

3. Test query performance

   -- Query by action type
   SELECT action_type, count() 
   FROM juplend_operations 
   GROUP BY action_type;
   
   -- Query by user
   SELECT * 
   FROM juplend_operations 
   WHERE user_address = 'USER_ADDRESS' 
   ORDER BY created_at DESC 
   LIMIT 100;

Troubleshooting

Connection Issues

Error: Failed to connect to PostgreSQL

• Check host, port, and credentials
• Verify network connectivity
• Check firewall rules

Error: Failed to connect to ClickHouse

• Verify ClickHouse is running
• Check HTTP interface is enabled (port 8123 or 9000)
• Verify credentials

Migration Issues

Error: Out of memory

• Reduce BATCH_SIZE
• Reduce MAX_CONCURRENT_BATCHES
• Increase system memory

Error: Too many connections

• Reduce MAX_CONCURRENT_BATCHES
• Increase PostgreSQL max_connections
• Check for connection leaks

Slow migration speed

• Increase BATCH_SIZE
• Increase MAX_CONCURRENT_BATCHES
• Use range-based strategy
• Check network latency
• Verify database indexes

Verification Failures

Error: Count mismatch

• Check for ongoing writes to PostgreSQL
• Verify no duplicate key violations
• Check ClickHouse logs for insert errors

Development

Run in development mode

cargo run -- --dry-run

Run tests

cargo test

Enable debug logging

RUST_LOG=debug cargo run

Next Steps

After successful migration, consider:

1. Setting up the performance dashboard (separate guide)
2. Implementing CI/CD pipeline
3. Creating materialized views for common queries
4. Setting up monitoring and alerts
5. Implementing incremental sync for ongoing data

Support

For issues or questions:

1. Check logs with RUST_LOG=debug
2. Verify database connections with --dry-run
3. Review ClickHouse system tables for errors

License

MIT License - see LICENSE file for details