IT Service Provider Turns to PostgreSQL for Scalability

PostgreSQL

A S Kumaresan, an Enterprise DB contest winner, tells LINUX For You why PostgreSQL was an obvious choice for an ICT provider.

When a client entrusts its database to an IT service provider, it expects a smooth flow of data at any time, even when there is high traffic. CSS Corp trusts PostgreSQL with case-tracking and handling transactions easily, even during peak hours.

CSS Corp is an IT service provider that offers an array of solutions — from application development, testing and optimisation, to enterprise-level cloud enablement and round-the-clock technical support services. One of its main services is storing its clients’ databases and tracking the data when the clients require the information. The organisation relies on open source solutions like PostgreSQL and PHP for case-tracking tools, to ensure this smooth flow of data.

An unmanageable volume of data

CSS Corp previously used MySQL 3.16 database software to manage its clients’ databases. It found that it could not handle a large number of users simultaneously during peak hours. “For example, when a modem manufacturer receives complaints about its service on email and voice calls, it stores any interaction with the complainants in its database for future reference,” Kumaresan says.

“About three thousand new cases are entered or updated every day, and the number of concurrent users (agents) sometimes exceeds hundred. The agents access these complaints, assign reference IDs to them, and address the issues. They have to pull information from our servers, and then deal with the issues. However, during peak hours, we had received complaints because when one hundred agents try to access the database and retrieve information at the same time, they have to wait a long while in case of MySQL.”

How it was rectified

CSS Corp decided to switch to PostgreSQL to solve this issue. PostgreSQL features a complete and compliant implementation of the SQL standard, along with several optional extensions. The solution was built strictly following the ACID (atomicity, consistency, isolation and durability) model, with an emphasis on data integrity and SQL standard compliance, speed notwithstanding. Therefore, it underwent optimisations and speedups, along with the addition of new features to its already large set of features. A multi-version concurrency control (MVCC) method has been used in the architecture, to provide concurrent access to the database, and in programming languages, to implement transactional memory. The MVCC concurrency model guarantees compliance with ACID requirements and data integrity.

A reliable solution

CSS Corp seems to be quite satisfied with the solution. “We found that the flow of data is much smoother; it even handles the load much better. PostgreSQL is relatively slow at low concurrency levels, but it scales well with increasing load levels, while providing enough isolation between concurrent accesses to avoid slowdowns at high write/read ratios. It is a much more robust and faster solution than MySQL. We are answerable to our clients, and we decided to opt for PostgreSQL since it has a majority of enterprise-level features to run any mission/business-critical application efficiently,” Kumaresan feels.

He also observed that reports of data loss due to a PostgreSQL malfunction are very rare. Implementing PostgreSQL databases did not cost the company much, as it used an existing Linux server, with a high RAM and hard disk capacity.

Kumaresan’s team initially faced some issues regarding cross-site scripting and SQL injections, but this was easily sorted out by coding using PHP. An in-house team of two people, including Kumaresan, provides support for any problem related to the databases.

The tryst with open source

When Kumaresan joined CSS Corp, the company was already using PostgreSQL.”The organisation uses PostgreSQL for many projects related to the clients’ case creation and tracking, customer satisfaction surveys, creating case reports, managing the knowledge base of the portals meant for its agents, and generating graphical reports based on customer inputs,” Kumaresan reveals.

However, Kumaresan himself had his first stint with FOSS only in 2007, when he started work using PHP and MySQL database. But he was constantly on the lookout for a robust database solution and is now convinced that PostgreSQL is among the best databases that open source has to offer.

All published articles are released under Creative Commons Attribution-NonCommercial 3.0 Unported License, unless otherwise noted.
Open Source For You is powered by WordPress, which gladly sits on top of a CentOS-based LEMP stack.

Creative Commons License.