Choosing the Right Database Option for Your Iron Foundry Applications

Posted in Community, Partners, · August 15, 2012

Iron Foundry offers a host of database services that developers can leverage when building Iron Foundry applications. While there may not be a “wrong” choice (although in some cases, there are horribly inefficient choices!), how can we choose the best database for the situation at hand? Below, we look at each database option in Iron Foundry and why you would, or would not, select it for your application.

MongoDB

What is it?

MongoDB is a popular open-source NoSQL database. While called a “document database”, MongoDB does not store unstructured documents like PDFs or Microsoft Word files, but rather, stores its data as JSON-like files. Each document represents a database record while a collection is similar to a database table. However, unlike traditional relational databases with a fixed schema, MongoDB collections have no such restriction and each document could have entirely different fields. Developers use the Mongo Query Language to find records in MongoDB collections. Through the use of drivers, MongoDB supports a wide variety of languages such as Ruby, Scala, C#, Node, Java and more. MongoDB supports a variety of features (such as replication and indexing) that you’d typically find in RDMS offerings, but also offers unique capabilities such as auto-sharding and batch processing via MapReduce.

When to use it?

There’s actually an excellent page on the MongoDB site that outlines the core use cases and anti-patterns. They also have a useful list of customer stories that explain (sometimes with attachments!) how they are using MongoDB. Basically, MongoDB is a good choice for high performing apps that have complex data objects with a variable schema. Don’t choose MongoDB if you have intense cross-object transactions and heavy SQL coding requirements. While there has been some backlash against MongoDB in the past year, it remains crazy popular for its performance, approachability and lively ecosystem.

Microsoft SQL Server

What is it?

SQL Server is the flagship database product from Microsoft. It uses a relational structure and is made up of databases, tables and records. The tables in a SQL Server database have a fixed schema made up of typed columns. SQL Server has some of the capabilities that you expect to find in a traditional relational database: stored procedures, indexes, constraints and cross-object transactions. Data is queried through the rich T-SQL syntax that allows variables, a full set of operations for creating/reading/updating/deleting data, control flow statements and more. Starting with SQL Server 2005, developers can also write stored procedures and triggers based on the .NET language. While SQL Server uses a proprietary data streaming format (TDS), there are still many ways to access SQL Server data from non-Microsoft clients through ODBC. There are also many libraries to make this easier in Ruby, Node, and Java. While relational databases sometimes get a bad rap for poor scalability, many high volume web sites rely heavily on relational databases like SQL Server. SQL Server consistently ranks as a leader among relational database providers in “transactions per second” metrics as calculated by TPC.

When to use it?

If you have a relatively static data structure and have many objects with inter-relationships, SQL Server is a good choice. Likewise, if you already have an investment in SQL Server tools, then using the Iron Foundry version will be very straightforward. Developers can easily use the SQL Server Management Studio against Iron Foundry instances and treat the PaaS database just like one installed locally. Microsoft themselves tout use cases for internal business applications, high volume storage for web applications, and more.

MySQL

What is it?

MySQL, now owned by Oracle, claims to be the most popular open source database and is the “M'” in the ubiquitous LAMP (Linux/Apache/MySQL/PHP) web stack. It is a relational database that runs on multiple platforms including Linux, Windows, Mac OS, Solaris and IBM AIX. MySQL has a wide range of capabilities including support for stored procedures, triggers, database views, indexing, and ACID-compliant transactions. Something that’s unique about MySQL (vs other relational databases) is that it offers multiple storage engines that can be used for a given table. Some engines support data types that others do not, and some offer differently levels of support for transactions and indexing. There are engines provided natively, as well as engines created by partners and community members. Queries against MySQL data are performed use SQL that should familiar to anyone who has coded against databases in the past. MySQL offers a series of drivers for client connectivity and there are plenty of other libraries for technologies like Node and Ruby.

When to use it?

Some of the largest web properties in the world rely on MySQL for their data storage. Many of the same reasons that you would use Microsoft SQL Server apply here as well. Structured data, object relationships, and a need for transactions and indexing. Also, if you already have a skills investment in MySQL, then using MySQL for your Iron Foundry application is a no-brainer. The MySQL team has a very nice article on why you’d choose MySQL over Microsoft SQL Server, and Microsoft has the same sort of article on their site. You won’t really go wrong with either choice!

Neo4j

What is it?

Neo4j is an open-source, NoSQL graph database. While many of us are familiar with the other types of NoSQL databases (document databases like MongoDB, or key-value stores like Redis), graph databases are a bit more obscure. Graph databases put a high priority on the relationships between objects and makes it easy to query these relationships. In fact, it’s often much easier than querying relationships in a relational database! The “things” in a graph database are called a node, and the “relationships” are called edges. The Neo4j database is Java-based and stores its data on disk. It’s designed to be very fast and also ACID transaction compliant. The database itself can be embedded within an application or deployed as a full-fledged standalone server. While the API places a premium on Java, developers can also access it through an HTTP/REST interface. Data queries are executed using the Cypher Query Language which is designed for efficient traversal of graphs.

When to use it?

This database will be a great choice for Iron Foundry applications that place a premium on relationships between schema-less data structures. That means that this will be a good fit for social networks, genome analysis, recommendation algorithms and more.

PostgreSQL

What is it?

PostgreSQL is an increasingly popular open-source object-relational database system. You can run PostgreSQL on nearly every operating system including Linux, UNIX, and Windows. It’s a very robust database that is ACID-compliant and has features like joins, views, triggers, stored procedures and native programming interfaces for popular languages/frameworks like Java, .NET, Python, Ruby, ODBC and more. PostgreSQL has a lot of sophisticated features such as GIST indexing, table inheritance, and a rules system. Queries against the structured tables are done using SQL and should be easy for any database programmer to use. An article about the rise of PostgreSQL generated a lengthy discussion on Hacker News where many proponents and detractors of the platform debated the merits and shortfalls of the platform. It’s a good read.

When to use it?

PostgreSQL has some prominent customers across industries. You’ll often find PostgreSQL compared to MySQL or SQL Server. In essence though, consider choosing PostgreSQL for your Iron Foundry application if you want to use an open-source database for a high-performing (web) application that uses SQL queries. It’s a great fit for complex scenarios that can take advantage of the differentiating features like inheritance and advanced query optimization.

Redis

What is it?

Redis is a fast, widely used open-source NoSQL database that uses a key-value store paradigm. Data is typically stored in-memory, although now users can optionally snapshot data to disk periodically. It runs on UNIX/Linux/MacOS (although non-production quality ports for Windows exist). Redis supports a handful of data types including strings, lists (of strings), sets (unordered collections of strings), sorted sets and hashes (which are useful for object representations). While optimized for individual key retrieval, database content can be filtered/searched through operations like ZRANGE, LRANGE, GETRANGE and more. Redis also has some cool features like content expiration, and a pub/sub engine that opens up a whole host of possibilities. There are a variety of data access libraries available for languages/frameworks like .NET, Java, Node, PHP and many more.

When to use it?

Use Redis with your Iron Foundry application when you want blazing speed and have a data set that can sit comfortably in RAM. You would need to be comfortable with the prospects of losing data in the event of a server failure. Many large organizations are using Redis, often to provide a high performing caching layer to their application. You’ll also see everything from storing session data to creating URL shorteners built with Redis.

BLOG