I have an application (written in Go) with a lot of GPS location information. Each record consists of a latitude and longitude plus somewhere around 50 fields of various pieces of meta data, some of it is small nested data structures.
The full data set is well over 1 billion records and growing steadily. They are kept in sharded RDBMS servers now, but we are looking at building a storage service that is better designed to deal with this information.
Information is accessed by an identifier indicating its source, and then by time. Every access pattern we can think of follows this same idea - source_id followed by time range. We are not trying to solve aggregation across different sources or other analytics problems, mainly just how to rapidly store and retrieve information by source and time range. Most reads are for recent time ranges, but occasionally data from months or even years back is requested and reasonable response time is desired.
Recent information can be corrected in some cases, so while historical data becomes read-only after a while, recent data may be updated frequently if a source sends in new or missing info.
We also want to have the ability to perform some additional calculations on this data as it is stored. For example, if a source sends in a certain value for a field, we might want to keep a separate record of each time this field changes. The volume is far less than the full set of data, and sometimes querying this instead of the raw data is useful.
With that background, I'm trying to determine how to best store this information on disk and/or what tools would be the best fit. In an ideal world we would have a format that is:
- Indexed according to source_id, timestamp (binary search seems appropriate)
- Partitioned into meaningful pieces so we can for example retire or backup old portions by month
- Human readable (and ideally editable)
- Accessible via supported tools (particularly if we can't meet the human readable requirement above)
Options considered with pros and cons:
- Just use an RDBMS like MySQL and store the data as fields or JSON as needed. Pros: Simple, existing tooling. Cons: a lot of performance is wasted on unneeded network I/O, data copying. The data is sharded so a set of sources all have their information on one server managed by one process. It's unclear as of this writing how significant this performance difference is, I plan on doing more performance testing.
- Use SQLite. Can be managed in much the same way as MySQL but avoids network overhead, does have some decent tooling.
- Use BoltDB or other single-file database system, possibly with a more compact data format like msgpack or protobuf. Pros: Faster and uses techniques like memory mapping to avoid copying, no network overhead. Cons: Tools to navigate boltdb are sparse and not well supported - we'd need to write most or all of our tools. BoltDB also seems like a poor archival format, with some wasted space internally, and not directly accessible to other programs.
- Use BoltDB or other database for recent data that requires change, but retire old data to a text format like JSON files, possibly with some separate indexing to make them more navigable. Tooling would still need to be written, but at least data files are more usable by other tools. Text files also compress well and can be stream decompressed if the data is in sequence (which it will be).
I'm tempted to use the last option, but it's a fair amount of work and feels like I'm inventing everything. It's hard to decide if it's worth the effort.
Is there some other format I should be considering for this information? Any suggestions or comments on the above data?
