Workload management using Reservations

Choosing between on-demand and flat-rate billing models

At any point in time, you can choose to switch between on-demand and flat-rate billing models, or you can choose to mix and match the two models together. The two pricing models are complementary to each other. On-demand billing is efficient; whereas, flat-rate is predictable.

Reservations trade offs.

Consider the following factors when choosing flat-rate:

  • Cost. Google recommends that your monthly analytics spend be in the ballpark of the minimum BigQuery flat-rate commitment (currently, 500 slots per month).
  • Efficiency. Consider on-demand if you value efficiency because you pay per query.
  • Predictability. You might prefer flat-rate if you value monthly predictability of pricing because you pay one flat monthly fee.
  • Resources. Flat-rate and on-demand billing models differ in how they offer capacity. For more information, see Estimating the amount of slots to purchase.

Reservations decision.

As a new customer, you might choose to start on flat-rate. At any point in time, you can observe what your spend would have been on on-demand billing, and at any point in time you can choose the model that works for you.

You can choose to mix-and-match on-demand and flat-rate billing models:

  • Use on-demand billing for predictable workloads like periodic data processing and ELT.
  • Use flat-rate billing for ad-hoc analytics and data science workloads.

Estimating the amount of slots to purchase

BigQuery is architected to scale linearly with increased resources. Depending on the workload, incremental capacity is likely to give you incremental benefits. Therefore, choosing the optimal number of slots to purchase is subjective to your performance, throughput, and utility preferences.

Determining the best number of slots to purchase requires you to examine the current slot usage of your projects along with the desired monthly price you want to pay. On-demand workloads currently have a soft slot cap of 2,000 slots, but it is important to check how many slots are actually being used by your projects using Stackdriver Logging, the Jobs API, or BigQuery Audit logs. For more information, see Visualizing slots available and slots allocated.

Slot usage timeline.

Getting started with BigQuery Reservations

To start using BigQuery Reservations, complete the BigQuery Quickstart and select your administration project.

Then, you must purchase slots and assign your organization to the reservation. If you do not complete both of these steps, your organization will still be charged on-demand rates.

Purchase slots

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. In the navigation panel, go to the Reservations section. Reservations nav button.
  3. Click the Buy slots button. Buy slots.
  4. Select Monthly for the commitment duration. Select monthly.
  5. Select US for the location.
  6. Enter 500 for the number of slots.
  7. Review and confirm your order by entering CONFIRM, then selecting PURCHASE. Enter confirm.
  8. Allow up to several minutes for the capacity to be provisioned. After the capacity is provisioned, the requested Capacity Commitment will have a green status. Slot commitments.

When capacity is purchased for the first time, a default reservation is created. The next step is assigning your organization to the reservation.

Assign organization to reservation

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section. Reservations nav button.
  3. Click the Assignments tab. Assignments tab.
  4. In the Search and create assignments section, click the Browse button and select your organization. Assignments project picker.
  5. In the Reservation picker, select the reservation name ending with default. Assignments reservation picker.
  6. Click Create.

Creating additional reservations with dedicated slots

When an organization is assigned to a reservation, all projects using BigQuery will fairly share those slots. Depending on the workload, sharing slots with other workloads may not be ideal. For example, a production workload likely does not want to share slots evenly with test workloads. If a project requires dedicated slots, additional reservations can be created with their own reserved slots. The following sections describe an example of creating and assigning 500 slots to my-prod-project.

Create prod reservation

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section.
  3. Click the Create Reservation button. Create reservation.
  4. In the Reservation name field, enter prod. Create reservation info.
  5. In the Location field, select US.
  6. For the number of slots, enter 500.
  7. Click Save.

The newly created prod reservation is visible in the overview table.

Reservation overview.

Assign my-prod-project to prod reservation

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section.
  3. Click the Assignments tab. Assignments tab.
  4. In the Search and create assignments section, click the Browse button and select your prod project. Assignments project picker.
  5. In the Reservation picker, select the reservation name ending with prod. Assignments reservation picker.
  6. Click Create.

Now, any query jobs submitted from my-prod-project will run under the prod reservation and have access to 500 slots.

Purchasing additional slots and increasing reservation sizes

As the size and complexity of workloads increase over time, you may find it necessary to purchase additional slots to improve performance.

Purchase additional slots

Follow the instructions in Getting started with BigQuery reservations to purchase an additional 500 slots.

Increase prod reservation size

To increase the size of the prod reservation, complete the following steps.

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section. Reservations section.
  3. Select the prod reservation previously created and click the edit button. Reservations edit.
  4. Enter the new reservation size: 1,000 slots. Increase reservation slots.
  5. Click Save.
  6. The updated prod reservation size is visible in the list of reservations. Updated reservation size.

CLI

bq update
  --project_id=$ADMIN_PROJECT
  --location=US
  --slots=500
  --reservation prod

Assigning projects/folders/organization to reservations

Project, folders, and organizations can all be assigned to reservations. A project reservation assignment has priority over a folder assignment, which has priority over an organization assignment.

Managing reservations in different regions

Reservations are a regional resource. Slots purchased and reservations created in one region cannot be used in any other region. Project, folders, and organizations can all be assigned to reservations in one region and run on-demand in another. In order to manage reservations in another region, you must change the region in the BigQuery Reservation page:

Console

  1. Click on the Reservations section in the BigQuery console UI.
  2. Click on the Location picker and select a region where you want to manage reservations. Select different region.
  3. Once a region is selected, you can purchase slots, create reservations, and assign a project to a reservations.

Assigning load and export jobs to reservations

The slots used by load and other pipeline jobs are free and best-effort. BigQuery does not make guarantees on ingest capability. If you need dedicated resources for load or less volatile performance, you can purchase slots and create reservations dedicated for load and other pipeline jobs.

The following example shows you how to assign my-project's pipeline jobs to your administration project's load reservation.

CLI

bq mk
  --project_id=admin_project_id
  --location=US
  --ignore_idle_slots=true
  --slots=1000
  --reservation my-pipeline

bq mk
  --project_id=my-project
  --location=US
  --reservation_assignment
  --reservation_id=admin_project_id:US.loads
  --assignee_id=admin_project_id
  --assignee_type=PROJECT
  --job_type=PIPELINE

Removing projects from reservations

You can remove projects from reservations by removing the reservation assignments. If a project is not assigned to any reservations, it uses pay-per-query (on-demand) pricing and is billed accordingly. A project doesn't use on-demand pricing if the parent folders or organization is assigned to a reservation.

To remove a project from a reservation:

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section.
  3. Click the Assignments tab. Assignments tab.
  4. For the assignment you want to delete, click Actions, and then Delete. Delete assignment.

Finding a project's reservation assignment

You can find out if your project, folder, or organization is assigned to a reservation by doing the following:

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section.
  3. Click the Assignments tab. Assignments tab.
  4. Click the project picker and select the desired project, folder, or organization. Assignments project picker.
  5. If the reservation assignment exists, it appears below the picker.

Deleting capacity commitments

A capacity commitment is eligible for deletion based on its commitment duration:

  • Monthly: After the first 30 days, you can delete your commitment at any time. Your bill will be prorated per-second.
  • Annual: After one year, you can choose not to renew your slot pool. At this time, your slot pool will convert to the monthly rate, become cancelable at any time, and your bill will be prorated per-second.

The commitment end date shows in the Reservations UI. You can delete your capacity commitment after the commitment date. If you would like to delete a capacity commitment, you must first make sure that there are enough unallocated slots. If not, you must decrease the number of slots in a reservation or remove reservations entirely.

To delete a capacity commitment in the UI, complete the following steps:

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section.
  3. Click the Slot commitments tab.
  4. For the commitment you want to delete, click Actions and then Delete. Commitments delete.
  5. Type "REMOVE" and press Proceed. Commitments delete.

Managing your workloads and departments using reservations

You can use BigQuery Reservations to isolate your committed capacity across workloads, teams, or departments by creating additional reservations and assigning projects to these reservations. A reservation is an isolated pool of resources with the added benefit of being able to take advantage of idle capacity across your entire organization.

For example, you may have a total committed capacity of 1,000 slots and three workload types: data science, ELT, and BI.

  • You can create a ds reservation with 500 slots, and assign all relevant Google Cloud projects to the ds reservation.
  • You can create an elt reservation with 300 slots, and assign projects you use for ELT workloads to the elt reservation.
  • You can create a bi reservation with 200 slots, and assign projects connected to your BI tools to the bi reservation.

Commitments delete.

In lieu of partitioning your capacity across workloads, you might choose to create reservations for individual teams or departments.

BigQuery Reservations for complex organizations

BigQuery Reservations is an organization-scoped resource. You purchase committed capacity centrally to be used across your entire organization. You can require individual departments or divisions to self-manage BigQuery Reservations, purchasing committed capacity, distributing that capacity across the department or division, and being billed for that capacity.

You can use a distinct Google Cloud organization for each of your departments or divisions. In this scenario, you define an administration project for each organization and manage BigQuery Reservations for that organization from the administration project. There is no sharing of committed or idle capacity across disparate organizations.

Using flat-rate and on-demand billing models together

You can use flat-rate and on-demand billing models together.

You can use flat-rate in one region and on-demand in another region:

  1. By default, you use on-demand in all regions.
  2. You opt a project, folder, or organization into flat-rate billing in a region by assigning it to a reservation.
  3. For example, if you purchase a slot commitment in the US multi-region and assign your organization to the default reservation, your organization will be on flat-rate billing in the US multi-region, and will remain on on-demand billing in all other regions.

Within a region, you can use both flat-rate and on-demand together:

  1. Do not assign your folder or organization to a reservation.
  2. Instead, explicitly assign projects to a reservation.
  3. Any project not assigned to a reservation will remain on-demand. For example:
    • If you have projects A, B, and C
    • You do not create an organization-wide assignment
    • You explicitly assign projects A and B to a reservation
    • Project C remains on on-demand billing

Any projects that are on on-demand billing use capacity that's separate from your committed capacity. Those projects do not affect the availability of your committed capacity.

Was this page helpful? Let us know how we did:

Send feedback about...

Need help? Visit our support page.