I need to be able to say:
* Lookup the _distance_ for the planet _mercury_ on the date _1900-01-01_
On the face of it, a relational database is best for that kind of query. However, if you won't get any fancier than that, you can get by with MLDBM or something similar.
Currently i do this using a postgres database, however, my question is,
is there a quicker way to do this in mod_perl - would a DB_File or some
other structure be better?
A DBM file will be faster. What you can do is build a key out of planet + date, so that you grab the right record with a single access. Either use MLDBM for storing hashes inside each record, or just a simple join/split approach.
MySQL would probably also be faster than PostgreSQL for this kind of simple read-only querying, but not as fast as a DBM file. SDBM_File is the fastest DBM around, if you can live with the space limitations it has.
perhaps something such as copying the whole 800,000 rows to
memory (as a hash?) on apache startup?
That would be the fastest by far, but it will use a boatload of RAM. It's pretty easy to try, so test it and see if you can spare the RAM it requires.
- Perrin