Evaluating databases for sensor data

Posted by Richy George on 18 March, 2024

This post was originally published on this site

The world has become “sensor-fied.”

Sensors on everything, including cars, factory machinery, turbine engines, and spacecraft, continuously collect data that developers leverage to optimize efficiency and power AI systems. So, it’s no surprise that time series—the type of data these sensors collect—is one of the fastest-growing categories of databases over the past five-plus years.

However, relational databases remain, by far, the most-used type of databases. Vector databases have also seen a surge in usage thanks to the rise of generative AI and large language models (LLMs). With so many options available to organizations, how do they select the right database to serve their business needs?

Here, we’ll examine what makes databases perform differently, key design factors to look for, and when developers should use specialized databases for their apps.

Understanding trade-offs to maximize database performance

At the outset, it’s important to understand that there is no one-size-fits-all formula that guarantees database superiority. Choosing a database entails carefully balancing trade-offs based on specific requirements and use cases. Understanding their pros and cons is crucial. An excellent starting point for developers is to explore the CAP theorem, which explains the trade-offs between consistency, availability, and partition tolerance.

For example, the emergence of NoSQL databases generated significant buzz around scalability, but that scalability often came at the expense of surrendering guarantees in data consistency offered by traditional relational databases.

Some design considerations that significantly impact database performance include:

• Storage format: The organization and storage format of data on hard drives heavily influences performance. With a rapidly increasing number of businesses storing vast volumes of data for analytical workloads, the adoption of column-based formats like Apache Parquet is on the rise.
• Data compression: The choice of compression algorithms directly impacts storage costs and query performance. Some algorithms prioritize minimizing data size, while others prioritize faster decompression, improving query performance.
• Index data structure: The indexing mechanism used by a database is pivotal for peak performance. While primary indexes aid the storage engine, secondary, user-defined indexes enhance read performance, although these could also introduce additional overhead for writing new data.
• Hot vs. cold storage: Modern database systems facilitate data movement between faster, more expensive, “hot” storage and slower, cheaper, “cold” storage. This tiered approach optimizes performance for frequently accessed data while economizing storage costs for data used less often.
• Disaster recovery: The disaster recovery mechanisms present in a database architecture inherently influence performance. While robust disaster recovery features enhance data security, they could also introduce performance overhead. For use cases that are not mission-critical, databases can trade certain safety guarantees for improved performance.

These and other factors collectively shape database performance. Strategically manipulating these variables allows teams to tailor databases to meet the organization’s specific performance requirements. Sacrificing certain features becomes viable for a given scenario, creating finely-tuned performance optimization.

Key specialty database considerations

Selecting the appropriate database for your application involves weighing several critical factors. There are three major considerations that developers should keep in mind when making a decision.

Tendencies in data access

The primary determinant in choosing a database is understanding how an application’s data will be accessed and utilized. A good place to begin is by classifying workloads as online analytical processing (OLAP) or online transaction processing (OLTP). OLTP workloads, traditionally handled by relational databases, involve processing large numbers of transactions by large numbers of concurrent users. OLAP workloads are focused on analytics and have distinct access patterns compared to OLTP workloads. In addition, whereas OLTP databases work with rows, OLAP queries often involve selective column access for calculations. Data warehouses commonly leverage column-oriented databases for their performance advantages.

The next step is considering factors such as query latency requirements and data write frequency. For near-real-time query needs, particularly for tasks like monitoring, organizations might consider time series databases designed for high write throughput and low-latency query capabilities.

Alternatively, for OLTP workloads, the best choice is typically between relational databases and document databases, depending on the requirements of the data model. Teams should evaluate whether they need the schema flexibility of NoSQL document databases or prefer the consistency guarantees of relational databases.

Finally, a crucial consideration is assessing if a workload exhibits consistent or highly active patterns throughout the day. In this scenario, it’s often best to opt for databases that offer scalable hardware solutions to accommodate fluctuating workloads without incurring downtime or unnecessary hardware costs.

Existing tribal knowledge

Another consideration when selecting a database is the internal team’s existing expertise. Evaluate whether the benefits of adopting a specialized database justify investing in educating and training the team and whether potential productivity losses will appear during the learning phase. If performance optimization isn’t critical, using the database your team is most familiar with may suffice. However, for performance-critical applications, embracing a new database may be worthwhile despite initial challenges and hiccups.

Architectural sophistication

Maintaining architectural simplicity in software design is always a goal. The benefits of a specialized database should outweigh the additional complexity introduced by integrating a new database component into the system. Adding a new database for a subset of data should be justified by significant and tangible performance gains, especially if the primary database already meets most other requirements.

By carefully evaluating these factors, developers can make educated and informed decisions when selecting a database that aligns with their application’s requirements, team expertise, and architectural considerations, ultimately optimizing performance and efficiency in their software solutions.

Optimizing for IoT applications

IoT environments have distinct characteristics and demands for deploying databases. Specifically, IoT deployments need to ensure seamless operation at both the edge and in the cloud. Here is an overview of database requirements in these two critical contexts.

Requirements for edge servers

The edge is where data is locally generated and processed before transmission to the cloud. For this, databases must handle data ingestion, processing, and analytics at a highly efficient level, which requires two things:

• High ingest rate: Edge servers must support rapid write capabilities for the huge data streams produced by IoT sensors without loss, even while experiencing latency. Similarly, databases need to handle data bursts while maintaining real-time ingestion to prevent data loss.
• Fast reads and analytics: Databases at the edge also require quick read capabilities and analytical tools. Local data processing enables real-time decision-making, which is streamlined by databases with built-in analytics functionalities to transform, classify, and aggregate sensor data.

Requirements for cloud data centers

In cloud data centers, databases play a crucial role in collecting, transforming, and analyzing data aggregated from edge servers. Key requirements include:

• Analysis commands: Database management systems should incorporate built-in analysis commands to streamline data processing and analysis, minimizing operational complexity and overhead.
• Downsampling and retention policies: Implementing downsampling techniques and retention policies helps efficiently manage historical data. Downsampling ensures high-precision data is retained for short durations, while less precise data is stored to capture longer-term trends. Automated data retention policies facilitate timely data deletion, optimizing storage utilization.
• Visualization engine: A robust visualization engine is crucial for monitoring the IoT system’s state. It can provide insights into system performance, helping teams make informed decisions based on real-time data visualization.
• Publish and subscribe mechanism: An efficient publish and subscribe capability allows for seamless communication and data exchange between edge devices and the cloud, ensuring data integrity and timely updates.

Because the database landscape evolves swiftly, developers must stay informed about the latest trends and technologies. While sticking to familiar databases is reliable, exploring specialized options can offer advantages that include cost savings, enhanced user performance, scalability, and improved developer efficiency.

Ultimately, balancing the organization’s business requirements, storage needs, internal knowledge, and (as always) budget constraints gives teams the best chance for long-term success.

Anais Dotis-Georgiou is lead developer advocate at InfluxData.

—

New Tech Forum provides a venue for technology leaders—including vendors and other outside contributors—to explore and discuss emerging enterprise technology in unprecedented depth and breadth. The selection is subjective, based on our pick of the technologies we believe to be important and of greatest interest to InfoWorld readers. InfoWorld does not accept marketing collateral for publication and reserves the right to edit all contributed content. Send all inquiries to doug_dineley@foundryco.com.