For decades, Constant Contact has been a household name among small and mid-sized businesses looking to grow through email and digital marketing. Behind that steady, familiar presence is a quietly innovative company that’s kept pushing forward, rolling out new tools and channels to meet the changing needs of today’s small businesses.
That evolution put new demands on Constant Contact’s data platform. They needed real-time event processing and interactive dashboards, without constant maintenance headaches.
At a March 2025 ClickHouse meetup in Boston, software engineer Abhijeet Kushe shared how Constant Contact rebuilt its real-time analytics stack with ClickHouse, why it was such an improvement over their previous stack, and what the team learned along the way.
The highs and lows of Apache Pinot #
In the fall of 2021, Constant Contact launched Automated Path Builder (APB), which helps customers engage contacts in real time with marketing workflows triggered by things like email opens, clicks, and order checkouts. The next summer, the company added SMS support, both for one-off sends and as part of APB journeys. With these new features came more advanced reporting requirements and a need for real-time, OLAP-style queries.
A proof of concept with Apache Pinot showed promise. It supported upserts, integrated cleanly with Amazon Kinesis, and allowed for quick data fixes during rollout. Things ran smoothly at first, but by 2023, as data volumes increased, maintenance became more frequent.
Event volume over time: dips during maintenance, steady growth after migrating to ClickHouse.
One of the biggest problems was how Pinot handled upserts. At the time, it didn’t support compaction, so the system created a new segment every day. By the end of 2024, those segments had grown to a combined total of around 180 GB. “This forced us to add memory as the data grew,” Abhijeet says. Repartitioning wasn’t an option, since segments were tightly coupled to Kinesis shards. And without any tooling for bulk imports, restoring data could take weeks of non-stop streaming.
It became clear the company needed a more reliable and scalable partner to help realize its objectives. “Time was running out,” Abhijeet says.
Transitioning from Apache Pinot to ClickHouse #
In 2024, the team began evaluating other OLAP databases. “We looked at ClickHouse, and it fit our requirements,” Abhijeet says. “With the Altinity ClickHouse operator and just a few lines of code, we were able to set it up within 15 or 20 minutes.”
To migrate their historical data, the team used AWS Athena CTAS queries to generate compressed JSON files in S3, then loaded the data directly into ClickHouse using the s3 table function. “ClickHouse has a great bulk insert feature,” Abhijeet says. “We were able to load all our data in three hours, as opposed to the two or three weeks to load a new Pinot table.”
For real-time updates, they used ClickHouse’s ReplacingMergeTree engine, which offered similar behavior to Pinot’s upserts. The data types also mapped cleanly: ClickHouse supported everything they needed, and added even more flexibility with things like UUIDs and LowCardinality strings.
They also used clickhouse-benchmark, a simple but “extremely powerful” tool, to test queries and guide instance sizing and system design. Since ClickHouse’s Java client mapped closely to their existing Pinot client, Abhijeet says, “It was very easy for me to migrate.”
In three months, Constant Contact was up and running on ClickHouse, with immediate results—including sub-second response times on a single node and one thread. Pinot, by comparison, needed four servers and three brokers for the same throughput, and even then, some queries took 6-7 seconds. “The difference was massive,” Abhijeet says.
A closer look at ClickHouse vs. Pinot #
Abhijeet didn’t sit back and simply admire the results. A self-described “curious engineer,” he wanted to understand why ClickHouse was so much faster than Pinot. So he read some whitepapers and started unpacking the architectural differences between the two systems.
One of the biggest differences came down to how each system accesses data on disk. Pinot uses memory-mapped (MMAP) files, which map segments directly into memory. While this approach can offer fast access under the right conditions, it comes with tradeoffs. Pinot’s segments often exceeded available RAM, leading to performance issues like TLB shootdowns and memory contention.
ClickHouse takes a different approach, using preads (position-based reads) that “allow us to read the file from any position,” Abhijeet says. “This outperformed MMAP in all cases.” It also benefits from CPU-level optimizations like prefetching and caching. “This allows ClickHouse to be way faster, whether it’s sequential or random reads,” he says.
Storage format was another big difference. ClickHouse is built on a log-structured merge tree (LSM) architecture designed for high-ingest workloads. It handles SSTable merges natively, making upserts and compaction more predictable and reliable. “Pinot, on the other hand, needed a custom merging or compaction format,” Abhijeet says. The 180 GB of Pinot segments they’d accumulated by the end of 2024 was compacted down to a single 35 GB part in ClickHouse.
The two systems also differ in how they’re built. Pinot, written in Java, needed 16 GB of heap memory per process. ClickHouse, written in C++, runs the same workloads with just 2 GB.
Finally, there was the matter of infrastructure. “Pinot is very component-heavy,” Abhijeet explains. “There are brokers, servers, controllers, ZooKeeper.” ClickHouse is much lighter, requiring just a single server and ClickHouse Keeper to operate.
All of this added up to a huge difference in cost and performance. Constant Contact was paying nearly 10x the infrastructure cost per month for Pinot. “And with ClickHouse, performance is 10 times faster, too,” Abhijeet says.
Lessons learned along the way #
Fifteen years ago, Abhijeet’s database professor gave him a piece of advice that stuck: “Know thy data.” It’s a simple mantra, but it paid off during Constant Contact’s migration from Pinot to ClickHouse.
Seemingly small design choices, like picking the right Int type or using enums instead of strings, can have a big impact. “Literally, 25% of compression comes for free with strong schema design,” Abhijeet says. “If your data design is perfect, then compression will be good.” He also emphasized the importance of choosing the right primary key up front—a lesson he learned the hard way, after having to rebuild a table from scratch.
Another big takeaway: automate your backups, and test them. Previously, Constant Contact had to manually write a script to back up ZooKeeper every hour. But since migrating to ClickHouse, that process runs much smoother, with daily backups and routine restore tests to avoid surprises.
A faster, more scalable database #
With ClickHouse, Constant Contact has a database that delivers blazing-fast performance, a flexible architecture, and plenty of room to grow. The team can spend less time maintaining, and more time building the features that power the company’s best-in-class digital marketing tools.
As expectations around speed and personalization keep rising, Constant Contact has the technical foundation it needs to continue helping SMBs punch above their weight in an ultra-competitive, digital-first world.
To learn more about ClickHouse and see how it can improve the speed and scalability of your team’s data operations, try ClickHouse Cloud free for 30 days.
