Bringing together the Apache Cassandra experts from the community and DataStax.

Want to learn? Have a question? Want to share your expertise? You are in the right place!

Not sure where to begin? Getting Started

 

question

dataserver17 avatar image
dataserver17 asked ·

Do I need multiple writes if I have multiple tables?

Hello,

maybe I have 3 Tables:

users_by_id

users_by_email

users_by_username

So, I have to write 3 write querys. But is that not expensive ? And maybe I have 11 Tables. Then I have to writes 11 write querys. Is there another solution?

data modeling
1 comment
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

@dataserver17, could you please provide additional details on the version of Cassandra/DSE cluster and the driver language and version? Thanks!

0 Likes 0 ·
stefano.lottini avatar image
stefano.lottini answered ·

Hello,

well, in most cases that's the least expensive solution to accommodate your needs. If you have these three tables, it means that your app workflow will need all three access patterns to get users. Most likely you will read more often than you write, so it is even more true that duplicating the data at write time is a performance gain on the long run.

There are other solutions (creating indices or even materialized views), which however, depending on the use case, may generate more load to the nodes and higher latency.

If you are concerned about having the best read performance, performing multiple writes is still your best option - and your client may issue the writes in parallel, spread over nodes compatibly with your replication factor.

In some cases you may also want to consider grouping your write statements inside a batch (in this case it would be a logged multi-table batch), but even that comes with some performance penalty.

Share
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Erick Ramirez avatar image
Erick Ramirez answered ·

Yes, you do need to do multiple writes to keep the denormalised tables in sync.

It is true that multiple table updates can be expensive but it is a tradeoff in preference to making reads really fast because you do not need to do lookups on multiple tables to retrieve the data -- for each of your app queries, you are just reading one table.

Cassandra can achieve single-digit millisecond latency by optimising reads to as little as a single disk seek. Unlike traditional RDBMS, denormalised tables have a preference for faster data retrievals at the expense of multiple writes. Cheers!

Share
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.