ScyllaDB Storage Design

[unknowntpo]

ScyllaDB Storage Design:

• Use user_id, list_key as partition key
• Use time, page_key as clustering key

Schema:

CREATE TABLE page (
   user_id int,
   list_key uuid,
   page_key uuid,
   time timestamp,
   PRIMARY KEY ((userID, list_key), time, page_key)
) WITH compaction = {
    'class': 'TimeWindowCompactionStrategy',
    'compaction_window_size': '1',
    'compaction_window_unit': 'DAYS'
};

CREATE INDEX IF NOT EXISTS ON page.page (page_key);

🚧 : TWC Time window compassion ?
Ref: Time Window CompactionStrategy (TWCS)

Drawbacks:

• Still not suitable to set multiple TTL values for given table

  Using multiple TTL values for a given table may lead to inefficiency when purging expired data, because an SSTable will remain until all of its data is expired.

🚧 ## CQL Design:

Insert:

INSERT INTO page.page (user_id, list_key, page_key, time) VALUES (?, ?, ?, ?) USING TTL 86400;

GetHead of today:

Expired data is automatically pruned by ScyllaDB, no need to apply filter on time.

SELECT *  FROM page.page  WHERE user_id = 1 AND list_key = 'popular' ORDER BY time LIMIT 1;

 user_id | list_key | time                            | page_key
---------+----------+---------------------------------+--------------------------------------
       1 |  popular | 2023-04-20 06:15:58.593000+0000 | d104bc84-df42-11ed-bd9d-06aab4780771

GetPage:

SELECT * FROM page.page WHERE page_key = ed5f7fcc-df42-11ed-9eb3-06aab4780771;
 user_id | list_key | time                            | page_key
---------+----------+---------------------------------+--------------------------------------
       1 |  popular | 2023-04-20 06:16:46.164000+0000 | ed5f7fcc-df42-11ed-9eb3-06aab4780771