- September 9, 2021
- By Nick Whitt
- 3 min read
Making Decisions With Your Database
Instead of asking “what database is best for my application”, make sure you are instead asking “which store best supports this data type”?
COMMON DILEMMAS
Introduction
In short
- Relational databases like MySQL and PostgreSQL are row-oriented and support ACID transactions, but switching between vendors offers no noticeable performance benefit.
- Key-value stores like Redis provide lower latency and higher throughput for sessions, caches, and real-time data by operating fully in memory.
- Object storage like S3 handles files and multimedia better than databases and can serve as a central data lake for analytics frameworks.
All too often, we receive questions of the following nature when it comes to how to manage your data:
- What database is best for my application?
- Can I get better performance by switching to a NoSQL database?
- How should I store files in my database?
Unfortunately, there are no simple answers; each needs to be considered on the basis of the application’s function and overall use within the organization. However, there are some general elements to consider.
RDBMS
Relational Data Stores
The most common data store we encounter are relational, or transactional, databases; such as MySQL, PostgreSQL, SQL Server, or Oracle. These systems are typically row-oriented data stores, highly normalized for referential integrity, supporting ACID-compliant transactions. The database is typically paired with the underlying web framework, comprising the model component of an MVC application. Though each database vendor offers unique features, switching from one to another won’t have a noticeable benefit.
Web frameworks are designed for model performance, as most will need at a minimum some level of user credentials (authentication) and permissions (authorization). However, many other elements of an application, while functional within a relational data store, are better served by another system.
IN-MEMORY SYSTEMS
Key-Value Data Stores
Most web applications store session states for subsequent requests; a key-value store such as Memcached or Redis could be used as the session data store. Compared to relational data stores, key-value data stores provide much lower latency with higher throughput, as they are implemented fully in-memory. In addition to sessions, a key-value store could be used as a data store for cached values, such as expensive query results or site configuration. Other simple state-based data, i.e. shopping carts or user preferences, as well as real-time data will benefit from key-value data stores. Due to the nature of in-memory systems, persistence and availability should be a factor in the decision to implement a key-value store in an application.
NOSQL OPTIONS
Document Data Stores
Sometimes referred to as “NoSQL” or “nonrelational", document databases store native JSON objects in semi-structured models. Document stores such as MongoDB, Elasticsearch, or DynamoDB allow for structurally independent objects to be stored alongside each other. Assume you have a CMS site with complex content needs. A document store could be used to support your content catalog, where each document (i.e. row) in the catalog (i.e. table) could have varying attributes (i.e. columns). Searching and aggregating data in a document store is much easier than maintaining complex joins, and horizontal scaling is much easier with document databases than relational data stores.
FILE SYSTEMS
Object Storage
In addition to data attributes, applications might need to store files, i.e. static assets, user uploads, or even multimedia. These various objects, while possible to store in a database, are better served from object data stores as name-value pairs. The most common object storage we encounter is S3, but many compatible services exist from other cloud hosting providers. Compared to traditional file systems, object storage is more flexible in size and format but less consistent; i.e. data read after an update are eventually consistent.
Object storage can also be used for any type of data, from structured (csv) and semi-structured (JSON) data to unstructured data dumps of any system. This allows for a central repository of data, also known as a data lake, independent of structure or content. Big data frameworks such as Spark, Presto, and Hive can natively integrate into the data storage, allowing for analytic consumption and visualization of the data store.
RIGHT TOOL
Use Cases
Of course, you are not limited to only one data store for your application. Your architecture should allow for multiple data use cases and include the appropriate store for each. As the old adage goes, “If you only have a hammer, everything starts to look like a nail.” Instead of asking “what database is best for my application”, make sure you are instead asking “which store best supports this data type”?
There are many other database types beyond the common ones listed here, including data warehouse stores, time series (IoT), graph databases, and ledger (blockchain) databases. Each has a unique benefit for a specific dataset, desired workload, or use case. Be sure to explore all the options, or contact us for a free 1/2 day assessment.
KEEP READING
Related Resources
Case Study
Automating 30,000 Daily Records for a Regional Bank
An AWS Lambda and Python pipeline now runs the transfer daily with no manual work, delivered in just under four months.
Read the regional bank case study
Case Study
Medical Record Extraction for a Healthcare Technology Company
Amazon Bedrock models and AWS Lambda now extract the data into a standard format, with less manual work and more accurate, timely reports.
Read the healthcare technology case study
Case Study
A GenAI Knowledge Base for a Building Materials Manufacturer
Employees ask questions in plain language and get answers almost instantly, from a knowledge base built on Amazon Bedrock and OpenSearch.
Read the building materials case study
