7 ms·

What would HN suggest to store about 1GB of data per day, mostly for archiving and offline analysis, with less than 10 columns including timestamp? We're curren

What would HN suggest to store about 1GB of data per day, mostly for archiving and offline analysis, with less than 10 columns including timestamp? We're currently writing everything to our postgres DB and flushing the table to S3 every few days but it's killing the app performance under high loads. I'm looking for something that is easy to set up and keep running with low to no maintenance.

kvz 10y ago
One idea might be to write to S3 directly and use Athena for your offline analysis
- cocoflunchy 10y ago
  Thanks for Athena, I hadn't heard about it!
weego 10y ago
If you want low to no maintenance and maintain some of what you have I'd suggest pushing it into kinesis instead of postgres and then dumping to s3 as you're already doing.
- throwaway2016a 10y ago
  Second this. This use case is exactly what Kinesis was built for in the first place. Additionally, Dynamodb can be decent for this as long as you only need to sort on time.
- cocoflunchy 10y ago
  Kinesis looks pretty good but it's yet another (proprietary) product to integrate and maintain... I'd rather avoid that if I can for now, we're a very small team and the fewer tools we have to use the better.
pdq 10y ago
CSV Same way most web servers log traffic.
- ju-st 10y ago
  Considering the use case ("archiving and offline analysis") this is likely the best choice. If you need some buzzwords or want to get fancy, try HDFS
- IanCal 10y ago
  Strongly recommend this. Your disks are not going to struggle with 1G/day, it's easy to setup & manage, easy to move data around and easy to process later. Worst case is it's quick to try and see if it suits.
- Mahn 10y ago
  Or perhaps SQLite, since it should perform similar to writing to a CSV file and you get querying out of the box afterwards.
  - saosebastiao 10y ago
    SQLite is a major powerhouse for stuff like this, and if your analysis is on a single computer with a single disk, analysis is almost always faster than any sort of highly parallel big data setup. I've loaded, analyzed, and reported on 2TB of data using SQLite in less time than it took for an 8 machine spark cluster took to load the data.
storyinmemo 10y ago
Can you elaborate on what it is that slows down your app performance? Is it bottle-necking on writing to the database? Does the database have read activity? Does the application slow down when there is read activity? With some basic assumptions on my part including that you can have a delay in writing data to the database (since it's archival and analysis), but you don't want the application to be delayed, putting a fast queue like Kafka or RabbitMQ in place could help. It'll buffer when the database is under load, isolating your application from that. Offline analysis might also skip the database entirely: columnar data formats (parquet) or log structured merge / sorted string tables (rocksdb) could work very well for archival or offline analysis in MR type environments.
- cocoflunchy 10y ago
  Yes, we only have one DB for everything and it is bottle-necking on writes. Queuing the writes is probably the easiest solution since we already have RabbitMQ setup... thanks for your answer.
  - saosebastiao 10y ago
    There are a lot of good suggestions here, but if this is the route you're going down and you are still using the same database, do everything you can to batch your inserts. In your queue consumer, aggregate as much as you can tolerate in memory, and then insert them all and commit the inserts in a single transaction. This eliminates a huge amount of MVCC overhead.
robochat 10y ago
You could try influxdb.
jdwyah 10y ago
Kinesis firehose to S3 and then query with Athena is pretty great. I've been very happy with the combo.
gaius 10y ago
What would HN suggest to store about 1GB of data per day, 1Gb per day is not a lot - that's 12k/sec which is trivial. Your Postgres must be badly misconfigured!
jeffrand 10y ago
We used InfluxDB along with a few other tools. Check out: https://www.influxdata.com/time-series-platform/ https://www.influxdata.com/time-series-platform/
mirekrusin 10y ago
If you don't have strong guarantees on that table (usually with high volume comes less requirements, ie. you can loose few rows here and there and it's not the end of the world) then you may want to try unlogged table (new versions of postgres do support it). Also make sure you're using BRIN index (if any) on that timestamp column.
tyingq 10y ago
I think the answer depends on what tool you're using for offline analysis. Reducing the number of intermediate copies and formats would be desirable, right? Start with that, and then find some asynchronous way (to avoid blocking your app) and dump the data straight to that format.
user5994461 10y ago
Given the "archive and offline analysis" requirements, which are special characteristics. The "hacked" way is to go CSV files, store them on s3. It is very limited but it is also very simple. (Not sure what software can run queries on CSV files directly). The "doing things right" way is to go for AWS RedShift or Google BigQuery. There is a learning curve at the start but it's really REALLY good and it will pay off by many folds later.
- patrec 10y ago
  sqlite can directly process csv
agacera 10y ago
If you are already invested in aws I suggest you to use the firehose + s3 to store data and use the Athena to query it.
zo1 10y ago
Don't write it to your main transactional database, then. Have a separate connection to a DB on another machine that is handling the load. Do that within the transaction/request from the client, or pop it into a message queue. The queue will "persist" the data, but will also schedule it for when your other box/DB can ingest it. At the end of the day, if you're pushing too-much data, you need to have a mechanism in place to let you scale. To me, a simple solution would be a message queue server with plenty of redundant storage space. From there on, you keep adding more consumers.
- GordonS 10y ago
  If the message queue becomes a bottleneck, buffering/batching can also help - instead of sending individual messages to the queue, send them in batches (based on the number, time period, or both).
vegabook 10y ago
teafiles
rodionos 10y ago
Try Axibase Time Series Database. We support SQL which should work well for analytics use cases. SQL docs: https://github.com/axibase/atsd-docs/tree/master/api/sql https://github.com/axibase/atsd-docs/tree/master/api/sql Analytics examples: https://github.com/axibase/atsd-use-cases https://github.com/axibase/atsd-use-cases

kvz 10y ago

One idea might be to write to S3 directly and use Athena for your offline analysis

cocoflunchy 10y ago
Thanks for Athena, I hadn't heard about it!

weego 10y ago

If you want low to no maintenance and maintain some of what you have I'd suggest pushing it into kinesis instead of postgres and then dumping to s3 as you're already doing.

throwaway2016a 10y ago
Second this. This use case is exactly what Kinesis was built for in the first place. Additionally, Dynamodb can be decent for this as long as you only need to sort on time.
cocoflunchy 10y ago
Kinesis looks pretty good but it's yet another (proprietary) product to integrate and maintain... I'd rather avoid that if I can for now, we're a very small team and the fewer tools we have to use the better.

pdq 10y ago

CSV Same way most web servers log traffic.

ju-st 10y ago
Considering the use case ("archiving and offline analysis") this is likely the best choice. If you need some buzzwords or want to get fancy, try HDFS
IanCal 10y ago
Strongly recommend this. Your disks are not going to struggle with 1G/day, it's easy to setup & manage, easy to move data around and easy to process later. Worst case is it's quick to try and see if it suits.
Mahn 10y ago
Or perhaps SQLite, since it should perform similar to writing to a CSV file and you get querying out of the box afterwards.
- saosebastiao 10y ago
  SQLite is a major powerhouse for stuff like this, and if your analysis is on a single computer with a single disk, analysis is almost always faster than any sort of highly parallel big data setup. I've loaded, analyzed, and reported on 2TB of data using SQLite in less time than it took for an 8 machine spark cluster took to load the data.

storyinmemo 10y ago

Can you elaborate on what it is that slows down your app performance? Is it bottle-necking on writing to the database? Does the database have read activity? Does the application slow down when there is read activity? With some basic assumptions on my part including that you can have a delay in writing data to the database (since it's archival and analysis), but you don't want the application to be delayed, putting a fast queue like Kafka or RabbitMQ in place could help. It'll buffer when the database is under load, isolating your application from that. Offline analysis might also skip the database entirely: columnar data formats (parquet) or log structured merge / sorted string tables (rocksdb) could work very well for archival or offline analysis in MR type environments.

cocoflunchy 10y ago
Yes, we only have one DB for everything and it is bottle-necking on writes. Queuing the writes is probably the easiest solution since we already have RabbitMQ setup... thanks for your answer.
- saosebastiao 10y ago
  There are a lot of good suggestions here, but if this is the route you're going down and you are still using the same database, do everything you can to batch your inserts. In your queue consumer, aggregate as much as you can tolerate in memory, and then insert them all and commit the inserts in a single transaction. This eliminates a huge amount of MVCC overhead.

robochat 10y ago

You could try influxdb.

jdwyah 10y ago

Kinesis firehose to S3 and then query with Athena is pretty great. I've been very happy with the combo.

gaius 10y ago

What would HN suggest to store about 1GB of data per day, 1Gb per day is not a lot - that's 12k/sec which is trivial. Your Postgres must be badly misconfigured!

jeffrand 10y ago

We used InfluxDB along with a few other tools. Check out: https://www.influxdata.com/time-series-platform/ https://www.influxdata.com/time-series-platform/

mirekrusin 10y ago

If you don't have strong guarantees on that table (usually with high volume comes less requirements, ie. you can loose few rows here and there and it's not the end of the world) then you may want to try unlogged table (new versions of postgres do support it). Also make sure you're using BRIN index (if any) on that timestamp column.

tyingq 10y ago

I think the answer depends on what tool you're using for offline analysis. Reducing the number of intermediate copies and formats would be desirable, right? Start with that, and then find some asynchronous way (to avoid blocking your app) and dump the data straight to that format.

user5994461 10y ago

Given the "archive and offline analysis" requirements, which are special characteristics. The "hacked" way is to go CSV files, store them on s3. It is very limited but it is also very simple. (Not sure what software can run queries on CSV files directly). The "doing things right" way is to go for AWS RedShift or Google BigQuery. There is a learning curve at the start but it's really REALLY good and it will pay off by many folds later.

patrec 10y ago
sqlite can directly process csv

agacera 10y ago

If you are already invested in aws I suggest you to use the firehose + s3 to store data and use the Athena to query it.

zo1 10y ago

Don't write it to your main transactional database, then. Have a separate connection to a DB on another machine that is handling the load. Do that within the transaction/request from the client, or pop it into a message queue. The queue will "persist" the data, but will also schedule it for when your other box/DB can ingest it. At the end of the day, if you're pushing too-much data, you need to have a mechanism in place to let you scale. To me, a simple solution would be a message queue server with plenty of redundant storage space. From there on, you keep adding more consumers.

GordonS 10y ago
If the message queue becomes a bottleneck, buffering/batching can also help - instead of sending individual messages to the queue, send them in batches (based on the number, time period, or both).

vegabook 10y ago

teafiles

rodionos 10y ago

Try Axibase Time Series Database. We support SQL which should work well for analytics use cases. SQL docs: https://github.com/axibase/atsd-docs/tree/master/api/sql https://github.com/axibase/atsd-docs/tree/master/api/sql Analytics examples: https://github.com/axibase/atsd-use-cases https://github.com/axibase/atsd-use-cases