TWCS with many unique partitions

ScyllaDB
, , ,
1

Hi, I have a table for storing user refresh tokens.

CREATE TABLE IF NOT EXISTS user_refresh_tokens (
    refresh_token text,
    user_id timeuuid,
    PRIMARY KEY (refresh_token)
) WITH compaction = {
    'class' : 'TimeWindowCompactionStrategy',
    'compaction_window_size' : 1,
    'compaction_window_unit' : 'DAYS',
    'min_sstable_size' : 52428800 -- 50mb
} AND default_time_to_live = 2592000; -- 30 days

Since access has a lifetime of 5 minutes, each user of the application will read and create a new record in this table every 5 minutes. Since I only need to store them for 30 days, I thought that the window strategy would be a good fit here.
But am I right? How correct is it to have only unique records in such a table?
Maybe it is better to use STCS with the same default_time_to_live ?

2

Also, here is screen of this table in prometheus from dev server, where low rate of requests and about 100 users that do refresh token every 5 minut. But is it normal that table has 122 sstables ?

image
image2990Ă—1396 438 KB

3

Is TWCS Appropriate for a Table with Unique Partitions?

TWCS (Time Window Compaction Strategy) is primarily optimized for time-series data, where each partition receives new data over time and records may be expired after a predictable duration. In your schema, each partition key (the refresh_token) is unique and will only receive a single write, never updated or appended.

  • Strengths of TWCS: It efficiently compacts SSTables within time windows and works best when partitions accumulate data and eventually expire all at once (typical in time-series workloads).

  • Limitations for Unique Keys: When every write generates a new partition (unique token), your data is essentially an “insert-only” workload with no clustering within partitions and no benefit from windowed compaction; each partition will expire independently, and TWCS’s windowing gains are minimal.

  • SSTable Management: You may end up with many very small SSTables if each token insert creates a new partition (which is probably what you already see, as mentioned in your second post), and TWCS will not compact across windows, so fragmentation and file count may increase, putting pressure on the system.

Conclusion: TWCS is not an ideal fit for this workload, since your data is not time-series in the classic sense; compaction efficiency is likely to be low for many unique partitions with no clustering or updates per partition.

Would STCS Be Better for Unique Partitions with TTL?

STCS (Size-Tiered Compaction Strategy) is the default strategy for general-purpose, insert-heavy workloads in Cassandra/ScyllaDB. It compacts together similarly sized SSTables, reducing fragmentation and efficiently handling lots of unique partitions—especially when all rows have a reasonably aggressive TTL.

  • Strengths for Unique Partitions: STCS will compact small files into larger ones and remove expired data from older SSTables according to TTL, avoiding the file explosion risk of TWCS in your scenario.

  • Efficient for Expiring Data: With all rows using default_time_to_live (30 days), expired tokens are purged during compaction events without risk of indefinitely persisting small SSTables.

  • Disk Utilization: Make sure your disk is sized appropriately, as STCS can require extra disk space during compactions.

Conclusion: For a use case where every write is a unique partition and data expires after a fixed period, STCS is usually the more effective option. It handles unique-partition workloads and repeated inserts efficiently, especially if you’re not reading or deleting tokens before their TTL expires.

Summary Table

Criterion TWCS STCS
Designed for time-series data Yes No
Handles unique partitions Poor fit Well-suited
File count management May produce many small SSTables Merges into larger SSTables
Works with TTL Yes, but not optimal for unique partitions Yes, efficient at purging expired rows

Recommendation

  • For a table with many unique, short-lived records (one per refresh token), STCS with TTL is generally preferable to TWCS.

  • Reserve TWCS for workloads where partitions accumulate over time and deletions occur in time-based batches.

For your user_refresh_tokens example, switch to STCS to avoid numerous small SSTables and inefficient compaction behavior inherent to TWCS with highly unique partition keys.

1 Like