ACID and BASE: SQL and NoSQL

When do you use a SQL database and when do you use a cache or a NoSQL data store? When does it make sense to use them together?

SQL is an acronym for Structured Query Language, which was standardized early in the history of relational databases. MySQL is the most widely-used open-source relational database. NoSQL stands for Not only SQL, and is an emerging alternative to SQL databases. NoSQL options include MongoDB, CouchBase, VoltDB, and Schooner's Membrain. How does a relational database like MySQL differ from NoSQL, and what are the main use cases for each?

SchoonerSQL is 100% compatible with MySQL and its standard InnoDB storage engine, and is full ACID-compliant relational database, like Oracle 11, IBM DB2, Microsoft SQL Server, and so on. This means that database transactions are processed in a way that guarantees data integrity. ACID is an acronym for:

• Atomic: Database modifications must follow an all-or-nothing rule and either be completed or not completed, but never leave data in some in-between state.
• Consistent: Any database modification must keep the database in a consistent state, or, more precisely, change the database from one consistent state to another consistent state.
• Isolated: Other operations cannot access or see data that has been modified during a transaction that has not yet completed. In other words, concurrent transactions must not interfere with one another.
• Durable: The database guarantees that once the user is told that the transaction has succeeded, the transaction will not be lost even in the case of system failure.

The Tradeoffs of ACID Compliance

The ACID mechanisms impose some performance overhead. That's obviously worthwhile when you need absolute data integrity, as when you're building an electronic funds transfer system. But some applications don't have stringent data integrity requirements. Losing a web cookie, for example, usually has no penalty other than a slight delay in web site access. For applications that don't need full data integity, why pay for the ACID overhead? This is an especially good question where you need extremely high performance. There are also important issues of elasticity and scalability: how many simultaneous writes and reads can be handled by a single database, and over how many servers must (or can) the workload be spread to achieve the desired performance goals?

The Tradeoffs of NoSQL

NoSQL data stores sacrifice ACID-compliance in a quest for high performance. NoSQL data stores range from simple key-value stores to document / object stores to extensible record stores. NoSQL data stores are genuinely useful for many applications. All NoSQL offerings relax one or more of the ACID properties, and usually have simpler APIs that don't offer the full power of SQL queries.

The NoSQL alternative to ACID is sometimes described as BASE, an acronym for:

• Basically Available,
• Soft state,
• Eventual consistency,

Schooner Solutions for ACID and BASE

SchoonerSQL™ is a full ACID-compliant SQL database, 100% compatible with the widely-used MySQL open-source database and its standard InnoDB storage engine. SchoonerSQL is optimized for 5 9s availability, high performance, and the lowest total cost of ownership of any SQL database solution.

Caching, or fast temporary storage of data retrieved from some underlying data source, is a very common technique for speeding up pure SQL database applications. This is especially true when the database resides on hard drives. Memcached, an open-source caching protocol, is very widely utilized because of its low latency and high scalability.

Because the memcached API is so popular Schooner believes it is the right foundation for more powerful NoSQL data stores. Implement memcached to exploit a lot of fast flash memory and add persistence and enterprise-grade high-availability / disaster-recovery. Suddenly, you have a superb enterprise-ready NoSQL solution for production use on demanding workloads. That's what Membrain is. Membrain can be used both as a pure cache or NoSQL replacement on jobs where a SQL database is not needed, or as a performance-enhancing complement to your SQL database.

For Further Reading

For a deeper dive into the differences between SQL and NoSQL and ACID versus BASE, check out these articles:

• BASE: An ACID Alternative, a 2008 ACM article by Dan Pritchett
• "High Performance Scalable Data Stores.", by database luminary and Schooner Advisory Board member, Dr. Rick Cattell
• Errors in Database Systems, Eventual Consistency, and the CAP Theorem, a CACM blog post by database pioneer Michael Stonebraker, which nicely discusses some of the tradeoffs between ACID's guaranteed permanent consistency and BASE's eventual consistency model.