SEARCH_INDEXES_BY_ORGANIZATION view

BigQuery search indexes provide freeindex management until your organization reaches thelimit in a given region. You can use theINFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view to understand yourcurrent consumption towards that limit, broken down by projects and tables. TheINFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view contains one row foreach search index for the whole organization associated with the current project.

Note: The data in theINFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION viewisn't kept in real time, and might be delayed by a few seconds to a few minutes.

Required permissions

To query theINFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view, you needthe following Identity and Access Management (IAM) permissions for your organization:

  • bigquery.tables.get
  • bigquery.tables.list

Each of the following predefined IAM roles includes the precedingpermissions:

  • roles/bigquery.admin
  • roles/bigquery.dataViewer
  • roles/bigquery.dataEditor
  • roles/bigquery.metadataViewer

This schema view is only available to users with definedGoogle Cloud organizations.

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

TheINFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view has the followingschema:

Column nameData typeValue
project_idSTRINGThe name of the project that contains the dataset.
project_numberSTRINGThe project number that contains the dataset.
index_catalogSTRINGThe name of the project that contains the dataset.
index_schemaSTRINGThe name of the dataset that contains the index.
table_nameSTRINGThe name of the base table that the index is created on.
index_nameSTRINGThe name of the search index.
index_statusSTRING The status of the index can be one of the following:
  • ACTIVE: the index is usable or being created.
  • PENDING DISABLEMENT: the total size of indexed base tables exceeds your organization'slimit; the index is queued for deletion. While in this state, the index is usable in search queries and you are charged for the search index storage.
  • TEMPORARILY DISABLED: either the total size of indexed base tables exceeds your organization'slimit, or the base indexed table is smaller than 10 GB. While in this state, the index is not used in search queries and you are not charged for the search index storage.
  • PERMANENTLY DISABLED: there is an incompatible schema change on the base table, such as changing the type of an indexed column fromSTRING toINT64.
index_status_detailsRECORD The record contains following fields:
  • throttle_status: indicates the throttle status of the search index, possible values are as follows:
    • UNTHROTTLED: the index is usable.
    • BASE_TABLE_TOO_SMALL: the base table size is smaller than 10 GB. This limit applies whether or not you use your own reservation for your index-management jobs. In this case, the index is temporarily disabled and search queries don't use the index.
    • BASE_TABLE_TOO_LARGE: the base table size exceeds your organization'slimit.
    • ORGANIZATION_LIMIT_EXCEEDED: the total size of indexed base tables in your organization exceeds your organization'slimit.
  • message: detailed message that describes the index status.
use_background_reservationBOOLIndicates whether the index maintenance uses theBACKGROUND reservation. This is set toFALSE when the index maintenance uses the limit.

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must include aregion qualifier.The following table explains the region scope for this view:

View nameResource scopeRegion scope
[`PROJECT_ID`.]`region-REGION`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATIONOrganization that contains the specified projectREGION

Replace the following:

  • Optional:PROJECT_ID: the ID of yourGoogle Cloud project. If not specified, the default project is used.

  • REGION: theregion foryour project. For example,`myproject`.`region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION.

    Note: You must usea region qualifierto queryINFORMATION_SCHEMA views. The location of the queryexecution must match the region of theINFORMATION_SCHEMA view.

Index throttling

If an index is throttled, its table size is not counted towards yourorganization's limit. This throttling occurs when the base table size fallsunder 10 GB or exceeds your organization'slimit. When an index is throttled,its management jobs are paused, causing the index to become stale andeventually temporarily disabled. Consequently, search queries are unable to usethe index.

You can set up alerts to get notified when a certain threshold is exceeded,similar tosetting up alerts for scheduled queries.For example, set up an alert when the table size exceeds 70% of the quota limit,so that you have time to act.

Examples

This section includes example queries of theINFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION view.

Find if the consumption exceeds the limit in a given region

The following example illustrates if the total indexed base table size across anorganization, utilizing shared slots within the US multi-region, exceeds 100 TB:

WITHindexed_base_table_sizeAS(SELECTSUM(base_table.total_logical_bytes)AStotal_logical_bytesFROM`region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATIONASsearch_indexJOIN`region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATIONASbase_tableON(search_index.table_name=base_table.table_nameANDsearch_index.project_id=base_table.project_idANDsearch_index.index_schema=base_table.table_schema)WHERETRUE-- Excludes search indexes that are permanently disabled.ANDsearch_index.index_status!='PERMANENTLY DISABLED'-- Excludes BASE_TABLE_TOO_SMALL search indexes whose base table size is-- less than 10 GB. These tables don't count toward the limit.ANDsearch_index.index_status_details.throttle_status!='BASE_TABLE_TOO_SMALL'-- Excludes search indexes whose project has BACKGROUND reservation purchased-- for search indexes.ANDsearch_index.use_background_reservation=false-- Outputs the total indexed base table size if it exceeds 100 TB,-- otherwise, doesn't return any output.)SELECT*FROMindexed_base_table_sizeWHEREtotal_logical_bytes>=109951162777600-- 100 TB

The result is similar to the following:

+---------------------+| total_logical_bytes |+---------------------+|     109951162777601 |+---------------------+

Find total indexed base table size by projects in a region

The following example gives the breakdown on each project in a US multi-regionwith the total size of indexed base tables:

SELECTsearch_index.project_id,search_index.use_background_reservation,SUM(base_table.total_logical_bytes)AStotal_logical_bytesFROM`region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATIONASsearch_indexJOIN`region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATIONASbase_tableON(search_index.table_name=base_table.table_nameANDsearch_index.project_id=base_table.project_idANDsearch_index.index_schema=base_table.table_schema)WHERETRUE-- Excludes search indexes that are permanently disabled.ANDsearch_index.index_status!='PERMANENTLY DISABLED'-- Excludes BASE_TABLE_TOO_SMALL search indexes whose base table size is-- less than 10 GB. These tables don't count toward limit.ANDsearch_index.index_status_details.throttle_status!='BASE_TABLE_TOO_SMALL'GROUPBYsearch_index.project_id,search_index.use_background_reservation

The result is similar to the following:

+---------------------+----------------------------+---------------------+|     project_id      | use_background_reservation | total_logical_bytes |+---------------------+----------------------------+---------------------+| projecta            |     true                   |     971329178274633 |+---------------------+----------------------------+---------------------+| projectb            |     false                  |     834638211024843 |+---------------------+----------------------------+---------------------+| projectc            |     false                  |     562910385625126 |+---------------------+----------------------------+---------------------+

Find throttled search indexes

This following example returns all search indexes that are throttled within theorganization and region:

SELECTproject_id,index_schema,table_name,index_nameFROM`region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATIONWHERE-- Excludes search indexes that are permanently disabled.index_status!='PERMANENTLY DISABLED'ANDindex_status_details.throttle_statusIN('ORGANIZATION_LIMIT_EXCEEDED','BASE_TABLE_TOO_LARGE')

The result is similar to the following:

+--------------------+--------------------+---------------+----------------+|     project_id     |    index_schema    |  table_name   |   index_name   |+--------------------+--------------------+---------------+----------------+|     projecta       |     dataset_us     |   table1      |    index1      ||     projectb       |     dataset_us     |   table1      |    index1      |+--------------------+--------------------+---------------+----------------+

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.