Here is the straight dope, one of internal teams at my customer site
is looking into MySql and replacing its storage engine so that they
can store large amount of streamed data. The key here is that the data
they are getting is several thousands of rows in an extremely short
duration. They say that only MySql provides them the ability to
replace the storage engine, which granted is easier.
If I go with the statement that postgres can basically do what they
intend to do for handling large datasets, I need to prepare my talking
points.
The requirements are as follows:
1. Large amount of streamed rows. In the order of @50-100k rows per
second. I was thinking that the rows can be stored into a file and the
file then copied into a temp table using copy and then appending those
rows to the master table. And then dropping and recreating the index
very lazily [during the first query hit or something like that]
The table size can grow extremely large. Of course, if it can be
partitioned, either by range or list.
2. Most of the streamed rows are very similar. Think syslog rows,
where for most cases only the timestamp changes. Of course, if the
data can be compressed, it will result in improved savings in terms of
disk size.
The key issue here is that the ultimate data usage is Write Once Read
Many, and in that sense I am looking for a very optimal solution for
bulk writes and maintaining indexes during bulk writes.
So with some intelligent design, it is possible to use postgres. Any
help in preparing my talking points is appreciated.
Regards
Dhaval