What to Consider Before Building a Large-Scale Time-Series Database
How Is Storage Optimized in a Time-Series Database?
We can examine storage optimization from two perspectives: the time required to process data and the space required to store it.
To reduce ingestion and retrieval time, techniques include buffered I/O, storage layouts with strong data locality, and caching.
Buffered I/O improves ingestion speed by collecting data in memory by partition and then writing it to files.
Machbase tag tables improve query speed by keeping related data together, so a query loads primarily the data it needs. A composite index based on time and tag supports this layout.
Frequently queried data can also be cached in memory. This avoids expensive disk I/O and improves response times.
To reduce storage requirements, data is commonly compressed before it is written to files.
Compressing whole rows without considering data types can reduce compression efficiency. It can also force queries to load unwanted columns, creating unnecessary I/O.
Column-oriented storage helps address these problems.
Compression algorithms can be chosen for each data type, improving overall compression. Queries load only the required columns into memory, reducing disk I/O and memory consumption.
How Do You Relieve Database Load?
Use EXPLAIN to inspect information about query execution, resource usage, and timing. Analyze the output to identify bottlenecks and choose improvements.
Possible improvements include tuning the query, tuning database settings, tuning operating-system settings, adding hardware resources, and improving the DBMS software itself.
Which Database Fits Which Workload?
Finance and economics: exchange-rate and stock-market analysis
Financial workloads need to be considered from both short-term and long-term perspectives.
When information arrives continuously and must be analyzed immediately, an in-memory database can be useful because of its low-latency processing capabilities.
For longer-term trends, large volumes must be retained in persistent storage and analyzed efficiently. Big-data platforms, including SQL-on-Hadoop systems, are one approach to this workload.
Environmental, government, and welfare services: analyzing relationships
Relational DBMSs are useful when relationships among records matter. As volumes grow, hybrid designs increasingly combine relational databases for relationship-oriented queries with column-oriented databases for efficient storage and aggregation.
One approach is to keep relatively small amounts of descriptive metadata in a relational DBMS, while storing the high-volume observations in a column-oriented system.
Social networking: managing large volumes of diverse data
Social-networking platforms help users communicate, share information, and build social relationships.
Graph theory represents data as nodes and edges. Users can be modeled as nodes and their relationships as edges, making a graph structure a natural fit for connected social data.
Manufacturing: analyzing data over time
Manufacturing is well suited to time-series databases. Automation produces growing amounts of data that require both real-time queries and analysis of longer-term trends across time windows.
Conventional relational, Hadoop, NoSQL, or real-time database designs may require substantial hardware for these workloads, increasing deployment cost.
A time-series database is designed for this type of data and can deliver strong performance with fewer hardware resources, enabling a stable system at lower cost.
How Are New Query Features Selected?
For example, when a customer requests a new feature, how do you decide whether and how to support it?
We actively incorporate customer requirements when they fit the SQL specification and the existing architecture.
Small changes that can be completed quickly are registered in the issue system and may be included in the next patch. Larger features become projects, are reviewed in roadmap meetings, and are planned for a future release.
Future how-to articles will introduce features developed in response to customer requests, including:
Using sequence columns in Machbase
PIVOT
Machbase uses the following development techniques to achieve high performance.
First, the core is written in C.
C programs can produce compact executables and run directly on the operating system without a virtual machine. They can also call many operating-system APIs directly, supporting efficient low-level implementation.
Developers can explicitly control memory allocation and release, which helps optimize memory use.
Languages such as Java and Python make development easier through automatic memory management, but the timing of reclamation is controlled differently. C gives the engine more direct control over when memory is released.
Second, the implementation uses techniques that improve concurrency.
Locks are commonly used to protect critical sections during concurrent operations.
Although locks prevent data corruption, contention and synchronization can also reduce performance.
Machbase has improved performance by applying lock-avoidance techniques and lock-free algorithms where appropriate.
Third, Machbase uses a memory manager optimized for its own workloads.
This helps minimize memory fragmentation and supports flexible allocation and release according to usage patterns.
Fourth, storage structures are optimized for the way data is used.
Finally, Machbase provides asynchronous interfaces beyond the standard ODBC specification to maximize ingestion speed.
See the manuals below for details of Machbase’s interfaces.
