HN Explorer

Ask HN: What's the best way to store time series data in a relational database?

10 points by thewarrior ↗ HN
We are currently having a write heavy load with about a half a million records being added every day. This could grow in future , so having one record for every minute with an index would slow down the writes.

Is it ok to have one record per hour with sixty columns for each minute ?

Or is it better to use a non relational database ?

14 comments

[ 2.8 ms ] story [ 37.4 ms ] thread
bjourne
> Is it ok to have one record per hour with sixty columns for each minute ?

No. That's wasteful and makes many types of queries very hard to write.

A good way of storing the data is only storing change events. So for example, each time a stock ticker changes you store a new row with the timestamp and the new value for the stock. This achieves very high level of "compression" since most stocks values doesn't change every minute. But it depends on what kind of data you have.

thewarrior
We have start and stop events for thousands of machines in order to measure their per hour / per minute utilizations .
mandeepj
> since most stocks values doesn't change every minute.

Are you serious? Stock data changes every second and sometimes earlier than that.

bjourne
No, most stock prices doesn't change that frequently.
kasey_junk
You've mentioned a few things about your write side (but didn't specify the critically important what you want to store, and what resolution your time series needs? You hinted at it only needs minute resolution?)

Further, you didnt mention at all your read requirements. Without that any recommendation you could possibly get would be useless.

thewarrior
We're storing floats at one minute resolution . We could have thousands of writes a second at peak load . Reads are not that heavy for now .
kasey_junk
The read volume is much less important than the kinds of queries you want to run on the data set. But from the sounds of it you do not have a SQL problem, the data doesn't have/need varying query styles/indexes, it probably degrades in importance quickly over time, and it isn't interrelated. Use one of the umpteenth time series databases that were written for this problem.

That said, any decently tuned RDBMS/sql app can easily handle thousands of writes a second, especially if you are just dumping them into giant buckets of resolution.

fsk
Your index wouldn't be clustered, is it? For a regular index, insert is O(log n). For a clustered index, insert is O(n).

You can also bulk insert records, rather than 1 at a time.

We have 2k operations per second on our aws rds.

pjungwir
Definitely agree re bulk inserting, say every 1 minute or 1 hour or whatever.

I was paying around recently with Amazon's Kinesis for this, but if you don't want the vendor lock-in you could easily roll your own system that bundles up events and stores a bunch at once.

fsk
If you do

open database connection

do one insert

close database connection

That adds up fast.

At work, I've been very disappointed in the performance of their Amazon RDS. They had unexplained master/slave replication issues.

You also can put a memcached in front of the database.