Hi, Is there an option to drop existing version when ingesting data for a time interval (similar to "dropExisting" in compaction)? We reingest data repeatedly as Druid does not support updates and this has led to a lot of unused segments in the metadata DB (~680K in total & only ~71K used). We want to avoid using

druid.coordinator.kill.on

config as we don't want to remove historical unused segments. We only want to remove unused segments which have been overwritten with a newer version. Is there a way to do this programmatically or through a config setting? Does having a large number of unused segments details in the metadata DB lead to any performance issues? To resolve the current situation, we are planning on manually executing a "kill task" on the table with the largest number of unused segments. But we wanted to see if there was a better option to purge the previous segments whenever we overwrite the data for a time interval. Thanks, AR.

with SQL batch ingest, yes, you can use

REPLACE INTO <table> OVERWRITE WHERE …

to replace data for a specific time chunk

Thanks Gian. Most of our batch loads use MSQ. We have a few which still use JSON spec but those will be migrated to MSQ or decommissioned in due course. So does MSQ ingestion with

REPLACE INTO .. OVERWRITE..

removes existing versions? Is there any recommendation/guideline on the number of segments in the metadata DB to avoid performance issues? Like I said, we don't want to lose historical unused segments but we are wary of them impacting performance of the metadata queries. Is there a way to remove segment metadata from metadata store but retain the files in the deep storage and then load them back when needed? Thanks, AR.

Is there any recommendation/guideline on the number of segments in the metadata DB to avoid performance issues?

in my experience after millions of segments is when you start to see slower metadata queries; things will still work but it may lengthen somewhat balancing and data loading times

it marks the existing versions unused; the metadata records are not actually removed, but they would become eligible for removal if you have autokill or use a kill task

That's what I understood too. We don't want to issue a kill task immediately as it would affect other ingestion tasks if the time interval is large. Keeping track of everything that was overwritten is also not trivial. Is there no way other than to issue a kill task after overwriting the segments for an interval? Thanks, AR.

The kill task shouldn’t really affect active ingestion. (There’s one corner case I can think of where a replacing job reads from the same table that it’s replacing into, and the kill tasks are trying to mark them as unused and get rid of them)

Is there no way other than to issue a kill task after overwriting the segments for an interval?

Currently I don’t think so other than fiddling with the segment metadata records manually which I wouldn’t recommend. IMO, it’d be better to manually submit kill tasks or enable auto-kill which has sufficient guard rails. Also, once Druid 30 is out, if you have many such versions of replaced data lying around, you may consider running kill task to purge just the older X versions (which should automatically happen implicitly based on the buffer period if auto-kill is enabled)

If you are going to run auto-kill I would suggest keeping the maxSegments small, e.g. 1000, to avoid latency issues with the Delete statement against the metadata DB. From my experience tasks set up this way run very quickly (usually < 1 min) so you can set the kill period to something short like 15 minutes, and once the backlog of unused segments is cleaned up the kill jobs will rarely run after that.

Thank you John. Your understanding is correct. This feature could potentially solve the issue if we can set up load rules which will automatically drop the segments from the historical but keep them in the deep storage. We will have to upgrade our Druid version for this but something that we will definitely explore. I am assuming that such deep storage only segments will not be removed by the auto kill tasks. Is this correct? Thanks, AR.

I am assuming that such deep storage only segments will not be removed by the auto kill tasks. Is this correct?

Correct. Kill tasks will only remove segments where druid_segments.used=0 ... these deep storage segments will have used=1, just will not be assigned to any Historicals.