Wat is nieuw in pg_clickhouse v0.10.0: Subqueries, TPC-H Speedups, C Driver en Aggregaten

In het kader van onze voortdurende investering in pg_clickhouse blijft het verbeteren van de pushdown-dekking voor analytische workloads onze belangrijkste focus. Onze directe maatstaf hiervoor is een volledige pushdown over de TPC-H benchmark-suite. Sinds onze laatste update in juni hebben we veel vooruitgang geboekt, onder andere op het TPC-H scorebord. Met de release van v0.10.0 is ons scorebord gestegen van 12 naar 16 van de 22 volledig gepushte TPC-H-queries; er blijven er dus nog maar zes over om de set te voltooien.

Daarnaast hebben we het volgende gerealiseerd:

• De binary driver is herbouwd op basis van een nieuwe, eenvoudige C-clientbibliotheek.
• Het aantal functies en aggregaten dat wordt gepusht, is meer dan verdubbeld.
• De binary driver is robuuster gemaakt tegen enkele concurrency-bugs (hieronder nader toegelicht).

Het scorebord

Drie extra TPC-H-queries worden nu volledig gepusht. Deze drie waren voorheen uiterst inefficiënt omdat pg_clickhouse, vanwege de vorm van de query, elke rij individueel van ClickHouse moest ophalen om vervolgens de subquery lokaal te evalueren:

Query	PostgreSQL	pg_clickhouse 0.3	pg_clickhouse 0.10	Pushdown
Q25	88 ms	3.446 ms	24 ms	✔
Q17	2107 ms	32.709 ms	37 ms	✔
Q22	270 ms	1.415 ms	45 ms	✼

( ✔ = de gehele query is één enkele foreign scan ) ( ✼ = gepusht, maar als meer dan één remote query; doorgaans een outer scan plus één InitPlan scan)

Query Q17 is het pronkstuk: een gecorreleerde subquery die het gemiddelde lquantity per onderdeel berekent. Toen deze nog één keer per outer row werd geëvalueerd tegen 6 miljoen regels (bij schaalfactor 1), duurde dit 32,7 seconden. Nu deze volledig is gepusht, is dat teruggebracht naar 37 milliseconden. Dit is een verschil van drie grootteordes en laat duidelijk zien waar pgclickhouse beter presteert dan het eigen plan van native PostgreSQL voor dezelfde query (2,1 s).

Zes queries blijven nog niet gepusht: Q13, Q15, Q16, Q18, Q20 en Q21. Q16 en Q18 wijzen ons de weg voorwaarts; pg_clickhouse pusht de SQL-vorm die zij nodig hebben al (IN en NOT IN worden gedeparsed als anti/semi-joins, zoals in Q2 en Q17). Wat hen blokkeert is dat PostgreSQL hun subqueries platlaat tot anti/semi-joins waarvan de inputs zelf joins zijn, en de deparser loopt nog niet door een join-tree aan beide zijden van een join. Q15 en Q20 hebben te maken met varianten van hetzelfde probleem. Dit is het volgende onderdeel voor de subquery pushdown.

De subquery-story voltooien

Het hoofdonderwerp in december was de planner leren om een volledige gecorreleerde EXISTS-subquery te pushen als één enkele LEFT SEMI JOIN, in plaats van een nested loop met één ClickHouse-roundtrip per outer row. Hierdoor steeg het aantal gepushte TPC-H-queries van 3 naar 12. De overige tien queries hadden één gemeenschappelijk probleem: de planner kon subqueries helemaal niet samenvoegen tot een join, waardoor er een SubPlan achterbleef. Dit is een onderdeel van een queryplan dat een volledig plan beschrijft voor een aparte query die als onderdeel van de volledige query wordt uitgevoerd, meestal één keer per rij.

Het pushen hiervan was het vijfde punt op onze roadmap en dit is in deze laatste release (0.10.0) opgeleverd (#289). Nu worden subqueries in Postgres subqueries in ClickHouse:

-- PostgreSQL Query
EXPLAIN (VERBOSE, COSTS OFF)
SELECT s.sale_id, s.amount FROM sales s
WHERE s.amount > (SELECT 1.5 * avg(s2.amount) FROM sales s2
                  WHERE s2.item_id = s.item_id)
ORDER BY s.sale_id;

Resultaat:

Foreign Scan on subplan_test.sales s
   Output: s.sale_id, s.amount
   Remote SQL: SELECT sale_id, amount FROM subplan_test.sales r1 WHERE ((r1.amount > (SELECT (1.5 * avg(q1_1.amount)) FROM subplan_test.sales q1_1 WHERE ((q1_1.item_id = (r1.item_id)))))) ORDER BY r1.sale_id ASC NULLS LAST
   SubPlan expr_1
     ->  Foreign Scan
           Output: ((1.5 * avg(s2.amount)))
           Relations: Aggregate on (sales s2)
           Remote SQL: SELECT (1.5 * avg(amount)) FROM subplan_test.sales WHERE ((item_id = {p1:Int32}))

De EXPLAIN toont nog steeds de SubPlan-node (dat is enkel de boekhouding van PostgreSQL voor de correlatie), maar je kunt zien dat de bovenste Remote SQL de volledige vergelijking bevat, inclusief de subquery, in één statement dat naar ClickHouse wordt verzonden. Door hetzelfde mechanisme kan pg_clickhouse de gehele TPC-H Q2 pushen: één Foreign Scan en één remote query. NOT IN krijgt dezelfde behandeling via een LEFT ANTI JOIN zodra de planner kan bewijzen dat deze transformatie veilig is.

Let op dat dit niet werkt met versies vóór ClickHouse 25.8, aangezien deze de gecorreleerde-subquery SQL-vorm niet ondersteunen; pg_clickhouse controleert de serverversie tijdens het plannen en valt terug op lokale evaluatie bij oudere servers.

NOT IN correct geïmplementeerd

Het pushen van de SQL was het gemakkelijke deel. Het moeilijkere was om ervoor te zorgen dat hetzelfde antwoord wordt berekend als PostgreSQL zou doen (#315, #317). ClickHouse's IN werkt met twee-waardige logica (two-valued logic), terwijl PostgreSQL drie-waardige logica (three-valued logic) gebruikt. Dit betekent dat x NOT IN (1, NULL) in PostgreSQL FALSE (als x=1) of NULL kan zijn, maar nooit TRUE. Bij een naïve pushdown kunnen deze expressies stilletjes resultaten inverteren waar een NULL betrokken is bij een vergelijking.

De fix in v0.10 volgt hoe het resultaat van elke expressie wordt geconsumeerd, zodat we weten hoeveel voorzorgsmaatregelen de query moet nemen om de resultaten consistent te houden:

• Een filterconditie kan NULL gratis behandelen als FALSE, dus ClickHouse-gedrag is prima in condities buiten een NOT.
• Een waardepositie of een negatie heeft extra controle op null-waarden nodig om de correcte Postgres-waarde in het resultaat te injecteren.
• Als pg_clickhouse kan bewijzen dat de operanden geen NULL kunnen zijn (door ze te herleiden naar een non-NULL constante, of een kolom met een NOT NULL-constraint), kunnen de guards die het Postgres-gedrag injecteren worden overgeslagen.

Een query met onze guards en Postgres-gedrag ziet er als volgt uit:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM tnull WHERE xn NOT IN (1, NULL) ORDER BY id;

Remote SQL: SELECT id FROM innulltest.tnull WHERE ((CASE WHEN xn IS NULL AND notEmpty([1,NULL]) THEN NULL WHEN countEqual([1,NULL], xn) > 0 THEN false WHEN countEqual([1,NULL], NULL) > 0 THEN NULL ELSE true END)) ORDER BY id ASC NULLS LAST

Een veelvoorkomend geval zonder NULL kan worden verzonden als een gewone native IN:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM tnull WHERE xn NOT IN (1, 500) ORDER BY id;

Remote SQL: SELECT id FROM innulltest.tnull WHERE ((xn NOT IN (1,500))) ORDER BY id ASC NULLS LAST

Een vervolgupdate (#317) heeft dit gedrag gegeneraliseerd naar de hele IN-familie van operatoren (IN, NOT IN, = ANY, = ALL, <> ANY, <> ALL), zowel in scalaire als array-vorm. Daarnaast is een bug opgelost waarbij <> ANY(array) feitelijk <> ALL berekende.

Dit alles rust op de aanname dat ClickHouse's IN zich inderdaad gedraagt volgens de twee-waardige logica. Een server-instelling (transformnullin) kan dit veranderen. Daarom hebben we transformnullin 0 toegevoegd aan de standaard pgclickhouse.sessionsettings.

Uitbreiding van pushdown-mogelijkheden

Naast het werk aan joins en subqueries is de lijst met individuele functies, operatoren en aggregaten die worden gepusht aanzienlijk gegroeid:

• Regex: Diverse updates (zie eerdere berichten).
• Aggregaten:
• Statistische aggregaten (#290): corr, covarpop/samp, stddevpop/samp, varpop/samp, anyvalue.
• Ordered-set aggregaten (#291): Gekoppeld aan de parametrische vormen van ClickHouse (percentile_cont/disc → quantile(s)/quantileExactLow).
• Partitiegewijze aggregatie (#298): Nuttig als analytische partities naar ClickHouse zijn verplaatst en transactionele partities in Postgres zijn gebleven. Berekent het aandeel van de foreign partition op ClickHouse in plaats van de rijen op te halen (vereist enablepartitionwiseaggregate).
• Overige:
• Formattering en encoding: encode(bytea, 'hex'|'base64'|'base64url') (#302).
• Strings: Drie-argumenten ltrim/rtrim/btrim (#307).
• Kostenschatting: Verbeterde cost functions zodat de planner vaker voor de goedkopere optie kiest bij MIN/MAX op ClickHouse (#310).
• Datums en tijden: Interval-arithmetiek uitgebreid naar date/timestamp operanden en aftrekking (#301). De CURRENT*/now()/clocktimestamp() familie is hersteld voor session time zone en sub-seconde precisie.

Een belangrijke architecturale wijziging is dat builtin function pushdown nu opt-in is (#245). Voorheen werd elke Postgres-builtin waarvan de naam overeenkwam met een ClickHouse-functie standaard verzonden, wat kon leiden tot subtiele verschillen in resultaten (bijv. trigonometrische functies zoals asin/acos waarbij Postgres een error geeft bij waarden buiten het domein, terwijl ClickHouse NaN retourneert). Sinds v0.3.0 wordt een functie alleen gepusht als deze expliciet is gemapt.

Driver Updates

In v0.3.1 hebben we de oude clickhouse-cpp volledig vervangen door ClickHouse/clickhouse-c, een nieuwe C-client (#254). Deze overstap loste crashes op die werden veroorzaakt door het mengen van C++ exception handling met de setjmp/longjmp-gebaseerde error handling van PostgreSQL. Bovendien streamt clickhouse-c resultaten blok voor blok, waardoor het geheugengebruik niet langer schaalt met de resultaatgrootte. De buildtijd en grootte van de library zijn hiermee met meer dan 75% verminderd.

Andere belangrijke driver-updates:

• HTTP Driver: Spreekt nu ook het Native-formaat van ClickHouse (dezelfde encoding/decoding als de binary driver) in plaats van het oude TSV-pad (#328). Hierdoor is fetch_size deprecated.
• Schrijfoperaties: De binary driver flusht gebufferde INSERT/COPY FROM-data nu na 64MiB (#303), in plaats van de volledige batch in het geheugen te houden.
• Beveiliging en compressie: Beide drivers hebben expliciete compressie (none/lz4/zstd) gekregen (#268) en TLS-controles (secure = on/off/auto, mintlsversion) (#272).
• Type-ondersteuning: Ondersteuning voor multidimensionale arrays voor zowel lezen als schrijven in beide drivers (#233), en Array(Nullable(T)) inserts via het binary protocol (#316).

Wat betreft betrouwbaarheid: concurrent foreign scans op de binary driver (bijv. bij een gecorreleerde subquery) kregen voorheen een crash omdat ze één gedeelde verbinding gebruikten; nu krijgt elke concurrent scan zijn eigen verbinding (#296). Ook is er een fix voor een specifieke querystructuur die faalde door een ongeldige relation OID te kiezen (#319).

Daarnaast is het verlies van sub-seconde precisie bij het invoeren van timestamps via HTTP opgelost (#300), en zijn diverse latente bugs verwijderd na een uitgebreide static-analysis pass (#313).

Nieuwe functionaliteiten

Er zijn functies toegevoegd die de mogelijkheden uitbreiden buiten automatische pushdowns:

• clickhouse_query(server, sql): Voert een willekeurige query uit tegen een geconfigureerde server. Ondersteunt nu ook de binary driver (#309).
• clickhouse_perform(server, sql): Een nieuwe procedure (aan te roepen met CALL in plaats van SELECT) voor statements die effect hebben maar geen rijen teruggeven, zoals CREATE TABLE (#329).
• clickhouseserverversion(server): Rapporteert de versie van de verbonden server (#293).

De functie clickhouserawquery() is deprecated en zal in de volgende release worden verwijderd. Stap over op clickhousequery() of CALL clickhouseperform().

Overzicht eerdere functionaliteiten

Voor de volledigheid volgt hier een overzicht van functies uit eerdere releases:

• JSON:
• Operatoren en functies: ->/->> en jsonbextractpath[_text]() mappen op ClickHouse's sub-column syntax (#169, #176).
• Types: Het native JSON-type van ClickHouse mapt op Postgres json.
• Arrays:
• Pushdown van meer dan een dozijn functies: arraycat, append, remove, tostring, length, hasAll/hasAny (voor @>/<@/&&) en slice-syntax (arr[L:U] als arraySlice()).
• Aggregaten:
• Window functies (#175): Volledige set wordt gepusht, waaronder ROW_NUMBER, RANK, LEAD/LAG, NTILE.
• Booleans en Strings (#184): booland, boolor, string_agg.
• Overig:
• to_char() met format-string validatie (#244).
• split_part() (#206).
• fuzzystrmatch's soundex()/levenshtein() (#210).

Wat nog openstaat

Naast de resterende zes TPC-H queries blijven veel punten van de oorspronkelijke roadmap openstaan: de overige niet-gedekte PostgreSQL-functies, lightweight DELETE/UPDATE en UNION pushdown. De beperking met betrekking tot join-trees aan beide zijden (die Q15/16/18/20 blokkeert) is het meest significante onderdeel en zal het onderwerp zijn van een volgend bericht.