Database Tutorials

How to choose right database and storage for the application

Pinterest LinkedIn Tumblr

For decades, organizations have been using a traditional relational database and trying to fit everything there, whether it is key/value-based user session data, unstructured log data, or analytics data for a data warehouse. However, the truth is, the relational database is meant for transaction data, and it doesn’t work very well for other data types.

Similarly, for specific data needs, you should choose the right tool that can do the heavy lifting, and scale without compromising performance. Solution architects need to consider multiple factors while choosing the data storage to match the right technology. Here are the important ones:

  • Durability requirement: How should data be stored to prevent data corruption?
  • Data availability: Which data storage system should be available to deliver data?
  • Latency requirement: How fast should the data be available?
  • Data throughput: What is the data read and write need?
  • Data size: What is the data storage requirement?
  • Data load: How many concurrent users need to be supported?
  • Data integrity: How to maintain the accuracy and consistency of data?
  • Data queries: What will be the nature of queries

In the following table, you can see different types of data with examples and appropriate storage types to use. Technology decisions need to be made based on storage type, as shown here:

Data TypeData ExampleStorage TypeStorage Example
Transactional, structured schemaUser order data, financial transactionRelational databaseAmazon RDS, Oracle, MySQL,
PostgreSQL, MariaDB
Microsoft SQL Server
Key-value pair, semi-structured,
User session data, application log, review,
NoSQLAmazon DynamoDB, MongoDB,
Apache HBase, Apache Cassandra, Azure Tables,
AnalyticsSales data, Supply chain intelligence, Business flowData warehouseIBM Teradata, Netezza, Greenplum, Google Amazon Redshift, BigQuery
In-memoryUser home page data, common dashboardCacheRedis cache, Memcached Amazon ElastiCache,
ObjectImage, videoFile-basedSAN, Amazon S3, Azure Blob
Storage, Google Storage
BlockInstallable softwareBlock-basedNAS, Amazon EBS, Amazon EFS,
Azure Disk Storage
StreamingIoT sensor data, clickstream dataTemporary storage for
streaming data
Apache Kafka, Amazon Kinesis,
Spark Streaming, Apache Flink
ArchiveAny kind of dataArchive storageAmazon Glacier, magnetic tape storage, virtual tape library storage
Web storageStatic web contents such as images, videos,
HTML pages
CDNAmazon CloudFront, Akamai CDN, Azure CDN, Google CDN, Cloudflare
SearchProduct search, content searchSearch index store and
Amazon Elastic Search, Apache Solr, Apache Lucene
Data catalogTable metadata, data about dataMeta-data storeAWS Glue, Hive metastore, Informatica data catalog, Collibra data catalog
MonitoringSystem log, network log, audit logMonitor dashboard and alertSplunk, Amazon CloudWatch, SumoLogic, Loggly

As you can see in the preceding table, there are various properties of data, such as structured, semi-structured, unstructured, key-value pair, streaming, and so on. Choosing the right storage helps to improve not only the performance of the application but also its scalability. For example, you can store user session data in the NoSQL database, which will allow application servers to scale horizontally and maintain user sessions at the same time.

While choosing storage options, you need to consider the temperature of the data, which could be hot, warm, or cold:

  • For hot data, you are looking for sub-millisecond latency and required cache data storage. Some examples of hot data are stock trading and making product recommendations in runtime.
  • For warm data, such as financial statement preparation or product performance reporting, you can live with the right amount of latency, from seconds to minutes, and you should use a data warehouse or a relational database.
  • For cold data, such as storing 3 years of financial records for audit purposes, you can plan latency in hours, and store it in archive storage.

I'm a full stack developer. I have experiences with Java, Android, PHP, Python, C#, Web development...I hope website will help everyone can learn code within 24h and apply it in working easily.

Comments are closed.