0

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?

  • I don't think you can use network overhead as a reason to not use a db like MySQL or Postgres when SQLite is inherently serverless and all read/writes must go to the disk. However, most applications would be fine with just using SQLite, and you can easily swap to another DB down the road if you decide you need it. – Jesse Sep 18 at 20:30
  • @Jesse Makes sense on SQLite. However, if all of the data for a set of sources is on a single machine which is able to serve all requests, it is my belief that the overhead of SQL plus encoding/decoding the data to and from the database engine could be a signifcant performance cost. Especially compared to storage solutions like Bolt that can do memory-mapped operations. – Brad Peabody Sep 18 at 21:14
1

Having efficient access is never going to correspond with being human editable. With that said, it sounds like to me your use case requirement here exactly matches what sqlite is built for: fast, well supported, single file database. SQLite database isn't readable in a plain text editor, but there are a lot of very good GUI SQLite tools. You can create a btree index in sqlite to speed up your access.

If you want to be able to retain human editable format, then you can write your data in a CSV format, then load your data into an in-memory sqlite database at startup. You'll of course have to pay a very heavy cost of data load and index creation in the program startup time, but access afterwards will be fast.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.