Saving temporary data to DB
I am storing temporary temperature data in a database which is actually just CSV data. The first column is the time in seconds, starting from zero, while (one or more) columns (columns) is the temperature:
0,197.5,202.4
1,196.0,201.5
2,194.0,206.5
3,192.0,208.1 ....etc
Each plot represents about 2000 seconds. I am currently compressing the data before storing it in the field output_profile longtext
.
CREATE TABLE `outputprofiles` (
`id` int(11) NOT NULL auto_increment,
`output_profile` longtext NOT NULL,
PRIMARY KEY (`id`)
It helps a lot ... I can compress the plot, which is 10K of plain text, down to about 2.5K. This data does not need to be searched or indexed as it is simply referenced in another table.
My question is, is there any other way to store this data? I am not thinking about which is more efficient in terms of storage space?
a source to share
Actually I don't quite understand what you mean by "plot compression". So you are compressing 2000 dimensions or compressing each row?
Space is cheap anyway. I would do it the traditional way, i.e. Two columns, one entry for each measurement.
If for some reason this doesn't work, and if you want to save 2000 measurements as one record, you can do it much better.
... Create a csv file with your measurements. , zip it (gzip -9 gives maximum compression), save it as blob (or longblob depending on the database you are using) NOT as longtext
Then just save it to the DB.
This will give you maximum compression.
a source to share
PostgreSQL has a lot of storage overhead because each tuple (a preview of a row in a table) is 28 bytes excluding data (PostgreSQL 8.3). There are integers 2, 4 and 8 bytes, and the timestamp is 8 bytes. I think the float is 8 bytes. Thus, PostgreSQL will require several additional GiB stores than MySQL to store 1,000,000,000 rows (depending on which storage you want to use in MySQL). But PostgreSQL is great at handling huge data compared to MySQL. Try running some DDL queries against a huge MySQL table and you will see what I mean. But this simple data you store should probably be easily broken down into large chunks, so a small simple MySQL can do the job just fine. But as I always say,if you're really not sure if you need a specific MySQL function, you should go to PostgreSQL.
I am limiting this post to MySQL and PostgreSQL only, as this question is only tagged with these two databases.
Edit: Sorry, I didn't see that you are actually saving the CSV to the DB.
a source to share