Registries and database architecture: diagnosis instead of a server upgrade, and search in 3–4 seconds instead of minutes

Analytics platforms age the same way: more data, more queries, slower response. The client’s registry search was sliding into minutes, and the obvious fix sat right on the surface: a beefier server. We didn’t rush to buy it. First we worked out what was actually slow, ran the hypothesis on a test bench, and the cause turned out not to be the hardware. The slow part was the “contains” operator: a substring-match search runs past the index, scanning millions of rows row by row, while the parser writes into that same table every second. The same query with “equals” returned in a millisecond. We could have closed it with a 5-hour fix, but the load was growing structurally. So instead of a new server we split the database into two tiers: an operational MySQL and an analytics replica on ClickHouse. Search now takes 3–4 seconds instead of minutes.

Snapshot

The problem

By 2024 the platform had hit the ceiling of the single MySQL database it had run on from the start. Search across TR CU RF declarations “barely worked at all,” in the client’s words: any column searched slowly and returned an error most of the time. The first reaction was the natural one: get a more powerful server.

Before pricing new hardware, we looked at where the time was actually going. It turned out some users were searching OGRN with the “contains” operator. That search runs past the index: the database has to scan rows in sequence, checking each one for a substring match. The same search with “equals” leans on the index and returns in a millisecond. We told the client straight: the issue isn’t capacity or a new server, it’s how the queries are formed. We scoped the fix at 5 hours: set “equals” as the default operator on the OGRN columns.

The client sharpened the picture, and it turned out harder. Across most text fields the “contains” operator is genuinely needed, and once the CRM widget moved to Bitrix24, the number of queries through it would grow several times over, with noticeably more users. Structural load on the heavy tables would grow regardless of how people searched. So the task moved from “speed up the query” to “move the analytics load off the operational database.” In a separate spec the client fixed the requirements in numbers:

• uninterrupted access at 20 consumers per minute,
• response no longer than 10 seconds,
• the architecture must allow user count to grow 2–3×,
• the CRM widget must return data without delay.

Why this is hard. Over-optimizing is as easy as under-optimizing. Moving to ClickHouse wholesale means rewriting half the Laravel code and losing ORM transactionality. Keeping everything on MySQL and pulling it along with indexes is no answer either: in six months the same thing starts again, materialized views conflict with the parser’s continuous writes, and a MySQL read replica won’t save you, because write load is climbing too. So we chose the middle path: operational work (parser writes, transactions) stays in MySQL, analytics and heavy search move to a replica on ClickHouse, with a container replicator between them. The price of that compromise is a few minutes of data lag in analytics behind the write. For B2B analytics, acceptable.

How we did it

1. Diagnosis first, architecture second.
You don’t re-architect blind. First we found the cause of the slowdown (the “contains” operator running past the index), named it to the client, and offered to keep it cheap: change the default operator. We moved to a rebuild only once it was clear that structural load would grow under any operator. Experiments with the new structure ran on a test bench, a home server: “if it works out, I’ll propose a solution.” A day later the research came back positive, the test contour was wired to one root account so the client could feel the speed firsthand, and only then did we write up the spec and the cost.

2. Two-server architecture: MySQL primary and a ClickHouse replica (73 h on spec).
The primary server (4 cores, 8 GB RAM, 80 GB disk) holds MySQL with the parsers. The secondary (8 cores, 12 GB, 100 GB) carries the container replicator and ClickHouse, a database built for fast reads and search. The replicator incrementally moves the four most massive tables to the secondary server (two for RF and two for the EAEU registries), syncing once an hour. We talked through one engineering nuance with the client up front: a table is added to the replicator by hand, and only if it has a numeric ID. By that ID the replicator works out how many new records appeared and pulls only those. The limit is deliberate: the four heavy tables get high speed, the rest run on the old mechanism, and a new table can be switched to ClickHouse with a single checkbox when needed.

3. Search from minutes to 3–4 seconds.
On those same four tables the average query now takes 3–4 seconds instead of the old minutes. The client confirmed: “the response really is very fast, and search works correctly.” Side effects surfaced along the way: the new database turned out case-sensitive and left service characters in strings, and we cleaned up both before rollout. The solution went to production on 5 June 2024. As a bonus, ClickHouse gave per-table query statistics that MySQL never had: you can see what users search for and how.

4. Registry of accredited bodies with per-company private lists (80 h on spec).
Source: Rosaccreditation, the registry of accredited bodies, around 30,000 records. Parsing follows the same principle as the documents. A full re-crawl of the reference runs weekly to catch changes. On top of the shared reference, the client asked for an editable list of “needed” accredited bodies, separate for each of the client’s companies, with no access between companies. This is row-level visibility control. The private lists rebuild every night, and one company can’t see another’s marks. The module was delivered 23–30 November 2022.

5. New-market-entrants database on ClickHouse, a year before the main migration (2023, 100 h on spec).
A separate task: find applicants appearing in the registries for the first time so managers can call them. The goal wasn’t analytical: a database for cold calls. We rejected the naive route immediately: running a full-database OGRN search on every new document would have paralyzed both analytics and the CRM for hours, because documents arrive almost continuously. Instead we built the very scheme that would become the main one a year later. The whole database flows incrementally to a parallel ClickHouse server, a query groups documents by OGRN and looks at the first-appearance date: if there are zero older documents, the OGRN counts as a new entrant. For reporting we added a BI layer (Rocket BI). In effect this was a live prototype of the two-server architecture: by the time of the main spec we already knew the replicator + ClickHouse pairing worked on this data. The module shipped ahead of schedule, closed in September 2023.

6. Data integrity as part of the architecture.
Fast search is useless if the tables hold garbage. Back in January 2022 we worked through the history of “broken dates”: around 26,000 records from 2018 stored corrupted dates, the day turned into a year by an old algorithm that once copied dates into the database incorrectly. We moved storage to the native “date” type, no copy algorithms. Values that didn’t carry over were restored by hand. Old data from 2017–2018 (around 170,000 records) went to the archive, and the table got more compact and faster along the way. Later, in 2024, we found the source of duplicates in the new-entrants database: duplicates appeared when a document’s country couldn’t be determined. The forward fix: no country means treat it as Russia. We found 20,000+ historical duplicates and gave the client an honest call: roughly 16 hours to process them, or leave them as is, knowing their nature. The client chose to leave them, since no new duplicates arise. The root cause (a missing address) he kept under a separate spec.

Results

What came of it: the primary MySQL serves parser writes and transactions, the ClickHouse replica takes heavy search and analytics, and an incremental replicator runs between them around the clock. Search across the four most massive tables sped up from minutes to 3–4 seconds. Visibility of the registry of accredited bodies is split across the client’s companies at the application level. The new-entrants database lives in its own contour and doesn’t interfere with the main work. The architecture holds the required SLA with the stated growth headroom.

We felt the results both at once and two years later. Login statistics for April–June 2026: 167 accounts in the system, 102 users active over 2 months, 66–79 people working weekly, and that figure doesn’t dip across any of the 9 weeks. A core of around 55 staff work in the system regularly, the most active logging in 10–12 times a day. The architecture, spec’d for 2–3× load growth, holds this regime without response degradation.

Process and timeline

Team

• Anton Hersun, Xaver Pro, project manager: performance diagnostics, choosing the two-server scheme, formalizing the SLA, specs and sign-off
• Analytics-panel developer: the module linking the platform to ClickHouse, the configurator, work on the replicator. He has run the DB and registries track from day one to today: connecting the reference registries in 2022, the ClickHouse prototype in 2023, and moving analytics to ClickHouse in 2024 were all led by one person. He knows every original schema and the query history.
• Infrastructure team under Anton’s lead: servers, replicator, monitoring, and recovery from hosting outages.

Screenshots and materials

To be added in a separate pass: the architecture diagram “parser → MySQL → replicator → ClickHouse → widget,” the query-statistics panel.

If your MySQL dies under a single report while a parser writes into the same place, send us the EXPLAIN of the slowest query and the schema. We’ll tell you what to split across two servers and what a single operator and index will cure. The review costs nothing.

Send the query and schema →