sql-benchmarks.yml

name: "SQL-related benchmarks"

on:
  workflow_call:
    inputs:
      mode:
        required: true
        type: string
      machine_type:
        required: false
        type: string
        default: c6id.metal
      benchmark_matrix:
        required: false
        type: string
        description: "JSON string containing the matrix configuration"
        # We do not include lance in the default configuration.
        default: |
          [
            {
              "id": "clickbench-nvme",
              "subcommand": "clickbench",
              "name": "Clickbench on NVME",
              "targets": "datafusion:parquet,datafusion:vortex,duckdb:parquet,duckdb:vortex,duckdb:duckdb"
            },
            {
              "id": "tpch-nvme",
              "subcommand": "tpch",
              "name": "TPC-H SF=1 on NVME",
              "targets": "datafusion:arrow,datafusion:parquet,datafusion:vortex,datafusion:vortex-compact,duckdb:parquet,duckdb:vortex,duckdb:vortex-compact,duckdb:duckdb",
              "scale_factor": "1.0"
            },
            {
              "id": "tpch-s3",
              "subcommand": "tpch",
              "name": "TPC-H SF=1 on S3",
              "local_dir": "vortex-bench/data/tpch/1.0",
              "remote_storage": "s3://vortex-ci-benchmark-datasets/${{github.ref_name}}/${{github.run_id}}/tpch/1.0/",
              "targets": "datafusion:parquet,datafusion:vortex,datafusion:vortex-compact,duckdb:parquet,duckdb:vortex,duckdb:vortex-compact",
              "scale_factor": "1.0",
              "iterations": "10"
            },
            {
              "id": "tpch-nvme-10",
              "subcommand": "tpch",
              "name": "TPC-H SF=10 on NVME",
              "targets": "datafusion:arrow,datafusion:parquet,datafusion:vortex,datafusion:vortex-compact,duckdb:parquet,duckdb:vortex,duckdb:vortex-compact,duckdb:duckdb",
              "scale_factor": "10.0"
            },
            {
              "id": "tpch-nvme-100-duckdb",
              "subcommand": "tpch",
              "name": "TPC-H SF=100 on NVME with DuckDB",
              "targets": "duckdb:parquet,duckdb:vortex,duckdb:vortex-compact,duckdb:duckdb",
              "scale_factor": "100.0"
            },
            {
              "id": "tpch-nvme-100-df",
              "subcommand": "tpch",
              "name": "TPC-H SF=100 on NVME with DataFusion",
              "targets": "datafusion:parquet,datafusion:vortex,datafusion:vortex-compact",
              "scale_factor": "100.0"
            },
            {
              "id": "tpch-s3-10",
              "subcommand": "tpch",
              "name": "TPC-H SF=10 on S3",
              "local_dir": "vortex-bench/data/tpch/10.0",
              "remote_storage": "s3://vortex-ci-benchmark-datasets/${{github.ref_name}}/${{github.run_id}}/tpch/10.0/",
              "targets": "datafusion:parquet,datafusion:vortex,datafusion:vortex-compact,duckdb:parquet,duckdb:vortex,duckdb:vortex-compact",
              "scale_factor": "10.0",
              "iterations": "10"
            },
            {
              "id": "tpcds-nvme",
              "subcommand": "tpcds",
              "name": "TPC-DS SF=1 on NVME",
              "targets": "datafusion:parquet,datafusion:vortex,datafusion:vortex-compact,duckdb:parquet,duckdb:vortex,duckdb:vortex-compact,duckdb:duckdb",
              "scale_factor": "1.0"
            },
            {
              "id": "statpopgen",
              "subcommand": "statpopgen",
              "name": "Statistical and Population Genetics",
              "targets": "duckdb:parquet,duckdb:vortex,duckdb:vortex-compact",
              "scale_factor": "100"
            },
            {
              "id": "fineweb",
              "subcommand": "fineweb",
              "name": "FineWeb NVMe",
              "targets": "datafusion:parquet,datafusion:vortex,datafusion:vortex-compact,duckdb:parquet,duckdb:vortex,duckdb:vortex-compact",
              "scale_factor": "100"
            },
            {
              "id": "fineweb-s3",
              "subcommand": "fineweb",
              "name": "FineWeb S3",
              "local_dir": "vortex-bench/data/fineweb",
              "remote_storage": "s3://vortex-ci-benchmark-datasets/${{github.ref_name}}/${{github.run_id}}/fineweb/",
              "targets": "datafusion:parquet,datafusion:vortex,datafusion:vortex-compact,duckdb:parquet,duckdb:vortex,duckdb:vortex-compact",
              "scale_factor": "100",
              "iterations": "10"
            },
            {
              "id": "polarsignals",
              "subcommand": "polarsignals",
              "name": "PolarSignals Profiling",
              "targets": "datafusion:vortex",
              "scale_factor": "1"
            },
          ]

jobs:
  bench:
    timeout-minutes: 120
    strategy:
      fail-fast: false
      matrix:
        include: ${{ fromJSON(inputs.benchmark_matrix) }}

    runs-on: >-
      ${{ github.repository == 'vortex-data/vortex'
          && format('runs-on={0}/runner=bench-dedicated/tag={1}{2}', github.run_id, matrix.id, (inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false) && '/extras=s3-cache' || '')
          || 'ubuntu-latest' }}
    steps:
      - uses: runs-on/action@v2
        if: inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false
        with:
          sccache: s3
      - uses: actions/checkout@v6
        if: inputs.mode == 'pr'
        with:
          ref: ${{ github.event.pull_request.head.sha }}

      - uses: actions/checkout@v6
        if: inputs.mode != 'pr'
      - name: Setup benchmark environment
        run: sudo bash scripts/setup-benchmark.sh
      - uses: ./.github/actions/setup-rust
        with:
          repo-token: ${{ secrets.GITHUB_TOKEN }}

      - name: Install DuckDB
        run: |
          wget -qO- https://github.com/duckdb/duckdb/releases/download/v1.4.2/duckdb_cli-linux-amd64.zip | funzip > duckdb
          chmod +x duckdb
          echo "$PWD" >> $GITHUB_PATH

      - uses: ./.github/actions/system-info

      - name: Build binaries
        shell: bash
        env:
          RUSTFLAGS: "-C target-cpu=native -C force-frame-pointers=yes"
        run: |
          packages="--bin data-gen --bin datafusion-bench --bin duckdb-bench"
          if [ "${{ matrix.build_lance }}" = "true" ]; then
            packages="$packages --bin lance-bench"
          fi
          cargo build $packages --profile release_debug

      - name: Generate data
        shell: bash
        env:
          RUST_BACKTRACE: full
        run: |
          # Extract all unique formats from targets (e.g., "datafusion:parquet,duckdb:vortex" -> "parquet,vortex")
          all_formats=$(echo "${{ matrix.targets }}" | tr ',' '\n' | sed 's/^[^:]*://' | sort -u | tr '\n' ',' | sed 's/,$//')

          # Build options string if scale_factor is set
          opts=""
          if [ -n "${{ matrix.scale_factor }}" ]; then
            opts="--opt scale-factor=${{ matrix.scale_factor }}"
          fi

          # Generate all data formats with a single command
          target/release_debug/data-gen ${{ matrix.subcommand }} \
            --formats "$all_formats" \
            $opts

      - name: Setup AWS CLI
        if: inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false
        uses: aws-actions/configure-aws-credentials@v6
        with:
          role-to-assume: arn:aws:iam::245040174862:role/GitHubBenchmarkRole
          aws-region: us-east-1

      - name: Upload data
        if: matrix.remote_storage != null && (inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false)
        shell: bash
        env:
          AWS_REGION: "us-east-1"
        run: |
          aws s3 rm --recursive ${{ matrix.remote_storage }}
          aws s3 cp --recursive ${{matrix.local_dir}} ${{ matrix.remote_storage }}

      - name: Setup Polar Signals
        if: inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false
        uses: polarsignals/gh-actions-ps-profiling@v0.8.1
        with:
          polarsignals_cloud_token: ${{ secrets.POLAR_SIGNALS_API_KEY }}
          labels: "branch=${{ github.ref_name }};gh_run_id=${{ github.run_id }};benchmark=${{ matrix.id }}"
          project_uuid: "e5d846e1-b54c-46e7-9174-8bf055a3af56"
          profiling_frequency: 199
          extra_args: "--off-cpu-threshold=0.03"  # Personally tuned by @brancz

      - name: Run ${{ matrix.name }} benchmark
        if: matrix.remote_storage == null || github.event.pull_request.head.repo.fork == true
        shell: bash
        env:
          OTEL_SERVICE_NAME: "vortex-bench"
          OTEL_EXPORTER_OTLP_PROTOCOL: "http/protobuf"
          OTEL_EXPORTER_OTLP_ENDPOINT: "${{ (inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false) && secrets.OTEL_EXPORTER_OTLP_ENDPOINT || '' }}"
          OTEL_EXPORTER_OTLP_HEADERS: "${{ (inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false) && secrets.OTEL_EXPORTER_OTLP_HEADERS || '' }}"
          OTEL_RESOURCE_ATTRIBUTES: "bench-name=${{ matrix.id }}"
        run: |
          bash scripts/bench-taskset.sh .github/scripts/run-sql-bench.sh "${{ matrix.subcommand }}" "${{ matrix.targets }}" \
            ${{ matrix.iterations && format('--iterations {0}', matrix.iterations) || '' }} \
            ${{ matrix.scale_factor && format('--scale-factor {0}', matrix.scale_factor) || '' }}

      - name: Run ${{ matrix.name }} benchmark (remote)
        if: matrix.remote_storage != null && (inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false)
        shell: bash
        env:
          AWS_REGION: "us-east-1"
          OTEL_SERVICE_NAME: "vortex-bench"
          OTEL_EXPORTER_OTLP_PROTOCOL: "http/protobuf"
          OTEL_EXPORTER_OTLP_ENDPOINT: "${{ (inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false) && secrets.OTEL_EXPORTER_OTLP_ENDPOINT || '' }}"
          OTEL_EXPORTER_OTLP_HEADERS: "${{ (inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false) && secrets.OTEL_EXPORTER_OTLP_HEADERS || '' }}"
          OTEL_RESOURCE_ATTRIBUTES: "bench-name=${{ matrix.id }}"
        run: |
          bash scripts/bench-taskset.sh .github/scripts/run-sql-bench.sh "${{ matrix.subcommand }}" "${{ matrix.targets }}" \
            ${{ matrix.iterations && format('--iterations {0}', matrix.iterations) || '' }} \
            --remote-storage "${{ matrix.remote_storage }}" \
            --benchmark-id "${{ matrix.id }}" \
            ${{ matrix.scale_factor && format('--scale-factor {0}', matrix.scale_factor) || '' }}

      - name: Install uv
        if: inputs.mode == 'pr'
        uses: spiraldb/actions/.github/actions/setup-uv@0.18.5
        with:
          sync: false
      - name: Compare results
        if: inputs.mode == 'pr'
        shell: bash
        run: |
          set -Eeu -o pipefail -x

          base_commit_sha=$(\
            curl -L \
              -H "Accept: application/vnd.github+json" \
              -H "Authorization: Bearer ${{ secrets.GITHUB_TOKEN }}" \
              https://api.github.com/repos/vortex-data/vortex/actions/workflows/bench.yml/runs\?branch\=develop\&status\=success\&per_page\=1 \
            | jq -r '.workflow_runs[].head_sha' \
          )

          python3 scripts/s3-download.py s3://vortex-ci-benchmark-results/data.json.gz data.json.gz --no-sign-request
          gzip -d -c data.json.gz | grep $base_commit_sha > base.json

          echo '# Benchmarks: ${{ matrix.name }}' > comment.md
          echo '' >> comment.md
          uv run --no-project scripts/compare-benchmark-jsons.py base.json results.json "${{ matrix.name }}" \
            >> comment.md
          cat comment.md >> $GITHUB_STEP_SUMMARY

      - name: Comment PR
        if: inputs.mode == 'pr' && github.event.pull_request.head.repo.fork == false
        uses: thollander/actions-comment-pull-request@v3
        with:
          file-path: comment.md
          # There is exactly one comment per comment-tag. If a comment with this tag already exists,
          # this action will *update* the comment instead of posting a new comment. Therefore, each
          # unique benchmark configuration must have a unique comment-tag.
          comment-tag: bench-pr-comment-${{ matrix.id }}

      - name: Comment PR on failure
        if: failure() && inputs.mode == 'pr' && github.event.pull_request.head.repo.fork == false
        uses: thollander/actions-comment-pull-request@v3
        with:
          message: |
            # 🚨🚨🚨❌❌❌ SQL BENCHMARK FAILED ❌❌❌🚨🚨🚨

            Benchmark `${{ matrix.name }}` failed! Check the [workflow run](${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}) for details.
          comment-tag: bench-pr-comment-${{ matrix.id }}

      - name: Upload Benchmark Results
        if: inputs.mode == 'develop'
        shell: bash
        run: |
          bash scripts/cat-s3.sh vortex-ci-benchmark-results data.json.gz results.json

      - name: Alert incident.io
        if: failure() && inputs.mode == 'develop'
        uses: ./.github/actions/alert-incident-io
        with:
          api-key: ${{ secrets.INCIDENT_IO_ALERT_TOKEN }}
          alert-title: "${{ matrix.name }} SQL benchmark failed on develop"
          deduplication-key: ci-sql-bench-${{ matrix.id }}-failure