<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Plutus</title>
    <description>The latest articles on DEV Community by Plutus (plutus-cloud).</description>
    <link>https://dev.to/plutus-cloud</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Forganization%2Fprofile_image%2F16007%2F31699329-41c3-4b6c-960f-1fcbb4873aac.png</url>
      <title>DEV Community: Plutus</title>
      <link>https://dev.to/plutus-cloud</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/plutus-cloud"/>
    <language>en</language>
    <item>
      <title>How we cut a ClickHouse delete from 12 seconds to under 2</title>
      <dc:creator>Chris Henderson</dc:creator>
      <pubDate>Tue, 13 Oct 2026 00:00:00 +0000</pubDate>
      <link>https://dev.to/plutus-cloud/how-we-cut-a-clickhouse-delete-from-12-seconds-to-under-2-31l1</link>
      <guid>https://dev.to/plutus-cloud/how-we-cut-a-clickhouse-delete-from-12-seconds-to-under-2-31l1</guid>
      <description>&lt;p&gt;Plutus keeps spend data current by replacing a window of rows, not appending to it. For every source, a sync deletes up to 400 days of rows and inserts them again, so a day a provider restates replaces the old row instead of sitting beside it.&lt;/p&gt;

&lt;p&gt;Appending would double-count the overlap. Providers revise recent days, and the table is a plain MergeTree that does not deduplicate, holding aggregates with no natural row key to upsert on. The obvious alternative, a &lt;code&gt;ReplacingMergeTree&lt;/code&gt; read with &lt;code&gt;FINAL&lt;/code&gt;, timed out at 30 s on a one-year query that took 2.3 s without it.&lt;/p&gt;

&lt;p&gt;In production, the delete in that path had a daily p50 of 9 to 15 s over the three days before the change, with a maximum of 27.7 s. The insert that followed took 0.17 s.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why was the delete slow?
&lt;/h2&gt;

&lt;p&gt;The cost tables were partitioned by month, 31 and 42 partitions each. A lightweight &lt;code&gt;DELETE&lt;/code&gt; in ClickHouse walks every partition in the table, whatever its predicate matches. In a local benchmark, a delete that matched one monthly partition took 15 to 17 s, no faster than one covering the whole window.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fn4anw185cy51eacm5j53.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fn4anw185cy51eacm5j53.png" alt="Two rows of partitions, with a 31-month table shown as 31 monthly partitions or 3 yearly ones. The delete walks all 31 monthly partitions, and the re-insert of a 14-month window adds 14 parts. With yearly partitions the delete walks all 3, and the re-insert adds 2 parts." width="800" height="284"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  What changed?
&lt;/h2&gt;

&lt;p&gt;Yearly partitions. There are far fewer to walk, and a 400-day re-insert writes 2 parts instead of 14.&lt;/p&gt;

&lt;p&gt;From &lt;code&gt;system.query_log&lt;/code&gt; in production, the daily p50 of the sync delete, in seconds:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Table&lt;/th&gt;
&lt;th&gt;Before (3 days, 10 to 130 deletes a day)&lt;/th&gt;
&lt;th&gt;After (10 days, 35 to 150 a day)&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;cost_entries&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;9 to 14 s&lt;/td&gt;
&lt;td&gt;1.0 to 1.8 s&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;usage_entries&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;12 to 15 s&lt;/td&gt;
&lt;td&gt;0.7 to 1.8 s&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;That is about a tenfold drop, and it has held every day since the change.&lt;/p&gt;

&lt;h2&gt;
  
  
  What did it cost?
&lt;/h2&gt;

&lt;p&gt;Yearly partitions alone made one-month reads scan about seven times the rows, because the monthly partitions had been pruning them for free. Moving the month into the sort key and filtering on it in the readers brought a one-month read back to the same 3.8M rows it scanned before.&lt;/p&gt;

&lt;p&gt;The longer-term plan is append-only: each sync is stamped with a generation, and a small pointer row says which generation is live. That is planned, not built. Until then, partition count was the lever we had.&lt;/p&gt;

&lt;p&gt;If your write path is delete-and-reinsert, count your partitions and the parts one re-insert writes. Those are the first two numbers to get.&lt;/p&gt;

</description>
      <category>clickhouse</category>
      <category>dataengineering</category>
    </item>
  </channel>
</rss>
