While, there are a lot of decisions you can make in software development that you can easily change later on, the type of database storage is NOT one of them! In fact it’s arguably the hardest thing to undo. Data migration is risky even on the same type of data storage, it’s a magnitude riskier moving from one type to another. But many dev teams and architects make this decision without really appreciating both the advantages and limitations they’re tying themselves to. Here’s a quick guide comparing them, and give you guidance in choosing. TL;DR most major RDBMSes support all types in some form, except for graph/object-oriented, so choosing a major RDBMS is the safest choice.

I. Relational Databases (RDBMS)

The relational model is defined by its reliance on tabular data and a rigid, predefined schema. This structure is meant to enforce data integrity and data consistency.

• Key Characteristics: Data is structured with relationships established through foreign keys (FKs) .The pre-defined schema ensures consistency when querying data.FKs are crucial for the preservation of data integrity across the system.
• Performance & Trade-offs: Writes are impacted because every insert and update requires the system to update related indexes and foreign-key relationships.The model is generally inefficient for custom fields due to the fixed schema.

II. Document-Oriented (“NoSQL”)

Document databases manage data as semi-structured, hierarchical data structures, typically JSON-like (often optimized as a binary format like BSON or JSONB).

• Flexibility and Speed: There is no need to define a schema upfront, which is ideal for rapidly evolving data and gives greater flexibility.Inserts are fast because there is no requirement to update foreign keys, simplifying the write process.
• Disadvantages: In practice, the lack of schema discipline causes problems in querying due to inconsistent data structures.Transactions spanning multiple documents are slow.
• Examples: MongoDB, CouchDB, Google BigTable.

III. Columnar

This model stores data by columns instead of rows, making it an optimization powerhouse for analytical workloads.

• Analytical Optimization: The structure is more compact and requires fewer page fetches for queries, as all data for a column is stored contiguously. It is specifically optimized for analytics and reporting.
• Performance Trade-offs: The design is very poor for transactional operations (OLTP). This structure can be implemented as a plugin for existing RDBMSs.
• Example: Cassandra (Facebook), Apache Parquet files.

IV. Key-Value

Key-value stores are simple, in-memory key-value pairs designed for speed and are periodically persisted in case of crashes.

• Primary Use Case: They enable the fast retrieval of simple data and handle high-speed reads/writes efficiently.They are typically paired with an RDBMS for complex data or data that requires strict confirmation of being saved (the “source of truth”).
• System Role: The key-value store handles simple, fast data access.The RDBMS handles more complex data or data requiring durability, with data synced between the two systems.
• Examples: Redis, Memcached.

V. Object-Oriented and Graph DBs

These databases are optimized for complex data relationships and structures.

• Stores data in a model similar to the object-oriented programming paradigm, providing no object-relational impedance (meaning the cost of joins is low).
• Performs well for complex graph data, such as CAD, games, robotic control, engineering simulation, DNA sequencing, and fraud ring discovery. In some AI use cases, this model can provide more accurate data representation than simple vector data.
• Performance Limitations: They generally perform poorly for analytics because queries must traverse the object graph, which loads too much memory and results in expensive aggregation. They are not suited for most business applications due to excessive memory use.

VI. Modern RDBMS Support Multiple Models

The latest generation of major RDBMSs recognizes the value of these diverse models and has evolved to support a multi-model approach.

• Integrated Non-Relational Support: Most major RDBMSs today support multiple storage models, including document-oriented capabilities out-of-the-box. This allows queries to run across both relational and non-relational data. The benefits include support for custom fields while retaining schema discipline and data integrity.
• Hybrid Analytics: Columnar support is usually available as plugins for RDBMSs.It‘s a best practice for the Analytics DB/data warehouse to be in a separate instance to prevent impact on the transactional database.
• In-Memory Caching: In-memory tables (such as PostgreSQL’s unlogged tables) can serve as efficient caches directly within the database.

I’m CEO of Orange & Bronze Software Labs. We provides software development, training, and advisory services. Contact [email protected] if you would like to learn more about us.