Freshworks Powers Real-Time Chat Analytics with the Citus Cloud Database

How to deliver historical and real-time chat analytics?

The Freshchat messaging service is unique in that it provides near real-time and historical analytics to enable customers to measure response times and better understand the end user experience. These insights allow companies using Freshchat to determine whether they’re meeting service-level agreements (SLAs), to decide whether they need to increase or reduce staff, and to quantify Freshchat’s impact on the organization.

For example, Freshchat allows its customers to measure and report on the chat response times for specific segments of end users (for example, all people in Italy.) These insights let service managers know if they need more customer service agents in a particular area. The Freshchat analytics can also help companies that use bot features to understand whether the bot is reducing ticket deflection.

Unable to find a database to handle the querying load for chat analytics

When first creating the analytics pipeline for Freshchat, the team tried out a leading analytics tool to ingest events and run queries on them. While the ingestion pipeline was able to take the load, the querying layer was not.

“We recorded events for all of our customers. But initially, we only gave the querying capability and dashboard to two or three customers. We found that for the kind of queries we wanted to ask the underlying system, the previous analytics solution just did not scale,” says Deepak Balasubramanyam, Technical Architect at Freshworks. “Half the time, we’d see that it didn’t work or customers would complain that the queries would return a response that was tantamount to, ‘I’m sorry, you can’t ask this question anymore because you’re either rate limited or the system is undergoing an event that causes performance degradation.’”

Balasubramanyam began looking for a database that would deliver the required performance to run both real-time and historical analytics.

He considered Cassandra or an in-memory database like VoltDB, but they didn’t meet all of the team’s requirements. For example, VoltDB doesn’t have a way to natively query the median of an aggregated group. And Cassandra would have required the team to build a querying layer on top of the database using Storm. While this was feasible, it would have increased deployment time and taken valuable engineering resources away from Freshchat’s core application.

“We needed a database that is designed for rapid development—a cloud database that we could quickly test and deploy and would help us get to market fast,” says Balasubramanyam.

Evaluating Citus was easy—and yielded impressive performance results

The team at Freshworks had already heard of Citus, so when it was time to decide on a new database for their chat analytics, Citus easily made it onto the short list. The first step was evaluate the databases.

With Citus, evaluation is easy because developers can either download the open source version or create a Citus Cloud developer account. “If you just want to use it for a couple of days to see how Citus works out for you, it’s easy to do that. Running a load test in a production ready environment is just as easy as trying the database on your dev box to see if it actually works. Citus has done a good job of removing the friction that can make it difficult to get started,” says Balasubramanyam.

While evaluating Citus, Balasubramanyam simulated database queries at the size and scale he predicted his team would need a year into the future. “I wanted to see how the database would behave if we used it for a year and we had about 2,000 customers using it sending ‘N’ first response times per day. I ended up with a database with about 500 million data points and ran a historical analytics report that queried a year’s worth of simulated data. The response for that query returned within 500 milliseconds,” says Balasubramanyam.

The decision to adopt Citus gave Freshchat the performance they needed, plus Postgres

After evaluating the options, the team selected the multi-tenant database from Citus Data. Because Citus is an extension to Postgres (essentially, software that layers on top of Postgres), Balasubramanyam had confidence that the cloud database would perform as expected. “We’ve used Postgres in production for a while now, and we know how it works, and that’s how Citus works. It’s not like you need to learn rules to understand how the product works, because you already know how Postgres works,” says Balasubramanyam.

Balasubramanyam said that because Citus is built on top of a transactional database like PostgreSQL, writing a pipeline that updates data a few times a day is possible and so is trickle loading small amounts of data. When he tried to trickle load data into Redshift, it locked the table and the only way to fix the problem was to restart the database. "That did not bode well with customers" says Balasubramanyam. "The good news is: Citus doesn’t have those kinds of problems."

And getting started with the Citus distributed database was easy

Just 15 days passed between Balasubramanyam’s decision to replace the existing analytics pipeline and the first event going into Citus. “That says something about the speed at which you can adopt the Citus cloud database. To build a system like this takes time—it can take three to six months in some companies. You don’t typically go from zero to production in two weeks,” says Balasubramanyam.

The Citus database as a service offers plenty of room to grow

Today, Freshchat consumes only 2% of the Citus database cluster’s CPU, giving the chat application plenty of headroom to grow. “It’s been pretty impressive, and it gives me the confidence that Citus has our back, even if Freshchat grows a sizeable amount,” says Balasubramanyam.

To monitor performance of the Citus distributed database, Balasubramanyam keeps an eye on the 95th percentile response time from Citus Cloud for all queries. “Usually for analytics applications, acceptable response times can be a matter of seconds. And this is especially true for long-term reporting that needs to query a huge amount of data. With Citus, it’s amazing. The response time on long-term queries of billions of rows is on the order of milliseconds. Citus is so darn fast!” he says.

Freshchat doesn’t have to think twice about how to scale their database

“Continually improving the customer support experience is important for our clients,” says Balasubramanyam. “But you can’t improve what you don’t measure. That’s what Citus helps us do: It helps us give our customer service clients the metrics they need to understand how their teams are performing.”

With the Citus database as a service, Balasubramanyam is no longer concerned about query response times or database scalability, even with the insane number of queries that are hitting the database at any point in time. “Thanks to the fast response times we get from Citus, plus the room we have to grow in our Citus database cluster, I don’t have to think twice about whether the database will scale or not. I can focus on the next new feature we’re rolling out,” he said.