I need to aggregate incoming data and save the resulting aggregates to a table. A sample of the data is below.
id,time 1967,2014-04-12 00:42:55+00 1967,2014-04-12 00:42:51+00 1972,2014-04-12 00:42:51+00 1972,2014-04-12 00:42:49+00 1972,2014-04-12 00:42:40+00
I need to do a simple aggregation:
SELECT id, MIN(time),MAX(time),COUNT(id) FROM xx GROUP BY id
I've tried a few things:
Directly updating a table each time a piece of data comes in. Was too slow of course.
Saving the incoming data to a temporary table and periodically running an aggregation query similar to the one above and then saving the result to a table. This approach was better but proved too much for my disks as volume increased.
My current approach is a daemon program that reads incoming data, aggregates it in memory and then saves the results to a table based on some time criteria. This approach works much better but still cannot scale as much as I'd like.
I am using Postgres 9.1 on a 4-disk RAID 10 system with 15k SCSI drives and 32gb of RAM. There is no disk partition, WAL and everything is on one disk. I realize this is a large performance hit. Hardware changes are problematic at this point. This system cannot keep up with what I need to do. At the moment I need to write thousands of these aggregates per second and the solution I'll choose has to scale to much more.
I have been exploring distributed database options and I like the ideas behind Cassandra. However I am not up to speed on all the options as I'm primarily a software developer. Moving to a distributed database in the cloud seems like a good option for a next step. Being able to sort by the min,max and count columns would be ideal but I can do that in the application layer if I have to.
Another important note is that the result of these data aggregations are actually rarely needed and are only for human consumption. A tiny fraction of these will actually be viewed. I tried generating the aggregations realtime (by querying a table that has the raw data) but they ended up with sequential scans on large tables (100m+ rows). If aggregating this much data and having most of it not seen is nonsensical I'd be happy to hear of a way to generate them upon request. My SQL skills are basic it is possible I am missing techniques that will help.
Here's some relevant parts of my postgres config:
shared_buffers = 22000MB temp_buffers = 256MB work_mem = 896MB maintenance_work_mem = 16MB fsync = off wal_buffers = 64MB checkpoint_segments = 128 checkpoint_timeout = 60min checkpoint_completion_target = 0.9 random_page_cost = 2.0 effective_cache_size = 8192MB default_statistics_target = 200 autovacuum_max_workers = 6 autovacuum_naptime = 15s autovacuum_vacuum_threshold = 25 autovacuum_analyze_threshold = 10 autovacuum_vacuum_scale_factor = 0.1 autovacuum_analyze_scale_factor = 0.05 autovacuum_vacuum_cost_delay = 10ms autovacuum_vacuum_cost_limit = 1000
So my questions:
How is my postgres config for these tasks? What kind of limits for this task am I looking at with my current hardware? Are there distributed databases well suited to my needs?