ClickHouse — the real-time analytics engine in the platform stack
The 4shopy stack includes ClickHouse — a columnar database built specifically for high-volume analytics. It is what we are building the platform’s traffic and sales statistics module on (in development): the data stays yours, reports appear in seconds, and your site doesn’t lose a single millisecond because of them — that is how the architecture is designed.
What ClickHouse is and why we use it
ClickHouse is a columnar OLAP (online analytics) database, created at Yandex for Yandex.Metrica — one of the largest web-analytics systems in the world — and open-sourced in 2016 under the Apache 2.0 licence. It is built for a single thing, which it does exceptionally well: fast aggregations over huge volumes of data. In the 4shopy architecture, ClickHouse is the engine chosen for the statistics layer: every visit, click and order becomes an event that can be analysed immediately.
The secret is columnar storage: instead of reading every full “row”, ClickHouse reads only the columns the report needs and processes them in large, vectorised blocks. That is why a question like “how many visitors did I have yesterday, per page, per source” gets answered in under a second even across millions of events — something a classic row-oriented database struggles with.
It is not a niche technology: Cloudflare uses it to analyse HTTP traffic at global scale, and Uber, eBay and Spotify use it for analytics and log processing at enormous volumes. On 4shopy, the same engine is part of the platform stack — without you hiring a data-engineering team.
Real-time statistics, without slowing your site down — how the module is designed
The platform’s statistics module is in development, and its architecture is simple: traffic events are collected asynchronously and written to ClickHouse, completely separate from the servers that deliver your pages. Your visitors never wait for analytics: the page loads just as fast no matter how many reports are running at that moment. There is no heavy third-party tracking script hurting your performance score — collection is minimal and first-party.
The reports in panel.4shopy.net will be computed directly in ClickHouse: page traffic, visitor sources, conversions, sales per product. Frequent aggregations are pre-computed via materialized views on every write, so the dashboard will open instantly — it does not kick off a heavy calculation every time you look at it.
- Live traffic: visits, pages, sources and devices — queryable seconds after collection
- Sales per product, orders and conversions for your store, aggregated in under a second
- Months and years of history at low cost — columnar compression shrinks storage several times versus raw data
- Zero impact on site speed: collection is asynchronous, reporting runs on a separate system
PostgreSQL + ClickHouse: why we use both
The platform stack combines two databases because they do fundamentally different things. PostgreSQL is the transactional ledger (OLTP): orders, payments, stock, user accounts — operations that must be perfectly correct one by one, with transactional guarantees. ClickHouse is the analysis room (OLAP): millions of events read at once to answer your questions about traffic and sales.
If heavy reports ran on the same database that processes orders, every report would compete with the customers paying at that very moment. The OLTP/OLAP split removes that conflict: a customer’s order never waits for your report, and your report never waits for someone else’s orders. Each engine does exactly what it was built to do.
- PostgreSQL: orders, Stripe payments, stock, customers — transactional correctness
- ClickHouse: traffic events, reports, statistics — aggregation at volume
- The design replicates relevant transactional data into ClickHouse as events, so sales reports never touch the database serving your store
First-party, privacy-friendly analytics — instead of Google Analytics
The statistics module’s principle: your site’s data stays on the platform’s infrastructure. It is not sent to Google or other advertising networks, not sold, and not merged with external user profiles. It is first-party analytics in the literal sense: the data about your visitors is yours.
Because the approach uses no third-party tracking cookies, GDPR compliance becomes much simpler. Context matters: in 2022, data-protection authorities in Austria, France and Italy declared the use of Google Analytics non-compliant with the GDPR over data transfers to the US. With first-party analytics hosted in Europe, that entire category of risk is gone from the start.
There is also a direct practical benefit: reports computed in ClickHouse are not sampled and do not show up hours late, as standard reports in free analytics tools often do. Complete numbers, in seconds.
Store and marketplace reports, instantly — what the module will deliver
For an online store, ClickHouse will mean immediate answers to the questions that matter: which products sell, which sources bring the customers who actually buy, where carts get abandoned. For a multi-vendor marketplace, the same aggregations will run across all orders: volume per vendor, commissions, top products per category — no manual exports and no spreadsheets.
And at scale, things stay cheap and fast. Columnar compression shrinks data several times versus the raw format, so you keep years of history at a low storage cost. When traffic grows from thousands to millions of events, aggregations stay in sub-second territory — the engine was designed for billions of rows from the start, not retrofitted later.
Pros and cons
Pros
- Aggregations over millions of events in under a second — reports that open instantly.
- Zero impact on site speed: analytics runs on a system separate from the one serving pages.
- Your traffic data stays yours, on the platform’s infrastructure — no transfer to Google or ad networks.
- No third-party tracking cookies — simpler GDPR compliance.
- Strong columnar compression — years of history at low storage cost.
- Open-source (Apache 2.0), no per-volume licences — cost doesn’t explode as traffic grows.
Limitations
- Not a transactional database — updating or deleting individual rows is a heavy operation; that is why orders and payments live in PostgreSQL.
- Optimised for large batched writes; many small frequent inserts require batching — the platform handles this, but it is a real engine limitation.
- Complex JOINs are less flexible than in PostgreSQL — the event schema must be designed for analytics from the start.
- Looking up a single row (point lookup) is slower than in an indexed OLTP database — it does not replace the store’s main database.
Quick verdict
ClickHouse is the right choice for analytics at volume: real-time traffic and sales statistics, with your own data, without touching site speed. For orders, payments and stock PostgreSQL remains — which is why the 4shopy stack combines both, each at what it does best. The statistics module built on ClickHouse is in development.
Specifications table
| Capability | Limit / threshold | Typical use | |
|---|---|---|---|
| Analytical aggregations | Under a second over millions of events (columnar + vectorised) | Slow at single-row lookups — not for OLTP point reads | Traffic and sales reports (statistics module, in development) |
| Event ingestion | High volumes, written in batches | Small frequent inserts need batching / async insert | Collecting pageviews, clicks, orders |
| Storage & compression | Columnar compression (LZ4/ZSTD), several times below raw volume | Highly random data compresses less | Months and years of event history, at low cost |
| Updates & deletes | Asynchronous mutations for occasional corrections | Unsuitable for frequent single-row UPDATEs | GDPR deletions (one user’s data, on request) |
| Materialized views | Aggregates pre-computed on every write | Aggregates must be defined upfront, per report | Instant dashboards: sales per day, product, source |
| Real-time | Events become queryable seconds after they happen | Not a millisecond-level streaming system | Live traffic and conversion statistics |
ClickHouse vs PostgreSQL vs Google Analytics / BigQuery — for analytics
| Criterion | ClickHouse (4shopy) | PostgreSQL (for analytics) | Google Analytics / BigQuery |
|---|---|---|---|
| Aggregation speed at volume | Under a second over millions of rows | Degrades visibly over tens of millions of rows (row storage) | BigQuery fast; GA4 has pre-aggregated standard reports, no free-form queries |
| Data ownership / GDPR | Your data, on the platform’s infrastructure, in Europe | Your data, on your servers | Data at Google; GA transfers to the US ruled GDPR non-compliant by EU authorities in 2022 |
| Cookies & consent | No third-party tracking cookies | Depends on your own implementation | Requires a consent banner for tracking |
| Real-time | Data queryable in seconds | Possible, but heavy reports load the transactional database | GA4 standard reports can lag by hours; BigQuery streaming costs extra |
| Cost at volume | Open-source — cost is just infrastructure, included in the subscription | Grows with indexes, vacuum and hardware at high volumes | BigQuery charges per volume queried; GA’s enterprise tier is expensive |
| Impact on site speed | Zero — async collection, reporting on a separate system | Reports can compete with orders on the same database | Third-party script (gtag.js) on the page — extra JavaScript and external requests |
Frequently asked questions
Does ClickHouse slow down my site or store?
No. ClickHouse runs on a system separate from the one serving your pages — that is how the architecture is designed. Traffic events are sent asynchronously, and reports never touch the store’s database. Your site loads just as fast with or without statistics open.
Why not simply use Google Analytics?
Three reasons. The data stays yours, on the platform’s infrastructure, not at Google. You need no third-party tracking cookies, so GDPR compliance is simpler — in 2022, authorities in Austria, France and Italy ruled GA non-compliant with the GDPR. And reports show up in seconds, complete, not sampled or hours late.
What is the difference between ClickHouse and PostgreSQL — why both?
PostgreSQL is the transactional (OLTP) database: it processes orders, payments and stock one by one, with correctness guarantees. ClickHouse is the analytical (OLAP) database: it reads millions of events at once for reports. The split means a heavy report never slows down your customers’ orders.
Is my traffic data safe and GDPR-compliant?
Yes — by design. Statistics are first-party: they live on the platform’s infrastructure, in Europe, and are never sold or shared with advertising networks. The approach uses no third-party tracking cookies, and a user’s data can be deleted on request, per the GDPR.
How “real-time” are the statistics?
An event — a visit, a click, an order — becomes visible in reports a few seconds after it happens. Aggregations over long periods (months, years) still answer in under a second, because ClickHouse was built precisely for this type of query.
Do I need to set anything up to get statistics on my site?
You will not need to. The statistics module is in development and built as part of the platform: once it goes live in the admin panel, collection starts automatically for published sites — no scripts to install and no external accounts.