Exceeded rate limits: too many table update operations for this table
A table accepts a few metadata updates every 10 seconds, counting loads, copies, DDL and queries that write to it. The error is temporary. Retry with backoff, then batch the writes or switch to DML or the Storage Write API.
Exceeded rate limits: too many table update operations for this table. For more information, see https://cloud.google.com/bigquery/docs/troubleshoot-quotas
What BigQuery means
Every table has a limit on how often its metadata can change. For a standard table BigQuery’s documentation gives 5 operations per 10 seconds, and for a partitioned table 50 modifications per 10 seconds. When your project goes over the limit for one table, the next update is refused with this message.
The limit counts more than edits to the table’s description. According to the documentation it counts:
- changes through the console, the
bqtool or a client library, includingtables.insert,tables.patchandtables.update - DDL statements on the table
- load jobs, copy jobs and query jobs that append to or overwrite the table, or that write to it with
DELETE,INSERT,MERGE,TRUNCATE TABLEorUPDATE
The documentation adds that DML statements count toward the limit but are not refused when it is reached. DML has dedicated limits of its own.
This is a rate limit. The API answers with HTTP 403 and the reason rateLimitExceeded, which BigQuery documents as a short-term limit.
How to see it happen
We started 12 bq update --description commands at once on one small table:
for i in $(seq 1 12); do
bq update --description "run $i" mydataset.mytable &
done
wait
Six of the twelve updates succeeded and six failed with the message at the top of the page. The API’s answer for a failed update is HTTP 403 with this body:
"message": "Exceeded rate limits: too many table update operations for this table. For more information, see https://cloud.google.com/bigquery/docs/troubleshoot-quotas",
"domain": "usageLimits",
"reason": "rateLimitExceeded"
How many calls pass before the first failure depends on what the table did in the previous 10 seconds, so do not count on a fixed number. We could not get the error from 16 ALTER TABLE ... SET OPTIONS statements run at once on one table. All 16 succeeded. If you hit the message in a real pipeline, the cause is usually many small jobs that each write to the same table.
The fixes
The documentation says this limit cannot be raised. The fixes are to write less often:
- Retry with exponential backoff. The error is temporary. Wait a few seconds, try again, and wait longer after each failure.
- Space the jobs out. Put a delay between jobs or table operations so that the rate stays under the limit.
- Use DML to change data. To insert or modify rows,
INSERT,UPDATE,DELETEandMERGEare not refused by this limit. They have their own limits, listed in Quotas and limits. - Combine load jobs. If you run one load job per file, load many files in one job. A Cloud Storage load takes a list of URIs or a wildcard such as
gs://my_path/*. - Batch small appends. Do not append one row at a time with load, query or copy jobs. Collect rows and write them in one job.
- Stream with the Storage Write API. For a high rate of appends, the documentation recommends the BigQuery Storage Write API.
To find the jobs that write to the table, group INFORMATION_SCHEMA.JOBS_BY_PROJECT by hour and job type with a filter on destination_table. The troubleshooting page has the query.
Other rate and quota messages
These messages are quoted from BigQuery’s troubleshooting page, except the partitioned-table message and the daily limit, which come from its quotas page.
| Message | What it means |
|---|---|
Exceeded rate limits: too many partitioned table update operations for this table | The same limit for a partitioned table: 50 modifications per 10 seconds. |
Your table exceeded quota for imports or query appends per table | The daily limit: 1,500 table modifications per table per day. This one is a quota. Run fewer, larger jobs. |
Quota exceeded: concurrent number of jobs that can be in the pending state | Too many jobs waiting at once. The documentation says this cannot be raised. Submit jobs more slowly. |
Quota exceeded: Your project and region exceeded quota for max number of jobs that can be queued per project. | The queue limit for queries. It cannot be raised. Spread the load over time and optimize the queries. |
Exceeded rate limits: too many concurrent queries with remote functions for this project | Too many queries with remote functions at once. This limit can be raised on request. |
Exceeded rate limits: too many concurrent queries with Python UDFs in this project. Your project: PROJECT_ID exceeded quota for maximum concurrent Python UDFs per project. | More than 10 Python UDFs at once in a project. |
Your project exceeded quota for free query bytes scanned | The free tier. See Quota exceeded: free query bytes scanned. |
Rate limit or quota
The reason field tells the two apart. rateLimitExceeded is a short-term limit: retry after a few seconds, with exponential backoff. quotaExceeded is a longer-term limit: the documentation says to wait 10 minutes or longer, and if you keep reaching it, to change the workload or ask for a higher quota. To list the recent ones in your project:
SELECT job_id, creation_time, error_result
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY)
AND error_result.reason IN ('rateLimitExceeded', 'quotaExceeded')
Replace region-us with the region of your project. See Quotas and limits.
In Querylab.io
The cause is how often a table is written, so the editor does not mark anything as you type. When a run fails with it, the status bar shows BigQuery’s message. For a single SELECT that failed with a rate limit, the bar also offers Try again, which runs the same query once more. There is no button for a script, a DML statement or a DDL statement, because running those again could repeat work that already happened. Wait and run it yourself, and space out the writes.