The growth-over-time issue is a complicated one, which PG at the moment won't help you with.
The general problem leads to the notion of "partioning" data. Oracle supports this (but dumbly, at least when Philip Greenspun wrote about it, not sure about 9i etc but Oracle has been paying more attention to the webspace and not the long-term accumulation of data space per se so I doubt much has changed).
PG doesn't support partioning at all.
For the near term, adding RAM and making sure that queries grow non-linearly (in the postitive logN(n) sense, not the negative n^2 sense!) is probably the best defence.
photo.net has never need to resort to partioning (either in-db or by other hacks) and that's a busier site than most of us will ever spawn, so take heart ...
