# How to Optimize Tombstone Issues Caused by Updating Primary Keys in ScyllaDB Materialized Views?

**URL:** <https://forum.scylladb.com/t/how-to-optimize-tombstone-issues-caused-by-updating-primary-keys-in-scylladb-materialized-views/4189>\
**Category:** ScyllaDB\
**Tags:** data-model, materialized-views, tombstone\
**Created:** [December 16, 2024, 11:27am UTC](https://forum.scylladb.com/t/how-to-optimize-tombstone-issues-caused-by-updating-primary-keys-in-scylladb-materialized-views/4189 "2024-12-16T11:27:16Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![qijia\_wang](https://sea2.discourse-cdn.com/flex016/user_avatar/forum.scylladb.com/qijia_wang/32/1435_2.png) [@qijia\_wang](https://forum.scylladb.com/u/qijia_wang)\
**Post date:** [December 16, 2024, 11:27am UTC](https://forum.scylladb.com/t/how-to-optimize-tombstone-issues-caused-by-updating-primary-keys-in-scylladb-materialized-views/4189/1 "2024-12-16T11:27:16Z")

</div>

I’m using ScyllaDB to store subscription relationships in the following business scenario:

**Requirements** : The data model needs to support these queries:

1. `app_id = ? AND user_id = ? AND ts >= <lastTs> LIMIT 10` for incremental data queries to sync with the client.
2. `app_id = ? AND user_id = ? AND target_user_id = ?` for precise subscription lookups.

**Table Structure** :

```auto
CREATE KEYSPACE IF NOT EXISTS userstore WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'};

CREATE TABLE IF NOT EXISTS subscription (
    app_id bigint,
    user_id text,
    target_user_id text,
    expiry int,
    ts bigint,
    PRIMARY KEY((app_id, user_id), target_user_id)
) WITH CLUSTERING ORDER BY (target_user_id ASC);

CREATE MATERIALIZED VIEW IF NOT EXISTS subscription_by_time AS
 SELECT app_id, user_id, target_user_id, ts, expiry
 FROM subscription
 WHERE app_id IS NOT NULL
   AND user_id IS NOT NULL
   AND target_user_id IS NOT NULL
   AND ts IS NOT NULL
 PRIMARY KEY((app_id, user_id), ts, target_user_id)
 WITH CLUSTERING ORDER BY (ts ASC);CREATE KEYSPACE IF NOT EXISTS userstore WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'};

```

Table Explanation:

- subscription: Supports Query 2 (app\_id = ? AND user\_id = ? AND target\_user\_id = ?).
- subscription\_by\_time: Supports Query 1 (app\_id = ? AND user\_id = ? AND ts \>= LIMIT 10).

**Issue** :

1. When a subscription relationship is updated (e.g., updating the ts field with the latest timestamp), this triggers an update on the materialized view, where ts is part of the primary key.
2. This operation generates tombstones (deleted entries) in the materialized view. Tombstone accumulation leads to slower queries and, in severe cases, may cause cluster instability.

Is there a data structure that satisfies the functional requirements while avoiding tombstone issues?

---

<div class="post-metadata">

**Author:** ![bhalevy](https://sea2.discourse-cdn.com/flex016/user_avatar/forum.scylladb.com/bhalevy/32/175_2.png) [@bhalevy](https://forum.scylladb.com/u/bhalevy)\
**Post date:** [April 15, 2025, 12:13pm UTC](https://forum.scylladb.com/t/how-to-optimize-tombstone-issues-caused-by-updating-primary-keys-in-scylladb-materialized-views/4189/2 "2025-04-15T12:13:09Z")

</div>

Hello @qijia_wang, as you correctly noted, modifying any column that is part of the materialized view primary key generates a tombstone for the previous value of the row and inserts a row with the new value.  
There was a recent fix in this area: [Improve timestamp heuristics for tombstone garbage collection by bhalevy · Pull Request #20446 · scylladb/scylladb · GitHub](https://github.com/scylladb/scylladb/pull/20446)  
that improves the garbage collection of those tombstones so they don’t accumulate over time.  
It is available since scylla-6.2.  
What version are you running?