ClickHouse ReplacingMergeTree dublikāti: kāpēc tie paliek un kā lasīt pareizi

ClickHouse ReplacingMergeTree dublikāti ir viens no biežākajiem pārsteigumiem komandām, kas nāk no PostgreSQL vai MySQL. Jūs izvēlaties šo dzinēju, jo vēlaties upsert, ievietojat rindas jaunu versiju, un nākamajā dienā vecā versija joprojām ir tur. Panelis rāda divkāršus ieņēmumus. Nekas nav salūzis: dzinējs ir veidots tieši tā.

Dokumentācija to saka tieši: deduplikācija notiek tikai sapludināšanas (merge) laikā, sapludināšana notiek fonā nezināmā laikā, un dzinējs negarantē, ka dublikātu nebūs. Kad to pieņemat, darbs sadalās divās daļās: vaicāt tā, lai rezultāts būtu pareizs vēl pirms sapludināšanas, un veidot tabulu tā, lai sapludināšana dublikātus tiešām varētu noņemt.

Kāpēc dublikāti paliek

Rinda tiek aizstāta tikai tad, kad fona sapludināšana nolasa abas versijas un ieraksta vienu. Viss, kas neļauj abām versijām satikties vienā sapludināšanā, uztur dublikātu dzīvu:

• Sapludināšana vēl nav notikusi. Jauni ievietojumi rada jaunas daļas (parts). ClickHouse tās sapludina, kad pats izlemj, nevis tad, kad jūs ievietojat. Svaigos datos dublikāti ir gandrīz vienmēr.
• Atslēga nav tā, ko domājat. Rindu unikalitāti nosaka tabulas ORDER BY sadaļa, nevis PRIMARY KEY. Ja ORDER BY ir (customer_id, order_id) un jaunā versija maina customer_id, tās uz visiem laikiem ir divas dažādas rindas.
• Versijas ir dažādās partīcijās. MergeTree dokumentācijā: daļas, kas pieder dažādām partīcijām, netiek sapludinātas. Ja partīcijas izteiksme atkarīga no kolonnas, kas mainās starp versijām, piemēram, PARTITION BY toYYYYMM(updated_at), katrs atjauninājums, kas šķērso mēneša robežu, atstāj pastāvīgu dublikātu. ReplacingMergeTree ceļvedis prasa nodrošināt, ka partīcijas atslēga rindai nemainās.
• Versijas ir dažādos šardos. Sapludināšana redz tikai lokālās tabulas daļas vienā serverī. Ja jūsu Distributed tabula šardē pēc kaut kā nejauša, divas vienas atslēgas versijas var nonākt dažādos šardos un nekad netiks sapludinātas. Šardējiet pēc deduplikācijas atslēgas izteiksmes.
• Daļas ir par lielu sapludināšanai. Fona sapludināšanu ierobežo max_bytes_to_merge_at_max_space_in_pool, kas pašreizējā pirmkodā pēc noklusējuma ir 150 GiB. Kad vecās daļas sasniedz šo izmēru, tās vairs savā starpā netiek sapludinātas, un dublikāti starp tām var palikt ilgi.

Pārbaudiet, cik dublikātu tiešām ir

-- how many duplicates are still physically there?
SELECT count() AS rows, uniqExact(order_id) AS keys, rows - keys AS extra_versions
FROM orders;

-- where are they? (same key in several parts or partitions)
SELECT order_id, count() AS versions, groupUniqArray(_partition_id) AS partitions
FROM orders
GROUP BY order_id
HAVING versions > 1
ORDER BY versions DESC
LIMIT 20;

extra_versions parāda, cik tabula saruktu pēc pilnas sapludināšanas un cik nepareizs šobrīd ir parasts count(). Otrais vaicājums parāda, vai dublikāti atrodas vienā partīcijā (tie, visticamāk, vēlāk tiks sapludināti) vai dažādās (tie netiks nekad). Lielā tabulā palaidiet to pa vienai partīcijai.

ver un is_deleted izvēle

Dzinējs pieņem divus neobligātus parametrus. ver ir versijas kolonna ar tipu UInt*, Date, DateTime vai DateTime64. Ar to uzvar rinda ar lielāko versiju; bez tās uzvar pēdējā ievietotā rinda. Izmantojiet īstu versiju, piemēram, atjaunināšanas laiku no avota vai žurnāla pozīciju no CDC. "Pēdējā ievietotā" ir atkarīga no secības, kādā atnāk partijas, un atkārtotas ielādes vai vēsturisko datu aizpilde mierīgi pārrakstīs jaunus datus ar veciem.

is_deleted ir UInt8 kolonna, kur 1 nozīmē, ka rinda ir dzēsta. Dokumentācija norāda, ka to var ieslēgt tikai tad, ja izmanto ver. Lai dzēstu rindu, jūs ievietojat tās jaunu versiju ar lielāku ver un is_deleted = 1. Pēc noklusējuma pats dzēšanas marķieris pēc sapludināšanas paliek, lai vēlāk atnākusi vecāka versija nevarētu rindu atdzīvināt. Vaicājumi ar FINAL neatgriež rindas, kuru jaunākā versija ir dzēšana.

Tabula, kas izskatās pareiza, bet kurai ir partīciju problēma:

CREATE TABLE orders
(
    order_id   UInt64,
    status     LowCardinality(String),
    amount     Decimal(18, 2),
    updated_at DateTime64(3),
    is_deleted UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(updated_at, is_deleted)
PARTITION BY toYYYYMM(updated_at)   -- changes with every new version
ORDER BY order_id;

Pasūtījumam, kas izveidots oktobrī un atjaunināts novembrī, tagad ir pa versijai katrā mēneša partīcijā, un tās nekad netiks sapludinātas. Drošāks variants partīcijo pēc nemainīgas kolonnas un liek to pašu izteiksmi kārtošanas atslēgas sākumā:

CREATE TABLE orders
(
    order_id   UInt64,
    created_at DateTime,
    status     LowCardinality(String),
    amount     Decimal(18, 2),
    updated_at DateTime64(3),
    is_deleted UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(updated_at, is_deleted)
PARTITION BY toYYYYMM(created_at)
ORDER BY (toYYYYMM(created_at), order_id);

Tagad rinda nevar pārvietoties starp partīcijām, un saskaņā ar ceļvedi kopš versijas 23.12 ClickHouse vaicājuma laikā izvairās no sapludināšanas starp partīcijām, ja partīcijas atslēga ir kārtošanas atslēgas prefikss. Tas padara arī FINAL lētāku. Ceļvedis arī iesaka ORDER BY kolonnas turēt nemainīgas un avota primāro atslēgu pievienot beigās.

Trīs veidi, kā lasīt pareizus rezultātus

-- 1. FINAL: merge at query time
SELECT status, sum(amount)
FROM orders FINAL
WHERE created_at >= '2026-11-01'
GROUP BY status;

-- 2. argMax: keep the latest version yourself
SELECT order_id,
       argMax(status, updated_at) AS status,
       argMax(amount, updated_at) AS amount
FROM orders
GROUP BY order_id
HAVING argMax(is_deleted, updated_at) = 0;

-- 3. LIMIT BY: one row per key
SELECT *
FROM orders
ORDER BY order_id, updated_at DESC
LIMIT 1 BY order_id;

• FINAL sapludina datus atmiņā vaicājuma laikā. Tas prasa papildu CPU un atmiņu un strādā lēnāk nekā tas pats vaicājums bez tā, bet tas ir vienkāršākais pareizais variants un daudz lētāks nekā piespiedu sapludināšana ar OPTIMIZE. Filtrējiet pēc atslēgas kolonnām WHERE, lai samazinātu deduplicējamo datu apjomu. Ceļvedis norāda, ka ar FINAL netiek izmantots PREWHERE.
• argMax ar GROUP BY dod tiešu kontroli un labi der agregācijām un materializētajiem skatiem. Jānorāda katra kolonna, un dzēšanas marķieri jāfiltrē pašiem.
• LIMIT 1 BY ir ērts maziem rezultātiem un atkļūdošanai. Lielā tabulā tas kārto daudz datu, tāpēc paneļus uz tā neveidojiet.

Ja katram vaicājumam pret tabulu vajag FINAL, iestatījums final to piemēro automātiski sesijai vai lietotāja profilam. Partīcijotām tabulām do_not_merge_across_partitions_select_final = 1 liek FINAL apstrādāt partīcijas neatkarīgi. Izmantojiet to tikai tad, ja rindas versijas nekad nevar būt dažādās partīcijās, citādi dublikāti atgriežas.

OPTIMIZE FINAL un CLEANUP: lietojiet taupīgi

OPTIMIZE TABLE ... FINAL piespiež sapludināšanu. Dokumentācija brīdina, ka tas nolasīs un ierakstīs lielu datu apjomu, un ceļvedis piebilst, ka tas var prasīt daudz atmiņas un var būt jāatkārto, nākot jauniem datiem. Tas arī ignorē parasto sapludināšanas izmēra ierobežojumu, tāpēc viena komanda var pārrakstīt veselu simtiem gigabaitu lielu partīciju. Palaidiet to pa partīcijām, vecām partīcijām, kurās vairs neraksta, klusās stundās un tikai tad, kad tiešām jāatgūst diska vieta.

Parastā sapludināšana dzēšanas marķierus nekad nenoņem. Lai tos izmestu, dokumentācija prasa eksperimentālo iestatījumu allow_experimental_replacing_merge_with_cleanup un vai nu manuālu OPTIMIZE ... FINAL CLEANUP, vai automātisku veco partīciju piespiedu sapludināšanu ar min_age_to_force_merge_seconds un saistītiem iestatījumiem:

ALTER TABLE orders MODIFY SETTING allow_experimental_replacing_merge_with_cleanup = 1;

OPTIMIZE TABLE orders PARTITION 202610 FINAL CLEANUP;

Tīriet tikai tās partīcijas, kurās vēlas, nesecīgas versijas vairs nevar atnākt; citādi veca versija var atdzīvoties. Ja vajag īstu dzēšanu, nevis marķierus, salīdziniet iespējas mūsu ceļvedī par rindu dzēšanu ClickHouse.

Kas mainās terabaitu mēroga tabulās

• Dublikāti vecās, lielās daļās var saglabāties mēnešiem. Kad daļas sasniedz sapludināšanas izmēra robežu, dzinējs tās vairs neapvieno. Vairāku terabaitu tabulā mēriet extra_versions katrai partīcijai un pieņemiet, ka rezultātus pareizus padara tikai FINAL vai jūsu pašu deduplikācija vaicājumos.
• FINAL izmaksas aug līdz ar datiem, ko tas skar. Mazā tabulā tās gandrīz nav redzamas. Miljardos rindu vaicājums bez filtra pēc atslēgas vai partīcijas var nolasīt daudz vairāk, nekā gaidāt. Partīcijojiet pēc nemainīgas laika kolonnas, turiet partīcijas izteiksmi kā ORDER BY prefiksu un vienmēr filtrējiet pēc tās.
• Piespiedu sapludināšana konkurē ar ievietošanu. OPTIMIZE FINAL lielai partīcijai izmanto diska joslu, CPU un pagaidu vietu, kas vajadzīga arī jaunu datu fona sapludināšanai. Ja tā atpaliek, ievietošanā sākas "too many parts" kļūdas.
• Šardēšanas kļūdas ir dārgi labot. Pāreja uz šardēšanas atslēgu, kas ietver deduplikācijas atslēgu, nozīmē datu pārdalīšanu. Izdariet to pareizi, pirms tabula izaug.
• Materializētie skati redz neapstrādātus ievietojumus. Materializētais skats virs ReplacingMergeTree avota saņem katru versiju, nevis deduplicēto rezultātu. No tā veidotie agregāti skaita katru versiju, ja vien to neparedzat dizainā.