Is DELETE + INSERT to update immutable data in rows a recommended approach?

ScyllaDB
, ,
1

In the leaderboard modeling blog, it’s mentioned that updating fields that are part of the primary key (whether partition or clustering keys) requires deleting the existing row and inserting a modified version.

However, since DELETE operations introduce tombstones, they can contribute to read latency issues, increased compaction overhead, and potential performance degradation over time. Additionally, frequent DELETE + INSERT operations could lead to bloated SSTables, impacting disk space and query efficiency.

Given these factors, in a large-scale environment is it advisable to rely on an approach which uses delete + insert very commonly?

leaderboard modeling blog - scylladb .com/2024/09/03/model-game-leaderboards-scylladb/

2

You are right that delete+insert gives quite some work to the database, but so does any workload which has a roughly constant-size working-set. If your dataset is not growing, it means that your writes are either overwrites or delete+insert. Both are challenging for the database, just in different ways. All that said, the database is designed to cope with this so such a workload on its own will not be a problem.

1 Like