Yugabyte-db: How i cant create index in CQL Table Without Transactions

[fabiocmazzo]

Hello,

I'm migrating to YugabyteDB from ScyllaDB, and i'am getting this error:

Invalid Table Definition. Transactions cannot be enabled in an index of a table without transactions enabled.

After this i created table with option WITH transactions = { 'enabled' : true }; but i don't understand the reason i need a transactional table to use a secondary index.

I searched the documentation and found nothing.

If anyone can explain, thank you very much.

[schoudhury]

Thanks for bringing this to our attention -- we will update docs to make this aspect clearer.

A secondary index in YBDB internally uses distributed transactions to ensure ACID guarantees on updates to the secondary index and the associated primary key. This blog post explains the concept: https://blog.yugabyte.com/yugabyte-db-1-1-new-feature-speeding-up-queries-with-secondary-indexes/

The above requires the table to have transactions flag turned on explicitly. In the future, we would like to auto-detect such situations so that the explicit transactions flag is not needed.

Would be great if you could highlight the reasons why you want to migrate from ScyllaDB to YBDB. Even though both expose an API compatible with Cassandra QL, their underlying architectures are radically different. In other words, they cater to different kinds of apps -- ScyllaDB is aimed to make existing Cassandra apps faster while YBDB is aimed to enable faster development of modern cloud apps that need both massive scalability and strong consistency. Here is another post for you to review: https://blog.yugabyte.com/apache-cassandra-lightweight-transactions-secondary-indexes-tunable-consistency/

[fabiocmazzo]

@schoudhury I'm mainly migrating for a feature on your RoadMap, "Change Data Capture". Today we regularly have problems synchronizing ScyllaDB with ElasticSearch, it's not easy to keep up to date. And every time we need to do maintenance directly on the database we have to run a synchronization process that tends to be increasingly costly. Today is viable because we do not have a huge amount of data, but in the future will be a problem.

[ttyusupov]

Thanks for bringing this to our attention -- we will update docs to make this aspect clearer.

In the future, we would like to auto-detect such situations so that the explicit transactions flag is not needed.

@schoudhury , do we already have or need to create tickets for both of those points?

[ndeodhar]

I see that @schoudhury has already the updated the docs: https://docs.yugabyte.com/latest/api/ycql/ddl_create_index/#semantics

Created https://github.com/YugaByte/yugabyte-db/issues/1959 to track auto enabling transactional property for tables.

@fabiocmazzo Thanks for your interest in YugaByte! I'm going to close this issue. Please feel to reopen if you have any further questions.