# Ivan Blinov: everything in one file > Hands-on data and AI platform architect. I build data platforms where the numbers have to be right to the cent, and the AI-agent infrastructure that lets a small team run them safely. Tech lead for the billing data of millions of sellers at one of the largest marketplaces in Eurasia (100+ TB); co-founder and CTO of a health-tech startup in Brazil; earlier an engineering manager whose team grew from 6 to 20. I work with coding agents and verify everything they produce. Data & AI Platform Architect, works remotely across the Americas (UTC-3 to UTC-6). Languages: Russian (Native), English (Full professional proficiency), French (Full professional proficiency), Spanish (Full professional proficiency), Portuguese (Professional working proficiency). Contact: contact@ivanblinov.com # About I'm a hands-on engineer and architect for data platforms where the numbers have to be right to the cent, and for the AI-agent infrastructure that lets a small team run them safely. Since 2023 I have worked at Wildberries, one of the largest marketplaces in Eurasia. There I am the tech lead of a 12-engineer team that owns billing and settlements data for millions of sellers: tariffs, logistics and storage fees, acquiring, compensations and contract documents, on Greenplum (about 100 TB), ClickHouse, PostgreSQL, Kafka and Airflow. In 2026 I built the team's AI-agent platform and started applying formal methods to its billing logic. I am also the co-founder and CTO of Groupsi, an online group-therapy platform for school and university students in Brazil. Before that I was an engineering manager at a Swiss crypto fund (the team grew from 6 to 20), team lead of a 10-engineer analytics platform for Russia's Federal State Statistics Service, a back-end developer at Sketchfab in Paris, and a builder of B2B SaaS and API-billing products from zero. ## How I work - I work with coding agents and verify everything they produce. Direction, verification and accountability are mine. - I look for the mechanism behind a problem, not just the symptom, and I measure before and after. - I put safety in capabilities and permissions, not in instructions. ## Languages and location English, Spanish and French at full professional proficiency, Portuguese at professional working proficiency, Russian native. I work remotely across the Americas (UTC−3 to UTC−6). ## Education - 42 — Digital Technologies Architect, 2017–2022 - Moscow Open Institute — Bachelor's degree, Project Management, 2017–2025 - Sorbonne Université — Classics and Classical Languages, 2015–2017 - Oracle Certified Associate, Java SE 8 Programmer ## Downloads - [CV as PDF](https://ivanblinov.com/assets/ivan-blinov-cv.pdf) or [on the web](https://ivanblinov.com/cv/) - [One-page profile as PDF](https://ivanblinov.com/assets/ivan-blinov-one-pager.pdf) - [Profile as JSON Resume](https://ivanblinov.com/resume.json) ## Contact [contact@ivanblinov.com](mailto:contact@ivanblinov.com) · [LinkedIn](https://www.linkedin.com/in/ivanblinov) · [GitHub](https://github.com/littleBro) --- Source: https://ivanblinov.com/about/ # Ivan Blinov **Data & AI Platform Engineer · Tech Lead** Remote · Americas (UTC−3 to UTC−6) contact@ivanblinov.com · ivanblinov.com · linkedin.com/in/ivanblinov · github.com/littleBro ## Summary Hands-on tech lead with 12 years in software, including 3+ years managing engineers (teams of up to 20). I build data platforms where numbers must be right to the cent — billing and settlements data for millions of sellers on one of the largest marketplaces in Eurasia, 100+ TB — and the AI-agent infrastructure that lets a small team run them safely. I verify critical logic with formal methods and build products from zero, most recently as co-founder and CTO of a Brazilian health-tech startup. ## Selected results - Built the AI platform of a 12-engineer data team: an MCP server with 100+ tools over 15 systems, an autonomous on-call agent and an agentic merge-request reviewer (benchmark: 6 of 7 reference findings vs ~2.5 for diff-only LLM review; first production run: 15 findings, zero false positives), all under a 21-ADR security architecture. - Root-caused a ClickHouse 23.8 global-lock bottleneck behind seller-portal timeouts, benchmarked three versions and redesigned the query path for ~5x throughput. - Raced an int32 overflow across ClickHouse, Greenplum and PostgreSQL. Product IDs were at 93.5% of the 32-bit range and growing by up to 20 million a day. Three ClickHouse clusters were migrated in a week, and 4.7 TB of partitioned PostgreSQL tables copied into 64-bit twins under a WAL budget. Five live functions that would have failed first were found in time. - Built BendKV with a team of AI coding agents in twelve days: a Redis-compatible server whose core is formally verified against 68 laws, passing 1,769 of Redis's 1,812 own tests. Differential testing independently found nine bugs in Redis and Valkey. The same methods applied to production billing surfaced 42 findings. - Grew an engineering team from 6 to 20 (MDF AG); hired 20 engineers and formed three project teams (Netfactory). ## Experience ### Wildberries (RWB) — Moscow, Russia **Tech Lead, Data Engineering — Seller Balance** · Jan 2024 – present Hands-on lead (architecture, design and code review, implementation) of a 12-engineer team that owns billing and settlements data for millions of sellers: tariffs, logistics and storage fees, acquiring, compensations, contract documents. Greenplum (~100 TB), ClickHouse, PostgreSQL, Kafka, Airflow; Russia and Georgia. - Lead the Iceberg-on-S3 lakehouse pilot for the Seller Balance data organization (petabyte-scale target). Ran the data team's production Spark job unchanged on Iceberg; wrote 11 ADRs; established reader compatibility across Spark, Trino and ClickHouse, exactly-once Kafka ingestion and DR drills. - Root-caused a global-lock bottleneck in ClickHouse 23.8 under portal load (~58K lock acquisitions/s per node), proved the upstream fix was absent in our version and redesigned the query path for ~5x throughput. - Led the RCA of a PostgreSQL out-of-memory failover cascade; the fix reworked the memory configuration and hardware sizing. - Led the six-month migration from Microsoft SQL Server to Greenplum and ClickHouse; moved a 12.7 TB ClickHouse cluster between datacenters; drive a fleet-wide int-to-bigint key migration ahead of ID overflow, including copying multi-terabyte partitioned tables under a WAL budget. - Designing cross-datacenter disaster recovery for the Greenplum warehouse: measured ~40 TB/day of WAL and evaluated five replication strategies. - Built the team's AI platform: - MCP server with 100+ tools, read/write annotations and tool allowlists; - autonomous on-call agent; - agentic MR reviewer with a Postgres job queue, GitLab CI integration and an egress-allowlisted sandbox; - personal read-only database roles for the whole team. - Ran a formal-methods pilot for billing that links executable specifications to Go and SQL code; 42 findings in calculation, versioning and synchronization logic. - Built a Go service from scratch to production on three Kubernetes clusters (OpenAPI, Vault, CI/CD, load testing); founded the team's internal web app and handed it over to the team; automated contract-document generation. - Introduced mandatory merge-request approvals; ~100 review assignments a year; designed the stocks data architecture for the Georgia rollout. **Senior Data Engineer** · Jul – Dec 2023 - Refactored the supplier reporting API (Python, FastAPI, asyncio, ClickHouse), added tests and moved its cache to Redis Sentinel for high availability; built Airflow and Kafka pipelines (Kafka Connect, Debezium) for the DataOps platform. ### Groupsi — Brazil **Co-founder & CTO** (part-time) · Jan 2026 – present Online group therapy for school and university students, live with real users. - Built the product end to end: React Native (Expo), React, Fastify, Prisma/PostgreSQL, Redis, self-hosted LiveKit video; LGPD consent tracking and audit logging. - Designed and applied an Azure production platform in Terraform: landing zone across eight subscriptions, Entra ID, controlled egress, managed identities; planned the GCP-to-Azure cutover with no security-control regressions. - Run an AI-native engineering process: coding agents implement through pull requests, a second agent coordinates and verifies, six automated review seats in CI. About 950 commits and 450+ pull requests in ten months. ### MDF AG — Zürich, Switzerland **Engineering Manager** · Jul 2021 – Dec 2022 - Grew a cross-functional engineering team from 6 to 20: hiring, delivery process, feedback and conflict resolution. - Led portfolio-monitoring systems combining blockchain and exchange data for investment risk analysis; built trade-execution tooling; redesigned the backtesting engine with NumPy and Numba for a 100x speedup. ### Netfactory — Moscow region, Russia **Engineering Team Lead** · Oct 2019 – Jun 2021 - Led a 10-engineer team delivering a data-analysis platform for Russia's Federal State Statistics Service (billions of records, hundreds of users); took an API billing product from zero to delivery with a team of five; hired 20 engineers and formed three project teams. ### Sketchfab — Paris, France **Back End Developer** · Jan – Sep 2019 - Python/Django backend for 4M active users: database tuning, caching, rate limiting, asynchronous processing; incident response with Datadog. ### Smartbank — Moscow region, Russia **Full Stack Developer** · Mar 2017 – Dec 2018 - Built a B2B SaaS from scratch for bank–business workflows: government registry integrations, OCR, qualified electronic signatures; led a small remote team. ### Self-employed — Paris, France **Full Stack Developer** · 2014 – 2017 - Built web products from scratch: marketplace, news aggregator, data parsers, chatbots. ## Open source and research - **BendKV** — Redis-compatible server in Bend2 with a formally verified core: 68 laws checked by Bend and by a kernel proved sound in Lean; passes 1,769 of Redis 7.2.16's 1,812 own tests; differential testing against Redis independently found nine bugs in Redis and Valkey, several since fixed upstream. - **heilbronn-triangle-bounds** — conditional Heilbronn triangle bounds in Lean with exact certificates. - **Open Kernel Challenge 2026** — LLM inference kernels on NVIDIA H200 (FP4 MoE, attention, fused decode layer); final standings November 2026. ## Skills - **Data:** Greenplum, ClickHouse, PostgreSQL, Kafka, Airflow, Spark, Trino, Iceberg, Hive Metastore, S3; data modeling, performance tuning, migrations, disaster recovery. - **Languages:** Python, Go, SQL, TypeScript; earlier Java, PHP. - **AI engineering:** MCP servers, coding agents (Claude Code, Codex, OpenCode), agent orchestration and evaluation, LLM gateways, agent security. - **Infrastructure:** Kubernetes, Docker, Terraform, Azure, GCP, AWS, GitLab CI, GitHub Actions, Vault, Grafana. - **Correctness:** TLA+, Dafny, Z3, Lean; property-based, differential and mutation testing. - **Leadership:** architecture and ADRs, design and code review, hiring, team scaling, incident RCA. ## Education - 42 — Digital Technologies Architect, 2017–2022 - Moscow Open Institute — Bachelor's degree, Project Management, 2017–2025 - Sorbonne Université — Classics and Classical Languages, 2015–2017 - Oracle Certified Associate, Java SE 8 Programmer ## Languages Russian — native · English, French, Spanish — full professional proficiency · Portuguese — professional working proficiency --- Source: https://ivanblinov.com/cv/ # AI agents for a data team, without handing them the keys to production *What I built in 2026 for a 12-engineer billing data team, what the agentic code reviewer actually caught, and which guardrails mattered.* **TL;DR** - The team's agents can read almost everything — databases, pipelines, logs, Git, the tracker — through one MCP server with 100+ tools. Safety lives in credentials and capabilities, not in prompts. - On a reference merge request with seven known issues, an agentic reviewer found six; diff-only LLM review found about two and a half. In its first days in production it posted 15 findings with zero false positives. Its real weakness was speed, not accuracy. - The guardrail that mattered most was the boring one: read-only access enforced by the databases themselves. ## The starting point The team owns billing data for millions of sellers on a large marketplace: tariffs, logistics and storage fees, acquiring, compensations. The stack is a Greenplum warehouse of about 100 TB, three ClickHouse clusters, several PostgreSQL databases, Kafka, two Airflow installations, Trino and S3, plus GitLab, an issue tracker, chat, a wiki, Grafana and OpenSearch. Review practice was typical for a fast team that knows its domain: - 130–200 merges a month from about ten authors; - of the last 40 merge requests, 37 had no comments, and 38 were merged by their own authors; - the median merge request touched one file and 22 lines, and was merged 36 minutes after it was opened. Most of the time this works. Every few weeks it doesn't. In one case, a change added three columns to a staging table and to four queries, but not to the column list of an INSERT thirty lines above in the same function. The job failed on the first real document that night. No reviewer looking at the diff could have seen it: the broken line was not in the diff. Engineers were already using Claude Code, Codex, Cursor and OpenCode on their laptops. The question was not whether to use agents. It was how to let them see production systems without letting them change production. ## Four decisions that held up **1. Safety in capabilities, not in prompts.** An agent can edit its own instructions and work around tool code; it cannot work around a database role. "Impossible" beats "forbidden". - Every engineer's agent got a personal read-only database role, in a resource group with statement timeouts. - Every MCP tool declares whether it reads or writes. Each agent gets an allowlist, validated at startup: a write tool or a typo in a read-only allowlist means the server refuses to start. - Read-only is enforced on the server (read-only transactions in PostgreSQL, a read-only profile in ClickHouse). On top of that, a client-side guard only lets read statements through. **2. MCP next to the agent, not a central proxy.** Each agent runs its own MCP server with its own credentials. Only credential issuance and audit collection are candidates for centralization, off the hot path. A central tool proxy would be one more high-value target. **3. Strip sensitive data at the source.** People use different models, and you cannot enforce one model on every laptop. So sensitive fields are removed where they live: read-only roles and masked views in the databases, secret masking in every tool that shows configuration. **4. Automate only with a trigger.** A new autonomous agent needs a reason to run without a human: off-hours, every merge request, every alert, a schedule. Being able to do something is not a reason. Most ideas turned out to need better context, not new agents: a conventions file every agent reads, and the on-call runbook packaged as a skill shared by Claude Code, OpenCode and Codex. ## What I built - **An MCP server with 108 tools across about fifteen systems; 89 of them read-only.** Each tool is also a plain command-line script, so it works and can be tested without MCP. Write operations, such as re-running a pipeline task, are separate tools that a given agent simply doesn't get. One gotcha for MCP builders: if your tools spawn subprocesses, close their stdin. Otherwise children inherit the protocol's stdio and the server hangs. - **An on-call agent.** It sweeps the orchestrator, the data-quality channels and the databases every hour during working hours. It posts to a private channel only when something is wrong, and pings the on-call engineer only for critical issues, at most once a day per issue. Its first sweep found a streaming job that had been stuck for three weeks without tripping a single alert. - **An agentic merge-request reviewer.** An orchestrator, a headless agent harness (OpenCode) and the same MCP server with a read-only allowlist. ## The reviewer: benchmark, shadow mode, production ### Benchmark: the diff is not enough I took a merge request with seven known findings from a careful human review: - **Diff-only review** through our internal model gateway: the best model found about 2.5 of 7, plus five noise items. The others found one, with a factual error, or ran out of output budget before answering. - **The agent** with read-only access to the repository: 6 of 7 in about ten minutes and twenty tool calls. On the INSERT case above, diff-only review missed the bug with every model. The agent found the exact blocker in about five minutes, plus a deployment-order risk. In this codebase, most real bugs live outside the diff. ### Which instructions work 17 agent runs with five instruction styles. The most consistent was "review this merge request" plus a short map of the repository: where schemas, migrations and consumers live, and a few conventions. Checklists did not help. "Generate hypotheses first, then check them" produced the only miss of the INSERT bug. Repeated runs differed by about one finding. ### Shadow mode: precision before anyone sees a note First day, 11 runs on 8 merge requests: 24 of 32 findings correct, including 7 of 9 blockers. The false positives had three causes: 1. **Library defaults instead of real configuration.** Kafka consumer settings live in the orchestrator's connections, not in the repository. Fixed with a read-only connection-inspection tool that masks secrets. 2. **Deployment topology** that is not visible from the code. 3. **A merge request merged while it was being reviewed.** The same day there was a production incident. On the first hotfix, the reviewer named the root cause and predicted that the hotfix alone would not restore the data. On the second hotfix, it flagged a missing column that then failed in production. Three minutes after the failure, the author pushed exactly the suggested fix. Nobody saw these notes; that is what made it a clean test. A retroactive run on an older merge request found the mechanism of an incident two weeks before it happened. ### Production: accurate, and too slow In the first two and a half days in production there were 12 runs and 9 published notes. They contained 15 findings and zero false positives: 9 confirmed, 5 plausible, 1 question. Two of the blockers were independently found by people as well. One a human reviewer had written to the author 22 minutes earlier; the other the reviewer verified against production data, and the author fixed it independently minutes before the note appeared. The problem was latency. During the day, merge requests lived 4–27 minutes, and a review took 20–30 minutes on the shared model gateway (11–22 tokens per second by day, 60–90 at night). Not one of six daytime notes in the main repository landed before the merge. A higher reasoning-effort setting cut agent time by about 40% with identical verdicts, but did not close the gap. The lesson: when a team merges in minutes, a reviewer has to be either fast or a gate. A slow advisory review that arrives after the merge changes nothing. ## The guardrails that mattered - **Rules come from the target branch, never from the merge request.** A change cannot rewrite its own review rules. - **The agent never holds tokens.** The orchestrator fetches context and publishes the result. - **The repository is a temporary shallow copy of the head commit**, and the agent's working directory is outside it, so a repository cannot inject agent configuration. - **Isolation:** outbound traffic goes through a proxy with a domain allowlist, and each run gets a fresh state directory under a separate OS user. - **Explicit outcomes:** every failure path leaves a "review did not happen" marker, so silence never reads as approval. A new commit aborts the running review. **The one time it went wrong.** While checking whether a read-only fallback was really read-only, the agent ran DROP TABLE IF EXISTS, INSERT and TRUNCATE against a table that did not exist, on a production server. Nothing was harmed: the table did not exist, and the read-only profile does reject writes to real tables (we reproduced it locally). But DROP IF EXISTS on a missing table returns success before any permission check. So the agent concluded that read-only mode does not block DDL and raised a false blocker. There were two fixes. A client-side guard now sends only read statements. And one line went into the prompt: never test access boundaries by attempting writes. Prompts are the second line of defense, not the first. We then paused both agents ourselves until an internal security review, and wrote the package for it: 21 architecture decision records for an on-premises agent platform. Its core is a disposable executor per job, with network boundaries outside the guest, a capability gateway with server-side scope, external audit, a kill switch and 39 acceptance tests, including adversarial ones. ## What I would tell another team - **Start with read-only roles enforced by the systems themselves.** Everything else is defense in depth. - **Give the agent the repository, not the diff.** - **A repository map beats a checklist.** - **Measure in shadow mode before anyone sees a note:** precision per severity, causes of false positives, time to note versus time to merge. - **Expect most false positives to come from facts outside the repository** — runtime configuration, deployment topology — and give the agent read-only tools for those facts. - **Decide early whether the reviewer is a gate or advice.** Advice that arrives after the merge is invisible. - **Measure the team before you start.** We didn't, so I can't tell you agents made us X% faster. I can tell you what they caught. --- Source: https://ivanblinov.com/writing/ai-agents-for-a-data-team/ # Idle CPUs, 60-second queries: a global lock in ClickHouse 23.8 *How a dashboard widget that needed two numbers took a ClickHouse cluster down for 48 minutes, and why the fix was a cheaper query rather than more hardware.* **TL;DR** - A widget was switched from PostgreSQL to ClickHouse 23.8. For 48 minutes the cluster rejected queries with TOO_MANY_SIMULTANEOUS_QUERIES, including other teams' queries, while 65 of 80 cores per node sat idle. - The bottleneck was a global mutex in the query Context. Each node handled about 56–59K acquisitions per second; past that, every query waits in line. The widget's call cost 6,849 acquisitions. - One metric explained the incident and pointed at the fix: lock acquisitions per query. The widget moved to a dedicated count() query costing about 230 acquisitions, 30 times less. The upstream fix exists, but never reached 23.8 LTS. ## The symptom A customer-facing dashboard had a widget showing two counters: items ready to be handed over, in total and today. Over a year earlier the counters had been added to an existing report function, so the widget called the full report with a page size of one and read the counters from the result. On PostgreSQL this cost about 10 ms per call, and nobody noticed. In the morning the widget was switched to a ClickHouse cluster: two replicas, 80 cores each. Within a minute: - queries started failing with TOO_MANY_SIMULTANEOUS_QUERIES (code 202) at the limit of 500 concurrent queries; - 58% of the widget's requests were rejected; the ones that got through took about 60 seconds (max 66); - other consumers suffered too: about 20,000 of their queries were rejected, one report's median latency went from 1 second to 60, another's from 33 ms to 13 seconds; - meanwhile, about 15 of 80 cores were busy, with no I/O wait and flat memory. The widget was rolled back after 48 minutes. Idle CPU with everything timing out is the signature of a lock, not of load. ## The mechanism In ClickHouse 23.8, the shared part of the query Context is protected by a single global mutex. A query takes it when it creates a context for a subquery, reads settings, and so on. A simple query takes it a handful of times. A view with dozens of subqueries takes it thousands of times. With the old query analyzer, each scalar subquery in a global WITH is recomputed at every nesting level where it is used. Below the mutex's ceiling, waiting is close to zero. Above it, every query on the node stands in one line, regardless of free CPU. On these nodes the ceiling was about 56–59K acquisitions per second. ## Measuring it ClickHouse counts lock acquisitions and lock wait per query. This query finds the most expensive query shapes: ```sql SELECT normalized_query_hash, count() AS calls, round(avg(query_duration_ms)) AS avg_ms, round(avg(ProfileEvents['ContextLock'])) AS locks_per_call, round(avg(ProfileEvents['ContextLockWaitMicroseconds']) / 1000) AS lock_wait_ms, round(avg(ProfileEvents['OSCPUVirtualTimeMicroseconds']) / 1000) AS cpu_ms FROM system.query_log WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 HOUR GROUP BY normalized_query_hash ORDER BY sum(ProfileEvents['ContextLock']) DESC LIMIT 20; ``` If lock wait is close to the query duration while CPU time is small, you are looking at this problem. For the node as a whole, `system.metric_log` gives acquisitions per second and the average number of threads waiting on the lock. During the incident there were about 545 waiting threads for 500 running queries: ```sql SELECT toStartOfMinute(event_time) AS minute, round(sum(ProfileEvent_ContextLock) / 60) AS locks_per_second, round(sum(ProfileEvent_ContextLockWaitMicroseconds) / 60e6, 1) AS avg_waiting_threads FROM system.metric_log WHERE event_time > now() - INTERVAL 3 HOUR GROUP BY minute ORDER BY minute; ``` System tables are per node, so run both on every replica. ## What queries cost | Query | Context lock acquisitions per call | |---|---| | `SELECT 1` | 6–19 | | `count()` with `FINAL` over 26 parts, one parameter | ~230 | | The report view with a page size of one (29 subqueries, 388 scalar-subquery cache misses, ~0.9 s CPU) | 6,849 | | A pending change to the same report, as first written | 44,000–67,000 | | The same change after rework | 4,000–5,200 | At about one request per second, the change in the last two rows would have consumed three quarters of the lock on its own. The rework made it more than ten times cheaper. ## One formula When the lock is saturated: **latency ≈ concurrent queries × lock acquisitions per query ÷ ceiling** 500 × 6,849 ÷ 58,000 ≈ 59 seconds. The observed median was 60. The same numbers give capacity: 58,000 ÷ 6,849 ≈ 8.5 calls per second per node. The widget sent about 43 per second across two nodes, two and a half times the ceiling. The formula also shows why raising `max_concurrent_queries` would not help: throughput stays the same, and latency grows with concurrency (1,000 slots ≈ 120 seconds), while more threads and memory are held. ## Versions: the fix exists, just not in 23.8 The upstream fix, "Context lock contention fix" ([ClickHouse#55121](https://github.com/ClickHouse/ClickHouse/pull/55121)), was merged in October 2023 and backported to 23.9. The cherry-pick to 23.8 ([#55236](https://github.com/ClickHouse/ClickHouse/pull/55236)) was closed without merging. 23.8 is an LTS release. I reproduced the incident on a 16-core Docker stand: synthetic data with the same 26-part layout, 300 concurrent callers. | Setup | Throughput | Median latency | Notes | |---|---|---|---| | 23.8, original view | 4.0 QPS | 68 s | 72 s of lock wait per query | | 24.3, old analyzer | 6.0 QPS | 42 s | lock wait 9 ms: the lock is gone, the query is now CPU-bound (2.3 s per call) | | 25.3, new analyzer | 25 QPS | 9.9 s | scalar subqueries computed once (388 → 10 cache misses), 0.53 s CPU per call | | 23.8, rewritten view | 22 QPS | — | same version, no upgrade | | any version, dedicated `count()` | 40–64 QPS | — | 0.2–0.3 s CPU per call | 24.3 removes the lock. With the new analyzer the original view failed, so we ran it with the old one. Only 25.3 with the new analyzer also stops recomputing scalar subqueries. On 80-core production nodes the absolute numbers are higher, but the ratios hold. Under load, CPU per query was 2–2.5 times the single-run figure. ## What fixed it 1. **A counter is not a report.** The widget came back the same day on a direct `count()` query: about 230 acquisitions per call instead of 6,849. It served up to ~50 requests per second with a median of 45–53 ms and no errors. A dedicated counter view was then load-tested at 400 requests per second per cluster without errors. 2. **Fewer subqueries in the report itself.** Move counters into the final SELECT, avoid scalar subqueries in a global WITH, use IN instead of building arrays, compute related counters in one pass. The rewritten view returned byte-identical results and was 3.5–5 times cheaper on the same version. 3. **Limits per consumer.** `max_concurrent_queries_for_user` and `max_execution_time` stop one consumer from taking all 500 slots and starving everyone else. 4. **An upgrade with a measured case.** 24.3 or later for the lock, 25.3 or later with the new analyzer for repeated scalar subqueries. ## Takeaways - **Idle CPU plus timeouts means a lock or a queue, not load.** Look at wait metrics before adding hardware. - **Measure the cost of a query in the scarce resource.** Here it was global lock acquisitions, not CPU or rows. Capacity is the ceiling divided by the cost per call. - **Give every high-frequency consumer its own cheap query.** Reusing a full report for a counter works until the traffic changes. - **Raising concurrency limits on a saturated lock makes things worse.** - **"LTS" does not mean the fix you need is there.** Check backports for your exact version. --- Source: https://ivanblinov.com/writing/clickhouse-global-context-lock/ # 665 GiB of dictionaries: when ClickHouse dictionaries stop scaling *Nine hashed dictionaries quietly grew to two thirds of a terabyte per node, and every reload tried to build a second copy. How we found that only five views used them, and replaced them with joins that read 17 granules out of 282,102.* **TL;DR** - On a two-node ClickHouse cluster with 1 TB of RAM per node, nine hashed dictionaries keyed by product and size IDs took 663.5 GiB on each node. - They rebuilt by full reload every two to four hours. A reload builds the new hash table next to the old one, so one dictionary needed 183 GiB more on top of 857 GiB already in use. Reloads hit the memory limit, retried forever, and dragged queries down with them. - Only five views used the dictionaries. Rewritten as a CTE plus `ANY LEFT JOIN`, with primary-key pruning through an `IN` subquery, they read 17 of 282,102 granules, and the dictionaries could go. ## The symptom Reports on the cluster started failing with `MEMORY_LIMIT_EXCEEDED`, in waves. Nothing obvious had changed: no new heavy queries, no data spike. `system.dictionaries` showed the cause. Two dictionaries had been stuck in `LOADED_AND_RELOADING` for three days, and the total `bytes_allocated` across nine dictionaries was 663.5 GiB on each node, against a server memory limit of 906 GiB (0.9 × RAM). ## The mechanism The dictionaries mapped product and size IDs to names, categories and attributes. - **Their source** was a view with `FINAL` over a `ReplicatedReplacingMergeTree` of 2.31 billion rows (139 GiB compressed), growing by about 51 million rows a day. - **Every two to four hours** each dictionary reloaded in full: ClickHouse reads the source and builds a new hash table while the old one keeps serving, then swaps them. - **The double-memory moment.** For the largest dictionary, the reload needed 183 GiB on top of the 857 GiB already allocated. The reload failed on the memory limit and retried, again and again. Every attempt pushed ordinary queries over the limit too. The growth explains why it worked before. Over two years the number of products grew about eight times, and one product dictionary went from 17 GiB to 134 GiB. Hashed dictionaries scale with the key space, not with what anyone actually looks up. ## Who actually used them Before touching anything, we needed the real consumers, not the ones the repository implied: - **DDL:** no materialized views or column defaults referenced the dictionaries; only views did. - **`query_log` on both replicas for three days** (logs are per node, so one replica gives half the picture). Exactly five views used the big dictionaries: - three language variants of a sales report, about 23,000 calls a day with a median result of 12 rows; - a geography report, about 39,000 calls a day; - a weekly sales report, about 7,000 calls a day. So 663 GiB of RAM per node was spent to look up names for a few dozen rows per call. ## The fix A precedent existed on another cluster. Instead of a dictionary lookup per row, each view first collects the IDs it needs, then joins only those rows of the source: ```sql WITH chrt AS ( SELECT chrt_id, subject_name, brand_name /* … */ FROM chrt_source FINAL WHERE is_deleted = 0 AND chrt_id IN (SELECT DISTINCT chrt_id FROM report_rows) ) SELECT r.*, coalesce(c.subject_name, '') AS subject_name /* … */ FROM report_rows AS r ANY LEFT JOIN chrt AS c USING (chrt_id) ``` The source table is ordered by the ID, so the `IN` subquery prunes by primary key even on ClickHouse 23.8: `EXPLAIN indexes = 1` showed 17 granules read out of 282,102. The join costs milliseconds and no standing memory. The five views were rewritten and benchmarked against the old ones on production data, and the dictionaries were dropped. About 540 GiB of RAM per node came back at once, and the memory-limit storm ended. The last dictionary, 126 GiB used by a single report, was left for a second phase. **A trick that didn't work.** A `DIRECT` layout over a view on `numbers()` avoids holding the dictionary in memory, but it relies on predicate pushdown into `numbers()` that arrived only in 23.11. On 23.8 it was a dead end. ## Takeaways - **A hashed dictionary costs twice its size at reload time.** Budget memory for the peak, not the steady state. - **Dictionaries scale with the key space, joins with the query.** For sparse lookups (a few dozen rows per call), a join with primary-key pruning beats a giant cache. - **Find consumers in the logs of every replica**, and in the DDL, before deciding anything. - **Watch growth.** An eight-fold growth over two years turns a convenience into an outage; a rule of "no new dictionaries on large ID spaces" is cheaper than the next incident. --- Source: https://ivanblinov.com/writing/clickhouse-dictionaries-665-gib/ # Airflow tasks stuck in "queued" forever after a twelve-second database blip *A short network flap between two datacenters left a task in "queued" for two and a half hours, and its DAG with it. The cause is a lost executor event: `SELECT … FOR UPDATE SKIP LOCKED` skips exactly the row it needs. Why restarting the scheduler is the wrong fix.* **TL;DR** - With Airflow 2.11 and the LocalExecutor, a task process that dies while updating its own row leaves an event the scheduler throws away. The scheduler reads executor events with `SELECT … FOR UPDATE SKIP LOCKED`, skips the row that is still locked, and drops the event. The task instance stays "queued", and nothing in the LocalExecutor ever times it out. - The trigger was a database blip of about twelve seconds, caused by a link flap between datacenters. Every connection crossed that link twice. - The fix is to clear the task instance. Restarting the scheduler kills every running task, because tasks are child processes in the scheduler's cgroup. ## The symptom One afternoon a sensor task sat in "queued" for two and a half hours. Its DAG allows one active run, so the whole pipeline behind it stopped until the on-call engineer cleared the task by hand. Alerts of the form "state attribute is queued" had appeared on three earlier days in the summer and autumn, and each time nobody found a cause. ## The setup - **Airflow 2.11** with the LocalExecutor (parallelism 128) on a VM in datacenter A. - **The metadata database:** PostgreSQL under Patroni, with pgbouncer on three VMs. Its leader is also in datacenter A. - **The way in:** HAProxy pods in a Kubernetes cluster in datacenter B. They pick the leader with Patroni's REST health check and drop all sessions when a node is marked down. So every database connection from the scheduler and its tasks went from A to B and back to A. Any flap on the A–B link broke all of them at once. ## What the logs showed The scheduler's event log has a gap of about twelve seconds, during which the database answered nobody. Then came `server closed the connection unexpectedly`, and less than a minute later one more drop, at connect time. Patroni did not fail over: the timeline had not changed in months. Two kinds of task failures came out of the blip: - **A task that failed while connecting** was handled correctly: marked failed, alert sent. - **A task that failed in the middle of updating its own row** to "running", with the row already locked `FOR UPDATE`, left a trace like this in the scheduler log: `Received executor event with state failed`, without the `TaskInstance Finished` line that should follow. ## The mechanism 1. The task process starts and runs `UPDATE task_instance SET state = 'running' …`. The row is locked. 2. The connection dies mid-transaction. The process exits; the LocalExecutor reports a failed event. 3. The scheduler processes executor events and loads the task instances with `SELECT … FOR UPDATE SKIP LOCKED`, so that several schedulers can share the work. 4. The row is still locked, most likely because the database has not yet noticed that the client is gone. `SKIP LOCKED` skips the row. The event is consumed and dropped. 5. The task instance stays "queued". The LocalExecutor in 2.11 has no handling for `task_queued_timeout`, so nothing ever revisits it. ## The fix, and the wrong fix **Clear the stuck task instance.** In the UI: Browse → Task Instances, state = queued, and check `queued_when`. **Do not restart the scheduler.** The service runs with `KillMode=control-group` and a 90-second stop timeout. Tasks run as child processes inside the scheduler's cgroup, so a restart kills everything that is running. Some of our long tasks can only be restarted together with their whole DAG. ## What we changed and recommend - **Alert on task instances queued longer than a few minutes**, with the DAG and the `queued_when` time, and link the runbook entry: clear, don't restart. - **Look for a network flap first.** On the next drop, check the inter-datacenter link channel for the same minute before anything else. The cause is invisible from Airflow's side. - **Fix the topology.** Keep the path between the scheduler, the pooler and the database leader inside one datacenter, or accept that any link flap breaks every session twice. - **Collect logs while they exist.** HAProxy pod logs rotated within about 30 minutes, and were gone by the time anyone looked. The scheduler writes about 5,500 lines a minute, so search it in one-hour windows. ## Takeaways - **`SKIP LOCKED` trades correctness for throughput.** Any event processed through it can be skipped, and something must come back for what was skipped. - **A task "queued" forever is a lost event, not a slow scheduler.** - **Know your process model before you restart anything.** - **Topology is part of reliability.** A connection that crosses a link twice fails twice as often. --- Source: https://ivanblinov.com/writing/airflow-tasks-stuck-in-queued/ # Expired certificates broke production twice, and nothing turned red *Once as a load-balancer black hole that swallowed 23 of 24 connections for fifteen hours, once as a silent 30-hour replica lag behind a database that kept answering. Both times the metrics existed and the alerts did not.* **TL;DR** - **A PostgreSQL read-only pool.** One replica's client certificate between the pooler and PostgreSQL expired. The node failed every login instantly, so a least-connections balancer kept sending it new connections: 23 of 24. The pool was broken for 15 hours before anyone noticed. - **A two-node ClickHouse cluster.** A 90-day certificate expired on both nodes at once. Replication between them stopped; one replica fell 30 hours behind on 37–39 tables while clients saw no errors, so half of all reads returned day-old data. - **The lessons:** alert on expiry, make health checks perform the real handshake, never pair least-connections with a backend that fails fast, and watch lag on every replica. ## Incident one: the black hole The read-only port of a PostgreSQL cluster is a virtual IP in front of three replicas. On each node pgbouncer runs in session mode and connects to the local PostgreSQL with a TLS client certificate. HAProxy balances with `leastconn`. What happened: - On one node the pooler's client certificate expired in the middle of the night. PostgreSQL rejected the pooler: `certificate verify failed`. The pooler told clients `server login has been failing, cached error: server conn crashed?`. - The node was healthy by every check that existed: PostgreSQL was up, TCP accepted connections. - A node that fails every connection instantly always has the fewest active connections, so least-connections sent it almost everything new. We counted 23 of 24 new connections going to the broken replica. - Errors looked intermittent from the client side, because the occasional connection that reached a healthy replica succeeded. The outage lasted fifteen hours before anyone looked. - The certificate's expiry date was already collected as a metric. Nobody had put an alert on it. **How to find it fast.** Take the certificate straight from the virtual IP (a TLS handshake after PostgreSQL's SSLRequest) and read its subject alternative names: they name the node behind it. The workaround is to take the node out of the pool, or to point the service at the primary until the certificate is reissued. ## Incident two: the silent lag A two-node ClickHouse cluster served reads for a seller-facing portal, split roughly evenly between the nodes. Both nodes used one certificate with a 90-day lifetime for the native TLS port and for interserver replication. It was issued on the same day for both, and it expired on the same day for both. At expiry: - **Replication stopped.** Fetching parts between replicas failed: `CERTIFICATE_VERIFY_FAILED` on one side, `SSLV3_ALERT_CERTIFICATE_EXPIRED` on the other. - **Retries exhausted the ephemeral ports.** `Cannot assign requested address`, and about 1.65 million `ServerErrorHandler` errors an hour in `text_log`, up from dozens. - **The lag grew to about 30 hours** on 37–39 tables, with a replication queue of about 19,000 entries. The other node lagged on the three tables that were written through the first. - **Clients saw nothing.** Queries succeeded on both nodes, and half of the answers came from data a day old. - **It was found by accident**, a day later, while investigating the noise in `text_log`. The certificate was reissued the next evening; the replicas caught up within a week. The next expiry is in 90 days. **How to find it fast.** When replicas lag or network errors spike, check the certificate first: `openssl s_client -connect :`. Look at lag on every replica, not just the one your tools connect to by default. ## Takeaways - **Every certificate needs an expiry alert.** Usually the metric already exists; the alert is a line of configuration. 90-day certificates need automated renewal, or at least a check that runs weeks before expiry. - **Health checks must exercise the real path.** "Port open" and "process up" said nothing about whether a client could log in. - **Least-connections plus a fast-failing backend is a black hole.** Use health checks that detect failed logins, or a balancing method that does not reward failure. - **Errors that clients don't see are the dangerous ones.** Replication lag needs its own alert on every replica, independent of query errors. - **Shared certificates fail together.** Two nodes with the same issue date gave us zero redundancy against expiry. --- Source: https://ivanblinov.com/writing/expired-certificates-twice/ # LowCardinality in ClickHouse: I measured the rule of thumb, and it was wrong both ways *"LowCardinality helps between a thousand and a hundred thousand distinct values, and hurts otherwise." A code review quoted this rule, so I measured it on ClickHouse 23.8. LowCardinality wins at any small number of values, even two, and starts losing badly at about twenty thousand per part.* ## The measurement ClickHouse 23.8.5.16 in Docker, 16 CPUs. - **Data:** one string column, 10 million rows, merged into one part, with values random relative to the sort key. - **Comparison:** the same data as `String` and as `LowCardinality(String)`. - **Method:** five runs, best time. | Distinct values | Disk, String → LC | GROUP BY, ms | `WHERE val = 'v1'`, ms | INSERT, ms | |---:|---|---|---|---| | 2 | 9.7 → 4.8 MiB | 18 → 9 | 15 → 5 | 617 → 604 | | 18 | 15.5 → 9.6 MiB | 18 → 8 | 16 → 4 | 617 → 912 | | 1,000 | 28.3 → 19.2 MiB | 27 → 10 | 16 → 6 | 719 → 705 | | 8,000 | 38.1 → 19.2 MiB | 35 → 25 | 17 → 6 | 765 → 904 | | 20,000 | 42.5 → 49.0 MiB | 66 → 181 | 20 → 95 | 628 → 1,482 | | 100,000 | 49.0 → 64.7 MiB | 171 → 414 | 20 → 256 | 674 → 2,204 | | 1,000,000 | 59.1 → 76.9 MiB | 339 → 714 | 23 → 262 | 777 → 4,210 | | 10,000,000 (all unique) | 39.9 → 44.7 MiB | 516 → 1,321 | 24 → 239 | 966 → 2,623 | Memory tells the same story. A GROUP BY over 8,000 values used 129 MiB as `String` and 18 MiB as LowCardinality. Over 10 million unique values it used 745 MiB and 2.3 GiB. ## Why the threshold sits there A LowCardinality column stores, per part, a dictionary of values and a small integer index per row. The dictionary is shared across the part only up to `low_cardinality_max_dictionary_size`, 8,192 by default. Beyond that, ClickHouse writes additional dictionaries per block. Below the limit you get a compact, shared dictionary and integer comparisons. Above it, by the shape of the curve, you pay for many dictionaries on top of the data. So the number that matters is **distinct values per part**, not per table. A column with a million values overall, sorted so that each part sees a few thousand, can still be a good LowCardinality column. ## Joins and dictionaries A second set of measurements, closer to how the column was actually used: - **A 198-row translation dictionary** is the same size either way (1.48 vs 1.44 KiB). - **Joining a 30-million-row fact to it with a full scan** was slower with a LowCardinality key: 524 vs 826 ms median. It held far less memory (14 vs 133 MiB): less memory, more CPU. - **Three joins as in the real view, for one supplier:** no meaningful difference (19 vs 20 ms). - **A low-cardinality attribute stored in the fact table itself** (18 values) is where LowCardinality shines: 80.7 → 28.4 MiB on disk, GROUP BY 111 → 30 ms, filter 91 → 20 ms. - **Mixed key types** (`Nullable(String)` on one side, `LowCardinality(String)` on the other) joined without any penalty. ## The rule I use now - **Use LowCardinality for any column with up to a few thousand distinct values per part**, including flags and statuses with two or three values. - **Avoid it above roughly 10–20 thousand distinct values per part.** IDs, names of millions of products and free text are worse as LowCardinality on disk, in queries and on insert. - **For small dimension tables it hardly matters.** For the columns of large facts it matters a lot. - **Measure on your own data.** The curve is cheap to reproduce: one table, one column, a loop over cardinalities. --- Source: https://ivanblinov.com/writing/clickhouse-lowcardinality-measured/ # Partitioning by modulo in ClickHouse: the DDL I wrote that made queries 13.7 times slower *`PARTITION BY supplier_id % 100` sounds like it spreads the load evenly. In practice it destroyed primary-key pruning: a lookup read 64% of the table instead of 3%. Here are the measurements, and why it happens.* ## The table and the query A `ReplacingMergeTree` with 266 million rows of stock by seller, product, size and warehouse: - `ORDER BY (supplier_id, nm_id, chrt_id, office_id)`; - `PARTITION BY supplier_id % 100`. I wrote that DDL myself, by analogy with an older table, when we moved the report from PostgreSQL. The service query asks for about 1,000 (seller, product) pairs at a time: ```sql SELECT … FROM stock FINAL WHERE (supplier_id, nm_id) IN (…1,000 pairs…) ``` The requested sellers' rows were 9.6% of the table. The query read **64% of all granules** (20,916 of 32,540) and 4.65 GiB, and took about 2.6 seconds with a warm cache. ## Three variants, same data | Table | Granules read | Time | Data read | |---|---|---|---| | `PARTITION BY supplier_id % 100` | 20,916 of 32,540 (64%) | ~2.6 s | 4.65 GiB | | No partitioning | 972 of 31,729 (3.1%) | 191 ms | 189 MiB | | `PARTITION BY intDiv(supplier_id, 5000000)` | 1,758 of 32,036 (5.5%) | 221 ms | — | Without partitions the query was **13.7 times faster and read 25 times less**. The range partitioning did well too, but the divisor was badly chosen: seller IDs are skewed, and 93% of rows landed in partition 0. Something like `intDiv(supplier_id, 100000)` would spread them better. Even the access pattern partitioning was supposed to help, one seller at a time (2.7 million rows), lost: 326 granules and 57–71 ms without partitions, against 337–446 granules and 75–129 ms with the modulo. The primary-key prefix already narrows the read to one seller; the partition only adds fragmentation. ## Why modulo breaks pruning ClickHouse skips granules using the `[min, max]` range of the key columns in each granule (`KeyCondition`). With `supplier_id % 100`: - every part contains every hundredth seller across the whole ID space; - so within a part, consecutive granules jump between sellers that are far apart in ID terms; - each granule's `[min, max]` range for `supplier_id` becomes about a hundred times wider than in a part ordered over a contiguous range. A thousand requested pairs then intersect the ranges of almost every granule. The index check knows ranges, not residues; it cannot tell that seller 1,234,567 is absent from a granule spanning sellers 1,200,000 to 1,300,000. It is not a planner bug, and it is not `FINAL`: both were ruled out by measurement. ## What I do now - **Partition for data management, not for query speed.** Retention, dropping old data, backfilling a month: those are partitioning's job. Pruning is `ORDER BY`'s job. - **Never partition by a hash or modulo of a column the queries filter on.** It spreads every key's neighbors across all parts and widens every granule's range. - **If you need partitions on an ID, use ranges** (`intDiv`) sized to the actual distribution of the IDs. - **Check with `EXPLAIN indexes = 1`** how many granules a typical query reads. A number close to the table's total is a design smell, not a slow disk. --- Source: https://ivanblinov.com/writing/clickhouse-modulo-partitioning/