CREATE TABLE t { t_id BIGSERIAL NOT NULL, year SMALLINT, yday, SMALLINT, a INT, b INT, c REAL, d INT, e REAL, f REAL, point_id INT );
These are daily values per year, and they repeat for each point on land. So, for one point I have one row for each day of the year for 20 years, for the next point I have one row for each day of the year for 20 years, and so on.
The expected table size is going to be in 100 billion rows plus.
A typical search would be to retrieve all the values of a <variable> for or for a particular year+yday combination. In other words, besides the automatic INDEX on t_id, the PRIMARY KEY, there will be INDEXes on year, yday, and point_id. The INDEX on year, yday might be a composite INDEX because they will be searched together.
Anyway, as I said, 100 billion+ rows totaling about 10 TB without the extra INDEXes (that is, the INDEXes other than the PK).
Any suggestions if there are any better ways to do this? Should I try to save space by converting the REALs into INTs? Or, who cares about space... disks are cheap.
Should I combine the different values into a single blob using some kind of data serialization technique and then store them in a single column? To be sure, that sounds like a silly idea to begin with, because I would have to extract the individual variables out of that blob on every query.
Should I partition the table into smaller tables using the technique discussed at http://http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html, or is it too much trouble as Pg will handle this data very well anyway.
Once the db is built, it will be relatively static. In other words, it will be readonly.
Fishing for ideas here.
In reply to Strategy for managing a very large database with Perl
by punkish
when small people start casting long shadows, it is time to go to bed
For:
Use:
& & < < > > [ [ ] ]