SQL or NoSQL? NewSQL !!
Fifteen years ago, one database was enough to hold all the application’s data. Over the years, applications started to collect more and more data, from people, devices, events, etc. The single instance could not hold this amount of data and the throughput of usage, it needed to be scaled.
There are two ways to scale a system:
- Vertical scaling (scale-up) - adding more resources to the computer such as memory, CPUs and network adapters.
- Horizontal scaling (scale-out) - adding more servers with the same or less computing power.
Because the traditional databases (RDBMS) such as MySQL, Oracle or SQL Server could not scale-out, companies needed to scale them up, by using monster computers that cost a lot of money.
This was the main reason for using NoSQL databases such as MongoDB, Cassandra and others. They offered a scale-out solution on commodity hardware which lowered the price.
But those databases needed to make compromises in order to support scale-out. To understand those compromises I will describe two concepts, the CAP theorem and ACID.
According to the “CAP theorem”, any database can only support two of the three:
- Consistency - every read would get you the most recent write.
- Availability - every request receives a response, without guarantee that it contains the most recent version of the information
- Partition tolerance - the system continues to operate despite arbitrary partitioning due to network failures.
For example, MongoDB is "CP" (Consistency + Partition tolerance), Cassndra is "AP", and RDBMS are "CA".
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties of database transactions:
- Atomicity - each transaction be "all or nothing".
- Consistency - any transaction will bring the database from one valid state to another.
- Isolation - ensures that the concurrent execution of transactions are executed serially, one after the other.
- Durability - ensures that once a transaction has been committed, it will remain in the database.
The ACID properties allow the developer to be sure of the correctness of the data, in any transaction or calculation, at any given time. There are no surprises, missing data, or problems of data integrity.
All RDBMS support ACID and support the “C” in the CAP theorem. That is why they are so reliable. In the other hand, NoSQL databases do not support ACID. Some NoSQL such as MongoDB support different model of consistency, usually eventually consistency. It means that consistency is guaranteed but not in any given time. It is fine for many usages but not for other (think about a finance transaction or even the order of comments in a blog post).
To overcome it companies had the following options:
- Use two (or more) databases, RDBMS for the data that needed high data integrity, and the other for the rest of the data such as streaming of events, data for analytics etc.
- Use NoSQL database and implement the ACID layer on top of it in the application server.
Both of the solutions may cause problems. Synchronize databases is not an easy job and also to maintain them. Develop a transactional layer on for a software developer that is not skilled in this can cause bugs in a very sensitive area of the application, and also hurt the performance.
This is the background for the invention of NewSQL databases.
The NewSQL databases come to solve it. The term was coined by the 451 Group, in their now famous report, “NoSQL, NewSQL and Beyond”. The NewSQL databases support ACID, and they are distributed, can be scale-out, be able to handle large volume of data with great performances.
There are 3 types of NewSQL:
- New architectures - databases that were designed to operate in a distributed cluster (Google Spanner, Clustrix, VoltDB, MemSQL).
- SQL engines - highly optimized storage engines for SQL (MySQL Cluster, Infobright, TokuDB)
- Transparent sharding - provide a sharding middleware layer to automatically split databases across multiple nodes (ScaleBase).
I will describe two different approaches, VoltDB that is build with new architecture and scaling-out Postgres.
VoltDB is an in-memory database, it is an ACID-compliant RDBMS which uses a shared nothing architecture. One of VoltDB designers is Michael Stonebraker, and many concepts of his academic researches such as shared nothing architecture were implemented in this database. In his article, “OLTP Through the Looking Glass, and What We Found There”, Stonebraker and his colleges explain why concepts about database architecture that were right at the 70’s, no longer valid now, especially because of the in-memory capability. He claims that new databases should use new modern architecture that fits today’s challenges. VoltDB is built with these concepts.
VoltDB is an in-memory database what makes it work faster than on-disk databases. How can it support ACID, or be be more specific, how can it support durabilty (After the transaction is committed, it is ensured that the data will not be lost).
There are few ways that in-memory database can support durability:
- Non-Volatile DIMM - RAM that its memory is not volatile.
- Non-volatile random access memory (NVRAM) - RAM that is backed up with a battery.
- Transaction logging - all the transactions are being logged and if there is a crash it can be recovered.
- High availability - the data is replicated between the nodes, should be combined with other methods to insure safe durability.
- Snapshots - every once in awhile a snapshot of the RAM is persisted to the disk.
VoltDB uses snapshot and transaction logging to ensure durability.
Shared nothing architecture (SN)
VoltDB uses a shared-nothing architecture. shared-nothing architecture (SN) is a distributed computing architecture in which each node is independent and self-sufficient, Instead of central entity that in control. That way VoltDB achieve database parallelism. There is no single point of failure and there is no bottle necks. The data is handled independently by each CPU core. It allows VoltDB scale by increasing cores.
Partitioning and scale out
VoltDB manages the partitions itself. VoltDB partitioning both the data and the transactions that access that data. So multiple transactions can be processed in parallel. Throughput is easily scaled by increasing either the number of partitions per node or the number of nodes in the cluster.
If a node needs a data from other partition it can do it but it will hurt the performance.
Because VoltDB doesn’t use the disk in its transactions, it doesn’t need to run multi-threaded (one of the main reasons that RDBMS databases run multi-threading is because it writes the data to the disk, and multi-threading is efficient way to handle the disk wait. Every core runs single thread which eliminating the need for locking and latching.
Stored procedure interface for transactions
VoltDB uses stored procedures for transactions, stored procedures are atomic: they either succeed or fail and rollback as a whole. It boosts the performance because in other RDBMS a lot of time is wasted on network delays because the goes back and forth from the database to the application server several times. Another advantage of stored procedures is that they are pre-compiled. VoltDB supports writing stored procedures in SQL and also in Java for more complex procedures.
A different approach from building new database with new architecture, is using an existing database with a maturity and proven stability, and adding to it the scale-out feature. This is done in many RDBMS, I will focus on Postgres.
There are many approaches available to scale PostgreSQL. There is no built in solution yet embedded in Postgres yet, but achieved by using postgres features by third parties tools.
Greenplum, Postgres-XL and more are some of the tools, I will describe how Citusdata does it.
Citus is a distributed database that combines the low-latency requests that power real-time applications with high throughput, interactive analytics on billions of events.
Citus does this by extending PostgreSQL to distribute your workload across multiple hosts, leveraging the memory, storage, and processing power of multiple machines.
Citus distributes the data across a cluster of commodity servers. Incoming SQL queries are then parallel processed across these servers.
One of the PostgreSQL instances in the cluster is the Citus master. The master stores metadata about the shards, and the worker PostgreSQL instances (Citus workers). All interaction with the database is done with the master through standard PostgreSQL API. All the data is distributed across the workers. Each table is logical “sharded”, it means that it is spread between shards according to the selected column. It enables the scale-out feature. Each shard is replicated on at least two of the workers, as a result, the loss of a single machine does not impact data availability.
The master receives the query, distribute it to the relevant workers according to the metadata, and then merges their results, and returns to the user. If a worker fails mid-query, Citus completes the query by re-routing the failed portions of the query to other workers which have a copy of the shard.
NewSQL databases are new type of databases that allow both data integrity and scalability in a low cost. They can either be build with new architecture that stored the data on memory, or expand the capabilities on existing databases.
What lies ahead? I believe that the traditional RDBMS will adopt the concepts of NewSQL, by implementing it them self or by acquiring technologies and integrate them into their products. In the future all databases will support scaling-out and distribution. This will reduce the market share of the popular NoSQL databases, and the dominance of the major players will remain as it is today. Although the decreasing price of memory and CPU’s, can cause single machine to be strong enough to handle big data, the train has already left the station and the competition between the companies will drive them to support distribution and scale-out capabilities.
- “OLTP Through the Looking Glass, and What We Found There” - Harizopoulos, Abadi, Madden, Stonebraker -- SIGMOD 2008