Why we moved from Postgres to ClickHouse, and kept both

Caplena's reports were getting slower, and a bigger database wasn't the answer. 

Databases are not interchangeable. The one that reliably saves a single survey response is not the one that can summarise millions of them, and for years we had been asking PostgreSQL to do both. 

We moved our customer feedback from PostgreSQL to ClickHouse in a single four-hour maintenance window. Reports for projects containing hundreds of thousands to millions of rows that used to time out, now load in mere seconds, and freshly uploaded data appears instantly.

But first we had to admit that PostgreSQL had been doing a job we should never have given it, and doing it remarkably well for far longer than we had any right to expect.

Why our reports got slower as customers grew 

Caplena accelerates the discovery of insights in customer feedback: analysis that used to mean weeks of reading through answers by hand takes minutes. 

The problem was that PostgreSQL (our database) could not keep up with the amount of data our customers were importing. The tempting fix is to buy a bigger database server, which would have bought us maybe a few months. What we did instead was move the heaviest part of our data into a different kind of database.

At Caplena we are big fans of AI and believe it can be an extremely useful tool, if applied to the right problems. A hammer excels at hitting nails but makes for an extremely poor nail clipper. The same goes for databases.

What PostgreSQL is built for, and what it isn't 

When Caplena started out, PostgreSQL was chosen as the data store. PostgreSQL is an open-source relational database system that has earned a strong reputation for its proven architecture, reliability, and data integrity, turning it into an industry standard. It is an excellent tool, but we were asking it to do something it was never built for.

PostgreSQL is built for what engineers call OLTP: online transaction processing. In plain terms that means a large number of small, precise operations, such as creating an account, saving a setting, fetching a single survey response or updating one row. It stores each record as a whole row, guarantees that related changes succeed or fail together, and is very good at keeping that kind of work correct and fast. What it is not designed for is repeatedly scanning millions of rows to compute summaries, like average sentiment by topic, volume over time, or a filter applied across an entire dataset. Those questions force the database to read far more than it needs, and as the tables grow, response times grow with them.

Scanning and aggregating millions of rows is exactly what Caplena does for its customers. Customers import tables of feedback containing comments, ratings and dates, and our models add the topics and sentiments they find in each response. The product then turns all of that into the reports and insights people actually work with. The metadata around a project, such as who owns it, which columns it has and who may see it, is a natural fit for PostgreSQL and still lives there today. The uploaded feedback itself was the part that had outgrown it.

What OLAP databases do differently, and why columnar storage is faster

OLAP, or online analytical processing, is the alternative.OLAP databases store data by column rather than by row, so a query that only needs dates and sentiment scores can skip everything else on disk. Storing a column together has a second benefit, because similar values end up next to each other, which compresses much better than whole mixed rows do. The trade-off is that OLAP databases are built to scan and aggregate large volumes at once, rather than to update individual records thousands of times a second. ClickHouse has become one of the most widely adopted databases in this category in recent years: open source, columnar, and fast enough that analytical queries which would stall a transactional database return in a fraction of the time.

Why we replicated with CDC before migrating

Caplena did not move the feedback data to ClickHouse in one step. The intermediate step was to keep PostgreSQL as the authoritative copy and stream changes into ClickHouse automatically. ClickHouse Cloud offers this as ClickPipes, a change data capture (CDC) pipeline that replicates every insert, update and delete in PostgreSQL into ClickHouse in batches. Reports could then be served from ClickHouse while the application carried on writing to PostgreSQL exactly as before. That let us prove the performance gain without immediately rewriting how data enters the system.

But CDC is replication, not a live connection. In this new setup, freshly uploaded data took around 30 seconds to show up in ClickHouse. Thirty seconds is a short delay in infrastructure terms and a long one in product terms, because a customer who has just uploaded new data expects to see it in their reports right away. That 30-second lag, together with the cost and the maintenance of running a replication pipeline next to the database, is why we decided to make ClickHouse the actual home of the feedback data rather than a copy of it.

How we moved the write path without breaking reports

Moving the write path is the risky part of a migration like this. If done incorrectly, both historical as well as future data could be lost indefinitely. Worse still, it would have been lost silently, without any loud error message to announce it. if ClickHouse also does not behave like PostgreSQL. It prefers appending new versions of a record over editing it in place, and a delete is recorded as a marker rather than erased on the spot. Append-only writes are the price of the columnar layout, since changing a single value in place would mean rewriting a large, sorted block of data. Every piece of code that saved a response, an individual answer or a topic assigned by our models therefore had to be rewritten. That works for us because feedback arrives in large batches and is rarely edited afterwards, which is exactly the write pattern ClickHouse is built for.

The migration was done in three stages. and the work leading up to it took seven months of planning, preparation and refactoring to ensure a seamless cutover.

First we untangled the data. PostgreSQL had enforced links between the three tables that were moving and the rest of the schema, and those links cannot span across databases. Removing them was its own software update, shipped weeks ahead of anything else. 

Second, we changed the application to write directly to ClickHouse. Reporting already read from ClickHouse, so that half of the product needed no changes at all.

Third came the migration itself. The cutover happened during a planned maintenance window announced to customers in advance. We took the API down and let the background job queues drain, so that nothing was left half-written. Then we waited for the CDC pipeline to catch up one final time and hand over the remaining changes before we switched it off. With both databases in agreement, we restructured the tables in ClickHouse, then deployed the new application code, which also cleaned up PostgreSQL by removing the tables that had moved out. After that we scaled everything back up. The whole operation was completed in under four hours. 

What we gave up: transactions and a second database to run 

Running two databases is more work than running one; it results in a more complicated tech stack and infrastructure. We took that cost on deliberately, and it does not go away. The bigger trade-off was transactions. 

PostgreSQL is ACID compliant, which means a change either lands completely or not at all, even when it touches several tables, and a reader never sees a half-finished write. ClickHouse makes no such promise. That opens the door to inconsistencies we simply could not have before: a write that lands partially, or two requests arriving at once and overwriting each other. We designed the system such as to make those unlikely rather than impossible, and that is an honest description of where we are.

Losing transactions is acceptable here because of what moved and what did not. The metadata that has to be exactly right, meaning who owns a project, who may see it and credit transactions, never left PostgreSQL. 

What moved to ClickHouse is uploaded feedback, which arrives in large batches and is almost never edited afterwards. Transactional guarantees matter far less for data nobody rewrites. They matter enormously for the project metadata, however, which is exactly why it stayed where it was.

What changed: analysis of millions of rows at half the cost

The difference was immediate. Imported feedback data now appears in Caplena reports instantaneously as there is no longer a pipeline in between. Where analytical queries performed with PostgreSQL would take tens to hundreds of seconds for projects with millions of rows, ClickHouse routinely chews through these in a second or two. In addition, PostgreSQL no longer holds the data it was never meant to aggregate, so it runs on a much smaller instance.

• It was scaled down from 8 vCPU / 52 GiB to 6 vCPU / 39 GiB.

• The disk storage shrunk from 1.7 TB to 512 GB.

• This reduced the monthly bill from €2,900 to just €1,200 on average. With a monthly ClickHouse bill of a mere €300, the migration cut our overall database expenses almost in half.

Caplena now runs two databases instead of one. PostgreSQL is the hammer, Clickhouse is the nail clipper and each gets to perform the task it was designed for. 

Our platform is a toolkit, not a hammer. Coding open responses, tracking sentiment over time, comparing segments: each part is built for one job and fast at it. Curious to see how our platform helps leading brands and agencies turn feedback data into insights, fast?