Skip to content

dpone performance and SLO guide

This guide documents the production-oriented bulk path for large database transfers. The current high-throughput focus is PostgreSQL -> Microsoft SQL Server, Microsoft SQL Server -> ClickHouse, and SQL Server/PostgreSQL-backed state tables.

Baseline bulk path

For PostgreSQL -> SQL Server, prefer native PostgreSQL COPY TO STDOUT plus Microsoft bcp:

source:
  type: postgres
  table: {schema: public, name: orders}
  options:
    export_format: mssql-delimited
    batch_commit_mode: whole

sink:
  type: mssql
  table: {schema: dbo, name: orders}
  strategy: {mode: full_refresh}
  options:
    bulk:
      mode: bcp
      bcp:
        batch_size: 100000
        packet_size: 16384

SQL Server loads are staging-first. File artifacts are loaded with bcp into staging tables first; FULL_REFRESH, INCREMENTAL_MERGE, and REPLACE commit through shadow tables and short metadata swaps instead of direct target bcp, target TRUNCATE, or target DELETE.

Partitioned parallel extract/load

For very large tables, enable Spark JDBC-style range partitioning:

source:
  type: postgres
  table: {schema: public, name: orders}
  options:
    export_format: mssql-delimited
    batch_commit_mode: whole
    partitioning:
      strategy: range
      column: id
      bounds:
        lower: 1
        upper: 15000000
      num_partitions: 8
      export_workers: 4
      load_workers: 4

The planner creates deterministic ranges:

  • non-final ranges use partition_column >= lower AND partition_column < upper;
  • the final range uses partition_column >= lower AND partition_column <= upper;
  • each partition writes one local artifact;
  • each artifact can be loaded independently by the SQL Server sink.

This is deliberately explicit. dpone does not guess bounds for production loads because a wrong bound can silently skip data. Use source profiling or a previous control-table snapshot to fill partitioning.bounds.lower and partitioning.bounds.upper.

Canonical transfer config taxonomy

Use nested option namespaces for new manifests:

Capability Canonical path Notes
Source partitioning source.options.partitioning.* export_workers controls source artifact writers; load_workers controls target artifact loaders.
Generic bulk mode sink.options.bulk.mode Example: bcp for MSSQL.
MSSQL bcp settings sink.options.bulk.bcp.* batch_size, packet_size, table_lock, timeout_seconds, field_terminator, row_terminator.
ClickHouse direct ingest sink.options.clickhouse_bulk.* mode, http.*, client.*, insert_settings, query_id, insert_deduplication_token.

Legacy flat aliases are accepted for migration, but dpone plan emits warnings and new examples should not use them. See Config alias migration.

Local 15M stress gate

Run against local Docker services:

PYTHONUNBUFFERED=1 uv run python tools/mssql_stress.py \
  --rows 15000000 \
  --batch-size 250000 \
  --bcp-path /opt/homebrew/bin/bcp \
  --partition-column id \
  --lower-bound 1 \
  --upper-bound 15000000 \
  --num-partitions 8 \
  --export-workers 4 \
  --load-workers 4 \
  --json-output /tmp/dpone-mssql-15m.json

Add SLO thresholds when the runner is stable:

uv run python tools/mssql_stress.py \
  --rows 15000000 \
  --partition-column id \
  --num-partitions 8 \
  --slo-pg-mssql-rps 180000 \
  --slo-mssql-clickhouse-rps 140000

The command exits with code 2 when any SLO is missed.

Run the long gate on a real x86_64 Linux host or CI runner with:

  • SQL Server 2019+ or 2022 running natively, not under architecture emulation.
  • msodbcsql18, mssql-tools18, and bcp installed locally.
  • PostgreSQL and ClickHouse on the same network segment as the runner.
  • Local NVMe or high-throughput ephemeral SSD for DPONE_EXPORT_TMP_DIR.
  • Enough free disk for uncompressed TSV artifacts.

Suggested matrix:

Rows Partitions Workers Expected purpose
1,000,000 1 1 Fast correctness smoke
15,000,000 1 1 Baseline bulk path
15,000,000 8 4 Parallel extract/load gate
50,000,000 16 8 Long-running soak

Tuning notes

  • Increase num_partitions until source scan, disk, or target logging saturates.
  • Keep export_workers below the number of CPU cores and source DB worker capacity.
  • Keep source.options.partitioning.load_workers conservative on SQL Server if the target table has many indexes.
  • Prefer heap loads, TABLOCK, and delayed index creation for large initial backfills.
  • Use SQL Server simple or bulk-logged recovery during controlled bulk windows when your operational policy allows it.
  • Avoid gzip for local PostgreSQL -> SQL Server loads. Compression saves disk but usually costs throughput and cannot be consumed directly by bcp.

Current roadmap for another throughput jump

  • Native ClickHouse file ingest via clickhouse-client INSERT ... FORMAT TabSeparated or HTTP streaming to avoid Python parsing for MSSQL -> ClickHouse.
  • Partition manifest persistence for resumable failed-partition retries.
  • Auto-bound discovery guarded by a source-count reconciliation step.
  • Optional staging-per-partition tables followed by a single final merge for heavily indexed SQL Server targets.

ClickHouse native ingest modes

For MSSQL -> ClickHouse, prefer HTTP streaming when the ClickHouse HTTP port is reachable from the runner:

sink:
  type: clickhouse
  options:
    clickhouse_bulk:
      mode: http
      http:
        host: clickhouse.example.com
        port: 8123

Local Docker example:

uv run python tools/mssql_stress.py \
  --rows 15000000 \
  --partition-column id \
  --num-partitions 8 \
  --export-workers 4 \
  --load-workers 4 \
  --clickhouse-bulk-mode http \
  --clickhouse-http-host 127.0.0.1 \
  --clickhouse-http-port 18123

clickhouse-client is also supported when a client binary is available near the runner:

uv run python tools/mssql_stress.py \
  --clickhouse-bulk-mode client \
  --clickhouse-client-command "clickhouse-client" \
  --clickhouse-client-host clickhouse.example.com \
  --clickhouse-client-port 9000

For local Docker-only checks, --clickhouse-client-command "docker exec -i dpone-it-clickhouse clickhouse-client" works, but it adds Docker exec overhead and is not the preferred performance benchmark path.

Verified local 15M result

On the local Docker/Apple Silicon setup, HTTP streaming avoided Python TSV parsing and improved MSSQL -> ClickHouse throughput from roughly 129k rows/s to 783k rows/s for 15M rows.

Mode Rows Throughput
Python TSV parse + native driver 15,000,000 128,972 rows/s
HTTP streaming FORMAT TabSeparated 15,000,000 782,828 rows/s

Verified local 10k, 1M and 10M artifact

The latest local-live benchmark artifact is stored in Native transfer benchmark artifact: 2026-06-09. Historical benchmark context is kept in Native transfer benchmark artifact: 2026-06-08. It includes separate correctness and throughput tables for:

  • Postgres -> MSSQL native full-refresh transfer;
  • MSSQL -> ClickHouse native full-refresh transfer;
  • raw JSON artifact paths under test_artifacts/live_certification/benchmarks/.

New runs include phase_metrics and transfers.*.artifact diagnostics so the operator can separate source export, target load/finalize and reconciliation time instead of tuning from a single total duration.

Native transfer tuning matrix

Use tools/mssql_benchmark_suite.py when you need a controlled matrix instead of one-off runs:

PYTHONUNBUFFERED=1 uv run python tools/mssql_benchmark_suite.py \
  --rows 10000,1000000 \
  --partitions 4,8 \
  --export-workers 2,4,8 \
  --load-workers 2,4,8 \
  --batch-size 100000 \
  --bcp-path /opt/homebrew/bin/bcp \
  --optimizer-profile high_throughput_safe \
  --clickhouse-bulk-mode http \
  --clickhouse-http-host 127.0.0.1 \
  --clickhouse-http-port 58123 \
  --output-dir test_artifacts/live_certification/benchmarks/native_tuning_matrix_latest \
  --markdown-output test_artifacts/live_certification/benchmarks/native_tuning_matrix_latest/summary.md \
  --continue-on-fail

For release evidence, use the full manual profile:

PYTHONUNBUFFERED=1 uv run python tools/mssql_benchmark_suite.py \
  --rows 10000,1000000,10000000 \
  --partitions 4,8 \
  --export-workers 2,4 \
  --load-workers 2,4 \
  --batch-size 100000 \
  --bcp-path /opt/homebrew/bin/bcp \
  --optimizer-profile high_throughput_safe \
  --clickhouse-bulk-mode http \
  --clickhouse-http-host 127.0.0.1 \
  --clickhouse-http-port 58123 \
  --output-dir test_artifacts/live_certification/benchmarks/native_release_suite_latest \
  --markdown-output test_artifacts/live_certification/benchmarks/postgres_mssql_native_benchmark_summary.md

The suite writes:

  • summary.json for automation and certification services;
  • summary.md or the configured --markdown-output for human release review;
  • one scenario JSON per row/partition/export-worker/load-worker combination.

The manual GitHub workflow .github/workflows/live-certification.yml exposes the same runner through run_native_benchmark_suite=true, native_benchmark_rows, and native_benchmark_partitions.

Interpretation:

  • increase export_workers when source export is slower than target ingest and the source database still has spare CPU/IO;
  • increase load_workers when target load/finalize is slower and ClickHouse/MSSQL can accept more parallel writers;
  • lower either worker count when partition skew, target part pressure, temp disk saturation or lock waits increase;
  • compare only scenarios from the same runner and the same Docker/native service profile.

--optimizer-profile high_throughput_safe maps to:

options:
  native_transfer:
    optimizer_profile: high_throughput_safe

The profile sets safe defaults for MSSQL bcp packet/batch/timeout values and ClickHouse HTTP/insert settings. Explicit bulk.bcp.* and clickhouse_bulk.* values override profile defaults.