question

We picked conservative defaults for Storage Attached Indexing (SAI) limits because we wanted users to avoid being surprised by unfavourable cluster performance if they decide to go overboard and index dozens of columns in lots of tables.

To answer your question directly, too many indexes can affect the performance of a database regardless of the database you use. To be clear, this isn't limited to Cassandra -- the performance hit of too many indexes applies to any NoSQL or relational databases.

I don't have readily available data to show you but I would posit that the write performance of a table degrades linearly as the number of indexes grows. I would even hypothesise that in some instances (in variations of data model, access pattern, data types), there's an inflection point where the degradation gets worse than linear.

The reality is that too many indexes on a table amplifies the writes and affects the performance for inserts and deletes. As most things, YMMV.

It is difficult to guess how it will affect your application because there are lots of moving parts -- use case, data model, access patterns, hardware configuration, network configuration, etc. Only you can determine how your cluster will perform based on extensive production-like testing. Cheers!