sql-bench-matrix.yml

name: SQL Benchmark Matrix

on:
  workflow_call:
    inputs:
      mode:
        required: true
        type: string
      matrix_preset:
        required: true
        type: string
        description: "Named benchmark matrix to run"
      machine_type:
        required: false
        type: string
        default: c8gd.metal-24xl

jobs:
  resolve-matrix:
    runs-on: ubuntu-latest
    timeout-minutes: 10
    permissions:
      contents: read
    outputs:
      benchmark_matrix: ${{ steps.resolve.outputs.benchmark_matrix }}
    steps:
      - uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1  # v7
        with:
          ref: ${{ inputs.mode == 'pr' && github.event.pull_request.head.sha || github.sha }}
          persist-credentials: false
      - name: Install uv
        uses: spiraldb/actions/.github/actions/setup-uv@a746510eafaa926484c354541cfc49b2ec06cc63  # 0.18.6
        with:
          sync: false
      - name: Resolve benchmark matrix
        id: resolve
        shell: bash
        env:
          MATRIX_PRESET: ${{ inputs.matrix_preset }}
        run: |
          set -Eeuo pipefail

          matrix="$(uv run --project bench-orchestrator vx-bench matrix "$MATRIX_PRESET")"
          echo "benchmark_matrix=$matrix" >> "$GITHUB_OUTPUT"

  build:
    timeout-minutes: 60
    env:
      FLAT_LAYOUT_INLINE_ARRAY_NODE: "1"
    runs-on: >-
      ${{ github.repository == 'vortex-data/vortex'
          && format('runs-on={0}/region=us-east-1/runner=bench-dedicated/family=c8gd.8xlarge+c8g.8xlarge+c8gn.8xlarge+m8gd.8xlarge+m8g.8xlarge+r8gd.8xlarge+r8g.8xlarge+x8g.8xlarge+i8g.8xlarge/image=ubuntu24-full-arm64-pre-v2/tag=build{1}', github.run_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@3d3c42e5aac5ba805825da76410c181273ba90b1  # v7
        with:
          ref: ${{ inputs.mode == 'pr' && github.event.pull_request.head.sha || github.sha }}
      - uses: ./.github/actions/assert-cpu
        if: github.repository == 'vortex-data/vortex'
      - uses: ./.github/actions/setup-prebuild
        with:
          repo-token: ${{ secrets.GITHUB_TOKEN }}
          enable-sccache: ${{ (inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false) && 'true' || 'false' }}
      - 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 [ "${{ inputs.mode }}" != "pr" ]; then
            packages+=(--bin lance-bench)
          fi
          cargo build "${packages[@]}" --profile release_debug
      - parallel:
          - name: Upload binaries
            uses: actions/upload-artifact@cf430e030ddbb5b0abf93d22962f4752f3646cd9  # v7
            with:
              name: bench-binaries
              path: |
                target/release_debug/data-gen
                target/release_debug/datafusion-bench
                target/release_debug/duckdb-bench
                target/release_debug/lance-bench
                target/release_debug/build/vortex-duckdb-cache/duckdb-lib-*-prebuilt/libduckdb.so
          - name: Pre-upload SQL benchmark debuginfo to Polar Signals
            if: inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false
            uses: ./.github/actions/upload-parca-debuginfo
            continue-on-error: true
            with:
              paths: |
                target/release_debug/datafusion-bench
                target/release_debug/duckdb-bench
                ${{ inputs.mode != 'pr' && 'target/release_debug/lance-bench' || '' }}
                polarsignals-cloud-token: ${{ secrets.POLAR_SIGNALS_API_KEY }}
                project-id: "e5d846e1-b54c-46e7-9174-8bf055a3af56"

  bench:
    needs: [resolve-matrix, build]
    timeout-minutes: 120
    env:
      FLAT_LAYOUT_INLINE_ARRAY_NODE: "1"
      # Makes python output nicer
      COLUMNS: 120
    strategy:
      fail-fast: false
      matrix:
        include: ${{ fromJSON(needs.resolve-matrix.outputs.benchmark_matrix) }}

    runs-on: >-
      ${{ github.repository == 'vortex-data/vortex'
          && format('runs-on={0}/region=us-east-1/runner=bench-dedicated/family={1}/image=ubuntu24-full-arm64-pre-v2/tag={2}{3}', github.run_id, inputs.machine_type, 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@3d3c42e5aac5ba805825da76410c181273ba90b1  # v7
        with:
          ref: ${{ inputs.mode == 'pr' && github.event.pull_request.head.sha || github.sha }}
          fetch-depth: ${{ inputs.mode == 'pr' && '0' || '1' }}
      - uses: ./.github/actions/setup-prebuild
        with:
          repo-token: ${{ secrets.GITHUB_TOKEN }}
          enable-sccache: ${{ (inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false) && 'true' || 'false' }}
      - name: Install uv
        uses: spiraldb/actions/.github/actions/setup-uv@a746510eafaa926484c354541cfc49b2ec06cc63  # 0.18.6
        with:
          sync: false

      - name: Install DuckDB
        uses: ./.github/actions/setup-duckdb

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

      - name: Download binaries
        uses: actions/download-artifact@9000827ccba6bdab643e8b6fd33ac0654aef8333  # v8
        with:
          name: bench-binaries
          path: target/release_debug/
      - name: Make binaries executable
        shell: bash
        run: chmod +x target/release_debug/*

      - name: Setup AWS CLI
        if: inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false
        uses: aws-actions/configure-aws-credentials@e1253824e5c10ff9df46874f81ed3ec929e19cfd  # v6
        with:
          role-to-assume: arn:aws:iam::245040174862:role/GitHubBenchmarkRole
          aws-region: us-east-1
          # Expand session window, otherwise some large scale runs time out.
          role-duration-seconds: 7200

      # Base data that vx-bench caches (statpopgen, sorted ClickBench) is restored
      # from, or saved to, an S3 cache. Fork PRs have no AWS credentials and always regenerate.
      - name: Generate data
        shell: bash
        env:
          AWS_REGION: "us-east-1"
          RUST_BACKTRACE: full
          S3_CACHE: ${{ (inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false) && 's3://vortex-ci-benchmark-datasets/data-gen-cache/' || '' }}
        run: |
          uv run --project bench-orchestrator vx-bench prepare-data "${{ matrix.subcommand }}" \
            --formats-json '${{ toJSON(matrix.data_formats) }}' \
            ${S3_CACHE:+--s3-cache "$S3_CACHE"} \
            ${{ matrix.scale_factor && format('--opt scale-factor={0}', matrix.scale_factor) || '' }}

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

      - name: Setup Polar Signals
        if: inputs.mode != 'pr' || github.event.pull_request.head.repo.fork == false
        uses: polarsignals/gh-actions-ps-profiling@68ae857e375a826606352016e5b90f01a2a7ff7a  # v0.8.1
        # Profiling is observability only, so a flaky agent download must not fail the benchmark.
        continue-on-error: true
        with:
          polarsignals_cloud_token: ${{ secrets.POLAR_SIGNALS_API_KEY }}
          labels: "branch=${{ github.ref_name }};gh_run_id=${{ github.run_id }};commit_sha=${{ inputs.mode == 'pr' && github.event.pull_request.head.sha || github.sha }};benchmark=${{ matrix.id }}"
          project_uuid: "e5d846e1-b54c-46e7-9174-8bf055a3af56"
          job_name: "${{ matrix.id }}"
          profiling_frequency: 199
          extra_args: "--debuginfo-strip=false"
          parca_agent_version: "0.49.0"

      - name: Setup benchmark environment
        run: sudo bash scripts/setup-benchmark.sh

      - name: Run ${{ matrix.name }} benchmark
        if: matrix.remote_key == 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 uv run --project bench-orchestrator vx-bench run "${{ matrix.subcommand }}" \
            --targets-json '${{ toJSON(matrix.targets) }}' \
            --output results.json \
            --ingest-jsonl results.ingest.jsonl \
            --no-build \
            ${{ matrix.iterations && format('--iterations {0}', matrix.iterations) || '' }} \
            ${{ matrix.scale_factor && format('--opt scale-factor={0}', matrix.scale_factor) || '' }}

      - name: Run ${{ matrix.name }} benchmark (remote)
        if: matrix.remote_key != 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 }}"
          REMOTE_STORAGE: "s3://vortex-ci-benchmark-datasets/${{ github.ref_name }}/${{ github.run_id }}/${{ inputs.matrix_preset }}/${{ matrix.remote_key }}/"
        run: |
          bash scripts/bench-taskset.sh uv run --project bench-orchestrator vx-bench run "${{ matrix.subcommand }}" \
            --targets-json '${{ toJSON(matrix.targets) }}' \
            --output results.json \
            --ingest-jsonl results.ingest.jsonl \
            --no-build \
            ${{ matrix.iterations && format('--iterations {0}', matrix.iterations) || '' }} \
            --opt remote-data-dir="$REMOTE_STORAGE" \
            ${{ matrix.scale_factor && format('--opt scale-factor={0}', matrix.scale_factor) || '' }}

      - name: Capture file sizes
        if: matrix.remote_key == null
        shell: bash
        run: |
          uv run --no-project scripts/capture-file-sizes.py \
            vortex-bench/data \
            --benchmark ${{ matrix.subcommand }} \
            --dataset-dir ${{ matrix.dataset_dir }} \
            --commit ${{ inputs.mode == 'pr' && github.event.pull_request.head.sha || github.sha }} \
            -o sizes.json
          cat sizes.json >> results.json

      - name: Fetch baseline from the benchmarks database
        if: inputs.mode == 'pr'
        uses: ./.github/actions/fetch-bench-baseline
        with:
          role-arn: ${{ vars.GH_BENCH_READ_ROLE_ARN }}
          region: ${{ vars.RDS_BENCH_REGION }}
          endpoint: ${{ vars.RDS_BENCH_INSTANCE_ENDPOINT }}
          database: ${{ vars.RDS_BENCH_DB_NAME }}

      - name: Compare results
        if: inputs.mode == 'pr'
        shell: bash
        run: |
          set -Eeu -o pipefail -x

          uv run --no-project scripts/compare-benchmark-jsons.py base.ingest.jsonl results.json \
            "${{ matrix.name }}" --pr-records results.ingest.jsonl > 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@24bffb9b452ba05a4f3f77933840a6a841d1b32b  # 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. The preset is
          # part of the tag because presets overlap on benchmark ids (`pr` and `pr-compact` share
          # nine) and can run concurrently, so a preset-less tag would let them clobber each other.
          comment-tag: bench-pr-comment-${{ matrix.id }}-${{ inputs.matrix_preset }}

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

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

      # v4 (Postgres) ingest -- the REQUIRED benchmark-results pipeline feeding the live
      # benchmarks website (see develop-bench.yml for the full rationale); a failure here fails the
      # job. Gated on inputs.mode == 'develop' + the ingest-role ARN var (the assume-role
      # input that MUST exist for OIDC to succeed). post-ingest.py mints the RDS IAM token
      # (boto3) from the assumed GitHubBenchmarkIngestRole; sslmode=verify-full validates
      # the cert. No "Install uv" step here: this job already installed the uv binary
      # in its "Install uv" step above, and the ingest runs `uv run --no-project --with`,
      # which needs nothing beyond the binary.
      - name: Configure AWS credentials for v4 ingest (OIDC)
        if: inputs.mode == 'develop' && vars.GH_BENCH_INGEST_ROLE_ARN != ''
        uses: aws-actions/configure-aws-credentials@e1253824e5c10ff9df46874f81ed3ec929e19cfd  # v6
        with:
          role-to-assume: ${{ vars.GH_BENCH_INGEST_ROLE_ARN }}
          aws-region: ${{ vars.RDS_BENCH_REGION }}
      - name: Ingest results to v4 Postgres
        if: inputs.mode == 'develop' && vars.GH_BENCH_INGEST_ROLE_ARN != ''
        shell: bash
        env:
          RDS_BENCH_INSTANCE_ENDPOINT: ${{ vars.RDS_BENCH_INSTANCE_ENDPOINT }}
          RDS_BENCH_DB_NAME: ${{ vars.RDS_BENCH_DB_NAME }}
          AWS_REGION: ${{ vars.RDS_BENCH_REGION }}
          BENCH_SITE_BASE_URL: ${{ vars.BENCH_SITE_BASE_URL }}
          BENCH_REVALIDATE_TOKEN: ${{ secrets.BENCH_REVALIDATE_TOKEN }}
        run: |
          set -Eeuo pipefail
          curl -fsSL https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem \
            -o "${RUNNER_TEMP}/rds-global-bundle.pem"
          DSN="postgresql://bench_ingest@${RDS_BENCH_INSTANCE_ENDPOINT}:5432/${RDS_BENCH_DB_NAME}?sslmode=verify-full&sslrootcert=${RUNNER_TEMP}/rds-global-bundle.pem"
          uv run --no-project --with 'psycopg[binary]' --with boto3 --with xxhash \
            scripts/post-ingest.py results.ingest.jsonl \
            --postgres "${DSN}" \
            --commit-sha "${{ github.sha }}" \
            --branch "${{ github.ref_name }}" \
            --region "${AWS_REGION}"

      - 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