Deferring Indexes
- how-to
How to create deferred indexes and build them later.
Introduction
When you create a primary or secondary index, you can mark it as deferred. This means the index is not built at once; you can build the deferred index later. This enables you to build multiple indexes more efficiently.
If you want to try out the examples in this section, follow the instructions given in Do a Quick Install to install Couchbase Server, configure a cluster, and load a sample dataset. Read the following for further information about the tools available for editing and executing queries:
Deferring an Index
You can defer an index to be built later using a N1QL statement or an SDK call.
Prior to the SDK API 3.3 releases (released alongside Server 7.1), the SDK calls only enabled you to create indexes in the default collection and default scope within a bucket. A N1QL statement enables you to create indexes in any collection and scope within a bucket. |
-
N1QL
-
.NET
-
Java
-
Node.js
-
Python
To defer a primary or secondary index to be built later:
-
Use the
WITH
clause to specify the index options. -
In the index options, set the
defer_build
attribute totrue
.
The following queries create a set of primary and secondary indexes in the landmark
keyspace, with build deferred until later.
CREATE INDEX idx_landmark_country
ON `travel-sample`.inventory.landmark(country)
USING GSI
WITH {"defer_build":true};
CREATE INDEX idx_landmark_name
ON `travel-sample`.inventory.landmark(name)
USING GSI
WITH {"defer_build":true};
CREATE PRIMARY INDEX idx_landmark_primary
ON `travel-sample`.inventory.landmark
USING GSI
WITH {"defer_build":true};
For further details and examples, refer to CREATE PRIMARY INDEX and CREATE INDEX.
To defer a primary or secondary index to be built later:
-
Use
CreatePrimaryQueryIndexOptions
orCreateQueryIndexOptions
to specify the index options. -
In the index options, invoke the
Deferred
method with the argumenttrue
.
The following examples create a set of primary and secondary indexes in the specified keyspace, with build deferred until later.
await cluster.QueryIndexes.CreatePrimaryIndexAsync(
"`travel-sample`",
options => options.Deferred(true)
);
await cluster.QueryIndexes.CreateIndexAsync(
"`travel-sample`",
"idx_name_email",
new[] { "name", "email" },
options => options.Deferred(true)
);
Click the GitHub button to view this code in context.
For further details, refer to CreatePrimaryQueryIndexOptions and CreateQueryIndexOptions.
To defer a primary or secondary index to be built later:
-
Use
CreatePrimaryQueryIndexOptions
orCreateQueryIndexOptions
to specify the index options. -
In the index options, invoke the
deferred
method with the argumenttrue
.
The following examples create a set of primary and secondary indexes in the specified keyspace, with build deferred until later.
CreatePrimaryQueryIndexOptions primaryOpts = CreatePrimaryQueryIndexOptions
.createPrimaryQueryIndexOptions()
.deferred(true);
cluster.queryIndexes().createPrimaryIndex("travel-sample", primaryOpts);
CreateQueryIndexOptions secondaryOpts = CreateQueryIndexOptions
.createQueryIndexOptions()
.deferred(true);
cluster.queryIndexes().createIndex(
"travel-sample",
"idx_name_email",
Arrays.asList("name", "email"),
secondaryOpts
);
Click the GitHub button to view this code in context.
For further details, refer to CreatePrimaryQueryIndexOptions and CreateQueryIndexOptions.
To defer a primary or secondary index to be built later:
-
Use
CreatePrimaryQueryIndexOptions
orCreateQueryIndexOptions
to specify the index options. -
In the index options, set the
deferred
property totrue
.
The following examples create a set of primary and secondary indexes in the specified keyspace, with build deferred until later.
await cluster
.queryIndexes()
.createPrimaryIndex('travel-sample', { name: '#primary', deferred: true })
await cluster
.queryIndexes()
.createIndex('travel-sample', 'idx_name_email', ['name', 'email'], {
deferred: true,
})
Click the GitHub button to view this code in context.
For further details, refer to CreatePrimaryQueryIndexOptions and CreateQueryIndexOptions.
To defer a primary or secondary index to be built later:
-
Use
CreatePrimaryQueryIndexOptions
orCreateQueryIndexOptions
to specify the index options. -
In the index options, set the
deferred
property toTrue
.
The following examples create a set of primary and secondary indexes in the specified keyspace, with build deferred until later.
cluster.query_indexes().create_primary_index(
"travel-sample",
CreatePrimaryQueryIndexOptions(deferred=True)
)
cluster.query_indexes().create_index(
"travel-sample",
"idx_name_email",
["name", "email"],
CreateQueryIndexOptions(deferred=True)
)
Click the GitHub button to view this code in context.
For further details, refer to N1QL Index Management.
Building a Deferred Index
You can build one or more deferred primary or secondary indexes using a N1QL statement. You can also build all deferred indexes in a keyspace using an SDK call.
Prior to the SDK API 3.3 releases (released alongside Server 7.1), the SDK calls only enabled you to build indexes in the default collection and default scope within a bucket. A N1QL statement enables you to build indexes in any collection and scope within a bucket. |
-
N1QL
-
.NET
-
Java
-
Node.js
-
Python
To build one or more deferred indexes, use the BUILD INDEX
statement:
-
Use the
ON
keyword to specify the keyspace which contains the index or indexes. -
Specify the index or indexes that you want to build in parentheses
()
.
The following example builds a single deferred index.
BUILD INDEX ON `travel-sample`.inventory.landmark(idx_landmark_country) USING GSI;
The following example builds multiple deferred indexes.
BUILD INDEX ON `travel-sample`.inventory.hotel(idx_landmark_name, idx_landmark_primary);
For further details and examples, refer to BUILD INDEX.
To build all deferred indexes in a keyspace, use the task BuildDeferredIndexesAsync
on the interface IQueryIndexManager
.
The following example builds all deferred indexes in the specified keyspace.
// Start building any deferred indexes which were previously created.
await cluster.QueryIndexes.BuildDeferredIndexesAsync("`travel-sample`");
// Wait for the deferred indexes to be ready for use.
await cluster.QueryIndexes.WatchIndexesAsync(
"`travel-sample`",
new[] { "idx_name_email" },
options => options.WatchPrimary(true)
);
Click the GitHub button to view this code in context.
For further details, refer to IQueryIndexManager().
To build all deferred indexes in a keyspace, use the buildDeferredIndexes
method.
The following example builds all deferred indexes in the specified keyspace.
// Start building any deferred indexes which were previously created.
cluster.queryIndexes().buildDeferredIndexes("travel-sample");
WatchQueryIndexesOptions opts = WatchQueryIndexesOptions
.watchQueryIndexesOptions()
.watchPrimary(true);
// Wait for the deferred indexes to be ready for use.
// Set the maximum time to wait to 3 minutes.
cluster.queryIndexes().watchIndexes(
"travel-sample",
Arrays.asList("idx_name_email"),
Duration.ofMinutes(3),
opts
);
Click the GitHub button to view this code in context.
For further details, refer to QueryIndexManager.
To build all deferred indexes in a keyspace, use the buildDeferredIndexes
function on a QueryIndexManager
object.
The following example builds all deferred indexes in the specified keyspace.
// Start building any deferred indexes which were previously created.
await cluster.queryIndexes().buildDeferredIndexes('travel-sample')
// Wait for the deferred indexes to be ready for use.
// Set the maximum time to wait to 3 minutes.
await cluster.queryIndexes().watchIndexes(
'travel-sample',
['idx_name_email'],
180000, // milliseconds
{ watchPrimary: true }
)
Click the GitHub button to view this code in context.
For further details, refer to QueryIndexManager.
To build all deferred indexes in a keyspace, use the build_deferred_indexes
function on a QueryIndexManager
object.
The following example builds all deferred indexes in the specified keyspace.
# Start building any deferred indexes which were previously created.
cluster.query_indexes().build_deferred_indexes("travel-sample")
# Wait for the deferred indexes to be ready for use.
# Set the maximum time to wait to 3 minutes.
cluster.query_indexes().watch_indexes(
"travel-sample",
["idx_name_email"],
WatchQueryIndexOptions(timeout=timedelta(minutes=3), watch_primary=True)
)
Click the GitHub button to view this code in context.
For further details, refer to N1QL Index Management.