1

I've been pondering on alternative solutions to speed up data retrieval from my DB. I currently have a relatively large database, about 1 billion rows and growing. We expect the DB to grow linearly with about 1.5 billion rows (300GB) per years. My DB is PostGreSQL on FreeBSD.

The database contains history sensor data which simply consists of a timestamp, a numeric value and 1 foreign key to a sensor. The data will never be updated, only written and is not accessed very often.

With this amount of data is it's not possible to keep everything in memory, but I still want fast data retrieval if possible. We're currently lookup at almost 6ms per table row lookup time because it's so far spread out over the harddisk that every lookup has maximum seek time.

I've been doing some small scale tests with storing data on disk and this seem a LOT faster. We're looking at 1.8s for a day from the DB versus 0.2ms retrieving it from disk (including value conversions). This is almost 10.000x faster.


Which leads to my question: Is there any particular reason not to store the history data as plain files on disk if I don't care about foreign key consistency?

3
  • What are your typical retrieval queries? Mar 5, 2015 at 9:09
  • Anything from days to months. You can assume about 100 entries per day per sensor. We usually on retrieve data for 1 sensor at a time
    – Exelian
    Mar 5, 2015 at 9:12
  • It's possible to go much faster than the DB with your own custom structure on disk but if you don't detail that structure and the queries you're doing on it, it's unanswerable. Mar 5, 2015 at 14:20

1 Answer 1

0

I think you've answered your own question.

If there's no need for foreign key consistency and you are only ever reading back this data then the difference in search times makes a compelling argument for storing the history data as plain text rather than in a database.

1
  • I was expecting as much, but just wondering whether there we're thing I might've overlooked in my overeager enthousiasm. I keep the question open for a bit longer to see if someone else wants the chime in.
    – Exelian
    Mar 5, 2015 at 11:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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