Databases in VoIP and SMS

Compexity of designing proper database solution for VoIP or SMS service

The importance of data in any online business can not be underestimated. The data is central also to the VoIP and SMS operations. Here we will shortly discuss some of the general principles when designing and implementing database systems for VoIP and SMS operations.

In regular web applications (like this blog for example) the primary goal for database is to quickly serve large number of clients and occasionally make infrequent, large writes. While that can become quite complex task for very large and traffic heavy sites, it is still relatively easy task in terms of the goals, i.e. the goals are clearly defined. In opposite, VoIP or SMS system database goals can be far more complex and sometimes contradict each other.

First, let's list main types of data to take care of:

Key system data which change relatively slowly, grow at low rate. Examples: customer and account data, tariff tables, payments, routes, setting and other supplementary data. The requirements are:

High availability
Fast read access (example - real time time authorization)
relatively fast write access (example- balance updates)
High reliability (e. g. - financial transactions)

Fast growing usage reports: like call detail records, message logs. The requirements are:

Large storage, growth can be millions of records per day, write only, no updates
Fast write access (i. e. real time billing systems)
Low speed read access
Data partitioning or purging possibility

Live system state data: active sessions, registrations, connections and similar. The requirements are:

Very fast read and write access, faster than disk read/write
Can cope with non perfect reliability

Aggregated data for reporting. The requirements are:

Good indexing to ensure fast reads
High reliability

As we can see from above, the data is very diverse, and may require completely different storage and access solutions for each type. While we don't want to recommend any specific database solution or implementation, here are some observations gathered from experience.

Most probably there is no single database engine which can fit all types of data requirements.

For Key system data, the relational database would probably be best choice, with possibility to implement multi-master (cluster) replication. Such a solution can ensure very high degree of availability and reliability. Clustered solutions increase read speeds by directing read requests sequentially to all nodes in the cluster, thus getting higher speed than single instance systems. While multi-master systems theoretically can be slower on writes (because they ned to ensure writes are propagated to all nodes in cluster), there are many techniques today which greatly reduces this associated overhead;

CDR and other logs: This is one of the most difficult tasks to implement. The data growth rates are extremely high, with several millions of records created daily on largest systems. since these are write only operations, we advise to use master-slave systems, with possibility to fail over. It is also good idea to separate this database system from key system data for reasons of manageability: in case of problems in the key system cluster if there is a need to completely restore one or several nodes, it is better not to have the very large data set of CDRs getting in the way.

Live system data can be stored in memory type storage, key-value stores. Most of them can also be replicated for high availability. The offer very fast access speeds, far beyond what the disk-based database systems can offer.

Finally: aggregated data: this data is usually built from the CDR and other logs. They are essential for reporting and for producing financial data (invoices). Therefore they should be capable of indexing, so can be read and queried in various ways.

As you can see above, the database for VoIP or SMS application faces far more difficult challenges that a conventional database system for typical online application. Proper design is key to having highly reliable and capable system.