Index Deployment Strategies

SQL++
,
1

We are in the process of converting our Couchbase deployment from map-reduce views to 100% N1QL. One of the things that was nice about views were that they were concretely named and referenced in code. Specifically, if you versioned a view and deployed it to couchbase you effectively had my_view_v1 and my_view_v2. As clients in a rolling upgrade transitioned from one code version to another, you saw references in couchbase tomy_view_v1 slowly trail off and drop to zero. After a while of observing no use, we would delete the old view knowing with 100% certainty that the new code and new view were working, and the old had been fully dropped from service.

Is something roughly equivalent possible with N1QL?

Our experience with USE INDEX is that the query planner may not honor it; it is a hint. That means that a second version of the index may still “mix it up” with the original. We also need to do testing to make sure that a USE INDEX in the face of replicas may cause confusion (i.e. if a node goes down and you gotta run on the replica index does the USE INDEX clause need to change - gotta test that).

Bottom line is I’m curious what others are doing, or what the recommended practice is, to get a smooth controlled production transition from a V1 to a V2 cut of an index.

2

USE INDEX is hint. If you provided and indexes qualify it uses that. If that doesn’t qualify it considers all other remaining indexes.

replica index will have same name, so there will not be any issue. Even one node is down it uses other node. Even it uses load balancing.

You should create index normally and use without USE INDEX. If some queries you can’t get right SLA or avoid IntersectScans then specify USE INDEX.

With 7.0.0 CBO (when stats available) it automatically picks right index.

https://index-advisor.couchbase.com/indexadvisor/#1

3

OK, that lines up with our observations.

But I just want to confirm what seems to be the clear statement. If I have NEW application code deployed that is intended to a use a new version of an index (what I’m calling a rev 2 - say we added a new covered field or something), then there is no predictable, controlled way to roll out that new index.

The goal here is to have a sequence like:

  1. Create new version of an existing index (say you add an extra covered field) to production cluster. Cluster now has BOTH v1 and v2 versions of the index loaded with replicas.
  2. Deploy new application code.
  3. Observe that the new application code is using the new index (historical queries AND queries that use the changes). Idea here is to KNOW that the new deployment is working well with the new code. If it is not, we can rollback application code and delete the new index. We KNEW the old index was still there and functional with the rolled back code.
  4. Assuming #3 worked. Observe that the OLD index is no longer taking any traffic. It is fully deprecated.
  5. Delete the OLD index to recover space.

If you leave it entirely to the query planner (no use index), then basically a new deployment could easily stay on the original index version (maybe even 100% with non-optimal fetches for the extra attribute), and no real chance to fully test and validate the new application code without deleting the old index. That could easily take away the ability to do a production rollback without down time or degradation.

Do people using N1QL without USE INDEX just accept that this risk of downtime or degradation is real?

Sorry to explore this more, but we definitely didn’t have this exposure with map-reduce views.

4

You can experiment this on test cluster and final one can be deployed on production.
If required provide USE INDEX hint.