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

dtln812 avatar image
dtln812 asked Erick Ramirez answered

Solving queue anti-pattern

Scenario:
There's a thread/group where users send messages. Those messages are saved in a table, waiting to be approved. After approving, this message should be removed from pending table, and moved to another table. Messages can be removed/approved in random order. So if we have a lot of messages approved per day in random order (in same group) we'll have a lot of tombstones for the basic select query, that will cause performance issues.
Basic potential table:

create table pending_messages (
	group_id text,
	status tinyint, -- 0, 1, 2, on 3 -> remove from this table
	message_id timeuuid,
	message Message,
	PRIMARY KEY (group_id, message_id)
);

Basic queries:

select * from message_queue where group_id='x';
update pending_messages set status=1/2 where group_id='x' and message_id=1234;

Possible solution #1:

Based on this article: https://foundev.medium.com/domain-modeling-around-deletes-1cc9b6da0d24

On a new message to be enqueued for approval, check if a table with name queue_{group_id} exists, if yes - insert this message to table. If not, create a new table with name queue_{group_id} and insert the message to the table. Have a worker that will have the list with created tables and check them - if there are no records - truncate table.

Table Schema:

create table queue_SOMEID (
	group_id text,
	status tinyint, -- 0, 1, 2, on 3 -> remove from this table
	message_id timeuuid,
	message Message,
	PRIMARY KEY (group_id, message_id)
);

Cons:

There should not be a lot of tables, if number of groups > 500+ - issues with memory usage and so on.

Possible solution #2:

Based on this article: https://foundev.medium.com/domain-modeling-around-deletes-1cc9b6da0d24

On a new message to be enqueued for approval, check if a table with name queue_{year_of_the_week} exists, if yes - insert this message to table. If not, create a new table with name queue_{year_of_the_week} and insert the message to the table. Have a worker that will have the list with created tables and check them - if there are no records - truncate table.

Table Schema:

create table queue_52 (
	group_id text,
	status tinyint, -- 0, 1, 2, on 3 -> remove from this table
	message_id timeuuid,
	message Message,
	PRIMARY KEY (group_id, message_id)
);

Cons:

There should not be a lot of tables, if number of messages that won't be processed for a long long time, could cause tables overflow too.

Possible solution #3:

On a new message to be enqueued for approval, get current week of the year and update the row with the corresponding group id and week of the year, by appending to the list the new message. When a message is removed, update the list with a new list that doesn't contain removed message. That way we avoid having a lot of tombstones (max 53 per group, if list is empty and I decide to remove the row, because week has passed). Lists can potentially become large, but not to an extreme, because of the week_of_the_year limitation.

Table Schema:

create table queue (
	group_id text,
	week_of_the_year int,
	messages list<frozen<Message>>,
	PRIMARY KEY (group_id, week_of_the_year)
);

Cons:

Potentially large lists?

Question:

Did I missed any major cons for some of the solutions, and what solution is more prefferable in this use case?

data modeling
10 |1000

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

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered

There isn't a quick answer for this other than your use case isn't a good fit for Cassandra.

It may be possible to come up with a workable solution but that is something that would involve paid consulting which is beyond the scope of this Q&A forum. Cheers!

Share
10 |1000

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